SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL;
A partition name of NULL signifies that the table is not partitioned.
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS;
WHERE TABLE_NAME = 'mytable';
SELECT TABLE_SCHEMA,
TABLE_NAME,
MAX(PARTITION_NAME) AS latest_partition
FROM information_schema.PARTITIONS
WHERE PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME;
SELECT PARTITION_NAME,
PARTITION_DESCRIPTION
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'myschema'
AND TABLE_NAME = 'mytable';
A partition_description of MAXVALUE indicates a catch-all partition that will take any rows that don't qualify for the other partitions.
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY RANGE (mygroup) (
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION p3 VALUES LESS THAN (20)
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY RANGE ( YEAR(mydate) ) (
PARTITION p0 VALUES LESS THAN (2000),
PARTITION p1 VALUES LESS THAN (2010),
PARTITION p2 VALUES LESS THAN (2020),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
CREATE TABLE mytable (
myid BIGINT UNSIGNED NOT NULL,
clock INT NOT NULL DEFAULT '0',
value DOUBLE NOT NULL DEFAULT '0',
PRIMARY KEY (`myid`,`clock`)
) ENGINE=InnoDB AUTO_INCREMENT=289796638
DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin
PARTITION BY RANGE (`clock`)
(PARTITION p2026_10_23 VALUES LESS THAN (1792796400),
PARTITION p2026_10_24 VALUES LESS THAN (1792882800),
PARTITION p2026_10_25 VALUES LESS THAN (1792972800),
PARTITION p2026_10_26 VALUES LESS THAN (1793059200),
PARTITION p2026_10_27 VALUES LESS THAN (1793145600),
PARTITION p2026_10_28 VALUES LESS THAN (1793232000),
PARTITION p2026_10_29 VALUES LESS THAN (1793318400),
PARTITION p2026_10_30 VALUES LESS THAN (1793404800),
PARTITION p2026_10_31 VALUES LESS THAN (1793491200),
PARTITION p2026_11_01 VALUES LESS THAN (1793577600),
PARTITION p2026_11_02 VALUES LESS THAN (1793664000),
PARTITION p2026_11_03 VALUES LESS THAN (1793750400),
PARTITION p2026_11_04 VALUES LESS THAN (1793836800),
PARTITION p2026_11_05 VALUES LESS THAN (1793923200),
PARTITION p2026_11_06 VALUES LESS THAN (1794009600),
PARTITION p2026_11_07 VALUES LESS THAN (1794096000),
PARTITION p2026_11_08 VALUES LESS THAN (1794182400),
PARTITION p2026_11_09 VALUES LESS THAN (1794268800),
PARTITION p2026_11_10 VALUES LESS THAN (1794355200)
);
To add more partitions for future dates...
ALTER TABLE mytable
ADD PARTITION (
PARTITION p2026_11_11 VALUES LESS THAN (1794441600),
PARTITION p2026_11_12 VALUES LESS THAN (1794528000),
PARTITION p2026_11_13 VALUES LESS THAN (1794614400),
PARTITION p2026_11_14 VALUES LESS THAN (1794700800),
PARTITION p2026_11_15 VALUES LESS THAN (1794787200),
PARTITION p2026_11_16 VALUES LESS THAN (1794873600),
PARTITION p2026_11_17 VALUES LESS THAN (1794960000)
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LIST (mygroup) (
PARTITION p0 VALUES IN (1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10),
PARTITION p2 VALUES IN (11,12,13,14,15),
PARTITION p3 VALUES IN (16,17,18,19,20)
);
ALTER TABLE mytable
ADD PARTITION (
PARTITION p4 VALUES IN (21,22,23,24,25)
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY RANGE COLUMNS (myid, mygroup, mychar) (
PARTITION p0 VALUES LESS THAN (5,5,'eee'),
PARTITION p1 VALUES LESS THAN (10,10,'jjj'),
PARTITION p2 VALUES LESS THAN (15,15,'ooo'),
PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY RANGE COLUMNS ( mydate ) (
PARTITION p0 VALUES LESS THAN ('2000-01-03'),
PARTITION p1 VALUES LESS THAN ('2000-01-05'),
PARTITION p2 VALUES LESS THAN ('2000-01-07'),
PARTITION p3 VALUES LESS THAN ('2000-01-09')
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LIST COLUMNS (mychar) (
PARTITION p0 VALUES IN ('aaa','bbb','ccc','ddd','eee'),
PARTITION p1 VALUES IN ('fff','ggg','hhh','iii','jjj'),
PARTITION p2 VALUES IN ('kkk','lll','mmm','nnn','ooo'),
PARTITION p3 VALUES IN ('ppp','qqq','rrr','sss','ttt')
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LIST COLUMNS ( mydate ) (
PARTITION p0 VALUES IN ('2000-01-01','2000-01-02'),
PARTITION p1 VALUES IN ('2000-01-03','2000-01-04'),
PARTITION p2 VALUES IN ('2000-01-05','2000-01-06'),
PARTITION p3 VALUES IN ('2000-01-07','2000-01-08')
);
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY HASH (mygroup)
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY HASH ( YEAR(mydate) )
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LINEAR HASH ( YEAR(mydate) )
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL PRIMARY KEY,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY KEY()
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL,
UNIQUE KEY (mychar)
)
PARTITION BY KEY(mychar)
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL PRIMARY KEY,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LINEAR KEY()
PARTITIONS 5;
CREATE TABLE mytable (
myid INT NOT NULL,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY RANGE( YEAR(mydate) )
SUBPARTITION BY HASH( TO_DAYS(mydate) )
(
PARTITION p0 VALUES LESS THAN (2000)
(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2010)
(
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN (2020)
(
SUBPARTITION s4,
SUBPARTITION s5
)
);
CREATE TABLE mytable (
myid INT NOT NULL PRIMARY KEY,
mydate DATE NOT NULL,
mygroup INT NOT NULL,
mychar CHAR(3) NOT NULL
)
PARTITION BY LIST (myid) (
PARTITION p0 VALUES IN (1,2,3,4,5),
PARTITION p1 VALUES IN (6,7,8,9,10),
PARTITION p2 VALUES IN (11,12,13,14,15),
PARTITION p3 VALUES IN (16,17,18,19,20)
);
ALTER TABLE mytable
PARTITION BY KEY(myid)
PARTITIONS 5;
There are two options for automating addition of new partitions...
An external scheduler (e.g. cron)
A MySQL EVENT
Community scripts are available (see Bibliography)
Best practice is to keep a rolling window, for example:
This means that if a scheduled job to add a new partition fails, inserts will continue to work whilst you work to address the issue.Daily partitions for the next 30 days
Weekly partitions for the next 12 weeks
Monthly partitions for the next 12 months