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 '