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

Duplicate / Copy records in the same MySQL table

提问by Digits

I have been looking for a while now but I can not find an easy solution for my problem. I would like to duplicate a record in a table, but of course, the unique primary key needs to be updated.

我一直在寻找一段时间，但我找不到解决我的问题的简单方法。我想复制一个表中的记录，但当然，唯一的主键需要更新。

I have this query:

我有这个查询：

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

the problem is that this just changes the IDof the row instead of copying the row. Does anybody know how to fix this ?

问题是这只是改变ID了行而不是复制行。有人知道如何解决这个问题吗？

//edit: I would like to do this without typing all the field names because the field names can change over time.

//edit：我想在不键入所有字段名称的情况下执行此操作，因为字段名称会随着时间的推移而改变。

回答by Alex

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.

我通常采用的方法是使用临时表。它的计算效率可能不高，但似乎可以正常工作！在这里，我完整地复制了记录 99，创建了记录 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!

希望这对你有用！

回答by Tim Ruehsen

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

Alex 的回答在多客户端环境中需要一些注意（例如锁定或事务）。

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

假设该AUTO ID字段是表中的第一个字段（通常情况下），我们可以使用隐式事务。

    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:

来自 MySQL 文档：

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

使用 AUTO_INCREMENT：您还可以为列显式分配 NULL 或 0 以生成序列号。

回答by Ingo

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

您肯定知道，DUPLICATE KEY 会触发，因此您可以预先选择 MAX(ID)+1：

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

回答by Perfect Square

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

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

回答by Al-Mothafar

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 the single line insert intostatement, and I think it is straight forward, without creating any new table (since it could be an issue with CREATE TEMPORARY TABLEpermissions):

我知道一个迟到的答案，但它仍然是一个常见问题，我想添加另一个对我有用的答案，只使用单行insert into语句，我认为它很简单，没有创建任何新表（因为它可能是CREATE TEMPORARY TABLE权限问题）：

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_INCREMENTid column, otherwise, you can add IDcolumn as well to statement:

该解决方案适用于AUTO_INCREMENTid 列，否则，您也可以将ID列添加到语句中：

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 straight forward, 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.

这真的很简单直接，您可以在一行中更新任何其他内容，而无需任何第二个更新语句供以后使用（例如：用额外的文本更新标题列或用另一个替换字符串），您也可以具体说明什么正是您想要复制，如果全部是，如果有，您可以这样做。

回答by bcsteeve

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.

我只需要这样做，发现亚历克斯的答案是一个完美的起点！。当然，您必须将@x 设置为表中的最高行号（我相信您可以通过查询获得它）。这仅在这种非常特殊的情况下有用，因此当您不想复制所有行时请小心使用它。根据需要调整数学。

回答by Alex Angelico

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 IDis automatically incremented and also I'm using a fixed value ('23') for EncuestaID.

因此，ID会自动递增，而且我对EncuestaID.

回答by zzapper

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

我也需要这个；我的解决方案是使用 SQLYOG（免费版）将所需记录导出为 SQL（创建插入）。

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.

然后我手动编辑它以删除 id，因为这需要自动生成，然后将插入复制到 SQLYog 中以执行它。这是无痛的。我想很多其他 MySQL GUI 也可以做到这一点。

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.

我现在也有这个插入内容以供重用，因为表格每天都被重写。

回答by Nik Dow

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.

略有变化，主要区别在于将主键字段（“varname”）设置为 null，这会产生警告但有效。通过将主键设置为 null，在最后一条语句中插入记录时，自动增量工作。

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;