如何通过编写存储过程和事件调度来实现MySQL按天分表？

目前在维护的一个项目中，有一个业务的数据量相对比较庞大，所以准备按照日期的维度对该业务进行分表。

自动分表或者定时建表的方案有很多，比如使用脚本配合定时器，这里就不多介绍了。

这次我采用的方案是通过编写MYSQL的存储过程来建表，然后使用时间调度来执行存储过程。

废话不多说，直接上代码。

• 第一步，建立一张基础表，可以用作默认，示例代码如下，我把索引和注释都去掉了

create table fsp_ad_flow
(
    id                  bigint auto_increment comment '主键编号'
        primary key,
    platform_id         int                        not null comment '',
    platform_type       tinyint                    not null comment '',
    spread_id           int                        not null comment '',
    book_id             int                        not null comment '',
    action_type         tinyint                    not null comment '',
    amount              varchar(30) default '0.00' not null comment '预估收益(分)',
    advertiser_id       varchar(80)                null comment '',
    advertiser_plan_id  varchar(80)                null comment '',
    advertiser_group_id varchar(80)                null comment '',
    merchant_id         int                        not null comment '',
    fsp_created_at      varchar(30)                null comment '',
    join_at             varchar(30)                null comment '',
    os                  varchar(80)                null comment '',
    created_at          timestamp                  null comment '创建时间',
    updated_at          timestamp                  null comment '更新时间'
)
    comment '广告流水表';

• 第二步，编写存储过程，代码大致如下，这里默认创建近七天的表

DELIMITER //

CREATE PROCEDURE create_ad_flow_table()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE partition_date DATE;
    DECLARE table_suffix VARCHAR(10);
    DECLARE current_date_val DATE;

    -- 设置日期范围，例如最近7天
    SET current_date_val = CURDATE();
    SET partition_date = DATE_ADD(current_date_val, INTERVAL 7 DAY);

    WHILE NOT done DO
        SET table_suffix = DATE_FORMAT(partition_date, '_%Y%m%d');
        SET @base_table_name = 'fsp_ad_flow';
        SET @partitioned_table_name = CONCAT(@base_table_name, table_suffix);
        SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS ', @partitioned_table_name, ' LIKE ', @base_table_name);

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET partition_date = DATE_SUB(partition_date, INTERVAL 1 DAY);
        IF partition_date < current_date_val THEN
            SET done = 1;
        END IF;
    END WHILE;
END;
//

DELIMITER ;

• 第三步，尝试调用存储过程，看是否得到预期结果

CALL create_ad_flow_table();

• 第四步，如果结果符合预期，我们接着来编写事件调度的代码，每天调用一次，保证业务程序在调用分表模型时，尽可能不会出现找不到表的异常

CREATE EVENT IF NOT EXISTS partition_tables_event
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
BEGIN
    CALL create_ad_flow_table();
END;

到这里，存储过程+事件调度的相关代码已经编写完了，直接拿去执行就可以了.

最后，我再做一些补充：

1. 如果想要查询一下事件调度的相关时间和状态，可以使用：

SHOW EVENTS

下图是查询出的事件结果

2. 如果想要查询数据库中编写过哪些存储过程，可以使用：

SHOW PROCEDURE STATUS;

3. 如果想要查询某个存储过程的具体信息，可以使用：

SHOW CREATE PROCEDUCE create_ad_flow_table;