如何在 MySQL 上一次删除一系列记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7972435/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete a range of records at once on MySQL?
提问by user994535
I have rows over 60000 in my table. How can I delete rows from 40000 to 50000 at once?
我的表中有超过 60000 行。如何一次删除 40000 到 50000 行？
回答by Yoram de Langen
You can use the betweenfunction:
您可以使用该between功能：
delete from exampleTable where id between 40000 and 50000
or:
或者：
delete from exampleTable where id >= 40000 and id <= 50000
pretty simple?
很简单？
回答by Evan
I'm assuming you want to do this based on the the default order of the table, and you don't have a natural key or some other way to order these in the table data.
我假设您想根据表的默认顺序执行此操作，并且您没有自然键或其他方式在表数据中对这些进行排序。
I.e. SELECT * FROM WORDSreturns:
即SELECT * FROM WORDS返回：
Hi
Hello
Bye
Goodbye
What's up
So if you said you wanted to delete rows 2 to 4, do you want to delete Hello, Bye, and Goodbye. Right?
因此，如果您说要删除第 2 行到第 4 行，是否要删除 Hello、Bye 和 Goodbye。对？
Is the return order of your table deterministic? In other words, to you get the results in the same order every time? Ideally, you would be able to identify a column in your table that contains data you could key off of, which would let you use Tricker's answer.
您的表的返回顺序是确定性的吗？换句话说，您每次都以相同的顺序获得结果？理想情况下，您将能够识别表中包含您可以关闭的数据的列，这将让您使用 Tricker 的答案。
If that isn't true, you will need to use the row number for your between statement. I'm not a mysql user, the code below would undoubtedly be cleaner if I were. Additionally, I don't even have a mysql database to test the query against. I put together a sql statement using this post as a reference for how to get a row num in mysql. With MySQL, how can I generate a column containing the record index in a table?
如果这不是真的，您将需要为您的 between 语句使用行号。我不是 mysql 用户，如果我是，下面的代码无疑会更清晰。此外，我什至没有一个 mysql 数据库来测试查询。我使用这篇文章整理了一个 sql 语句，作为如何在 mysql 中获取行号的参考。使用 MySQL，如何生成包含表中记录索引的列？
My query assumes you have some kind of primary key on your table. If not, there is no way to guarantee that you wont delete extraneous rows, plus it's good table design.
我的查询假设您的表上有某种主键。如果没有，则无法保证您不会删除无关的行，而且它的表设计很好。
DELETE FROM YOUR_TABLE
WHERE primarykey =
(SELECT primarykey FROM
(SELECT t.primarykey,
@curRow := @curRow + 1 AS row_number
FROM YOUR_TABLE t
JOIN (SELECT @curRow := 0) r)
WHERE row_number between 40000 and 50000);
回答by A programmer
If you dont have a primary key and want to delete by row count i.e row range, it can be done via mysql export import. Do a custom export - Specify the start row and count. In your case, you ll need two exports: one for rows 0 - 40k, second for 50-last. Then import those two files into the table. Note: If importing large files is a problem, I recommend BigDump. http://www.ozerov.de/bigdump/
如果您没有主键并希望按行数（即行范围）删除，则可以通过 mysql 导出导入来完成。进行自定义导出 - 指定起始行和计数。在您的情况下，您需要两个导出：第一个用于第 0 - 40k 行，第二个用于第 50 行。然后将这两个文件导入表中。注意：如果导入大文件有问题，我推荐 BigDump。http://www.ozerov.de/bigdump/
回答by varshini
Table: grocery
表：杂货
id item
----------
1 rice
2 soap
3 rice
4 rice
DELETE FROM 'grocery' WHERE item="rice" LIMIT 2;
从“杂货”中删除项目=“大米”限制2；
This is easier than all the other ideas. for this particular item , there are 2 records can be deleted.
这比所有其他想法都容易。对于此特定项目，可以删除 2 条记录。
And the answer is,
而答案是，
2 rows deleted.
2 rows deleted.
SELECT * FROM grocery;
选择 * 从杂货店；
id item
----------
1 rice
2 soap
