按条件截断

Question

truncate -> 这将重置整个表，那么有没有一种通过 truncate 重置特定记录/检查条件的方法呢？

例如：我想重置所有数据并在表中保留最近30天的数据。

谢谢。

- Sharpeye500

---

实际上，如果将表分成分区，您可以仅截断所选的分区，而不是清除整个表。 - Daniel.P.

Answer 1

不，TRUNCATE 要么全清空要么不清空。你可以使用 DELETE FROM <table> WHERE <conditions> 来删除数据，但这样会失去 TRUNCATE 的速度优势。

- ceejayoz

---

谢谢，假设这个条件，我如何以优化的方式保留最近10天的数据并删除其余数据（比如表中有1000万条记录）。 - Sharpeye500

TRUNCATE的速度很快是因为它不写入日志。 - OMG Ponies

除了速度截断，还可以释放磁盘空间，但删除会保留磁盘空间。 - ImranRazaKhan

Answer 2

简短回答是“不行”：MySQL不允许您在TRUNCATE语句中添加WHERE子句。 这里是MySQL关于TRUNCATE语句的说明文档。

但好消息是，您可以通过(某种程度上)绕过此限制。

使用DELETE的简单、安全、干净但慢速解决方案。

首先，如果表足够小，只需使用DELETE语句即可（必须提及）：

1. LOCK TABLE my_table WRITE;
2. DELETE FROM my_table WHERE my_date<DATE_SUB(NOW(), INTERVAL 1 MONTH);
3. UNLOCK TABLES;

LOCK和UNLOCK语句并非强制要求，但它们可以加快速度并避免潜在的死锁。

不幸的是，如果你的表很大，这将会非常缓慢...而且既然你正在考虑使用TRUNCATE语句，我想这是因为你的表很大。

所以，以下是一种使用TRUNCATE语句解决问题的方法：

简单、快速但不安全的TRUNCATE方案

1. CREATE TABLE my_table_backup AS
      SELECT * FROM my_table WHERE my_date>=DATE_SUB(NOW(), INTERVAL 1 MONTH);
2. TRUNCATE my_table;
3. LOCK TABLE my_table WRITE, my_table_backup WRITE;
4. INSERT INTO my_table SELECT * FROM my_table_backup;
5. UNLOCK TABLES;
6. DROP TABLE my_table_backup;

不幸的是，如果其他进程同时向表中插入记录，则此解决方案会有些不安全：

• 在步骤1和步骤2之间插入的任何记录都将丢失
• TRUNCATE语句将AUTO-INCREMENT计数器重置为零。因此，在步骤2和步骤3之间插入的任何记录的ID都将低于较旧的ID，甚至可能与在步骤4中插入的ID发生冲突（请注意，AUTO-INCREMENT计数器将在步骤4后恢复到其正确值）。

不幸的是，无法锁定表并截断它。但是我们可以（某种方式）使用RENAME绕过该限制。

半简单、快速、安全但嘈杂的解决方案，使用TRUNCATE

1. RENAME TABLE my_table TO my_table_work;
2. CREATE TABLE my_table_backup AS
     SELECT * FROM my_table_work WHERE my_date>DATE_SUB(NOW(), INTERVAL 1 MONTH);
3. TRUNCATE my_table_work;
4. LOCK TABLE my_table_work WRITE, my_table_backup WRITE;
5. INSERT INTO my_table_work SELECT * FROM my_table_backup;
6. UNLOCK TABLES;
7. RENAME TABLE my_table_work TO my_table;
8. DROP TABLE my_table_backup;

这种方法应该是完全安全且相当快速的。唯一的问题是其他进程会在几秒钟内看到表my_table消失。这可能导致各处的日志显示错误。因此它是一个安全的解决方案，但是有一些“嘈杂”的问题。

免责声明：我不是MySQL专家，所以这些解决方案可能并不优秀。我能保证的是它们对我工作得很好。如果一些专家能够评论这些解决方案，我将不胜感激。

- MiniQuark

---

非常好的答案，处理限制的方式也很棒。谢谢。 - Bertus Kruger

Answer 3

作为对您的问题的回应：“我想重置所有数据并保留表中最近30天的数据。”

您可以创建一个事件。请参考https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html 例如：

CREATE EVENT DeleteExpiredLog
ON SCHEDULE EVERY 1 DAY
DO
DELETE FROM log WHERE date < DATE_SUB(NOW(), INTERVAL 30 DAY);

每日清理您的表格，使最新的30天数据保持可用。

- Felipe Torres

Answer 4

你可以使用datapump带有查询子句的方式简单地导出表格，并使用table_exists_action=replace子句将其导入回来。它将会删除并重新创建你的表格，并且非常快速。在实施之前，请先阅读相关资料。

- user4603856