利用存储过程实现按月动态创建表
创建表的SQL语句大家都不陌生，按月份创建表之前，自然也需要一份原生创建表的DDL语句，如下：
CREATE TABLE `month_bills_202211` (
`month_bills_id` int(8) NOT NULL AUTO_INCREMENT COMMENT '账单ID',
`serial_number` varchar(50) NOT NULL COMMENT '流水号',
`bills_info` text NOT NULL COMMENT '账单详情',
`pay_money` decimal(10,3) NOT NULL COMMENT '支付金额',
`machine_serial_no` varchar(20) NOT NULL COMMENT '收银机器',
`bill_date` timestamp NOT NULL COMMENT '账单日期',
`bill_comment` varchar(100) NULL DEFAULT '无' COMMENT '账单备注',
PRIMARY KEY (`month_bills_id`) USING BTREE,
UNIQUE `serial_number` (`serial_number`),
KEY `bill_date` (`bill_date`)
)
ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Compact;
上述的语句会创建一张月份账单表，这张表主要包含七个字段，如下：
字段 简介 描述
month_bills_id 月份账单ID 主要作为月份账单表的主键字段
serial_number 流水号 所有账单流水数据的唯一流水号
bills_info 账单详情 顾客本次订单中，购买的所有商品详情数据
pay_money 支付金额 本次顾客共计消费的总金额
machine_serial_no 收银机器 负责结算顾客订单的收银机器
bill_date 账单日期 本次账单的结算日期
bill_comment 账单备注 账单的额外备注
其中注意的几个小细节：
①日期字段使用的是timestamp类型，而并非datetime，因为前者更省空间。
②账单详情字段用的是text类型，因为这个字段可能会出现很多的信息。
③定义了一个和表没有关系的自增字段作为主键，用于维护聚簇索引树的结构。
除开有上述七个字段外，还有三个索引：
索引字段 索引类型 索引作用
month_bills_id 主键索引 主要作用就是用来维护聚簇索引树
serial_number 唯一索引 当需要根据流水号查询数据时使用
bill_date 唯一联合索引 当需要根据日期查询数据时使用
到这里就有了最基本的建表语句，主要是用来创建第一张月份账单表，如果想要实现动态按照每月建表的话，还需要用到存储过程来实现，接着来写一个存储过程。
最终撰写出的存储过程如下：
DELIMITER //
DROP PROCEDURE IF EXISTS create_table_by_month //
CREATE PROCEDURE create_table_by_month()
BEGIN
-- 用于记录下一个月份是多久
DECLARE nextMonth varchar(20);
-- 用于记录创建表的SQL语句
DECLARE createTableSQL varchar(5210);
-- 执行创建表的SQL语句后，获取表的数量
DECLARE tableCount int;
-- 用于记录要生成的表名
DECLARE tableName varchar(20);
-- 用于记录表的前缀
DECLARE table_prefix varchar(20);
-- 获取下个月的日期并赋值给nextMonth变量
SELECT SUBSTR(
replace(
DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
'-', ''),
1, 6) INTO @nextMonth;
-- 设置表前缀变量值为td_user_banks_log_
set @table_prefix = 'month_bills_';
-- 定义表的名称=表前缀+月份，即 month_bills_2022112 这个格式
SET @tableName = CONCAT(@table_prefix, @nextMonth);
-- 定义创建表的SQL语句
set @createTableSQL=concat("create table if not exists ",@tableName,"(
month_bills_id int(8) NOT NULL AUTO_INCREMENT COMMENT '账单ID',
serial_number varchar(50) NOT NULL COMMENT '流水号',
bills_info text NOT NULL COMMENT '账单详情',
pay_money decimal(10,3) NOT NULL COMMENT '支付金额',
machine_serial_no varchar(20) NOT NULL COMMENT '收银机器',
bill_date timestamp NOT NULL DEFAULT now() COMMENT '账单日期',
bill_comment varchar(100) NULL DEFAULT '无' COMMENT '账单备注',
PRIMARY KEY (month_bills_id) USING BTREE,
UNIQUE serial_number (serial_number),
KEY bill_date (bill_date)
) ENGINE = InnoDB
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ROW_FORMAT = Compact;");
-- 使用 PREPARE 关键字来创建一个预备执行的SQL体
PREPARE create_stmt from @createTableSQL;
-- 使用 EXECUTE 关键字来执行上面的预备SQL体：create_stmt
EXECUTE create_stmt;
-- 释放掉前面创建的SQL体（减少内存占用）
DEALLOCATE PREPARE create_stmt;
-- 执行完建表语句后，查询表数量并保存再 tableCount 变量中
SELECT
COUNT(1) INTO @tableCount
FROM
information_schema.TABLES
WHERE TABLE_NAME = @tableName;
-- 查询一下对应的表是否已存在
SELECT @tableCount 'tableCount';
END //
delimiter ;
上述这个存储过程比较长，但基本上都写好了注释，所以阅读起来应该还是比较轻松的，也包括该存储过程在MySQL5.1、8.0版本中都测试过，所以大家也可以直接用，主要拆解一下里面较为难理解的一句SQL，如下：
SELECT SUBSTR(
replace(
DATE_ADD(CURDATE(), INTERVAL 1 MONTH),
'-', ''),
1, 6) INTO @nextMonth;
这条语句执行之后会生成一个202212这样的月份数字，主要用来作为表名的后缀，以此来区分不同的表，但里面用了几个函数组合出了该效果，下面做一下拆解，如下：
-- 在当前日期的基础上增加一个月，如2022-11-12 23:46:11，会得到2022-12-12 23:46:11
select DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
-- 使用空字符代替日期中的 - 符号，得到 20221212 23:46:11 这样的效果
select replace('2022-12-12 23:46:11', '-', '');
-- 对字符串做截取，获取第一位到第六位，得到 202212 这样的效果
select SUBSTR("20221212 23:46:11",1,6);
经过上述拆解之后大家应该能看的很清楚，最终每次调用该存储过程时，都会基于当前数据库的时间，然后向后增加一个月，同时将格式转化为YYYYMM格式，接下来调用该存储过程，如下：
call create_table_by_month();
+------------+
| tableCount |
+------------+
| 1 |
+------------+
当返回的值为1而并非0时，就表示已经在数据库中查到了前面通过存储过程创建的表，即表示动态创建表的存储过程可以生效！接着为了能够每月定时触发，可以在MySQL中注册一个每月执行一次的定时事件，如下：
create EVENT
create_table_by_month_event -- 创建一个定时器
ON SCHEDULE EVERY
1 MONTH -- 每间隔一个月执行一次
STARTS
'2022-11-28 00:00:00' -- 从2022-11-28 00:00:00后开始
ON COMPLETION
PRESERVE ENABLE -- 执行完成之后不删除定时器
DO
call create_table_by_month(); -- 每次触发定时器时执行的语句
MySQL5.1版本中除开引入了存储过程/函数、触发器的支持外，还引入了定时器的技术，也就是支持定时执行一条SQL，此时咱们可借助MySQL自带的定时器来定时调用之前的存储过程，最终实现按月定时创建表的需求！
但定时器在使用之前，需要先查看定时器是否开启，如下：show variables like 'event_scheduler';如果是OFF关闭状态，需要通过set global event_scheduler = 1 | on;命令开启。如果想要永久生效，MySQL8.0以下的版本可找到my.ini/my.conf文件，然后找到[mysqld]的区域，再里面多加入一行event_scheduler = ON的配置即可。
这里再附上一些管理定时器的命令：
-- 查看创建的定时器
show events;
select * from mysql.event;
select * from information_schema.EVENTS;
-- 删除一个定时器
drop event 定时器名称;
-- 关闭一个定时器任务
alter event 定时器名称 on COMPLETION PRESERVE DISABLE;
-- 开启一个定时器任务
alter event 定时器名称 on COMPLETION PRESERVE ENABLE;
经过上述几步后，就能够让MySQL自己按月创建表了，但为啥我会将定时器的时间设置为2022-11-28 00:00:00这个时间后开始呢？因为202211这张表我已经手动建立了，不将建立表的工作放在月初一号执行，这是因为前面的存储过程是创建下月表，而不是创建当月表，同时月底提前创建下月表，还能提高容错率，在MySQL定时器故障的情况下，能预留人工介入的时间。
网友评论