MySQL分区表语法解读_Mysql

mysql分区表语法

1.创建分区表

分区键需要和主键设置为复合主键，分区表不可直接转换成非分区表，需要重新建非分区表并导入数据

按年份

create table partitioned_table_year (
    id int,
    content varchar(50),
    created_time datetime,
                primary key (id,created_time)
) partition by range(year(created_time)) ( 
    partition p2024 values less than (2025),
    partition p2025 values less than (2026),
    partition p2026 values less than (2027)
);

按月份

create table partitioned_table_month (
    id int,
    content varchar(50),
    created_time datetime,
                primary key (id,created_time)
) partition by range  columns(created_time)  (  
    partition p202410 values less than ('2024-11-01'),
    partition p202411 values less than ('2024-12-01'), 
    partition p202412 values less than ('2025-01-01')
);

修改表结构，增加分区

alter table `partitioned_table_month` modify column `created_time` datetime(0) not null ,
drop primary key,
add primary key (`id`, `created_time`) using btree;

alter table partitioned_table_month partition by range columns(created_time) (  
    partition p202410 values less than ('2024-11-01'),
    partition p202411 values less than ('2024-12-01'), 
    partition p202412 values less than ('2025-01-01')
                 );

删除分区，注意：删除分区的时候会同时删除数据

alter table partitioned_table_month drop partition p202407,p202408;

2.查询

查看表分区

select  
    table_name,
    partition_name, 
    partition_method, 
    partition_expression, 
    partition_description,
    table_rows,
    avg_row_length,
    data_length,
    index_length 
from 
    information_schema.partitions
where 
    table_schema = 'xxx' and table_name = 'partitioned_table_month';

查看分区数据

select * from partitioned_table partition (p2024,p2025)

3.利用存储过程批量修改非分区表为分区表

创建联合主键存储过程，先设置联合主键字段非空，再删除原id去掉主键，再设置联合主键

delimiter $$
drop procedure if exists auto_create_pk$$
create procedure `auto_create_pk`(in `table_name` varchar(64),in `column_name` varchar(64),in `column_comment` varchar(64))
begin
	  set @sql = concat("alter table `",table_name,"` modify column `",column_name,"` datetime  not null comment '",column_comment,"',
		drop primary key,
		add primary key ( `id`, `",column_name,"` ) using btree;");
		prepare stmt from @sql;
		execute stmt;
		deallocate prepare stmt;
end$$ 
delimiter ;

创建按年自动分区存储过程

delimiter $$
drop procedure if exists auto_create_partition_year$$
create procedure `auto_create_partition_year`(in `table_name` varchar(64),in `column_name` varchar(64))
begin
		declare partitioned longtext;
		declare n int;
		set n = 2025;
		set partitioned = '';
		while n <= 2027 do
			set partitioned = concat(partitioned,",partition p",n," values less than (",n+1,")");
			set n = n + 1;
			end while;  
		set @sql = concat ("alter table ",table_name,"  partition by range(year(",column_name,")) (",substr(partitioned,2,length(partitioned)),");") ; 
		prepare stmt from @sql;
		execute stmt;
		deallocate prepare stmt;
end$$ 
delimiter ;

创建按月自动分区存储过程

delimiter $$
drop procedure if exists auto_create_partition_month$$
create procedure `auto_create_partition_month`(in `table_name` varchar(64),in `column_name` varchar(64))
begin
		declare partitioned longtext;
		declare n int;
		declare m int;
		set n = 2015;
		set partitioned = '';
		while n <= 2030 do
		set m = 1;
			while m < 12 do
				set partitioned = concat(partitioned,",partition p",n,lpad(m,2,0)," values less than ('",n,"-",lpad(m+1,2,0),"-01')");
				set m = m + 1;
			end while;  
			if m = 12 then 
				set partitioned = concat(partitioned,",partition p",n,"12 values less than ('",n+1,"-01-01')");
			end if;
			set n = n + 1;
			end while;   
		set @sql = concat ("alter table ",table_name,"  partition by range columns(",column_name,")  (",substr(partitioned,2,length(partitioned)),");") ; 
		prepare stmt from @sql;
		execute stmt;
		deallocate prepare stmt;
end$$ 
delimiter ;

-- 查询存储过程
show procedure status like 'auto_create_partition%';
-- 执行联合主键
call auto_create_pk('table_a','a_time','时间'); 
-- 执行按年自动分区
call auto_create_partition_year('table_b','b_time'); 
-- 执行按月自动分区
call auto_create_partition_month('table_c','c_time');