MySQL存储过程自动按年/月/日分表分区

PARTITION 创建分区

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 创建分表

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'); -- 按年分表，按月分区