[数据库]MySQL创建分区表 按天自动分区

分区表 按天自动分区

创建测试数据库

create database csl_test character set utf8 collate utf8_unicode_ci;

创建测试表

注：要分区的字段 需要为主键。

use csl_test; # 切换到刚刚创建的测试数据库

create table t_partition_test (
    pk_id bigint(20) not null auto_increment,
    time datetime not null,
    msg varchar(200),
    primary key (pk_id,time)
)engine=innodb default charset=utf8 collate utf8_unicode_ci comment="test partition";

插入测试数据

insert into t_partition_test(time,msg) values
    ('2021-02-01 13:34:23',"20210201133423"),
    ('2021-02-02 14:23:13',"20210202142313");

手动分区

在自动分区前，需对表进行手动分区，这样自动分区才能进行。

分区字段应为主键包含字段，不可为索引。
可使用该命令修改主键字段
alter table t_partition_test drop primary key, add primary key (pk_id, time);

批量分区

alter table t_partition_test partition by range columns(time)(
    partition p20210201 values less than('2021-02-02'),
    partition p20210202 values less than('2021-02-03'),
    partition p20210203 values less than('2021-02-04')
);

单条分区

alter table t_partition_test add partition (partition p20210201 values less than ('2021-02-02') );

删除分区

由于数据是存放在分区中，所以删除分区 也会删除 对应分区的数据。

alter table t_partition_test drop partition p20210201;

查看表分区

select partition_name,partition_description as val from information_schema.partitions
where table_name = 't_partition_test' and table_schema = 'csl_test';

创建增加删除分区的存储过程

-- 定义mysql提交查询语句 结束标识 为 $$
delimiter $$
DROP PROCEDURE IF EXISTS p_partition_test # 创建过程 p_partition_test
$$
CREATE PROCEDURE p_partition_test()
BEGIN
  DECLARE v_sysdate date; # 声明 当前时间
  DECLARE v_mindate date; # 声明 目前分区的最小值
  DECLARE v_maxdate date; # 声明 目前分区值中的最大值
  DECLARE v_pt varchar(20); # 声明 分区名称 数字部分
  DECLARE v_maxval varchar(20); # 声明 最大值
  DECLARE i int; # 声明 ??
  
  -- 增加新分区
  -- select into A from B 用法；A表作为临时表，不存在。 
  -- 不同于insert into A select from B，A必须存在。
  SELECT max(cast(replace(partition_description, '''', '') AS date)) AS val
  INTO   v_maxdate
  FROM   INFORMATION_SCHEMA.PARTITIONS
  WHERE  TABLE_NAME = 't_partition_test' AND TABLE_SCHEMA = 'csl_test';
  
  set v_sysdate = sysdate(); # 赋值v_sysdate为当前时间
  
  -- INTERVAL 时间计算的关键字
  WHILE v_maxdate <= (v_sysdate + INTERVAL 3 DAY) DO
    SET v_pt = date_format(v_maxdate ,'%Y%m%d');
    SET v_maxval = date_format(v_maxdate + INTERVAL 1 DAY, '%Y-%m-%d');
    SET @sql = concat('alter table t_partition_test add partition (partition p', v_pt, ' values less than(''', v_maxval, '''))');
    -- SQL语句预处理，在对于反复调用相同或极度相似的语句，进行预处理会优化处理速度，我感觉这里的用法应该没有效果
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    -- 删除预处理
    DEALLOCATE PREPARE stmt;
    SET v_maxdate = v_maxdate + INTERVAL 1 DAY; # 最大值 加一操作
  END WHILE;
  
  --删除旧分区
  SELECT min(cast(replace(partition_description, '''', '') AS date)) AS val
  INTO   v_mindate
  FROM   INFORMATION_SCHEMA.PARTITIONS
  WHERE  TABLE_NAME = 't_partition_test' AND TABLE_SCHEMA = 'csl_test';
    
  -- 删除一年前的旧分区
  WHILE v_mindate <= (v_sysdate - INTERVAL 1 YEAR) DO
    SET v_pt = date_format(v_mindate - INTERVAL 1 DAY,'%Y%m%d');
    SET @sql = concat('alter table t_partition_test drop partition p', v_pt);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET v_mindate = v_mindate + INTERVAL 1 DAY;
  END WHILE;
 
END$$
-- 重新定义 MySQL提交查询语句的 结束标识 为 ;
delimiter ;

手动调用存储过程

call p_partition_test; # call p_partition_test();

开启事件

mysql默认是关闭定时任务

查看定时任务是否开启，OFF是没有开启：

show variables like '%event_scheduler%';
-- 或者
select @@event_scheduler;

临时开启定时任务（重启之后，配置会回复）

set global event_scheduler = 1;

永久开启，修改配置文件 my.cnf，

event_scheduler=ON;

创建event事件

delimiter $$
drop event if exists auto_pt $$
create event auto_pt
on schedule
-- every 1 minute
every 1 day
starts '2021-02-01 13:19:02'
do
BEGIN
    call p_partition_test();
END$$
delimiter ;