mysql按照时间自动创建分区表_mysql 表分区、按时间函数分区、删除分区、自动添加表分区...

mysql 表分区的几种方式：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

常用的按时间分区可以按照年、月、日等条件用来分区，因为是按时间条件分区所以选用RANGE分区方式。

分区的切条件判断：

LESS THAN：如果是数值就是小于等于，时间则是小于。

LESS THAN MAX:不等于。

IN:包含于某某区间。

按实际分区的时间函数选用：

按日分区切分时的条件可以用day()、to_days()时间函数，需要注意的是必须是返回值是整形的。

按月分区采用month()时间函数;

其他条件分区时间函数可到我关于时间函数的博文查看：https://blog.csdn.net/qq_36216193/article/details/90404527

其中按时间字段create_date按天分区，首先需要把date字段改成主键，后添加分区切分规则

ALTER TABLE js_sys_log

DROP PRIMARY KEY,

ADD PRIMARY KEY (id, create_date); --之前id是主键，所以这里第2主键为联合主键

ALTER TABLE js_sys_log ADD PRIMARY KEY (create_date);

1

2

3

4

5

为了便于管理分区名通常以“p时间”来命名。

示例一

select PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'table';：

alter table table PARTITION by RANGE (created_at)(

PARTITION p20170101 values less than (UNIX_TIMESTAMP('2017-01-01')),

PARTITION p20180101 values less than (UNIX_TIMESTAMP('2018-01-01')),

PARTITION p20190101 values less than (UNIX_TIMESTAMP('2019-01-01')),

PARTITION p20200101 values less than (UNIX_TIMESTAMP('2020-01-01')),

PARTITION p20210101 values less than (UNIX_TIMESTAMP('2021-01-01')),

PARTITION pmax values less than (MAXVALUE)

);

后期添加分区：

示例二：

ALTER TABLE js_sys_log ADD PARTITION (PARTITION p20181111 VALUES LESS THAN (TO_DAYS ('2018-11-11')));

1

2

删除分区：

示例三：

ALTER TABLE js_sys_log DROP PARTITION p20181101;

1

2

查询MySQL的系统字典库得知所有的分区详情信息

示例四：

SELECT * FROM information_schema. PARTITIONS t WHERE t.PARTITION_NAME IS NOT NULL

1

2

在分区创建后可以通过过程和事件控制自动增加表分区。

1、查看是否开启事件

show variables like "event_scheduler";

1

2、开启事件(本次设置中生效)

SET GLOBAL event_scheduler = on;

或

SET GLOBAL event_scheduler = 1;

3、开启时间(长期生效)

编辑 mysql 配置文件，并在【mysqld】下添加该变量：

再重启 mysqld 服务， 登录 mysql ，查看配置，确认是否生效。

4、设置分区脚本

use test;

DELIMITER ||

-- 删除存储过程

drop procedure if exists 事件名称 ||

-- 注意：使用该存储过程必须保证相应数据库表中至少有一个手动分区

-- 创建存储过程[通过数据库名和对应表名]-建多少个分区，分区时间间隔为多少

-- databasename：创建分区的数据库

-- tablename：创建分区的表的名称

-- partition_number：一次创建多少个分区

-- partitiontype：分区类型[0按天分区，1按月分区，2按年分区]

-- gaps：分区间隔，如果分区类型为0则表示每个分区的间隔为 gaps天；

-- 如果分区类型为1则表示每个分区的间隔为 gaps月

-- 如果分区类型为2则表示每个分区的间隔为 gaps年

create procedure 事件名称 (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)

L_END:

begin

declare max_partition_description varchar(255) default '';

declare p_name varchar(255) default 0;

declare p_description varchar(255) default 0;

declare isexist_partition varchar(255) default 0;

declare i int default 1;

-- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]

select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename and table_name = tablename limit 1;

-- 如果不存在则打印错误并退出存储过程

if isexist_partition <=> "" then

select "partition table not is exist" as "ERROR";

leave L_END;

end if;

-- 获取最大[降序获取]的分区描述[值]

select partition_description into max_partition_description from information_schema.partitions where table_schema = databasename and table_name = tablename order by partition_description desc limit 1;

-- 如果最大分区没有,说明没有手动分区,则无法创建自动分区

if max_partition_description <=> "" then

select "partition table is error" as "ERROR";

leave L_END;

end if;

-- 替换前后的单引号[''两个引号表示一个单引号的转义]

-- set max_partition_description = REPLACE(max_partition_description, '''', '');

-- 或使用如下语句

set max_partition_description = REPLACE(max_partition_description-1, '\'', '');

-- 自动创建number个分区

while (i <= partition_number) do

if (partitiontype = 0) then

-- 每个分区按天递增,递增gaps天

set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day);

elseif (partitiontype = 1) then

-- 每个分区按月递增,递增gaps月

set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month);

else

-- 每个分区按年递增,递增gaps年

set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);

end if;

-- 删除空格

set p_name = REPLACE(p_description, ' ', '');

-- 例如10.20的记录实际是less than 10.21

set p_description = DATE_ADD(p_description, interval 1 day);

-- 如果有横杆替换为空

set p_name = REPLACE(p_name, '-', '');

-- 删除时间冒号

set p_name = REPLACE(p_name, ':', '');

-- alter table tablename add partition ( partition pname values less than ('2019-05-22 23:59:59') );

set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');

-- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');

-- 打印sql变量

-- select @sql;

-- 准备sql语句

PREPARE stmt from @sql;

-- 执行sql语句

EXECUTE stmt;

-- 释放资源

DEALLOCATE PREPARE stmt;

-- 递增变量

set i = (i + 1) ;

end while;

end ||

-- 恢复语句中断符

DELIMITER ;

5、事件处理(每天执行一次)

DELIMITER ||

drop event if exists 事件名称 ||

create event 事件名称

on schedule every 1 day

starts '2019-05-22 23:59:59'

do

BEGIN

call 事件名称 ('数据库名', '分区表名', 1, 0, 1);

END ||

DELIMITER ;

6、删除事件

DROP EVENT [IF EXISTS] 事件名称;

1

7、查看事件

show events;

1

8、表分区查看：

explain partitions select * from 分区表名;

原文链接：https://blog.csdn.net/qq_36216193/java/article/details/90412637