mysql - 如何在MySQL中使用自动增量字段复制一行并插入同一个表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9156340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy a row and insert in same table with a autoincrement field in MySQL?
提问by Navdroid
In MySQL I am trying to copy a row with an autoincrementcolumn ID=1and insertthe data into same table as a new row with column ID=2.
在MySQL中，我试图复制一排的自动增量column ID=1和插入数据到同一个表的新行column ID=2。
How can I do this in a single query?
如何在单个查询中执行此操作？
回答by mu is too short
Use INSERT ... SELECT:
使用INSERT ... SELECT：
insert into your_table (c1, c2, ...)
select c1, c2, ...
from your_table
where id = 1
where c1, c2, ...are all the columns except id. If you want to explicitly insert with an idof 2 then include that in your INSERT column list and your SELECT:
这里c1, c2, ...是除了所有列id。如果您想显式插入id2 则将其包含在您的 INSERT 列列表和您的 SELECT 中：
insert into your_table (id, c1, c2, ...)
select 2, c1, c2, ...
from your_table
where id = 1
You'll have to take care of a possible duplicate idof 2 in the second case of course.
id当然，在第二种情况下，您必须处理2的可能重复项。
回答by parvus
IMO, the best seems to use sql statements only to copy that row, while at the same time only referencing the columns you must and want to change.
IMO，最好似乎只使用 sql 语句来复制该行，同时只引用您必须和想要更改的列。
CREATE TEMPORARY TABLE temp_table ENGINE=MEMORY
SELECT * FROM your_table WHERE id=1;
UPDATE temp_table SET id=NULL; /* Update other values at will. */
INSERT INTO your_table SELECT * FROM temp_table;
DROP TABLE temp_table;
See also av8n.com - How to Clone an SQL Record
Benefits:
好处：
- The SQL statements 2 mention only the fields that need to be changed during the cloning process. They do not know about – or care about – other fields. The other fields just go along for the ride, unchanged. This makes the SQL statements easier to write, easier to read, easier to maintain, and more extensible.
- Only ordinary MySQL statements are used. No other tools or programming languages are required.
- A fully-correct record is inserted in
your_tablein one atomic operation.
- SQL 语句 2 仅提及在克隆过程中需要更改的字段。他们不了解或关心其他领域。其他领域只是顺其自然，没有改变。这使得 SQL 语句更易于编写、更易于阅读、更易于维护和更可扩展。
- 只使用普通的 MySQL 语句。不需要其他工具或编程语言。
your_table在一个原子操作中插入一个完全正确的记录。
回答by Vijay
Say the table is user(id, user_name, user_email).
说桌子是user(id, user_name, user_email)。
You can use this query:
您可以使用此查询：
INSERT INTO user (SELECT NULL,user_name, user_email FROM user WHERE id = 1)
回答by Petr Hladík
This helped and it supports a BLOB/TEXT columns.
这有帮助，它支持 BLOB/TEXT 列。
CREATE TEMPORARY TABLE temp_table
AS
SELECT * FROM source_table WHERE id=2;
UPDATE temp_table SET id=NULL WHERE id=2;
INSERT INTO source_table SELECT * FROM temp_table;
DROP TEMPORARY TABLE temp_table;
USE source_table;
回答by curmil
For a quick, clean solution that doesn't require you to name columns, you can use a prepared statement as described here: https://stackoverflow.com/a/23964285/292677
对于不需要您命名列的快速、干净的解决方案，您可以使用如下所述的准备好的语句：https: //stackoverflow.com/a/23964285/292677
If you need a complex solution so you can do this often, you can use this procedure:
如果您需要一个复杂的解决方案以便经常执行此操作，则可以使用以下过程：
DELIMITER $$
CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _whereClause text, _omitColumns text)
SQL SECURITY INVOKER
BEGIN
SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns)), 'id') INTO @omitColumns;
SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns
WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 ORDER BY ORDINAL_POSITION INTO @columns;
SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ')',
'SELECT ', @columns,
' FROM ', _schemaName, '.', _tableName, ' ', _whereClause);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END
You can run it with:
您可以使用以下命令运行它：
CALL duplicateRows('database', 'table', 'WHERE condition = optional', 'omit_columns_optional');
Examples
例子
duplicateRows('acl', 'users', 'WHERE id = 200'); -- will duplicate the row for the user with id 200
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts'); -- same as above but will not copy the created_ts column value
duplicateRows('acl', 'users', 'WHERE id = 200', 'created_ts,updated_ts'); -- same as above but also omits the updated_ts column
duplicateRows('acl', 'users'); -- will duplicate all records in the table
DISCLAIMER: This solution is only for someone who will be repeatedly duplicating rows in many tables, often. It could be dangerous in the hands of a rogue user.
免责声明：此解决方案仅适用于经常重复复制许多表中的行的人。它在流氓用户的手中可能很危险。
回答by Mark Pruce
You can also pass in '0' as the value for the column to auto-increment, the correct value will be used when the record is created. This is so much easier than temporary tables.
您还可以传入 '0' 作为自动递增的列的值，创建记录时将使用正确的值。这比临时表容易得多。
Source: Copying rows in MySQL(see the second comment, by TRiG, to the first solution, by Lore)
资料来源： 在 MySQL 中复制行（见第二条评论，由 TRiG，到第一个解决方案，由 Lore）
回答by IR.Programmer
insert into MyTable(field1, field2, id_backup)
select field1, field2, uniqueId from MyTable where uniqueId = @Id;
回答by RBILLC
A lot of great answers here. Below is a sample of the stored procedure that I wrote to accomplish this task for a Web App that I am developing:
这里有很多很棒的答案。下面是我为完成我正在开发的 Web 应用程序的此任务而编写的存储过程示例：
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
-- Create Temporary Table
SELECT * INTO #tempTable FROM <YourTable> WHERE Id = Id
--To trigger the auto increment
UPDATE #tempTable SET Id = NULL
--Update new data row in #tempTable here!
--Insert duplicate row with modified data back into your table
INSERT INTO <YourTable> SELECT * FROM #tempTable
-- Drop Temporary Table
DROP TABLE #tempTable
回答by Nathan Wailes
If you're able to use MySQL Workbench, you can do this by right-clicking the row and selecting 'Copy row', and then right-clicking the empty row and selecting 'Paste row', and then changing the ID, and then clicking 'Apply'.
如果您能够使用 MySQL Workbench，您可以通过右键单击该行并选择“复制行”，然后右键单击空行并选择“粘贴行”，然后更改 ID，然后单击“应用”。
Copy the row:
复制行：
Paste the copied row into the blank row:
将复制的行粘贴到空白行中：
Change the ID:
更改标识：
Apply:
申请：
回答by Privateer
I tend to use a variation of what mu is too short posted:
我倾向于使用 mu 太短张贴的变体：
INSERT INTO something_log
SELECT NULL, s.*
FROM something AS s
WHERE s.id = 1;
As long as the tables have identical fields (excepting the auto increment on the log table), then this works nicely.
只要表具有相同的字段（日志表上的自动增量除外），就可以很好地工作。
Since I use stored procedures whenever possible (to make life easier on other programmers who aren't too familiar with databases), this solves the problem of having to go back and update procedures every time you add a new field to a table.
由于我尽可能使用存储过程（为了让其他不太熟悉数据库的程序员更轻松），这就解决了每次向表中添加新字段时都必须返回并更新过程的问题。
It also ensures that if you add new fields to a table they will start appearing in the log table immediately without having to update your database queries (unless of course you have some that set a field explicitly)
它还确保如果您向表中添加新字段，它们将立即开始出现在日志表中，而无需更新您的数据库查询（当然，除非您有一些显式设置字段）
Warning:You will want to make sure to add any new fields to both tables at the same time so that the field order stays the same... otherwise you will start getting odd bugs. If you are the only one that writes database interfaces AND you are very careful then this works nicely. Otherwise, stick to naming all of your fields.
警告：您需要确保同时向两个表添加任何新字段，以便字段顺序保持不变……否则您将开始遇到奇怪的错误。如果您是唯一一个编写数据库接口的人并且您非常小心，那么这很有效。否则，请坚持命名所有字段。
Note:On second thought, unless you are working on a solo project that you are sure won't have others working on it stick to listing all field names explicitly and update your log statements as your schema changes. This shortcut probably is not worth the long term headache it can cause... especially on a production system.
注意：再想一想，除非您正在从事一个单独的项目，并且您确定不会让其他人参与其中，否则请坚持明确列出所有字段名称，并在架构更改时更新您的日志语句。这种快捷方式可能不值得它可能导致的长期头痛......尤其是在生产系统上。
