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

问题描述

  insert into table select * from table where primarykey = 1 
  

我只想复制一行插入到同一个表（即，我想复制表中的现有行），但我想这样做而不必列出所有的select后的列，因为这表中有太多列。

但是当我这样做时，会出现错误：

键1的重复条目'xxx'

我可以通过创建另一个具有相同列我想要复制的记录的容器：

 创建表oldtable_temp像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;有没有更简单的方法来解决这个问题？
 
 

$ b < h2_lin>解决方案

我使用Leonard Challis的技术进行了一些改动：

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

作为临时表，应该不会有多个记录，因此您不必担心主键。将它设置为null允许MySQL自己选择值，所以没有创建重复的风险。

如果你想超级肯定你只得到一个要插入的行，可以在INSERT INTO行的末尾添加LIMIT 1。

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

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:

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?

解决方案

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;

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.

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.

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

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