【MySQL】パーティションの作成、追加、削除、確認

MySQL のパーティションの作成、追加、確認についてメモメモ

・作成
ALTER TABLE tableName PARTITION BY RANGE (TO_DAYS(updateDate)) (
PARTITION p20190831 VALUES LESS THAN (TO_DAYS(‘2019-09-01 00:00:00’)),
PARTITION p20190901 VALUES LESS THAN (TO_DAYS(‘2019-09-02 00:00:00’)),
PARTITION p20190902 VALUES LESS THAN (TO_DAYS(‘2019-09-03 00:00:00’))
);

・追加
ALTER TABLE tableName ADD PARTITION (
PARTITION p20191123 VALUES LESS THAN (TO_DAYS(‘2019-11-24 00:00:00’)),
PARTITION p20191124 VALUES LESS THAN (TO_DAYS(‘2019-11-25 00:00:00’))
);

・削除
ALTER TABLE tableName DROP PARTITION p20191124;

・確認
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = tableName;