在同一MySQL表中复制/复制记录 [英] Duplicate / Copy records in the same MySQL table

问题描述

我已经寻找了一段时间，但找不到适合我问题的简单解决方案.我想在表中复制一条记录，但是当然，唯一的主键需要更新.

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.

我有这个查询:

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

问题在于，这仅更改了行的ID，而不是复制行.有人知道如何解决这个问题吗?

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

//edit:我想在不键入所有字段名称的情况下执行此操作，因为字段名称会随着时间变化.

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

推荐答案

我通常使用的方法是使用临时表.它可能在计算上效率不高，但看起来还可以！在这里，我要完全复制记录99，创建记录100.

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;

希望对您有用！

这篇关于在同一MySQL表中复制/复制记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！