I need to truncate all partitions except some partitions in a table in MYSQL. How can I do that.
I can delete all partitions using alter table table_name truncate partition ALL;
But I dont want to truncate some partitions. I have 55 partitions in my table.
You can truncate multiple partitions by listing them by name, separated by commas:
ALTER TABLE <tablename> TRUNCATE PARTITION p1, p2, p3, ...;
I'm afraid you will have to name them all in this statement. There is no syntax for "ALL except for a few."
You can get a list of partitions:
SELECT partition_name
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema=? AND table_name=?
AND partition_name NOT IN ('p13', 'p14', 'p15', ...);
The example NOT IN list shows excluding a few partitions. You would replace this with whatever condition you want to use to match those you do not want to truncate.
Fetch the results and join them together with commas, which should be an elementary bit of code in your favorite programming language.