MySQL脚本用于删除 block 中的数据，直到低于id的所有内容都被删除

Question

我需要一个 MySQL Skript 来执行以下操作:

删除数据库的 block ，直到删除所有大于 10000 的 link_id

例如:

x = 10000
DELETE FROM pligg_links WHERE link_id > x and link_id < x+10000
x = x + 10000
...

所以它会删除

DELETE FROM pligg_links WHERE link_id > 10000 and link_id < 20000

然后

DELETE FROM pligg_links WHERE link_id > 20000 and link_id < 30000

直到所有小于10000的id都被删除

我需要这个，因为数据库非常非常大(超过一个演出)

提前致谢

Answer 1

您可以使用LIMIT语句来控制一步删除的项目数量:

DELETE FROM pligg_links
WHERE link_id > 10000
LIMIT 1000;

http://dev.mysql.com/doc/refman/5.1/en/delete.html说:

The MySQL-specific LIMIT row_count option to DELETE tells the server the maximum number of rows to be deleted before control is returned to the client. This can be used to ensure that a given DELETE statement does not take too much time. You can simply repeat the DELETE statement until the number of affected rows is less than the LIMIT value.

如果您想自动删除，可以使用 SELECT ROW_COUNT(); 确定已删除的行数:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count