What is the difference between mysql drop partition and truncate partition

Question

Can someone explain the difference between below commands?

ALTER TABLE A DROP PARTITION p0;

and

ALTER TABLE A TRUNCATE PARTITION p0;

In which scenarios should we use DROP/TRUNCATE partition?

Answer 1

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

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

TRUNCATE PARTITION leaves the partition in place, but empty.

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.

I have not seen a need for TRUNCATE.

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.