MySQL 用条件截断

Truncate with condition

提问by Sharpeye500

truncate ->this resets the entire table, is there a way via truncate to reset particular records/check conditions.

truncate -> 这会重置整个表，有没有办法通过 truncate 来重置特定的记录/检查条件。

For ex: i want to reset all the data and keep last 30 days inside the table.

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

Thanks.

谢谢。

回答by ceejayoz

No, TRUNCATEis all or nothing. You can do a DELETE FROM <table> WHERE <conditions>but this loses the speed advantages of TRUNCATE.

不，TRUNCATE要么全有，要么全无。您可以执行 aDELETE FROM <table> WHERE <conditions>但这会失去TRUNCATE.

回答by MiniQuark

The short answer is no: MySQL does notallow you to add a WHEREclause to the TRUNCATEstatement. Here's MySQL's documentationabout the TRUNCATEstatement.

简短的回答是没有：MySQL的不能让你一个补充WHERE条款的TRUNCATE声明。这是有关该语句的MySQL 文档TRUNCATE。

But the good news is that you can (somewhat) work around this limitation.

但好消息是你可以（在某种程度上）解决这个限制。

Simple, safe, clean but slow solution using DELETE

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

First of all, if the table is small enough, simply use the DELETEstatement (it had to be mentioned):

首先，如果表足够小，只需使用DELETE语句（不得不提到）：

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

The LOCKand UNLOCKstatements are not compulsory, but they will speed things up and avoid potential deadlocks.

该LOCK和UNLOCK语句是不是强制性的，但他们会加快速度，并避免潜在的死锁。

Unfortunately, this will be very slow if your table is large... and since you are considering using the TRUNCATEstatement, I suppose it's because your table is large.

不幸的是，如果您的表很大，这将非常慢......并且由于您正在考虑使用该TRUNCATE语句，我想这是因为您的表很大。

So here's one way to solve your problem using the TRUNCATEstatement:

因此，这是使用该TRUNCATE语句解决问题的一种方法：

Simple, fast, but unsafe solution using 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;

Unfortunately, this solution is a bit unsafe if other processes are inserting records in the table at the same time:

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

• any record inserted between steps 1 and 2 will be lost
• the TRUNCATEstatement resets the AUTO-INCREMENTcounter to zero. So any record inserted between steps 2 and 3 will have an ID that will be lower than older IDs and that might even conflict with IDs inserted at step 4 (note that the AUTO-INCREMENTcounter will be back to it's proper value after step 4).

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

Unfortunately, it is not possible to lock the table and truncate it. But we can (somehow) work around thatlimitation using RENAME.

不幸的是，无法锁定表并截断它。但是，我们可以（在某种程度上）工作围绕该限制使用RENAME。

Half-simple, fast, safe but noisy solution using TRUNCATE

半简单、快速、安全但嘈杂的解决方案，使用 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;

This should be completely safe and quite fast. The only problem is that other processes will see table my_tabledisappear for a few seconds. This might lead to errors being displayed in logs everywhere. So it's a safe solution, but it's "noisy".

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

Disclaimer: I am not a MySQL expert, so these solutions might actually be crappy. The only guarantee I can offer is that they work fine for me. If some expert can comment on these solutions, I would be grateful.

免责声明：我不是 MySQL 专家，所以这些解决方案实际上可能很糟糕。我能提供的唯一保证是它们对我来说很好用。如果有专家可以对这些解决方案发表评论，我将不胜感激。

回答by Felipe Torres

As a response to your question: "i want to reset all the data and keep last 30 days inside the table."

作为对您问题的回答：“我想重置所有数据并将过去 30 天的数据保留在表中。”

you can create an event. Check https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

你可以创建一个事件。检查https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

For example:

例如：

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

Will run a daily cleanup in your table, keeping the last 30 days data available

将在您的表中运行每日清理，保持最近 30 天的数据可用

回答by user4603856

You can simply export the table with a query clause using datapump and import it back with table_exists_action=replace clause. Its will drop and recreate your table and take very less time. Please read about it before implementing.

您可以使用 datapump 简单地使用查询子句导出表，然后使用 table_exists_action=replace 子句将其导入回来。它会删除并重新创建您的表，并且花费的时间非常少。请在实施之前阅读它。