mysql水平分表

Px存储过程+事件（定时任务）实现定期水平分表，比如按月分表。
1、首先创建存储过程

存储过程：
入参为表前缀，数据库名。

CREATE DEFINER=`root`@`%` PROCEDURE `add_table_monthly`(IN tab_prefix varchar(64), IN db_name varchar(32))
    COMMENT '每月新建分表,格式：prefix_202201'
BEGIN
        declare i int;
        declare batch_num int default 1;
        declare new_table varchar(64);
        declare templet_table varchar(64);

        set templet_table = concat(tab_prefix, date_format(now(), '%Y%m'));
        set i = 1;

        while i <= batch_num do
            START TRANSACTION;
            set new_table = concat(tab_prefix, date_format(date_add(now(), interval i month), '%Y%m'));

            set @check_sql = CONCAT("SELECT count(1) INTO @exist_flag FROM information_schema.TABLES WHERE TABLE_SCHEMA='",db_name,"' AND TABLE_NAME='",new_table,"'");
            PREPARE pre_check FROM @check_sql;
            EXECUTE pre_check;
            DEALLOCATE PREPARE pre_check;

            IF @exist_flag = 0 THEN
                set @alter_sql = CONCAT("CREATE TABLE ", new_table, " LIKE ", templet_table);
                PREPARE pre_sql FROM @alter_sql;
                EXECUTE pre_sql;
                DEALLOCATE PREPARE pre_sql;
            END IF;

            COMMIT ;
            set i=i+1;
        end while;

    END

2、创建事件（定时任务），调用存储过程

    定时任务：
    BEGIN
        CALL add_table_monthly('user_plan_info_', 'mydb');
    END

3、水平分表场景
（1）数据量大；
（2）业务只需要按月维度进行操作（比如统计）；