mysql单表删除千万条记录

当mysql表有大量数据要删除的时候，sql的执行时间会非常长，甚至会执行超时。

比较好的办法有两种，

• 用存储过程，一点点删除
• 用中间表，通过导出表到新表，然后truncate原表，再重命名新表、原表的名字。

存储过程伪过程

drop procedure if exists huge_delete;
delimiter //
create procedure huge_delete()
begin
 declare rows int;
 declare rows_deleted int;
 set rows = 1;
 set rows_deleted = 10000;
 while rows > 0
 do
    delete from db.tabA where predicate_col < curdate() - interval 90 day order by `id` limit 10000;
    set rows = row_count();
    set rows_deleted = rows_deleted + row_count();
    select rows_deleted as "Rows Deleted";
 end while;
end //
delimiter ;

中间表的方式，条件需要自己设置。

CREATE TABLE `tabA_copy` as select * from `tabA` where predicate_col > curdate() - interval 30 day;

tabA_copy 保留需要的数据

Rename table tabA to tabA_archive;

Rename table tabA_copy to tabA;

对于设置了GTID的mysql， 通过第二种方式执行建表语句时会报1786错误。

行建表语句时CREATE TABLE  aaa  AS SELECT * FROM bbb;  报：  

错误代码： 1786
Statement violates GTID consistency: CREATE TABLE ... SELECT.

解决：

  在my.cnf 中将

gtid_mode = ON
enforce_gtid_consistency = ON

改为

gtid_mode = OFF
enforce_gtid_consistency = OFF

这个参数不支持动态修改，需要修改配置文件后重启mysql。