mysql - 将数据复制到另一个数据库后删除数据

Question

我有一个存储过程，其内容如下:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`MailMe`@`%` PROCEDURE `sp_archivev3`()
BEGIN

INSERT INTO 
     send.sgev3_archive(a_bi,
                        b_vc,
                        c_int,
                        d_int,
                        e_vc,
                        f_vc,
                        g_vc,
                        h_vc,
                        i_dt,
                        j_vc,
                        k_vc,
                        l_vc,
                        m_dt,
                        n_vch,
                        o_bit)
SELECT     a_bi,
           b_vc,
           c_int,
           d_int,
           e_vc,
           f_vc,
           g_vc,
           h_vc,
           i_dt,
           j_vc,
           k_vc,
           l_vc,
           m_dt,
           n_vch,
           o_bit

FROM   send.sgev3

WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();


END

因为，我的查询是将记录从 send.sgev3 插入到 send.sgev3_archive 中。我想再做一件事。选择并插入 send.sgev3_archive 后，我想删除 send.sgev3 表中的记录。我应该在上面的代码中将 DELETE 查询写在 SELECT 查询的正下方吗？只是想确认一下，因为我不想弄乱我的真实数据并在没有复制的情况下意外删除任何记录。请指教。

Answer 1

是的。包括一个 DELETE 声明

DELETE FROM   send.sgev3
WHERE m_dt BETWEEN  '2014-06-09' AND CURDATE();

为了更加确定 INSERT 在 DELETE 调用之前完成；将 INSERT 和 DELETE 包装在 Transaction Block 中说

START TRANSACTION;
INSERT INTO send.sgev3_archive ...
SELECT ... FROM send.sgev3
COMMIT;

您还可以通过使用存储过程中的退出处理程序 来处理过程中的错误情况和ROLLBACK 整个事务。下面的帖子已经展示了一种方法来做同样的事情。看一看。

How can I use transactions in my MySQL stored procedure?

MySQL Rollback in transaction

编辑:

why transaction is necessary? Can't I just proceed like the way I have mentioned in my question?

而不是向你解释为什么；让我们给你看一个例子(很像你的场景)

假设您有一个名为 parent 的表声明为

create table parent(id int not null auto_increment primary key,
`name` varchar(10),city varchar(10));

向其中插入一些记录

insert into parent(`name`,city) values('sfsdfd','sdfsdfdf'),('sfsdfd','sdfsdfdf'),('sfsdfd',null)

现在，您有另一个名为 child 的表，定义如下(注意最后一列有 not null 约束)

create table child(id int not null auto_increment primary key,
`name` varchar(10),city varchar(10) not null) 

现在执行下面的语句(你当前正在做什么)

insert into child(`name`,city) select * from parent;
delete from parent;

结果:由于 child 表中的 not null 约束，INSERT 将失败，但 delete 将成功。

要避免这种情况，您需要使用 Transaction。这样一来，如果 INSERT 失败，您根本不会执行 delete。

有关如何在事务中处理此问题的伪代码

start transaction

insert into child(`name`,city) select * from parent;

if(ERROR)
rollback
exit from stored proc

else
commit
delete from parent;

旁注:exit from stored proc 可以使用 LEAVE 实现