MySQL无法截断和删除分区表中的所有数据

Question

如前所述，我正在MySQL中进行并练习一个分区数据。由于我想清除所有的数据，数据仍然在那里，不能删除一次。

这是我创建的分区表。

CREATE TABLE events
(
    event_id INT unsigned NOT NULL AUTO_INCREMENT,
    inserted_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_updated_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    data JSON NOT NULL,
    KEY (event_id)
)
default charset utf8 
PARTITION BY RANGE(to_days(inserted_date))
(
PARTITION p20210301 VALUES LESS THAN (to_days('2021-04-01')),
PARTITION p20210401 VALUES LESS THAN (to_days('2021-05-01')),
PARTITION p20210501 VALUES LESS THAN (to_days('2021-06-01')),
PARTITION p20210601 VALUES LESS THAN (to_days('2021-07-01')),
PARTITION p20210701 VALUES LESS THAN (to_days('2021-08-01')),
PARTITION p20210801 VALUES LESS THAN (to_days('2021-09-01')),
PARTITION p20210901 VALUES LESS THAN (to_days('2021-10-01')),
PARTITION p20211001 VALUES LESS THAN (to_days('2021-11-01')),
PARTITION p20211101 VALUES LESS THAN (to_days('2021-12-01')),
PARTITION p20211201 VALUES LESS THAN (to_days('2022-01-01')),
PARTITION p20220101 VALUES LESS THAN (to_days('2022-02-01')),
PARTITION p20220201 VALUES LESS THAN (to_days('2022-03-01')),
PARTITION p20220301 VALUES LESS THAN (to_days('2022-04-01')),
PARTITION p20220401 VALUES LESS THAN (to_days('2022-05-01')),
PARTITION p20220501 VALUES LESS THAN (to_days('2022-06-01')),
PARTITION future VALUES LESS THAN (MAXVALUE) 
);

SELECT 
    * 
FROM 
    plover_audit_log.events

truncate table plover_audit_log.events;

delete from plover_audit_log.events;

我很好奇我的MySQL软件有问题吗？还是分区表导致了这种情况？因为我有一个没有分区的备份表,所以可以很好地使用Truncate,Delete和Select脚本。

Answer 1

分区表需要稍有不同的指令来执行截断

从所有分区中删除数据

ALTER plover_audit_log.events TRUNCATE PARTITION ALL;

从特定分区中删除所有数据

ALTER plover_audit_log.events TRUNCATE PARTITION p20210301;

参考https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html

Answer 2

数据由ProcessList中的一个过程运行。因此首先需要从ProcessList中删除该pid。像kill pid；那样，执行truncate并检查数据是否仍然persist