MySQL 查询最后分区

-- 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;