0

How can I drop all partitions from a table in MySQL. I know you can drop specific partitions with ALTER TABLE table_name DROP PARTITION p0, p1, but is there a way to not list every single partition name?

Cirrus86
  • 336
  • 4
  • 13
  • Drop all partitions == truncate table? Drop the table and re-create it without partitioning. – Akina Feb 26 '20 at 06:10
  • By drop all partitions I mean remove the partitions while keeping the data intact. From my understanding `DROP PARTITION` removes partitions while keeping data intact, and `TRUNCATE PARTITION` removes the data within the partition – Cirrus86 Feb 26 '20 at 06:12
  • *From my understanding DROP PARTITION removes partitions while keeping data intact* Investigate: [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=18911cca6bef434e87569490fd60d20e). – Akina Feb 26 '20 at 06:21
  • After looking at the documentation again you are correct, thank you – Cirrus86 Feb 26 '20 at 06:43

1 Answers1

6

ALTER TABLE table_name REMOVE PARTITIONING can achieve what you want.

test fiddle

danblack
  • 12,130
  • 2
  • 22
  • 41