## MySQL存储过程自动按年/月/日分表分区
#### PARTITION 创建分区
``` SQL
DROP PROCEDURE IF EXISTS `proc_create_partition`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition`(
p_table VARCHAR(128), -- 表名
p_date DATETIME, -- 生成指定起始日期
p_time_field VARCHAR(32), -- 时间字段
p_counts INT, -- 生成多少个分区
p_type VARCHAR(12) -- day按天 month 按月，year按年
)
BEGIN
SET @partition_table = 'p';
IF( p_time_field IS NULL ) THEN
SET p_time_field = 'pubtime'; -- 没有传参数，默认字段名 pubtime
END IF;
SET @has = NULL;
SELECT REPLACE(`partition_name`,@partition_table,'') INTO @has
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = p_table
ORDER BY partition_ordinal_position DESC LIMIT 1; -- 获得最大的时间分区表
SET @partition_names = '';
IF (@has IS NULL) THEN
SET @partition_names = CONCAT('ALTER TABLE ',p_table,' PARTITION BY RANGE (TO_DAYS(',p_time_field,'))');-- 新建
ELSE
SET @partition_names = CONCAT('ALTER TABLE ',p_table,' ADD PARTITION');-- 添加
END IF;
IF( p_counts IS NULL OR p_counts < 3) THEN
SET p_counts = 3; -- 没有传参数，默认生成指定时间后面三个的分区
END IF;
IF( p_date IS NULL ) THEN
SET p_date = now(); -- 没有传参数，默认从当前时间
END IF;
IF( p_type IS NULL ) THEN
SET p_type = 'month'; -- 没有传参数，默认从当前时间
END IF;
SET @total = p_counts;
SET @month_time = 0;
SET @months = '';
WHILE @month_time < @total DO -- 生成 当前，未来3次分区 （网页文本问题，‹ 不是小于号注意自行修改）
SET @time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL @month_time MONTH) ,'%Y%m'); -- 默认按月分区
SET @date_time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL (@month_time+1) MONTH) ,'%Y-%m-01');
IF(p_type='year') THEN -- 按年分区
SET @time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL @month_time YEAR) ,'%Y');
SET @date_time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL (@month_time+1) YEAR) ,'%Y-01-01');
END IF;
IF(p_type='day') THEN -- 按天分区
SET @time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL @month_time DAY) ,'%Y%m%d');
SET @date_time = DATE_FORMAT( DATE_ADD( p_date , INTERVAL (@month_time+1) DAY) ,'%Y-%m-%d');
END IF;
SET @day_has = NULL;
SELECT partition_name INTO @day_has
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_name = p_table AND partition_name = CONCAT('p',@time)
ORDER BY partition_ordinal_position DESC LIMIT 1; -- 查询是否存在分区
IF (@day_has IS NULL AND @total > @month_time ) THEN
SET @months = CONCAT(@months,'PARTITION ',@partition_table,@time,' VALUES LESS THAN (TO_DAYS (''',DATE(@date_time),''')),');
END IF;
SET @month_time = @month_time + 1;
END WHILE;
IF ( length(@months) > 0 AND SUBSTRING(@months,-1) = ',' ) THEN
SET @months = left(@months, length(@months)-1); -- 去掉末尾逗号
END IF;
SET @s1 = '';
IF (@months IS NOT NULL AND @months != '') THEN
SET @s1 = CONCAT(@partition_names,'(',@months,')');
-- 执行生成
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END IF;
SELECT @s1,@days,@has,@partition_names; -- 查看结果
END;;
DELIMITER;
-- 调用
CALL proc_create_partition('user',now(),'pubtime',3,'year') -- 生成user表未来三年分区
CALL proc_create_partition('user',now(),'pubtime',3,'month') -- 生成user表未来三个月分区
CALL proc_create_partition('user',now(),'pubtime',365,'day') -- 生成user表当前时间一整年365天分区
```
#### TABLE 创建分表
``` SQL
DROP PROCEDURE IF EXISTS `proc_create_partition_table`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `proc_create_partition_table`(
p_dbname varchar(32),-- 数据库
p_table varchar(128), -- 目标表名
p_tag_table varchar(128), -- 模版表名
p_type varchar(12), -- month 按月分表，year按年分表
p_partition_type varchar(12), -- 分区类型，day按天分区，month按月分区
p_date datetime -- 生成指定年月
)
BEGIN
IF( p_date IS NULL ) THEN
SET p_date = now(); -- 没有传参数，默认从当前时间
END IF;
IF( p_type IS NULL ) THEN
SET p_type = 'year'; -- 没有传参数，默认按年
END IF;
SET @table_name = CONCAT(p_table,'_',DATE_FORMAT( p_date ,'%Y')); -- 按年分表
IF(p_type='month') THEN
SET @table_name = CONCAT(p_table,'_',DATE_FORMAT( p_date ,'%Y%m'));-- 按月分表
END IF;
SET @s1 = CONCAT('CREATE TABLE ',@table_name,'(LIKE ',p_tag_table,')');
SET @has_table = 0;
SELECT count(1) INTO @has_table
FROM information_schema.`tables`
where table_type='BASE TABLE' and table_schema = p_dbname and table_name = @table_name;
IF (@has_table = 0) THEN
-- 执行生成表
PREPARE stmt2 FROM @s1;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
SELECT @s1; -- 查看结果
-- 生成分区
IF p_type='year' and p_partition_type = 'day' THEN -- 按年分表，按天分区，
SET @f_day = DATE_FORMAT( p_date ,'%Y-01-01');
CALL proc_create_partition(@table_name, @f_day ,'pubtime', datediff(date_add(@f_day,interval 1 year),@f_day) ,'day');
END IF;
IF p_type='year' and p_partition_type = 'month' THEN -- 按年分表，按月分区，
CALL proc_create_partition(@table_name,DATE_FORMAT( p_date ,'%Y-01-01'),'pubtime',12,'month');
END IF;
IF p_type='month' and p_partition_type = 'day' THEN -- 按月分表，按天分区，
CALL proc_create_partition(@table_name,p_date,'pubtime',day(LAST_DAY(p_date)),'day');
END IF;
ELSE
SELECT 'nothing todo' as result;
END IF;
END;;
DELIMITER;
-- 调用
CALL `fun_create_partition_table`('dbname','user','user','year','month','2020-01-01'); -- 按年分表，按月分区
```