在同一个 MySQL 表中复制/复制记录

我已经找了一段时间了，但我找不到一个简单的办法来解决我的问题。我希望在表中复制一条记录，但是当然，惟一的主键需要更新。

我有个疑问:

INSERT INTO invoices
SELECT * FROM invoices AS iv WHERE iv.ID=XXXXX
ON DUPLICATE KEY UPDATE ID = (SELECT MAX(ID)+1 FROM invoices)

问题是，这只是更改了行的 ID，而不是复制行。有人知道怎么修吗？

//edit: 我希望在执行此操作时不要键入所有字段名，因为字段名可能随时间变化。

137303 次浏览
小开

You KNOW for sure, that the DUPLICATE KEY will trigger, thus you can select the MAX(ID)+1 beforehand:

INSERT INTO invoices SELECT MAX(ID)+1, ... other fields ... FROM invoices AS iv WHERE iv.ID=XXXXX
小开

I needed this as well; my solution was to use SQLYOG (free version) to export the desired record as SQL (creates an insert).

I then hand edited this to remove the id as this needs to be auto-generated and then copied the insert into SQLYog to execute it. This was painless. I guess plenty of other MySQL GUIs can do this as well.

This provides me with a record I can use for test purposes on a live system.

I now have this insert for reuse as well, as the table is rewritten daily.

小开
最佳答案

The way that I usually go about it is using a temporary table. It's probably not computationally efficient but it seems to work ok! Here i am duplicating record 99 in its entirety, creating record 100.

CREATE TEMPORARY TABLE tmp SELECT * FROM invoices WHERE id = 99;


UPDATE tmp SET id=100 WHERE id = 99;


INSERT INTO invoices SELECT * FROM tmp WHERE id = 100;

Hope that works ok for you!

小开

Alex's answer needs some care (e.g. locking or a transaction) in multi-client environments.

Assuming the AUTO ID field is the first one in the table (a usual case), we can make use of implicit transactions.

CREATE TEMPORARY TABLE tmp SELECT * from invoices WHERE ...;
ALTER TABLE tmp drop ID; # drop autoincrement field
# UPDATE tmp SET ...; # just needed to change other unique keys
INSERT INTO invoices SELECT 0,tmp.* FROM tmp;
DROP TABLE tmp;

From the MySQL docs:

Using AUTO_INCREMENT: You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

小开

I have a similar issue, and this is what I'm doing:

insert into Preguntas  (`EncuestaID`, `Tipo` , `Seccion` , `RespuestaID` , `Texto` )  select '23', `Tipo`, `Seccion`, `RespuestaID`, `Texto` from Preguntas where `EncuestaID`= 18

Been Preguntas:

CREATE TABLE IF NOT EXISTS `Preguntas` (
`ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`EncuestaID` int(11) DEFAULT NULL,
`Tipo` char(5) COLLATE utf8_unicode_ci DEFAULT NULL,
`Seccion` int(11) DEFAULT NULL,
`RespuestaID` bigint(11) DEFAULT NULL,
`Texto` text COLLATE utf8_unicode_ci ,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=522 ;

So, the ID is automatically incremented and also I'm using a fixed value ('23') for EncuestaID.

小开

Your approach is good but the problem is that you use "*" instead enlisting fields names. If you put all the columns names excep primary key your script will work like charm on one or many records.

INSERT INTO invoices (iv.field_name, iv.field_name,iv.field_name
) SELECT iv.field_name, iv.field_name,iv.field_name FROM invoices AS iv
WHERE iv.ID=XXXXX
小开

Slight variation, main difference being to set the primary key field ("varname") to null, which produces a warning but works. By setting the primary key to null, the auto-increment works when inserting the record in the last statement.

This code also cleans up previous attempts, and can be run more than once without problems:

DELETE FROM `tbl` WHERE varname="primary key value for new record";
DROP TABLE tmp;
CREATE TEMPORARY TABLE tmp SELECT * FROM `tbl` WHERE varname="primary key value for old record";
UPDATE tmp SET varname=NULL;
INSERT INTO `tbl` SELECT * FROM tmp;
小开

I just wanted to extend Alex's great answer to make it appropriate if you happen to want to duplicate an entire set of records:

SET @x=7;
CREATE TEMPORARY TABLE tmp SELECT * FROM invoices;
UPDATE tmp SET id=id+@x;
INSERT INTO invoices SELECT * FROM tmp;

I just had to do this and found Alex's answer a perfect jumping off point!. Of course, you have to set @x to the highest row number in the table (I'm sure you could grab that with a query). This is only useful in this very specific situation, so be careful using it when you don't wish to duplicate all rows. Adjust the math as necessary.

小开

A late answer I know, but it still a common question, I would like to add another answer that It worked for me, with only using a single line insert into statement, and I think it is straightforward, without creating any new table (since it could be an issue with CREATE TEMPORARY TABLE permissions):

INSERT INTO invoices (col_1, col_2, col_3, ... etc)
SELECT
t.col_1,
t.col_2,
t.col_3,
...
t.updated_date,
FROM invoices t;

The solution is working for AUTO_INCREMENT id column, otherwise, you can add ID column as well to statement: 

INSERT INTO invoices (ID, col_1, col_2, col_3, ... etc)
SELECT
MAX(ID)+1,
t.col_1,
t.col_2,
t.col_3,
... etc ,
FROM invoices t;

It is really easy and straightforward, you can update anything else in a single line without any second update statement for later, (ex: update a title column with extra text or replacing a string with another), also you can be specific with what exactly you want to duplicate, if all then it is, if some, you can do so.

小开

You can alter the temporarily table to change the ID field to a bigint or so without the NOT NULL requirement, then set the ID to 0 in that temp table. After that add it back to the original table and the NULL will trigger the auto increment.

CREATE TEMPORARY TABLE tmptable SELECT * FROM x WHERE (id='123');
ALTER TABLE tmptable CHANGE id id bigint;
UPDATE tmptable SET id = NULL;
INSERT INTO x SELECT * FROM tmptable;

提交答案