mysql - 将行从一个表移动到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19821736/
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
mysql - move rows from one table to another
提问by Marty Wallace
If i have two tables that are identical in structure, how can i move a set of rows from 1 table to the other?
如果我有两个结构相同的表，如何将一组行从一个表移动到另一个表？
The set of rows will be determined from a select query.
行集将根据选择查询确定。
for example:
例如：
customer table
person_id | person_name | person_email
123 tom [email protected]
persons table
person_id | person_name | person_email
a sample select would be:
样本选择将是：
select * from customer_table where person_name = 'tom';
I want to move the row from customer table to person table
我想将行从客户表移动到人员表
Ideally removing the data from the original table, but this wouldnt be a deal breaker.
理想情况下从原始表中删除数据，但这不会破坏交易。
回答by Nelson
A simple INSERT INTO SELECTstatement:
一个简单的INSERT INTO SELECT语句：
INSERT INTO persons_table SELECT * FROM customer_table WHERE person_name = 'tom';
DELETE FROM customer_table WHERE person_name = 'tom';
回答by CodeMonkey
INSERT INTO Persons_Table (person_id, person_name,person_email)
SELECT person_id, customer_name, customer_email
FROM customer_table
WHERE "insert your where clause here";
DELETE FROM customer_table
WHERE "repeat your where clause here";
回答by Robert R.
The answer of Fabio is really good but it take a long execution time (as Trilarion already has written)
Fabio 的答案非常好，但执行时间很长（因为 Trilarion 已经写过）
I have an other solution with faster execution.
我有另一个执行速度更快的解决方案。
START TRANSACTION;
set @N := (now());
INSERT INTO table2 select * from table1 where ts < date_sub(@N,INTERVAL 32 DAY);
DELETE FROM table1 WHERE ts < date_sub(@N,INTERVAL 32 DAY);
COMMIT;
@N gets the Timestamp at the begin and is used for both commands. All is in a Transaction to be sure nobody is disturbing
@N 在开始时获取时间戳，并用于两个命令。一切都在交易中，以确保没有人打扰
回答by Jake Pucan
INSERT INTO Persons_Table (person_id, person_name,person_email)
SELECT person_id, customer_name, customer_email
FROM customer_table
ORDER BY `person_id` DESC LIMIT 0, 15
WHERE "insert your where clause here";
DELETE FROM customer_table
WHERE "repeat your where clause here";
You can also use ORDER BY, LIMIT and ASC/DESC to limit and select the specific column that you want to move.
您还可以使用 ORDER BY、LIMIT 和 ASC/DESC 来限制和选择要移动的特定列。
回答by lukyer
BEGIN;
INSERT INTO persons_table select * from customer_table where person_name = 'tom';
DELETE FROM customer_table where person_name = 'tom';
COMMIT;
回答by escube
I had to solve the same issue and this is what I used as solution.
我必须解决同样的问题，这就是我用作解决方案的方法。
To use this solution the source and destination table must be identical, and the must have an id unique and autoincrement in first table (so that the same id is never reused).
要使用此解决方案，源表和目标表必须相同，并且必须在第一个表中具有唯一的 id 和自动增量（以便永远不会重复使用相同的 id）。
Lets say table1 and table2 have this structure
假设 table1 和 table2 具有这种结构
|id|field1|field2
You can make those two query :
您可以进行这两个查询：
INSERT INTO table2 SELECT * FROM table1 WHERE
DELETE FROM table1 WHERE table1.id in (SELECT table2.id FROM table2)
回答by Rakesh Chaudhari
To move and delete specific records by selecting using WHERE query,
要通过选择使用 WHERE 查询来移动和删除特定记录，
BEGIN TRANSACTION;
Insert Into A SELECT * FROM B where URL="" AND email ="" AND Annual_Sales_Vol="" And OPENED_In="" AND emp_count="" And contact_person= "" limit 0,2000;
delete from B where Id In (select Id from B where URL="" AND email ="" AND Annual_Sales_Vol="" And OPENED_In="" AND emp_count="" And contact_person= "" limit 0,2000);
commit;
