在 MySQL 中，我可以复制一行插入到同一个表中吗？

insert into table select * from table where primarykey=1

我只想复制一行插入到同一个表中(也就是说，我想复制表中现有的一行) ，但是我不想在“ select”之后列出所有的列，因为这个表有太多的列。

但是当我这样做的时候，我得到了一个错误:

键1的重复条目‘ xxx’

我可以通过为要复制的记录创建另一个具有相同列的表作为临时容器来处理这个问题:

create table oldtable_temp like oldtable;
insert into oldtable_temp select * from oldtable where key=1;
update oldtable_tem set key=2;
insert into oldtable select * from oldtable where key=2;

有更简单的解决方法吗？

263656 次浏览
小开

我假设你希望新的记录有一个新的 primarykey? 如果 primarykey是 AUTO_INCREMENT，那么只要这样做:

INSERT INTO table (col1, col2, col3, ...)
SELECT col1, col2, col3, ... FROM table
WHERE primarykey = 1

... 其中 col1, col2, col3, ...是表 除了 primarykey中的所有列。

如果它不是一个 AUTO_INCREMENT列，并且你想要能够为 primarykey选择新的值，它是相似的:

INSERT INTO table (primarykey, col2, col3, ...)
SELECT 567, col2, col3, ... FROM table
WHERE primarykey = 1

... 其中 567是 primarykey的新值。

小开

更新07/07/2014-基于我的答案，通过 格里姆。，是一个更好的解决方案，因为它改进了我的解决方案，所以我建议使用它。

您可以这样做，而无需使用以下语法列出所有列:

CREATE TEMPORARY TABLE tmptable SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable SET primarykey = 2 WHERE primarykey = 1;
INSERT INTO table SELECT * FROM tmptable WHERE primarykey = 2;

您可以决定以另一种方式更改主键。

小开

您还可以尝试转储表，找到 insert 命令并编辑它:

mysqldump -umyuser -p mydatabase --skip-extended-insert mytable > outfile.sql

--skip-extended-insert为每行提供一个插入命令。然后，您可以在您喜欢的文本编辑器中找到该行，提取命令并将主键改为“ default”。

小开

本程序假定:

  • 您没有 _ copy _ temp _ table
  • 您的主键是 int
  • 您可以创建表

当然，这并不完美，但在某些情况下(可能大多数情况下) ，它会奏效。

DELIMITER $$
CREATE PROCEDURE DUPLICATE_ROW(copytable VARCHAR(255), primarykey VARCHAR(255), copyid INT, out newid INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @error=1;
SET @temptable = '_duplicate_temp_table';
SET @sql_text = CONCAT('CREATE TABLE ', @temptable, ' LIKE ', copytable);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql_text = CONCAT('INSERT INTO ', @temptable, ' SELECT * FROM ', copytable, ' where ', primarykey,'=', copyid);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql_text = CONCAT('SELECT max(', primarykey, ')+1 FROM ', copytable, ' INTO @newid');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql_text = CONCAT('UPDATE ', @temptable, ' SET ', primarykey, '=@newid');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql_text = CONCAT('INSERT INTO ', copytable, ' SELECT * FROM ', @temptable, '');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sql_text = CONCAT('DROP TABLE ', @temptable);
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SELECT @newid INTO newid;
END $$
DELIMITER ;


CALL DUPLICATE_ROW('table', 'primarykey', 1, @duplicate_id);
SELECT @duplicate_id;
小开

我用了 Leonard Challis 的技巧做了一些改动:

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;

作为一个临时表，永远不应该有多个记录，所以您不必担心主键。将其设置为 null 允许 MySQL 自己选择值，这样就不存在创建重复值的风险。

如果希望确保只有一行可以插入，可以将 LIMIT1添加到 INSERTINTO 行的末尾。

注意，我还将主键值(在本例中为1)附加到临时表名。

小开

下面的一些内容是从这个网站上搜集到的。下面是我在具有任意数量字段的表中复制记录时所做的操作:

这也假设您在表的开头有一个 AI 字段

function duplicateRow( $id = 1 ){
dbLink();//my db connection
$qColumnNames = mysql_query("SHOW COLUMNS FROM table") or die("mysql error");
$numColumns = mysql_num_rows($qColumnNames);


for ($x = 0;$x < $numColumns;$x++){
$colname[] = mysql_fetch_row($qColumnNames);
}


$sql = "SELECT * FROM table WHERE tableId = '$id'";
$row = mysql_fetch_row(mysql_query($sql));
$sql = "INSERT INTO table SET ";
for($i=1;$i<count($colname)-4;$i++){//i set to 1 to preclude the id field
//we set count($colname)-4 to avoid the last 4 fields (good for our implementation)
$sql .= "`".$colname[$i][0]."`  =  '".$row[$i]. "', ";
}
$sql .= " CreateTime = NOW()";// we need the new record to have a new timestamp
mysql_query($sql);
$sql = "SELECT MAX(tableId) FROM table";
$res = mysql_query($sql);
$row = mysql_fetch_row($res);
return $row[0];//gives the new ID from auto incrementing
}
小开

我不得不这样做，这是我的手动解决方案:

  1. 在 Phpmyadmin中，检查要复制的行
  2. 在查询结果操作的底部，单击 “出口”
  3. 在下一页选中 ‘另存为文件’，然后单击 “开始”
  4. 使用文本编辑器打开导出的文件，找到主字段的值并将其更改为惟一的值。
  5. 返回到 Phpmyadmin，单击 进口选项卡，找到要导入的文件。在 浏览下的 sql 文件中，单击 “开始”，应该插入重复的行。

如果你不知道 质点字段是什么，回头看看你的 Phpmyadmin页面，点击 「结构」选项卡，在 「索引」下面的页面底部，它会告诉你哪个 “场地”有一个 ‘ Keyname’值 “质点”。

有点长，但是如果您不想处理标记，只需要复制一行就可以了。

小开

很抱歉，这是我发现了与谷歌，因为我发现这个有用的，但有问题的，我想贡献一个重要的修改，为任何人谁挖掘了这一点。

首先，我使用的是 SQLServer，而不是 MySQL，但我认为它应该类似地工作。我使用了 Leonard Challis 的解决方案，因为它很简单，并且满足了需求，但是这里有一个问题——如果你只是简单地取 PK 并将其增加1，那么如果你已经添加了其他记录，因为问题行被添加了，会发生什么情况。我决定最好让系统来处理 PK 的自动递增，所以我做了以下事情:

SELECT * INTO #tmpTable FROM Table WHERE primarykey = 1
--Optionally you can modify one or more fields here like this:
--UPDATE #tmpTable SET somefield = newData
ALTER TABLE #tmpTable DROP COLUMN TicketUpdateID
INSERT INTO Tickets SELECT * FROM #tmpTable
DROP TABLE #tmpTable

我相信这在 MySQL 中也会有类似的效果，但是对不起，我不能测试这个

小开

我知道这是一个老问题，但这里有另一个解决方案:

假设主键是自动递增的，这将复制主表中的一行，并使用新的主表 ID 创建链表数据的副本。

获取列名的其他选项:
- 显示来自 tablename的列; (列名: 字段)
- 描述 tablename(列名: 字段)
- SELECT Column _ name FROM information _ schema. Column WHERE table _ name = ‘ tablename’(Column name: Column _ name) < br/> 

//First, copy main_table row
$ColumnHdr='';
$Query="SHOW COLUMNS FROM `main_table`;";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
while($Row=mysql_fetch_array($Result))
{
if($Row['Field']=='MainTableID')     //skip main table id in column list
continue;
$ColumnHdr.=",`" . $Row['Field'] . "`";
}
$Query="INSERT INTO `main_table` (" . substr($ColumnHdr,1) . ")
(SELECT " . substr($ColumnHdr,1) . " FROM `main_table`
WHERE `MainTableID`=" . $OldMainTableID . ");";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
$NewMainTableID=mysql_insert_id($link);


//Change the name (assumes a 30 char field)
$Query="UPDATE `main_table` SET `Title`=CONCAT(SUBSTRING(`Title`,1,25),' Copy') WHERE `MainTableID`=" . $NewMainTableID . ";";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);


//now copy in the linked tables
$TableArr=array("main_table_link1","main_table_link2","main_table_link3");
foreach($TableArr as $TableArrK=>$TableArrV)
{
$ColumnHdr='';
$Query="SHOW COLUMNS FROM `" . $TableArrV . "`;";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
while($Row=mysql_fetch_array($Result))
{
if($Row['Field']=='MainTableID')     //skip main table id in column list, re-added in query
continue;
if($Row['Field']=='dbID')    //skip auto-increment,primary key in linked table
continue;
$ColumnHdr.=",`" . $Row['Field'] . "`";
}


$Query="INSERT INTO `" . $TableArrV . "` (`MainTableID`," . substr($ColumnHdr,1) . ")
(SELECT " . $NewMainTableID . "," . substr($ColumnHdr,1) . " FROM `" . $TableArrV . "`
WHERE `MainTableID`=" . $OldMainTableID . ");";
$Result=Wrappedmysql_query($Query,$link,__FILE__,__LINE__);
}
小开

Max233肯定是在正确的轨道上，至少在自增量情况下是这样。 但是，不要执行 ALTERTABLE 这将显示一个错误，但是下面的 INSERT 临时表中的所有字段都将发生，并且 NULL auto 字段将获得 独一无二的价值。

小开

我只是想发布一段 PHP 代码，因为我认为我收集列的方法在代码中比前面的示例要干净一些。这还显示了如何轻松地更改字段，在本例中添加字符串。但是您也可以用新添加的记录替换一个外键字段，以防您也想复制一些子记录。

  // Read columns, unset the PK (always the first field in my case)
$stmt = $conn->prepare('SHOW COLUMNS FROM template');
$stmt->execute();


$columns = $stmt->fetchAll();
$columns = array_map(function ($element) { return $element['Field']; }, $columns);


unset($columns[0]);


// Insert record in the database. Add string COPY to the name field.
$sql = "INSERT INTO `template` (".implode(",", $columns).")";
if ($key = array_search('name', $columns))
$columns[$key] = "CONCAT(name, ' COPY')";
$sql .= " SELECT ".implode(",", $columns)." FROM `template` WHERE `id` = ".$id;


$stmt = $conn->prepare($sql);
$stmt->execute();
小开

我可能会迟到，但我有一个类似的解决方案，已经为我工作。

 INSERT INTO `orders` SELECT MAX(`order_id`)+1,`container_id`, `order_date`, `receive_date`, `timestamp` FROM `orders` WHERE `order_id` = 1

这样我就不需要创建临时表等等了。由于行是在同一个表中复制的，因此可以很容易地使用 Max(PK)+1函数。

我来寻找这个问题的解决方案(忘记了语法) ，最后我做了自己的查询。有时候事情就是这么有趣。

问候

小开

如果主键是自动增量，只需指定除主键以外的每个字段。

INSERT INTO 表(field1，field2，field3) SELECT (field1，field2，field3) FROM 表 WHERE primarykey = 1

小开

我更新了@LeonardChallis 的解决方案，因为它不像其他解决方案那样适合我。我删除了临时表中的 WHERE子句和 SET primaryKey = 0，这样 MySQL 就可以自动增加主键

CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable;
UPDATE tmptable SET primaryKey = 0;
INSERT INTO myTable SELECT * FROM tmptable;

这当然是为了复制表中的 所有行。

小开

这可以通过一些创造性来实现:

SET @sql = CONCAT('INSERT INTO <table> SELECT null,
', (SELECT GROUP_CONCAT(COLUMN_NAME)
FROM information_schema.columns
WHERE table_schema = '<database>'
AND table_name = '<table>'
AND column_name NOT IN ('id')), '
from <table> WHERE id = <id>');


PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

这将导致新行获得一个自动递增的 id，而不是来自选定行的 id。

小开

如果表的主键字段是自动递增字段，则可以对列使用 query。例如，名为 test_tbl的表有3个字段作为 id, name, age。id是一个主键字段和自动增量，因此您可以使用以下查询来复制该行:

INSERT INTO `test_tbl` (`name`,`age`) SELECT `name`,`age` FROM `test_tbl`;

此查询将导致复制每一行。

如果表的主键字段不是自动递增字段，那么可以使用以下方法:

INSERT INTO `test_tbl` (`id`,`name`,`age`)
SELECT 20,`name`,`age` FROM `test_tbl` WHERE id = 19;

这个查询的结果是作为 id=20插入的 id=19的重复行。

小开

我使用了格里姆的技巧，做了一点小小的改动: 如果有人在寻找这个查询，是因为主键问题导致无法执行简单的查询:

INSERT INTO table SELECT * FROM table WHERE primakey=1;

在我的 MySql 安装版本5.6.26中，key 不能为空，并且会产生一个错误:

#1048 - Column 'primakey' cannot be null

因此，在创建临时表之后，我将主键更改为 be null。

CREATE TEMPORARY TABLE tmptable_1 SELECT * FROM table WHERE primarykey = 1;
ALTER TABLE tmptable_1 MODIFY primarykey int(12) null;
UPDATE tmptable_1 SET primarykey = NULL;
INSERT INTO table SELECT * FROM tmptable_1;
DROP TEMPORARY TABLE IF EXISTS tmptable_1;
小开

创建一个表

    CREATE TABLE `sample_table` (
`sample_id` INT(10) unsigned NOT NULL AUTO_INCREMENT,
`sample_name` VARCHAR(255) NOT NULL,
`sample_col_1` TINYINT(1) NOT NULL,
`sample_col_2` TINYINT(2) NOT NULL,


PRIMARY KEY (`sample_id`),
UNIQUE KEY `sample_id` (`sample_id`)


) ENGINE='InnoDB' DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

插入一行

INSERT INTO `sample_table`
VALUES(NULL, 'sample name', 1, 2);

上面的克隆行插入

INSERT INTO `sample_table`
SELECT
NULL AS `sample_id`, -- new AUTO_INCREMENT PRIMARY KEY from MySQL
'new dummy entry' AS `sample_name`,  -- new UNIQUE KEY from you
`sample_col_1`, -- col from old row
`sample_col_2` -- col from old row
FROM `sample_table`
WHERE `sample_id` = 1;

测试

SELECT * FROM `sample_table`;
小开

这是我在这个网站上找到的答案 描述如何执行上述 1 你可以在这页的底部找到答案。基本上，您要做的就是将要复制的行复制到内存中保存的临时表中。然后使用 update 更改主键编号。然后将其重新插入到目标表中。然后放下桌子。

这是它的代码:

创建临时表 rescueteam ENGINE = MEMORY SELECT * From Mfitnessreport4 WHERE rID = 1; # 1 row infections。 更新 rescueteam SET rID = Null WHERE rID = 1; # 1 row affected.INSERT 从 rescueteam进入 fitnessreport4选择 * ; # 1行受到影响。 DROP TABLE rescueteam # MySQL 返回一个空结果集(即0
行)。

我创建了临时桌子救援队。我从我的原始表健康报告中复制了这一行。然后，我将临时表中的行的主键设置为 null，这样就可以将其复制回原始表，而不会出现副本键错误。我昨天晚上试了这个代码，它起作用了。

小开

我会在下面使用,

insert into ORDER_ITEM select * from ORDER_ITEM where ITEM_NUMBER =123;
小开

在第一个查询中，你只需要指定列就可以了，这样你就可以在插入中排除主键，从而实现表上的自动增量，为条目自动创建一个新的主键。

例如，更改以下内容:

insert into table select * from table where primarykey=1

这样说:

INSERT INTO table (col1, col2, col3)
SELECT col1, col2, col3
FROM table
WHERE primarykey = 1

只是不要在 INSERT 或查询的 SELECT 部分的列列表中包含主键列。

小开

这个解决方案显示上述工程也选择行完美。例如，我正在为我的 nice2work 项目创建演示行，这个工作非常完美。

CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;
DROP TABLE tmptable;


//  You can use this same also directly into your code like (PHP Style)
$sql = "CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;DROP TABLE tmptable;";
小开

使用更新字段和自动递增值克隆行

CREATE TEMPORARY TABLE `temp` SELECT * FROM `testing` WHERE id = 14;


UPDATE `temp` SET id = (SELECT id FROM testing ORDER by id DESC LIMIT 1
)+1, user_id = 252 ,policy_no = "mysdddd12" where id = 14;


INSERT INTO `testing` SELECT * FROM `temp`;


DROP TEMPORARY TABLE IF EXISTS `temp`;
小开

我在 科哈数据库中用来在 条形码列中插入前缀为“ C”的重复项目:

INSERT INTO items (`biblionumber`, `biblioitemnumber`, `barcode`, `dateaccessioned` ) SELECT `biblionumber`, `biblioitemnumber`,  CONCAT('C',`barcode`), `dateaccessioned` FROM `items` WHERE barcode='14832';
小开

这是一个额外的解决方案的答案，由“格里姆...” 有一些关于它的主键为 null 的注释。 一些关于它不工作的评论。 以及对解决方案的一些评论。 没有一个解决方案对我们有效，我们有 MariaDB 和 InnoDB 表。

我们无法将主键设置为允许 null。 使用0而不是 NULL 会导致主键的重复值错误。 SET SQL_SAFE_UPDATES = 0;也不起作用。

如果我们将主键改为 UNIQUE，那么来自“ Grim...”的解决方案确实有效

小开

从上面的答案和其他关于 SO 的答案中，我得到了这个作为我克隆一个记录的最终代码:

CREATE TEMPORARY TABLE temptable SELECT * FROM things WHERE Thing_ID = 10000345;
UPDATE temptable SET Thing_ID = 0;
INSERT INTO things SELECT * FROM temptable;
DROP TEMPORARY TABLE IF EXISTS temptable;

将 PRIMARY 键的值 Thing _ ID 设置为 = 0比修改 temtable 以允许 NULL 并将 PRIMARY 键设置为 NULL 要短——对我来说，这在 MySQL 中是可行的。


提交答案