将数据从一个MySQL表移动到另一个 [英] Move data from one MySQL table to another

问题描述

当用户单击名为删除的按钮时，我试图将数据从一个数据库(注册)移动到另一个数据库. (我想将数据移动到名为已归档"的表中

I am trying to move data from one database (registrations) to another when the user clicks a button named delete. (I want to move the data to a table named archived)

这是我尝试过的方法(可从Google找到):

Here is what i have tried (found from Google):

 $result=mysql_query("Insert Into archived (select * from registrations WHERE id=$id") ;
 $row = mysql_fetch_array($result);

这不会动...任何人都可以帮忙吗?

This doesn't move it... can anyone help?

推荐答案

首先，您缺少一个圆括号，在这种情况下，您根本不需要使用该圆括号.

Firstly you're missing one parenthesis, which you don't have to use in this case at all

将查询字符串更改为

Insert Into archived (select * from registrations WHERE id=$id)
                     ^                                        ^

或者只是

Insert Into archived select * from registrations WHERE id=$id

这里是 SQLFiddle 演示

Here is SQLFiddle demo

其次，INSERT不返回结果集，因此您不应该使用mysql_fetch_array().

Secondly INSERT doesn't return a resultset so you shouldn't use mysql_fetch_array().

第三，如果您打算移动，而不仅仅是复制数据，那么您还需要删除随后复制的行.

Thirdly if your intent was to move not just to copy data then you also need to delete the row that you copied afterwards.

现在您可以将其全部放入存储过程中

Now you can put it all in a stored procedure

DELIMITER $$
CREATE PROCEDURE move_to_archive(IN _id INT)
BEGIN
    START TRANSACTION;
    INSERT INTO archived 
    SELECT * 
      FROM registrations 
     WHERE id = _id;
    DELETE
      FROM registrations 
     WHERE id = _id;
    COMMIT;
END$$
DELIMITER ;

样品用量:

CALL move_to_archive(2);

这里是 SQLFiddle 演示

Here is SQLFiddle demo

这篇关于将数据从一个MySQL表移动到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！