# simple case with DATETIME column showing partitions added in INSERT set timestamp= unix_timestamp('2026-05-02 00:00:00');
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-0100:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21 00:00:00') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22 00:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23 00:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24 00:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25 00:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26 00:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27 00:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-20'0
p1 RANGE COLUMNS `c` '2026-04-21 00:00:00'0
p2 RANGE COLUMNS `c` '2026-04-22 00:00:00'0
p3 RANGE COLUMNS `c` '2026-04-23 00:00:00'0
p4 RANGE COLUMNS `c` '2026-04-24 00:00:00'0
p5 RANGE COLUMNS `c` '2026-04-25 00:00:00'0
p6 RANGE COLUMNS `c` '2026-04-26 00:00:00'0
p7 RANGE COLUMNS `c` '2026-04-27 00:00:00'0
p8 RANGE COLUMNS `c` '2026-04-28 00:00:00'0
p9 RANGE COLUMNS `c` '2026-04-29 00:00:00'0
p10 RANGE COLUMNS `c` '2026-04-30 00:00:00'0
p11 RANGE COLUMNS `c` '2026-05-01 00:00:00'0
p12 RANGE COLUMNS `c` '2026-05-02 00:00:00'1
p13 RANGE COLUMNS `c` '2026-05-03 00:00:00'0 set timestamp= unix_timestamp('2026-05-06 00:00:00');
insert into t1 values ('2026-05-04');
select * from t1;
c 2026-05-0100:00:00 2026-05-0400:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21 00:00:00') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22 00:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23 00:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24 00:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25 00:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26 00:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27 00:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04 00:00:00') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05 00:00:00') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-20'0
p1 RANGE COLUMNS `c` '2026-04-21 00:00:00'0
p2 RANGE COLUMNS `c` '2026-04-22 00:00:00'0
p3 RANGE COLUMNS `c` '2026-04-23 00:00:00'0
p4 RANGE COLUMNS `c` '2026-04-24 00:00:00'0
p5 RANGE COLUMNS `c` '2026-04-25 00:00:00'0
p6 RANGE COLUMNS `c` '2026-04-26 00:00:00'0
p7 RANGE COLUMNS `c` '2026-04-27 00:00:00'0
p8 RANGE COLUMNS `c` '2026-04-28 00:00:00'0
p9 RANGE COLUMNS `c` '2026-04-29 00:00:00'0
p10 RANGE COLUMNS `c` '2026-04-30 00:00:00'0
p11 RANGE COLUMNS `c` '2026-05-01 00:00:00'0
p12 RANGE COLUMNS `c` '2026-05-02 00:00:00'1
p13 RANGE COLUMNS `c` '2026-05-03 00:00:00'0
p14 RANGE COLUMNS `c` '2026-05-04 00:00:00'0
p15 RANGE COLUMNS `c` '2026-05-05 00:00:00'1
p16 RANGE COLUMNS `c` '2026-05-06 00:00:00'0
p17 RANGE COLUMNS `c` '2026-05-07 00:00:00'0
drop table t1;
# simple case with DATE column
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 DAY AUTO
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-30'0
p1 RANGE COLUMNS `c` '2026-05-01'0
p2 RANGE COLUMNS `c` '2026-05-02'1
p3 RANGE COLUMNS `c` '2026-05-03'0
p4 RANGE COLUMNS `c` '2026-05-04'0
p5 RANGE COLUMNS `c` '2026-05-05'0
p6 RANGE COLUMNS `c` '2026-05-06'0
p7 RANGE COLUMNS `c` '2026-05-07'0
drop table t1;
# oracle interval syntax
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-30'0
p1 RANGE COLUMNS `c` '2026-05-01'0
p2 RANGE COLUMNS `c` '2026-05-02'1
p3 RANGE COLUMNS `c` '2026-05-03'0
p4 RANGE COLUMNS `c` '2026-05-04'0
p5 RANGE COLUMNS `c` '2026-05-05'0
p6 RANGE COLUMNS `c` '2026-05-06'0
p7 RANGE COLUMNS `c` '2026-05-07'0
drop table t1;
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION p0 VALUES LESS THAN ('2025-04-30')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 MONTH
(PARTITION `p0` VALUES LESS THAN ('2025-04-30') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 MONTH
(PARTITION `p0` VALUES LESS THAN ('2025-04-30') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2025-05-30') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2025-06-30') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2025-07-30') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2025-08-30') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2025-09-30') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2025-10-30') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2025-11-30') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2025-12-30') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-01-30') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-02-28') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-03-28') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-28') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2025-04-30'0
p1 RANGE COLUMNS `c` '2025-05-30'0
p2 RANGE COLUMNS `c` '2025-06-30'0
p3 RANGE COLUMNS `c` '2025-07-30'0
p4 RANGE COLUMNS `c` '2025-08-30'0
p5 RANGE COLUMNS `c` '2025-09-30'0
p6 RANGE COLUMNS `c` '2025-10-30'0
p7 RANGE COLUMNS `c` '2025-11-30'0
p8 RANGE COLUMNS `c` '2025-12-30'0
p9 RANGE COLUMNS `c` '2026-01-30'0
p10 RANGE COLUMNS `c` '2026-02-28'0
p11 RANGE COLUMNS `c` '2026-03-28'0
p12 RANGE COLUMNS `c` '2026-04-28'0
p13 RANGE COLUMNS `c` '2026-05-28'1
drop table t1;
## NUMTODSINTERVAL takes only DAY, HOUR, MINUTE or SECOND
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (NUMTODSINTERVAL(1, 'YEAR'))
(
PARTITION p0 VALUES LESS THAN ('2024-04-30')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
## NUMTOYMINTERVAL takes only YEAR or MONTH
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (NUMTOYMINTERVAL(1, 'HOUR'))
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
# 1.5 day interval truncated to +1d, +2d, +1d, +2d, ...
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 36 Hour
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 36 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 36 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-30'0
p1 RANGE COLUMNS `c` '2026-05-01'0
p2 RANGE COLUMNS `c` '2026-05-03'1
p3 RANGE COLUMNS `c` '2026-05-04'0
p4 RANGE COLUMNS `c` '2026-05-06'0
p5 RANGE COLUMNS `c` '2026-05-07'0
drop table t1;
# DATE column with 1 week interval
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Week
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 7 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 7 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-11') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-20'0
p1 RANGE COLUMNS `c` '2026-04-27'0
p2 RANGE COLUMNS `c` '2026-05-04'1
p3 RANGE COLUMNS `c` '2026-05-11'0
drop table t1;
# DATETIME column with 1 hour interval
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 hour
(
PARTITION p0 VALUES LESS THAN ('2026-05-05')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB)
insert into t1 values ('2026-05-05 03:00:00');
select * from t1;
c 2026-05-0503:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-05 01:00:00') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-05 02:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-05 03:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-05 04:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-05 05:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-05 06:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-05 07:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-05 08:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-05 09:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-05 10:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-05 11:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-05 12:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-05 13:00:00') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-05 14:00:00') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05 15:00:00') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-05 16:00:00') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-05 17:00:00') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-05-05 18:00:00') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-05-05 19:00:00') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-05-05 20:00:00') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-05-05 21:00:00') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-05-05 22:00:00') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-05-05 23:00:00') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-05-06 01:00:00') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-05-05'0
p1 RANGE COLUMNS `c` '2026-05-05 01:00:00'0
p2 RANGE COLUMNS `c` '2026-05-05 02:00:00'0
p3 RANGE COLUMNS `c` '2026-05-05 03:00:00'0
p4 RANGE COLUMNS `c` '2026-05-05 04:00:00'1
p5 RANGE COLUMNS `c` '2026-05-05 05:00:00'0
p6 RANGE COLUMNS `c` '2026-05-05 06:00:00'0
p7 RANGE COLUMNS `c` '2026-05-05 07:00:00'0
p8 RANGE COLUMNS `c` '2026-05-05 08:00:00'0
p9 RANGE COLUMNS `c` '2026-05-05 09:00:00'0
p10 RANGE COLUMNS `c` '2026-05-05 10:00:00'0
p11 RANGE COLUMNS `c` '2026-05-05 11:00:00'0
p12 RANGE COLUMNS `c` '2026-05-05 12:00:00'0
p13 RANGE COLUMNS `c` '2026-05-05 13:00:00'0
p14 RANGE COLUMNS `c` '2026-05-05 14:00:00'0
p15 RANGE COLUMNS `c` '2026-05-05 15:00:00'0
p16 RANGE COLUMNS `c` '2026-05-05 16:00:00'0
p17 RANGE COLUMNS `c` '2026-05-05 17:00:00'0
p18 RANGE COLUMNS `c` '2026-05-05 18:00:00'0
p19 RANGE COLUMNS `c` '2026-05-05 19:00:00'0
p20 RANGE COLUMNS `c` '2026-05-05 20:00:00'0
p21 RANGE COLUMNS `c` '2026-05-05 21:00:00'0
p22 RANGE COLUMNS `c` '2026-05-05 22:00:00'0
p23 RANGE COLUMNS `c` '2026-05-05 23:00:00'0
p24 RANGE COLUMNS `c` '2026-05-06 00:00:00'0
p25 RANGE COLUMNS `c` '2026-05-06 01:00:00'0
drop table t1;
# DATETIME column with some decimal points in partition range value
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 hour
(
PARTITION p0 VALUES LESS THAN ('2026-05-05 01:23:45.6789')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-05 01:23:45.6789') ENGINE = InnoDB)
insert into t1 values ('2026-05-05 03:00:00');
select * from t1;
c 2026-05-0503:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-05 01:23:45.6789') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-05 02:23:45.6789') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-05 03:23:45.6789') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-05 04:23:45.6789') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-05 05:23:45.6789') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-05 06:23:45.6789') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-05 07:23:45.6789') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-05 08:23:45.6789') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-05 09:23:45.6789') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-05 10:23:45.6789') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-05 11:23:45.6789') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-05 12:23:45.6789') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-05 13:23:45.6789') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-05 14:23:45.6789') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-05 15:23:45.6789') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05 16:23:45.6789') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-05 17:23:45.6789') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-05 18:23:45.6789') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-05-05 19:23:45.6789') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-05-05 20:23:45.6789') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-05-05 21:23:45.6789') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-05-05 22:23:45.6789') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-05-05 23:23:45.6789') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-05-06 00:23:45.6789') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-05-05 01:23:45.6789'0
p1 RANGE COLUMNS `c` '2026-05-05 02:23:45.6789'0
p2 RANGE COLUMNS `c` '2026-05-05 03:23:45.6789'1
p3 RANGE COLUMNS `c` '2026-05-05 04:23:45.6789'0
p4 RANGE COLUMNS `c` '2026-05-05 05:23:45.6789'0
p5 RANGE COLUMNS `c` '2026-05-05 06:23:45.6789'0
p6 RANGE COLUMNS `c` '2026-05-05 07:23:45.6789'0
p7 RANGE COLUMNS `c` '2026-05-05 08:23:45.6789'0
p8 RANGE COLUMNS `c` '2026-05-05 09:23:45.6789'0
p9 RANGE COLUMNS `c` '2026-05-05 10:23:45.6789'0
p10 RANGE COLUMNS `c` '2026-05-05 11:23:45.6789'0
p11 RANGE COLUMNS `c` '2026-05-05 12:23:45.6789'0
p12 RANGE COLUMNS `c` '2026-05-05 13:23:45.6789'0
p13 RANGE COLUMNS `c` '2026-05-05 14:23:45.6789'0
p14 RANGE COLUMNS `c` '2026-05-05 15:23:45.6789'0
p15 RANGE COLUMNS `c` '2026-05-05 16:23:45.6789'0
p16 RANGE COLUMNS `c` '2026-05-05 17:23:45.6789'0
p17 RANGE COLUMNS `c` '2026-05-05 18:23:45.6789'0
p18 RANGE COLUMNS `c` '2026-05-05 19:23:45.6789'0
p19 RANGE COLUMNS `c` '2026-05-05 20:23:45.6789'0
p20 RANGE COLUMNS `c` '2026-05-05 21:23:45.6789'0
p21 RANGE COLUMNS `c` '2026-05-05 22:23:45.6789'0
p22 RANGE COLUMNS `c` '2026-05-05 23:23:45.6789'0
p23 RANGE COLUMNS `c` '2026-05-06 00:23:45.6789'0
drop table t1;
# more than 1 starting partition
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20'),
PARTITION p1 VALUES LESS THAN ('2026-04-25')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-0100:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-26 00:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-27 00:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-04 00:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-05 00:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
drop table t1;
# no need to create new partition when existing ones are sufficient
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01'),
PARTITION p1 VALUES LESS THAN ('2026-05-06')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-0100:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
drop table t1;
# find a gap big enough for names of 10 new partition names
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p9 VALUES LESS THAN ('2026-04-01'),
PARTITION p29 VALUES LESS THAN ('2026-04-04'),
PARTITION p19 VALUES LESS THAN ('2026-04-23'),
PARTITION p40 VALUES LESS THAN ('2026-04-27')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p9` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-04') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p40` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-0100:00:00
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p9` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-04') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p40` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-04 00:00:00') ENGINE = InnoDB,
PARTITION `p37` VALUES LESS THAN ('2026-05-05 00:00:00') ENGINE = InnoDB,
PARTITION `p38` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p39` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
drop table t1;
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p40 VALUES LESS THAN ('2026-04-27')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p40` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
select * from t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p40` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1;
# INSERT ... SELECT
select to_days('2026-04-01') = 740072;
to_days('2026-04-01') = 740072 1
select to_days('2026-05-01') = 740102;
to_days('2026-05-01') = 740102 1
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01')
);
insert into t1 select from_days(seq) from seq_740073_to_740102;
select * from t1;
c 2026-04-02 2026-04-03 2026-04-04 2026-04-05 2026-04-06 2026-04-07 2026-04-08 2026-04-09 2026-04-10 2026-04-11 2026-04-12 2026-04-13 2026-04-14 2026-04-15 2026-04-16 2026-04-17 2026-04-18 2026-04-19 2026-04-20 2026-04-21 2026-04-22 2026-04-23 2026-04-24 2026-04-25 2026-04-26 2026-04-27 2026-04-28 2026-04-29 2026-04-30 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-02') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-03') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-05') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-06') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-07') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-08') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-09') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-10') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-11') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-04-12') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-13') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-04-14') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-04-15') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-04-16') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-04-17') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-04-18') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-04-19') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-01'0
p1 RANGE COLUMNS `c` '2026-04-02'0
p2 RANGE COLUMNS `c` '2026-04-03'1
p3 RANGE COLUMNS `c` '2026-04-04'1
p4 RANGE COLUMNS `c` '2026-04-05'1
p5 RANGE COLUMNS `c` '2026-04-06'1
p6 RANGE COLUMNS `c` '2026-04-07'1
p7 RANGE COLUMNS `c` '2026-04-08'1
p8 RANGE COLUMNS `c` '2026-04-09'1
p9 RANGE COLUMNS `c` '2026-04-10'1
p10 RANGE COLUMNS `c` '2026-04-11'1
p11 RANGE COLUMNS `c` '2026-04-12'1
p12 RANGE COLUMNS `c` '2026-04-13'1
p13 RANGE COLUMNS `c` '2026-04-14'1
p14 RANGE COLUMNS `c` '2026-04-15'1
p15 RANGE COLUMNS `c` '2026-04-16'1
p16 RANGE COLUMNS `c` '2026-04-17'1
p17 RANGE COLUMNS `c` '2026-04-18'1
p18 RANGE COLUMNS `c` '2026-04-19'1
p19 RANGE COLUMNS `c` '2026-04-20'1
p20 RANGE COLUMNS `c` '2026-04-21'1
p21 RANGE COLUMNS `c` '2026-04-22'1
p22 RANGE COLUMNS `c` '2026-04-23'1
p23 RANGE COLUMNS `c` '2026-04-24'1
p24 RANGE COLUMNS `c` '2026-04-25'1
p25 RANGE COLUMNS `c` '2026-04-26'1
p26 RANGE COLUMNS `c` '2026-04-27'1
p27 RANGE COLUMNS `c` '2026-04-28'1
p28 RANGE COLUMNS `c` '2026-04-29'1
p29 RANGE COLUMNS `c` '2026-04-30'1
p30 RANGE COLUMNS `c` '2026-05-01'1
p31 RANGE COLUMNS `c` '2026-05-02'1
p32 RANGE COLUMNS `c` '2026-05-03'0
p33 RANGE COLUMNS `c` '2026-05-04'0
p34 RANGE COLUMNS `c` '2026-05-05'0
p35 RANGE COLUMNS `c` '2026-05-06'0
p36 RANGE COLUMNS `c` '2026-05-07'0
drop table t1;
select to_days('2026-05-01') = 740102;
to_days('2026-05-01') = 740102 1
select to_days('2026-05-08') = 740109;
to_days('2026-05-08') = 740109 1
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01')
);
insert into t1 select from_days(seq) from seq_740102_to_740109; ERROR HY000: Table has no partition for value from column_list
select * from t1;
c
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-02') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-03') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-05') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-06') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-07') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-08') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-09') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-10') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-11') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-04-12') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-13') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-04-14') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-04-15') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-04-16') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-04-17') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-04-18') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-04-19') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
drop table t1;
# LOAD DATA INFILE
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
load data infile 'load.data' into table t1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
p12 RANGE COLUMNS `c` '2026-05-02'1
p13 RANGE COLUMNS `c` '2026-05-03'1
p14 RANGE COLUMNS `c` '2026-05-04'1
p15 RANGE COLUMNS `c` '2026-05-05'1
p16 RANGE COLUMNS `c` '2026-05-06'1
drop table t1;
# LOAD DATA INFILE ... REPLACE set timestamp= unix_timestamp('2026-05-02 00:00:00');
create table t1 (c date key, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
insert into t1 values ('2026-05-02', 1); set timestamp= unix_timestamp('2026-05-06 00:00:00');
load data infile 'load.data'replace into table t1 FIELDS TERMINATED BY ',';
select * from t1;
c d 2026-05-023
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date NOT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1;
# LOAD DATA INFILE failure for future dates
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
load data infile 'load.data' into table t1; ERROR HY000: Table has no partition for value from column_list
select * from t1;
c
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
drop table t1;
# LOAD DATA INFILE IGNORE for future dates
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
load data infile 'load.data' ignore into table t1;
Warnings:
Warning 1526 Table has no partition for value from column_list
Warning 1526 Table has no partition for value from column_list
select * from t1;
c 2026-05-05 2026-05-06
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
p16 RANGE COLUMNS `c` '2026-05-06'1
p17 RANGE COLUMNS `c` '2026-05-07'1
drop table t1;
# UPDATE set timestamp= unix_timestamp('2026-05-02 00:00:00');
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 values ('2026-04-28'), ('2026-04-29'), ('2026-04-30');
select * from t1;
c 2026-04-28 2026-04-29 2026-04-30
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
p4 RANGE COLUMNS `c` '2026-04-29'1
p5 RANGE COLUMNS `c` '2026-04-30'1
p6 RANGE COLUMNS `c` '2026-05-01'1 set timestamp= unix_timestamp('2026-05-06 00:00:00');
update t1 set c = date_add(c, interval 6 day);
select * from t1;
c 2026-05-04 2026-05-05 2026-05-06
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
p10 RANGE COLUMNS `c` '2026-05-05'1
p11 RANGE COLUMNS `c` '2026-05-06'1
p12 RANGE COLUMNS `c` '2026-05-07'1 set timestamp= unix_timestamp('2026-05-08 00:00:00');
update t1 set c = date_add(c, interval 3 day); ERROR HY000: Table has no partition for value from column_list
select * from t1;
c 2026-05-04 2026-05-05 2026-05-06
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-08') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description
p10 RANGE COLUMNS `c` '2026-05-05'
p11 RANGE COLUMNS `c` '2026-05-06'
p12 RANGE COLUMNS `c` '2026-05-07' set timestamp= unix_timestamp('2026-05-06 00:00:00');
drop table t1;
# ALTER TABLE, no interval => have interval
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 values ('2026-05-01'); ERROR HY000: Table has no partition for value from column_list
ALTER TABLE t1 PARTITION BY RANGE COLUMNS (c) INTERVAL 1 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 values ('2026-05-01');
SELECT * FROM t1;
c 2026-05-01
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name partition_method partition_expression partition_description table_rows
p7 RANGE COLUMNS `c` '2026-05-02'1
drop table t1;
# ALTER TABLE, have interval => no interval
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB)
alter table t1
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`)
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB)
insert into t1 values ('2026-05-01'); ERROR HY000: Table has no partition for value from column_list
drop table t1;
# ALTER TABLE, adding a partition to a RANGE COLUMNS INTERVAL
# partitioned table
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
ALTER TABLE t1 ADD PARTITION (PARTITION p1 VALUES LESS THAN ('2026-04-27'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB)
insert into t1 values ('2026-05-01');
ALTER TABLE t1 ADD PARTITION (PARTITION p6 VALUES LESS THAN ('2026-05-02')); ERROR HY000: VALUES LESS THAN value must be strictly increasing for each partition
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB)
ALTER TABLE t1 ADD PARTITION (PARTITION p7 VALUES LESS THAN ('2026-05-10'));
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-10') ENGINE = InnoDB)
ALTER TABLE t1 ADD PARTITION (PARTITION p8 VALUES LESS THAN MAXVALUE); ERROR HY000: MAXVALUE is not allowed in range partitioning with interval
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-10') ENGINE = InnoDB)
drop table t1;
# with subpartitions.
# note that only [LINEAR] KEY and [LINEAR] HASH are allowed
# subpartitioning methods
select to_days('2026-04-20') = 740091;
to_days('2026-04-20') = 740091 1
select to_days('2026-05-01') = 740102;
to_days('2026-05-01') = 740102 1
create table t1 (c date, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
SUBPARTITION BY KEY ALGORITHM=XXH3 (d) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 select from_days(seq), seq from seq_740091_to_740102;
select * from t1;
c d 2026-04-21740092 2026-04-23740094 2026-04-24740095 2026-04-20740091 2026-04-22740093 2026-04-25740096 2026-04-27740098 2026-04-26740097 2026-04-30740101 2026-04-28740099 2026-04-29740100 2026-05-01740102
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL,
`d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
SUBPARTITION BY KEY ALGORITHM = XXH3 (`d`)
SUBPARTITIONS 2
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select partition_name, subpartition_name, partition_method, partition_description, table_rows from information_schema.partitions where table_name='t1'and table_rows > 0;
partition_name subpartition_name partition_method partition_description table_rows
p0 p0sp0 RANGE COLUMNS '2026-04-25'3
p0 p0sp1 RANGE COLUMNS '2026-04-25'2
p1 p1sp0 RANGE COLUMNS '2026-04-28'2
p1 p1sp1 RANGE COLUMNS '2026-04-28'1
p2 p2sp0 RANGE COLUMNS '2026-05-01'1
p2 p2sp1 RANGE COLUMNS '2026-05-01'2
p3 p3sp0 RANGE COLUMNS '2026-05-04'1
drop table t1;
# with an index
select to_days('2026-04-20') = 740091;
to_days('2026-04-20') = 740091 1
select to_days('2026-05-01') = 740102;
to_days('2026-05-01') = 740102 1
create table t1 (c date key, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 select from_days(seq), seq from seq_740091_to_740102;
select * from t1;
c d 2026-04-20740091 2026-04-21740092 2026-04-22740093 2026-04-23740094 2026-04-24740095 2026-04-25740096 2026-04-26740097 2026-04-27740098 2026-04-28740099 2026-04-29740100 2026-04-30740101 2026-05-01740102
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date NOT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1;
# REPLACE
select to_days('2026-04-15') = 740086;
to_days('2026-04-15') = 740086 1
select to_days('2026-04-24') = 740095;
to_days('2026-04-24') = 740095 1
create table t1 (c date key, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 select from_days(seq), seq from seq_740086_to_740095;
select * from t1;
c d 2026-04-15740086 2026-04-16740087 2026-04-17740088 2026-04-18740089 2026-04-19740090 2026-04-20740091 2026-04-21740092 2026-04-22740093 2026-04-23740094 2026-04-24740095
alter table t1
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
); replace into t1 values ('2026-05-01', 123), ('2026-04-15', 456), ('2026-04-29', 789);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date NOT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select * from t1;
c d 2026-04-15456 2026-04-16740087 2026-04-17740088 2026-04-18740089 2026-04-19740090 2026-04-20740091 2026-04-21740092 2026-04-22740093 2026-04-23740094 2026-04-24740095 2026-04-29789 2026-05-01123
drop table t1;
# REPLACE ... SELECT
select to_days('2026-04-15') = 740086;
to_days('2026-04-15') = 740086 1
select to_days('2026-04-24') = 740095;
to_days('2026-04-24') = 740095 1
create table t1 (c date key, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 select from_days(seq), seq from seq_740086_to_740095;
select * from t1;
c d 2026-04-15740086 2026-04-16740087 2026-04-17740088 2026-04-18740089 2026-04-19740090 2026-04-20740091 2026-04-21740092 2026-04-22740093 2026-04-23740094 2026-04-24740095
alter table t1
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
); replace into t1 select date_add(c, interval 5 day), d from t1;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date NOT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
select * from t1;
c d 2026-04-15740086 2026-04-16740087 2026-04-17740088 2026-04-18740089 2026-04-19740090 2026-04-20740086 2026-04-21740087 2026-04-22740088 2026-04-23740089 2026-04-24740090 2026-04-25740091 2026-04-26740092 2026-04-27740093 2026-04-28740094 2026-04-29740095
drop table t1;
# INSERT .. ON DUPLICATE KEY UPDATE (ODKU) set timestamp= unix_timestamp('2026-05-02 00:00:00');
create table t1 (c date key, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
insert into t1 values ('2026-05-02', 1); set timestamp= unix_timestamp('2026-05-06 00:00:00');
insert into t1 values ('2026-05-02', 2)
on duplicate key update c = date_add(c, interval 4 day);
select * from t1;
c d 2026-05-061
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date NOT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1;
# multi-table update
select to_days('2026-04-10') = 740081;
to_days('2026-04-10') = 740081 1
select to_days('2026-04-15') = 740086;
to_days('2026-04-15') = 740086 1
select to_days('2026-04-20') = 740091;
to_days('2026-04-20') = 740091 1
select to_days('2026-04-24') = 740095;
to_days('2026-04-24') = 740095 1
create table t1 (c date, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-21')
);
create table t2 (c date, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
insert into t1 select from_days(seq), seq from seq_740081_to_740091;
insert into t2 select from_days(seq), seq from seq_740086_to_740095;
alter table t1
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
alter table t2
PARTITION BY RANGE COLUMNS (c)
INTERVAL 3 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
update t1, t2 set t1.c = date_add(t1.c, interval 10 day),
t2.c = date_add(t2.c, interval 10 day)
where t1.c = t2.c;
select * from t1;
c d 2026-04-10740081 2026-04-11740082 2026-04-12740083 2026-04-13740084 2026-04-14740085 2026-04-25740086 2026-04-26740087 2026-04-27740088 2026-04-28740089 2026-04-29740090 2026-04-30740091
select * from t2;
c d 2026-04-21740092 2026-04-22740093 2026-04-23740094 2026-04-24740095 2026-04-25740086 2026-04-26740087 2026-04-27740088 2026-04-28740089 2026-04-29740090 2026-04-30740091
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL,
`d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`c` date DEFAULT NULL,
`d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 3 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1, t2;
# cannot alter to a partition with a lower date even with interval if
# date does not fit. this is because auto partition creation only
# happens in DML
create table t1 (c date)
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
);
insert into t1 values ('2026-04-29');
alter table t1
PARTITION BY RANGE COLUMNS (c)
interval 3 day
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
); ERROR HY000: Table has no partition for value from column_list
drop table t1;
# failing insertion of dates in the future
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB)
insert into t1 values ('2026-05-07'); ERROR HY000: Table has no partition for value from column_list
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-02 00:00:00') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-03 00:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-04 00:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-05 00:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-06 00:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-07 00:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-08 00:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-09 00:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-10 00:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-11 00:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-04-12 00:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-13 00:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-04-14 00:00:00') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-04-15 00:00:00') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-04-16 00:00:00') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-04-17 00:00:00') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-04-18 00:00:00') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-04-19 00:00:00') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-20 00:00:00') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-04-21 00:00:00') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-04-22 00:00:00') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-04-23 00:00:00') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-04-24 00:00:00') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-04-25 00:00:00') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-04-26 00:00:00') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-04-27 00:00:00') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-04 00:00:00') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05 00:00:00') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
drop table t1;
# passthrough behaviour
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB)
begin;
insert into t1 values ('2026-05-01');
insert into t1 values ('2026-05-02');
select * from t1;
c 2026-05-0100:00:00 2026-05-0200:00:00
rollback;
select * from t1;
c
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-02 00:00:00') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-03 00:00:00') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-04 00:00:00') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-05 00:00:00') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-06 00:00:00') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-07 00:00:00') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-08 00:00:00') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-09 00:00:00') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-10 00:00:00') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-11 00:00:00') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-04-12 00:00:00') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-13 00:00:00') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-04-14 00:00:00') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-04-15 00:00:00') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-04-16 00:00:00') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-04-17 00:00:00') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-04-18 00:00:00') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-04-19 00:00:00') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-20 00:00:00') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-04-21 00:00:00') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-04-22 00:00:00') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-04-23 00:00:00') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-04-24 00:00:00') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-04-25 00:00:00') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-04-26 00:00:00') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-04-27 00:00:00') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-04-28 00:00:00') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-04-29 00:00:00') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-30 00:00:00') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-01 00:00:00') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-02 00:00:00') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-03 00:00:00') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-04 00:00:00') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05 00:00:00') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-06 00:00:00') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-07 00:00:00') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-04-01'0
p1 RANGE COLUMNS `c` '2026-04-02 00:00:00'0
p2 RANGE COLUMNS `c` '2026-04-03 00:00:00'0
p3 RANGE COLUMNS `c` '2026-04-04 00:00:00'0
p4 RANGE COLUMNS `c` '2026-04-05 00:00:00'0
p5 RANGE COLUMNS `c` '2026-04-06 00:00:00'0
p6 RANGE COLUMNS `c` '2026-04-07 00:00:00'0
p7 RANGE COLUMNS `c` '2026-04-08 00:00:00'0
p8 RANGE COLUMNS `c` '2026-04-09 00:00:00'0
p9 RANGE COLUMNS `c` '2026-04-10 00:00:00'0
p10 RANGE COLUMNS `c` '2026-04-11 00:00:00'0
p11 RANGE COLUMNS `c` '2026-04-12 00:00:00'0
p12 RANGE COLUMNS `c` '2026-04-13 00:00:00'0
p13 RANGE COLUMNS `c` '2026-04-14 00:00:00'0
p14 RANGE COLUMNS `c` '2026-04-15 00:00:00'0
p15 RANGE COLUMNS `c` '2026-04-16 00:00:00'0
p16 RANGE COLUMNS `c` '2026-04-17 00:00:00'0
p17 RANGE COLUMNS `c` '2026-04-18 00:00:00'0
p18 RANGE COLUMNS `c` '2026-04-19 00:00:00'0
p19 RANGE COLUMNS `c` '2026-04-20 00:00:00'0
p20 RANGE COLUMNS `c` '2026-04-21 00:00:00'0
p21 RANGE COLUMNS `c` '2026-04-22 00:00:00'0
p22 RANGE COLUMNS `c` '2026-04-23 00:00:00'0
p23 RANGE COLUMNS `c` '2026-04-24 00:00:00'0
p24 RANGE COLUMNS `c` '2026-04-25 00:00:00'0
p25 RANGE COLUMNS `c` '2026-04-26 00:00:00'0
p26 RANGE COLUMNS `c` '2026-04-27 00:00:00'0
p27 RANGE COLUMNS `c` '2026-04-28 00:00:00'0
p28 RANGE COLUMNS `c` '2026-04-29 00:00:00'0
p29 RANGE COLUMNS `c` '2026-04-30 00:00:00'0
p30 RANGE COLUMNS `c` '2026-05-01 00:00:00'0
p31 RANGE COLUMNS `c` '2026-05-02 00:00:00'0
p32 RANGE COLUMNS `c` '2026-05-03 00:00:00'0
p33 RANGE COLUMNS `c` '2026-05-04 00:00:00'0
p34 RANGE COLUMNS `c` '2026-05-05 00:00:00'0
p35 RANGE COLUMNS `c` '2026-05-06 00:00:00'0
p36 RANGE COLUMNS `c` '2026-05-07 00:00:00'0
drop table t1;
select to_days('2026-04-01') = 740072;
to_days('2026-04-01') = 740072 1
select to_days('2026-05-01') = 740102;
to_days('2026-05-01') = 740102 1
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01')
);
begin;
insert into t1 select from_days(seq) from seq_740073_to_740102;
select * from t1;
c 2026-04-02 2026-04-03 2026-04-04 2026-04-05 2026-04-06 2026-04-07 2026-04-08 2026-04-09 2026-04-10 2026-04-11 2026-04-12 2026-04-13 2026-04-14 2026-04-15 2026-04-16 2026-04-17 2026-04-18 2026-04-19 2026-04-20 2026-04-21 2026-04-22 2026-04-23 2026-04-24 2026-04-25 2026-04-26 2026-04-27 2026-04-28 2026-04-29 2026-04-30 2026-05-01
rollback;
select * from t1;
c
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-01') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-02') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-03') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-04') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-05') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-06') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-07') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-08') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-09') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-10') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-11') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-04-12') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-04-13') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-04-14') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-04-15') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-04-16') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-04-17') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-04-18') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-04-19') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
drop table t1;
# failures
create table t1 (c1 datetime, c2 datetime)
PARTITION BY RANGE COLUMNS (c1, c2)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-01', '2026-04-01')
); ERROR HY000: Too many fields in 'range interval partition fields'
create table t1 (c datetime)
PARTITION BY LIST COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES IN ('2026-04-01')
); ERROR HY000: LIST partition type does not support INTERVAL
create table t1 (c int)
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN (740072)
); ERROR HY000: Field 'c' is of a not allowed type for this type of partitioning
# timestamp
create table t1 (c timestamp) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 hour
(
PARTITION p0 VALUES LESS THAN ('2026-05-04 12:34:56.789')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-04 12:34:56.789') ENGINE = InnoDB)
insert into t1 values ('2026-05-05 10:00:00.123456');
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 HOUR
(PARTITION `p0` VALUES LESS THAN ('2026-05-04 12:34:56.789') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-05-04 13:34:56.789') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-05-04 14:34:56.789') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-05-04 15:34:56.789') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-05-04 16:34:56.789') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-05-04 17:34:56.789') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-05-04 18:34:56.789') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-05-04 19:34:56.789') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-05-04 20:34:56.789') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-05-04 21:34:56.789') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-05-04 22:34:56.789') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-04 23:34:56.789') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-05 00:34:56.789') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-05 01:34:56.789') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-05 02:34:56.789') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05 03:34:56.789') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-05 04:34:56.789') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-05 05:34:56.789') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-05-05 06:34:56.789') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-05-05 07:34:56.789') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-05-05 08:34:56.789') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-05-05 09:34:56.789') ENGINE = InnoDB,
PARTITION `p22` VALUES LESS THAN ('2026-05-05 10:34:56.789') ENGINE = InnoDB,
PARTITION `p23` VALUES LESS THAN ('2026-05-05 11:34:56.789') ENGINE = InnoDB,
PARTITION `p24` VALUES LESS THAN ('2026-05-05 12:34:56.789') ENGINE = InnoDB,
PARTITION `p25` VALUES LESS THAN ('2026-05-05 13:34:56.789') ENGINE = InnoDB,
PARTITION `p26` VALUES LESS THAN ('2026-05-05 14:34:56.789') ENGINE = InnoDB,
PARTITION `p27` VALUES LESS THAN ('2026-05-05 15:34:56.789') ENGINE = InnoDB,
PARTITION `p28` VALUES LESS THAN ('2026-05-05 16:34:56.789') ENGINE = InnoDB,
PARTITION `p29` VALUES LESS THAN ('2026-05-05 17:34:56.789') ENGINE = InnoDB,
PARTITION `p30` VALUES LESS THAN ('2026-05-05 18:34:56.789') ENGINE = InnoDB,
PARTITION `p31` VALUES LESS THAN ('2026-05-05 19:34:56.789') ENGINE = InnoDB,
PARTITION `p32` VALUES LESS THAN ('2026-05-05 20:34:56.789') ENGINE = InnoDB,
PARTITION `p33` VALUES LESS THAN ('2026-05-05 21:34:56.789') ENGINE = InnoDB,
PARTITION `p34` VALUES LESS THAN ('2026-05-05 22:34:56.789') ENGINE = InnoDB,
PARTITION `p35` VALUES LESS THAN ('2026-05-05 23:34:56.789') ENGINE = InnoDB,
PARTITION `p36` VALUES LESS THAN ('2026-05-06 00:34:56.789') ENGINE = InnoDB)
select partition_name, partition_method, partition_expression, partition_description, table_rows from information_schema.partitions where table_name='t1';
partition_name partition_method partition_expression partition_description table_rows
p0 RANGE COLUMNS `c` '2026-05-04 12:34:56.789'0
p1 RANGE COLUMNS `c` '2026-05-04 13:34:56.789'0
p2 RANGE COLUMNS `c` '2026-05-04 14:34:56.789'0
p3 RANGE COLUMNS `c` '2026-05-04 15:34:56.789'0
p4 RANGE COLUMNS `c` '2026-05-04 16:34:56.789'0
p5 RANGE COLUMNS `c` '2026-05-04 17:34:56.789'0
p6 RANGE COLUMNS `c` '2026-05-04 18:34:56.789'0
p7 RANGE COLUMNS `c` '2026-05-04 19:34:56.789'0
p8 RANGE COLUMNS `c` '2026-05-04 20:34:56.789'0
p9 RANGE COLUMNS `c` '2026-05-04 21:34:56.789'0
p10 RANGE COLUMNS `c` '2026-05-04 22:34:56.789'0
p11 RANGE COLUMNS `c` '2026-05-04 23:34:56.789'0
p12 RANGE COLUMNS `c` '2026-05-05 00:34:56.789'0
p13 RANGE COLUMNS `c` '2026-05-05 01:34:56.789'0
p14 RANGE COLUMNS `c` '2026-05-05 02:34:56.789'0
p15 RANGE COLUMNS `c` '2026-05-05 03:34:56.789'0
p16 RANGE COLUMNS `c` '2026-05-05 04:34:56.789'0
p17 RANGE COLUMNS `c` '2026-05-05 05:34:56.789'0
p18 RANGE COLUMNS `c` '2026-05-05 06:34:56.789'0
p19 RANGE COLUMNS `c` '2026-05-05 07:34:56.789'0
p20 RANGE COLUMNS `c` '2026-05-05 08:34:56.789'0
p21 RANGE COLUMNS `c` '2026-05-05 09:34:56.789'0
p22 RANGE COLUMNS `c` '2026-05-05 10:34:56.789'1
p23 RANGE COLUMNS `c` '2026-05-05 11:34:56.789'0
p24 RANGE COLUMNS `c` '2026-05-05 12:34:56.789'0
p25 RANGE COLUMNS `c` '2026-05-05 13:34:56.789'0
p26 RANGE COLUMNS `c` '2026-05-05 14:34:56.789'0
p27 RANGE COLUMNS `c` '2026-05-05 15:34:56.789'0
p28 RANGE COLUMNS `c` '2026-05-05 16:34:56.789'0
p29 RANGE COLUMNS `c` '2026-05-05 17:34:56.789'0
p30 RANGE COLUMNS `c` '2026-05-05 18:34:56.789'0
p31 RANGE COLUMNS `c` '2026-05-05 19:34:56.789'0
p32 RANGE COLUMNS `c` '2026-05-05 20:34:56.789'0
p33 RANGE COLUMNS `c` '2026-05-05 21:34:56.789'0
p34 RANGE COLUMNS `c` '2026-05-05 22:34:56.789'0
p35 RANGE COLUMNS `c` '2026-05-05 23:34:56.789'0
p36 RANGE COLUMNS `c` '2026-05-06 00:34:56.789'0
drop table t1;
# year is not allowed in range column partitioning
create table t1 (c year) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 minute
(
PARTITION p0 VALUES LESS THAN ('2025')
); ERROR HY000: Field 'c' is of a not allowed type for this type of partitioning
# interval less than a day for date column
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL '23.59.59' HOUR_SECOND
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR HY000: RANGE COLUMN partition by a DATE with INTERVAL smaller than date
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL '23.59.60' HOUR_SECOND
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
drop table t1;
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL '24.00.00' HOUR_SECOND
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
drop table t1;
# bad interval values
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL -1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 0 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (NUMTODSINTERVAL(0, 'DAY'))
(
PARTITION p0 VALUES LESS THAN ('2026-04-30')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1.1 SECOND_MICROSECOND
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p1 VALUES LESS THAN ('2026-04-01'),
PARTITION p0 VALUES LESS THAN MAXVALUE
); ERROR HY000: MAXVALUE is not allowed in range partitioning with interval
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day; ERROR HY000: For RANGE partitions each partition must be defined
create table t1 (c datetime) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Second
(
PARTITION p1 VALUES LESS THAN ('2026-04-01')
);
insert into t1 values ('2026-01-01'); ERROR HY000: Too many partitions (including subpartitions) were defined
drop table t1;
# MDEV-39807 Auto creating partition out of range SET timestamp=UNIX_TIMESTAMP('2026-05-02 00:00:00');
CREATE TABLE t1 (c DATETIME) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (c)
INTERVAL 999999999 DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
INSERT INTO t1 VALUES ('2026-05-01'); ERROR22003: partition range value is out of range in 'INTERVAL'
DROP TABLE t1;
## DATE has an extra check for finer time, make sure no
## overflow happens there
create table t1 (c date)
PARTITION BY RANGE COLUMNS (c)
INTERVAL 18446744073709551615 HOUR
(
PARTITION p0 VALUES LESS THAN ('2026-04-25')
);
INSERT INTO t1 VALUES ('2026-05-01'); ERROR22003: partition range value is out of range in 'INTERVAL'
DROP TABLE t1;
create table NUMTODSINTERVAL(NUMTODSINTERVAL int);
drop table NUMTODSINTERVAL;
#
# MDEV-40048 Prelocking with range interval auto partitioning
#
# Trigger / PRELOCK_ROUTINE
create table t1 (c date)
partition by range columns (c) interval 1 day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
create table t2 (x int);
create trigger tr after delete on t2 for each row
insert into t1 values (now());
insert into t2 values (2),(3);
delete from t2;
select * from t1;
c 2026-05-02 2026-05-02
drop table t1, t2;
# Trigger + LOCK TABLES + SP / PS set timestamp= unix_timestamp('2026-04-25 00:00:00');
create table t1 (c date)
partition by range columns (c) interval 1 day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
insert into t1 values ('2026-04-25');
create table t2 (x int);
insert into t2 values (1),(2),(3),(4),(5),(6);
create trigger tr after delete on t2 for each row
insert into t1 values (now());
create procedure sp() delete from t2 where x < 4;
prepare ps from 'delete from t2 where x > 3';
lock tables t1 write, t2 write; set timestamp= unix_timestamp('2026-05-02 00:00:00');
call sp;
call sp; set timestamp= unix_timestamp('2026-05-06 00:00:00');
execute ps;
execute ps;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = MyISAM,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = MyISAM,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = MyISAM,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = MyISAM,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = MyISAM,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = MyISAM,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = MyISAM,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = MyISAM,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = MyISAM,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = MyISAM,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = MyISAM,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = MyISAM,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = MyISAM,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = MyISAM,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = MyISAM,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = MyISAM,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = MyISAM,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = MyISAM)
unlock tables;
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = MyISAM,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = MyISAM,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = MyISAM,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = MyISAM,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = MyISAM,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = MyISAM,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = MyISAM,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = MyISAM,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = MyISAM,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = MyISAM,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = MyISAM,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = MyISAM,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = MyISAM,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = MyISAM,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = MyISAM,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = MyISAM,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = MyISAM,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = MyISAM)
select * from t1;
c 2026-04-25 2026-05-02 2026-05-02 2026-05-02 2026-05-06 2026-05-06 2026-05-06
drop table t1, t2;
drop prepare ps;
drop procedure sp;
# Auto-create failed error set timestamp= unix_timestamp('2026-05-02 00:00:00');
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
call mtr.add_suppression("Error number .*(File exists|file operation)");
call mtr.add_suppression("InnoDB: The file '.*test/t1#P#p1\\.ibd' already exists");
insert into t1 values ('2026-04-22'); ERROR HY000: Got error184"Tablespace already exists" from storage engine InnoDB
show warnings;
Level Code Message Error1030 Got error184"Tablespace already exists" from storage engine InnoDB
Warning 4268 Range partition table %s.%s: adding INTERVAL partition(s) failed
drop table t1;
# LOCK TABLE
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
lock tables t1 write;
insert into t1 values ('2026-04-25'); set timestamp= unix_timestamp('2026-05-06 00:00:00');
update t1 set c= date_add(c, interval 10 day);
unlock tables;
select * from t1;
c 2026-05-05
drop table t1;
# LOCK TABLES + VIEW set timestamp= unix_timestamp('2026-04-25 00:00:00');
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
create view v1 as select * from t1;
insert into t1 values ('2026-04-22'); set timestamp= unix_timestamp('2026-05-02 00:00:00');
update v1 set c= date_add(c, interval 10 day);
select * from t1;
c 2026-05-02
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB) set timestamp= unix_timestamp('2026-05-06 00:00:00');
lock tables v1 write;
update v1 set c= date_add(c, interval 3 day);
select * from t1;
c 2026-05-05
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
unlock tables;
drop view v1;
drop tables t1;
# LOCK TABLES + PS / SP set timestamp= unix_timestamp('2026-04-25 00:00:00');
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
insert into t1 values ('2026-04-25'); set timestamp= unix_timestamp('2026-04-28 01:00:00');
execute immediate 'update t1 set c = date_add(c, interval 3 day)';
select * from t1;
c 2026-04-28
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB)
prepare s from 'update t1 set c= date_add(c, interval 2 day)'; set timestamp= unix_timestamp('2026-04-30 02:00:00');
execute s; set timestamp= unix_timestamp('2026-05-02 02:00:00');
execute s;
select * from t1;
c 2026-05-02
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB) set timestamp= unix_timestamp('2026-05-06 03:00:00');
lock tables t1 write;
execute s;
execute s;
select * from t1;
c 2026-05-06
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB)
unlock tables;
drop prepare s;
create procedure sp() update t1 set c= date_add(c, interval 1 day); set timestamp= unix_timestamp('2026-05-08 04:00:00');
call sp;
call sp;
select * from t1;
c 2026-05-08
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-05-08') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB) set timestamp= unix_timestamp('2026-05-09 05:00:00');
lock tables t1 write;
call sp; set timestamp= unix_timestamp('2026-05-10 05:00:00');
call sp;
select * from t1;
c 2026-05-10
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2026-04-27') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2026-04-28') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2026-04-29') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2026-04-30') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2026-05-01') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2026-05-02') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2026-05-03') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2026-05-04') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2026-05-05') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2026-05-06') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2026-05-07') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2026-05-08') ENGINE = InnoDB,
PARTITION `p19` VALUES LESS THAN ('2026-05-09') ENGINE = InnoDB,
PARTITION `p20` VALUES LESS THAN ('2026-05-10') ENGINE = InnoDB,
PARTITION `p21` VALUES LESS THAN ('2026-05-11') ENGINE = InnoDB)
unlock tables;
drop procedure sp;
drop table t1;
# create function set timestamp= unix_timestamp('2026-04-25 00:00:00');
create table t1 (c date, d int) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
create table t2 (c int key, d int);
insert into t2 values (42, 24);
create function f(n int) returns int
begin
insert into t1 values(now(), 123); return (select d from t2 where c=n);
end|
select * from t2 where d = f(42);
c d 4224
select * from t1;
c d 2026-04-25123
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL,
`d` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2026-04-22') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2026-04-23') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2026-04-24') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2026-04-25') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2026-04-26') ENGINE = InnoDB)
DROP TABLE t1, t2;
drop function f;
# update one table while reading an auto-partitioned table set timestamp= unix_timestamp('2026-04-20 00:00:00');
create table t1 (c date);
insert into t1 values ('2026-04-20');
create table t2 (d date)
PARTITION BY RANGE COLUMNS (d)
INTERVAL 1 Day
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
insert into t2 select c from t1;
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`d`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = MyISAM,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = MyISAM) set timestamp= unix_timestamp('2026-04-25 00:00:00');
update t1 set c = date_add(c, interval 1 day) where c = (select max(d) from t2);
show create table t2;
Table Create Table
t2 CREATE TABLE `t2` (
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`d`) INTERVAL 1 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = MyISAM,
PARTITION `p1` VALUES LESS THAN ('2026-04-21') ENGINE = MyISAM)
DROP TABLE t1, t2;
#
# MDEV-40088 Assertion `fixed()' failed with INTERVAL (SELECT 1) DAY
#
# Existing error should remain the same
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
(
PARTITION p0 VALUES LESS THAN ((select '2026-04-20'))
); ERROR HY000: This partition function is not allowed
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (SELECT 1) DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR42000: INTERVAL does not support subqueries or stored functions
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL (1 + 2 + 3) DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
);
show create table t1;
Table Create Table
t1 CREATE TABLE `t1` (
`c` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
PARTITION BY RANGE COLUMNS(`c`) INTERVAL 6 DAY
(PARTITION `p0` VALUES LESS THAN ('2026-04-20') ENGINE = InnoDB)
drop table t1;
# Existing error should remain the same, even if subqueries are
# banned for INTERVAL
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL 1 DAY
(
PARTITION p0 VALUES LESS THAN ((select '2026-04-20'))
); ERROR HY000: This partition function is not allowed
create table t1 (c date) engine=innodb
PARTITION BY RANGE COLUMNS (c)
INTERVAL "hello" DAY
(
PARTITION p0 VALUES LESS THAN ('2026-04-20')
); ERROR HY000: Wrong parameters for partitioned `t1`: wrong value for 'INTERVAL'
Messung V0.5 in Prozent
¤ Dauer der Verarbeitung: 0.63 Sekunden
(vorverarbeitet am 2026-10-08)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung und die Messung sind noch experimentell.
