在MySQL中，我能否复制一行并将其插入到同一张表中？

Question

insert into table select * from table where primarykey=1

我想复制表中的一行数据并将其插入到同一张表里（即，我想要复制该表中的一个现有行），但我不想在“select”之后列出所有列，因为这个表有太多的列。

但是，当我尝试这样做时，我收到了以下错误：

重复条目'xxx'，键为1

我可以通过创建另一个与原表具有相同列的临时容器来复制所需记录以避开这个问题：

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;

有没有更简单的方法来解决这个问题？

- lina

---

我只是对于硬编码的键值有些评论。我会采用类似于max(oldtable.id) + oldtable_temp.key的方式，这样我可以确保id递增且唯一。 - guy mograbi

可能是 在同一张 MySQL 表中复制记录 的重复问题。 - Organic Advocate

@OrganicAdvocate，这个问题有更多的答案和更多的浏览量，比那个问题要多。 - Drew

是的，不要使用update oldtable_tem set key=2;，而是使用update oldtable_tem set key=NULL;，然后简单地使用insert into oldtable select * from oldtable_tem;。 - Timofey Bugaevsky

Answer 1

我采用了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自己选择值，因此不会创建重复项。

如果您想确保只插入一行，可以在INSERT INTO语句的末尾添加LIMIT 1

请注意，我还将主键值（在本例中为1）附加到了临时表名称后面。

- Grim...

---

这个解决方案是正确的（与其他一些得到赞同的解决方案相比），因为它允许MySQL选择主键值。 - Jan Hettich

主键如何为空？ - Imran Shafqat

如果它为空，插入时会自动分配下一个人工智能编号。 - Grim...

如果表的模式不允许在主键中使用NULL，则将其设置为0而不是NULL。 - Yzmir Ramirez

IMRAN - 你很有理由这样问。我认为临时表的 ID 字段可能没有自动设置为主键（我相当确定在这种情况下 SQL Server 不会这样做），但它确实是主键。我需要在第一行之后执行 ALTER TABLE tmptable_1 MODIFY primarykey INT NULL; 才能使解决方案生效。 - DJDave

Answer 2

更新于2014年7月7日 - 根据我下面的回答，Grim...给出的答案是更好的解决方案，因为它改进了我的解决方案，因此我建议使用那个。

您可以使用以下语法而无需列出所有列：

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;

您可以决定以其他方式更改主键。

- LeonardChallis

---

这基本上是与原帖提供的解决方案相同，只不过使用了“真实”的临时表，并采用稍微更干净的创建模式。我认为原帖作者要求的是一种“更好的方法”，而不是清理其现有语法。我真的不太明白为什么会有那么多人为此点赞。 - Sepster

谢谢。动态函数进展顺利。 - Sonal Khunt

这个过程完成后是否有必要删除临时表？ - SSH This

默认情况下，当您的数据库连接终止时，MySQL会删除所有临时表。如果仍然希望在此期间删除它们，则可以通过发出DROP TABLE命令来实现。更多信息请参见 - LeonardChallis

假设您已经创建了 tmptable，则最后一行不需要再添加 WHERE primarykey = 2 语句。（即只需使用 INSERT INTO table SELECT * FROM tmptable。） - Marcus

Answer 3

我猜你想让新记录有一个新的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的新值。

- Jordan Running

---

为什么这个回答没有回答问题却得到了7个赞？*...但我不想在“select”后面列出所有的列，因为这个表有太多的列…* - LeonardChallis

-1，因为这恰好是OP想要避免的。请阅读问题，或者至少给出像“这是唯一可能的方法”这样的答案。 - devios1

因为有时候问题本身就是错误的。:) 不改变内容的情况下复制行会增加冗余。如果可能的话，建议更改数据库结构以最小化冗余。 - Torben

即使按照正常惯例做法行不通，也并不意味着是错误的。许多时候标准最佳实践并不适用。例如：数据库代表文档，用户需要在进行更改之前保存文档的副本。 - ima747

这个问题并没有错。假如你已经存储了一些复杂的数据结构，并花费了时间来配置它们，但是用户现在想要复制它，因为她想调整一些细节使其符合当前的需求，同时又想保留旧版本以备历史记录之用，该怎么办呢？ - s.m.

因为您可能不想与原帖完全相同，所以您可能会着陆在这个页面上。 - jx12345

Answer 4

你的第一个查询很接近正确，你只需要指定列名，这样你可以在插入数据时排除主键，从而启用表上可能存在的自动增量功能来自动生成新的主键值。

比如将这段代码改成：

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 查询的列列表中不包括 primarykey 列。

- Braeden Black

---

有没有一种方法可以通过select语句提取大部分列，但在insert命令中手动更新其中一个列？例如 insert into table ("val1", col2, col3) select col2, col3 from table where primarykey = 1 或类似的语句？ - fIwJlxSzApHEZIl

找到了！复制一行的值，但在插入语句中添加自己的一个值：https://dev59.com/CYDba4cB1Zd3GeqPBTYC - fIwJlxSzApHEZIl

抱歉刚刚收到你的问题通知，很高兴你找到了答案，那确实是正确的方法。 - Braeden Black

Answer 5

您还可以尝试将该表转储，查找插入命令并对其进行编辑：

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

--skip-extended-insert参数会为每一行生成一个插入命令。您可以在喜欢的文本编辑器中查找该行，提取命令并将主键更改为“default”。

- donothingsuccessfully

---

我给你点赞，因为这是一个很好的“创新”方法，在某些情况下可能会在运行时之外有所帮助。但主要是第一个回答实际上解答了问题并没有仅仅语法上调整已经由OP提供的解决方案。 - Sepster

Answer 6

这可以通过一些创意实现：

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。

- curmil

Answer 7

本程序假定:

• 您没有"_duplicate_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;

- Piotr Kaczmarek

---

+1 还没有确认这个 works，但方法是可行的，并且具有价值，因为它在这里是独一无二的，确实回答了 OP 的问题。 - Sepster

Answer 8

如果您的表的主键字段是自增字段，那么您可以使用带列的查询。例如，您的表名为 test_tbl，有三个字段 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=19 的重复行插入为 id=20。

- user2595171

---

那是最好的答案，情况非常简单明了： INSERT INTO tableName (fieldName1, fieldName2, fieldName3) SELECT fieldName1, fieldName2, fieldName3 FROM tableName WHERE 1（一次性复制所有内容） - jakubplus

赞同 - 这个答案简洁明了，符合实际“复制”一行的精神。 - user37309

Answer 9

我使用了Grim的技巧，并做了一点修改：如果有人寻找这个查询，是因为由于主键问题无法执行简单的查询：

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

使用我的 MySql 安装 5.6.26 版本时，键不能为 null，会产生错误：

#1048 - Column 'primakey' cannot 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;

- Luca Camillo

---

在新版本的MariaDB/MySQL中，另外一种方法是将primaryKey设为0，这会使得自动增量插入时能够正常工作。 - shelbypereira

谢谢，这真的为我解决了一个 InnoDB 表的问题！ - Alex Kogan

Answer 10

克隆带有更新字段和自增值的行

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`;

- Shrinivas Mahindrakar

---

@Torben，请在问题左侧勾选标记（将其标记为已接受），这样每个人都将知道它对您起作用了。 - HosseyNJF