php mysql在表之间移动行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5420156/
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 row between tables
提问by David19801
I have 2 tables, table1 and table2.
我有 2 张桌子，table1 和 table2。
I have a row in table1 and want to move it to table2 and remove it from table1. Basically, a cut+paste.
我在 table1 中有一行，想将其移动到 table2 并将其从 table1 中删除。基本上，剪切+粘贴。
I am using php. My current plan is to select from table1, store data in php, insert into table2, then delete from table1. This seems like a very long process.
我正在使用 php。我目前的计划是从table1中选择，将数据存入php，插入到table2中，然后从table1中删除。这似乎是一个非常漫长的过程。
Is this really the best way to do this?
这真的是最好的方法吗？
回答by Pieter888
You're going to need at least 2 queries:
您将需要至少 2 个查询：
INSERT INTO table2 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table 1 WHERE <insert_where_clause_here>
DELETE FROM table1 WHERE <your_where_clause>
I see no shorter way of doing this using MySQL
我看不到使用 MySQL 执行此操作的更短方法
回答by biakaveron
Use INSERT INTO ... SELECTto insert data from another table
使用INSERT INTO ... SELECT从另一个表插入数据
回答by Your Common Sense
Just make it one table and make a flag field to determine if record is moderated or not.
只需将其制作为一张表并制作一个标志字段即可确定记录是否经过审核。
So, the only thing you will need is just simple update query.
因此，您唯一需要的只是简单的更新查询。
That's the way such things being done in general.
这就是这些事情的一般处理方式。
回答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 auto-increment 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
|id|字段 1|字段 2
You can make those two queries:
您可以进行这两个查询：
INSERT INTO table2
SELECT *
FROM table1
WHERE <your_where_clause>
DELETE FROM table1
WHERE table1.id in
(SELECT table2.id
FROM table2)
This seems to me to be a better solution then the one suggested by Pieter888, because that solution doesn't take into account that data selected can be different than the data deleted (because data in table 1 can change between first and second query), while this way you are safe.
在我看来，这比 Pieter888 建议的解决方案更好，因为该解决方案没有考虑到选择的数据可能与删除的数据不同（因为表 1 中的数据可以在第一次和第二次查询之间更改），这样你就安全了。
回答by George Langley
I found that I had to prep the data between the initial query and the insert. This was on a codeigniter-based CMS, so it uses their functions and return formatting, but may help someone.
我发现我必须在初始查询和插入之间准备数据。这是在基于 codeigniter 的 CMS 上，因此它使用它们的函数并返回格式，但可能对某人有所帮助。
function move_row($id = 0) {
/* first copy it from table_one to table_two */
$query = $this->db->query('select * from table_one where id = ' . $id);
$row = $query->row_array(); /* this appears to be required */
$this->db->insert('table_two',$row);
/* then delete it from table_one */
return($this->db->query('delete from table_one where id = ' . $id));
}
(Bare-bones, no error-checking, etc.)
（准系统、无错误检查等）
回答by Jonas Stensved
The short answer to the question as asked, is as a lot of people already answered, no.
对所问问题的简短回答是因为很多人已经回答了，不。
But what I sense you're looking for is a way to insert into the new table and remove from the old in one operation. This is what transactionsare for.
但是我感觉您正在寻找的是一种在一次操作中插入新表并从旧表中删除的方法。这就是交易的目的。
The changes won't be affected until the transaction is commited which will have the same effect as if the rows where actually moved.
在提交事务之前，更改不会受到影响，这与实际移动的行具有相同的效果。
(Not moved internally in the MySQL engine but for users/queries)
（不是在 MySQL 引擎内部移动，而是用于用户/查询）
I would use INSERT SELECTand wrap the commands in a TRANSACTION something like this:
我会使用INSERT SELECT并将命令包装在 TRANSACTION 中，如下所示：
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN
ROLLBACK;
CALL ERROR_ROLLBACK_OCCURRED;
END;
START TRANSACTION;
INSERT INTO table_new SELECT a,b,c FROM table_old WHERE x = 'something';
DELETE FROM table_old WHERE x = 'something';
COMMIT;
回答by ramon cayangyang
You can use multiple query one at a time like this
您可以像这样一次使用多个查询
On the command button that has the function of committing your data on table 1
在具有提交表 1 数据功能的命令按钮上
Use this:
用这个：
$query = mysql_query(insert into table1)
Instead of this:
取而代之的是：
mysql_query(insert into table2)
And under the query that insert into table 1 write this:
在插入表 1 的查询下写下：
$query2 = mysql_query(INSERT into TABLE2)
TRUNCATE TABLE TABLE2;
It gives you the privileges to perform two queries one at a time
它为您提供一次执行两个查询的权限
