在 MySQL 中，我可以复制一行插入到同一个表中吗?
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.
如果您想确保只插入一行，可以将 LIMIT 1 添加到 INSERT INTO 行的末尾.
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.
相关文章