SQL 删除整个表，除了一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14366093/
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
DELETE whole table except for one row
提问by MurifoX
Suppose I have a database with a table which contains 200k+ rows.
This table has a fixed tuple with id 1800. The rest of the tuples sequence starts at 300k+.
I have a need to clean this table, delete all records without delete the one register with id 1800. I came up with 3 types of query i could possibly run:
假设我有一个包含 200k+ 行的表的数据库。
这个表有一个固定的元组，id 为 1800。元组序列的其余部分从 300k+ 开始。
我需要清理这张表，删除所有记录而不删除 ID 为 1800 的一个寄存器。我想出了 3 种可能运行的查询类型：
DELETE FROM table WHERE id > 1800
DELETE FROM table WHERE id <> 1800
DELETE FROM table WHERE id NOT IN (1800)
I have a feeling that the first one is quicker than the others, but I am not sure, as all of the other data have ids way greater than 1800.
我有一种感觉，第一个比其他的要快，但我不确定，因为所有其他数据的 id 都大于 1800。
Which one of them is quicker, and why? Also, if there is a quicker way to delete the records excluding the one that cannot be deleted, let me know.
其中哪一个更快，为什么？另外，如果有一种更快的方法可以删除除无法删除的记录之外的记录，请告诉我。
回答by Gordon Linoff
The quickest way in most databases would be:
大多数数据库中最快的方法是：
- Select the record with id 1800 into a temporary table
- Drop the original table
- Copy the data from the temp table into the full table
- 选择id为1800的记录放入临时表
- 删除原始表
- 将临时表中的数据复制到全表中
Admittedly, this may not be possible due to triggers, constraints, and permissions. In many databases you can do something similar by modifying (2) to truncate the table instead of dropping it.
诚然，由于触发器、约束和权限，这可能是不可能的。在许多数据库中，您可以通过修改 (2) 来截断表而不是删除它来做类似的事情。
As for your original question, the overhead with actually deleting the rows and the data associated with them is going to dominate the query. How you do the comparison is irrelevant.
至于您的原始问题，实际删除行及其相关数据的开销将主导查询。您如何进行比较无关紧要。
Sample code
示例代码
create temp table saved as
select * from t where id = 1800
truncate table t
insert into t
select * from saved
I'm not sure about Postgres naming conventions for temporary tables, but this is the idea.
我不确定 Postgres 临时表的命名约定，但这就是想法。
回答by Quassnoi
As long as those affect same records, those will have similar performance.
只要这些影响相同的记录，它们就会具有相似的性能。
There is a slight chance the former will use an index seek rather than more efficient full table scan, but it's negligible.
前者使用索引查找而不是更高效的全表扫描的可能性很小，但可以忽略不计。
回答by Kuberchaun
If you can't move the ID to a new table you might want to try and delete in groups or batches. Sometimes having a transaction with a large chunk of records is not handled the fastest. This is the case for any database oracle and microsoft database products included.
如果您无法将 ID 移动到新表，您可能想要尝试分组或批量删除。有时，处理包含大量记录的事务的速度不是最快的。包括的任何数据库 oracle 和 microsoft 数据库产品都是这种情况。
BEGIN TRANSACTION;
DELETE FROM table WHERE id >= 0 and id < 20000 and id != 1800;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DELETE FROM table WHERE id >= 20000 and id < 40000 and id != 1800;
COMMIT TRANSACTION;
etc
etc
回答by Anvesh Reddy
If you want to save only the last record and remove all the other records you can make use of below query which worked for me
如果您只想保存最后一条记录并删除所有其他记录，您可以使用以下对我有用的查询
delete from public.table_name
WHERE lastrun_ts < (
select MAX(lastrun_ts)
FROM public.table_name
ORDER BY MAX(lastrun_ts) DESC
);
