在 MySQL 中，我可以复制一行插入同一个表吗? [英] In MySQL, can I copy one row to insert into the same table?

问题描述

insert into table select * from table where primarykey=1

我只想复制一行以插入同一个表中(即，我想复制表中的现有行)但我想这样做而不必在选择"之后列出所有列，因为这个表的列太多了.

I just want to copy one row to insert into the same table (i.e., I want to duplicate an existing row in the table) but I want to do this without having to list all the columns after the "select", because this table has too many columns.

但是当我这样做时，我得到了错误:

But when I do this, I get the error:

密钥 1 的重复条目 'xxx'

Duplicate entry 'xxx' for key 1

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

I can handle this by creating another table with the same columns as a temporary container for the record I want to copy:

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;

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

Is there a simpler way to solve this?

推荐答案

我使用了 Leonard Challis 的技术，并做了一些更改:

I used Leonard Challis's technique with a few changes:

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 自行选择值，因此不存在创建重复项的风险.

As a temp table, there should never be more than one record, so you don't have to worry about the primary key. Setting it to null allows MySQL to choose the value itself, so there's no risk of creating a duplicate.

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

If you want to be super-sure you're only getting one row to insert, you could add LIMIT 1 to the end of the INSERT INTO line.

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

Note that I also appended the primary key value (1 in this case) to my temporary table name.

这篇关于在 MySQL 中，我可以复制一行插入同一个表吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！