代码语言：javascript复制
-- MySQL 5.6
select sname,tname,pname,position,method,expression
from (
select if (table_schema=@sname and table_name=@tname, @a:=@a 1, @a:=0) rn,
@sname:=table_schema, @tname:=table_name,
table_schema sname,table_name tname,partition_name pname,
partition_ordinal_position position,
partition_method method,
partition_expression expression
from information_schema.partitions,(select @a:=0,@sname:='',@tname:='') t1
where partition_name is not null
order by table_schema,table_name,partition_ordinal_position desc) t where rn=0;
-- MySQL 8
select * from (
select table_schema sname,table_name tname,partition_name pname,
partition_ordinal_position position,
partition_method method,
partition_expression expression,
row_number() over (partition by table_schema, table_name order by partition_ordinal_position desc) rn
from information_schema.partitions
where partition_name is not null) t
where rn=1
order by sname,pname,position,tname;
-- 生成按天分区
select concat('partition p',adddate('2020-12-31',id) 0,' VALUES LESS THAN (',adddate('2020-12-31',id 1) 0,'),') from nums limit 365;
