mysql drop分区和truncate分区有什么区别 [英] What is the difference between mysql drop partition and truncate partition

问题描述

有人可以解释以下命令之间的区别吗?

Can someone explain the difference between below commands?

ALTER TABLE A DROP PARTITION p0;

和

ALTER TABLE A TRUNCATE PARTITION p0;

我们应该在哪些场景下使用DROP/TRUNCATE分区?

In which scenarios should we use DROP/TRUNCATE partition?

推荐答案

都扔掉了数据.而且它不是事务性的"，因此您无法使用 ROLLBACK 恢复数据.

Both throw the data away. And it is not 'transactional', so you cannot recover the data with a ROLLBACK.

DROP PARTITION 也会从分区列表中删除分区.

DROP PARTITION also removes the partition from the list of partitions.

TRUNCATE PARTITION 将分区留在原处，但为空.

TRUNCATE PARTITION leaves the partition in place, but empty.

DROP PARTITION 的一个常见用法是删除旧"行.想象一张只需要保存 90 天的信息表.使用 PARTITION BY RANGE(TO_DAYS(...)) 并有每周分区.然后，每周 DROP 最旧的和 ADD 一个新分区.更多讨论此处.

A common usage of DROP PARTITION is to remove "old" rows. Think of a table of of information that needs to be kept for only 90 days. Use PARTITION BY RANGE(TO_DAYS(...)) and have weekly partitions. Then, every week DROP the oldest and ADD a new partition. More discussion here.

我没有看到需要 TRUNCATE.

请注意，您可以从 PARTITIONing 中获得任何好处的用例很少.到目前为止，我发现仅用于 PARTITION BY RANGE.

Be aware that there are very few use cases where you can get any benefit from PARTITIONing. So far, I have found uses only for PARTITION BY RANGE.

这篇关于mysql drop分区和truncate分区有什么区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！