mysql 建表 月分区_Mysql按月创建表分区

前言

下面仅记录一次对空表的一次分区过程，以日志表为例。

CREATE TABLE `sys_log` (

`log_id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键ID',

`user_id` int(10) DEFAULT NULL COMMENT '用户ID',

`class_function` varchar(255) DEFAULT NULL COMMENT '类和方法名',

`function_id` int(10) DEFAULT NULL COMMENT '功能id',

`description` varchar(255) DEFAULT NULL COMMENT '功能名称',

`sub_desc` varchar(255) DEFAULT NULL COMMENT '子功能',

`ip` varchar(255) DEFAULT NULL,

`create_time` datetime DEFAULT NULL COMMENT '创建时间',

`type` int(10) DEFAULT NULL COMMENT '类型【1：异常 2：使用方法】',

PRIMARY KEY (`log_id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建联合主键

删除原有主键，创建联合主键，这一步你可以在建表的时候就创建好。

ALTER TABLE `sys_log` DROP PRIMARY KEY,ADD PRIMARY KEY (log_id, create_time);

手动创建分区

手动创建分区，这一步你也可以在建表的时候就创建好。

ALTER TABLE `sys_log` PARTITION by RANGE COLUMNS (create_time)

(PARTITION `p1` VALUES LESS THAN ('2019-01-01'),

PARTITION `p2` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);

创建存储过程

1.创建用于对单张表分区的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition`(in_tbname VARCHAR(64))

BEGIN

SELECT DATABASE() INTO @dbname;

SET @tbname = in_tbname;

#查询表的最近一次分区

SELECT

REPLACE (partition_name, 'p', '') INTO @PMAX

FROM

INFORMATION_SCHEMA.PARTITIONS

WHERE

TABLE_SCHEMA = @dbname

AND table_name = @tbname

ORDER BY

partition_ordinal_position DESC

LIMIT 1;

#查询表的最近一次分区

SELECT

REPLACE(partition_description, "'", '') INTO @DNAME

FROM

INFORMATION_SCHEMA.PARTITIONS

WHERE

TABLE_SCHEMA = @dbname

AND table_name = @tbname

ORDER BY

partition_ordinal_position DESC

LIMIT 1, 1;

SET @t=CONCAT('alter table `',@dbname,'`.',@tbname,' reorganize partition p',@PMAX,

' into(partition p',@PMAX,' values less than (''',date(DATE_ADD(@DNAME,INTERVAL 1 MONTH)),'''),',

'partition p',@PMAX+1,' values less than MAXVALUE)');

SELECT @t;

PREPARE stmt FROM @t;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

COMMIT;

END

2.创建存储过程调用单表分区存储过程对所有表进行分区

CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition_all`()

BEGIN

DECLARE tbname varchar(32);

DECLARE tmpSql varchar(256);

DECLARE done INT DEFAULT FALSE ;

#查询已手动分区的表

DECLARE part_cursor CURSOR FOR (SELECT DISTINCT table_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = DATABASE() AND partition_expression IS NOT NULL AND table_name NOT LIKE '%bak');

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

#循环对表添加分区

OPEN part_cursor;

myLoop: LOOP

FETCH part_cursor INTO tbname;

IF done THEN

LEAVE myLoop;

END IF;

#调用分区存储过程

CALL proc_create_partition(tbname);

COMMIT;

END LOOP myLoop;

CLOSE part_cursor;

END

创建事件

创建事件从2019年8月起每月1号调用存储过程给表添加分区

CREATE DEFINER=`root`@`%` EVENT `e_create_partition`

ON SCHEDULE EVERY 1 MONTH STARTS '2019-08-01 00:00:00'

ON COMPLETION PRESERVE DISABLE

DO CALL proc_create_partition_all()

参考：

https://blog.csdn.net/zhangkai19910815/article/details/96424294

https://blog.csdn.net/aofavx/article/details/50393281

https://www.cnblogs.com/freeton/p/4265228.html

https://blog.csdn.net/aofavx/article/details/50378360