错误1206，每当尝试从表中删除记录时 [英] error 1206 whenever trying to delete records from a table

问题描述

我有一个拥有超过4000万条记录的表.我想使用sql查询删除约150000条记录:

I have a table with more than 40 million records.i want to delete about 150000 records with a sql query:

DELETE
FROM t
WHERE date="2013-11-24"

但是出现错误1206(锁的总数超过了锁表的大小). 我进行了很多搜索并更改了缓冲池的大小:

but I get error 1206(The total number of locks exceeds the lock table size). I searched a lot and change the buffer pool size:

innodb_buffer_pool_size=3GB

但是没有用. 我也试图锁定表，但也没有用:

but it didn't work. I also tried to lock tables but didn't work too:

Lock Tables t write;
DELETE
FROM t
WHERE date="2013-11-24";
unlock tables;

我知道一个解决方案是拆分删除过程，但我希望这是我的最后选择. 我正在使用mysql服务器，服务器操作系统是centos，服务器Ram是4GB.

I know one solution is to split the process of deleting but i want this be my last option. I am using mysql server, server OS is centos and server Ram is 4GB.

我将不胜感激.

推荐答案

您可以在删除操作上使用Limit，并尝试一次删除大约10,000条记录的数据，例如:

You can use Limit on your delete and try deleting data in batches of say 10,000 records at a time as:

DELETE
FROM t
WHERE date="2013-11-24"
LIMIT 10000

您还可以包括ORDER BY子句，以便按子句指定的顺序删除行:

You can also include an ORDER BY clause so that rows are deleted in the order specified by the clause:

DELETE
FROM t
WHERE date="2013-11-24"
ORDER BY primary_key_column
LIMIT 10000

这篇关于错误1206，每当尝试从表中删除记录时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！