MySQL存储过程——循环建表

60 阅读 0 评论 40 点赞

我是靠谱客的博主乐观香菇，最近开发中收集的这篇文章主要介绍MySQL存储过程——循环建表，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

创建下个月的每天对应的表user_2022_05_01、user_2022_05_02、...

需求描述：

我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！

-- 思路：循环构建表名 user_2022_05_01 到 user_2022_05_31；并执行create语句。

-- 预备知识：

-- 查询当前时间日期

SELECT now();

-- 获取下个月的日期

SELECT date_add(now(), INTERVAL 1 MONTH);

-- 获取下个月的年份

SELECT YEAR(date_add(now(), INTERVAL 1 MONTH));

-- 获取下个月的月份

SELECT MONTH (date_add( now(), INTERVAL 1 MONTH));

-- 获取下个月的最后一天

SELECT DAYOFMONTH(LAST_DAY(date_add( now(), INTERVAL 1 MONTH)));

#以下该代码可直接运行
drop database if exists mydb_proc_demo;
create database mydb_proc_demo;
use mydb_proc_demo;
drop procedure if exists proc21_auto_create_tables_next_month;
delimiter $$
create procedure proc21_auto_create_tables_next_month()
begin
declare next_year int; -- 下一个月的年
declare next_month int; -- 下一个月
declare last_day int; -- 下一个月的最后一天
declare next_date DATE; -- 下个月的日期
declare next_month_str char(2); -- 月份小于10在前面填充一位0
declare next_day_str char(2); -- 天数小于10在前面填充一位0
declare table_name varchar(20);
declare i int default 1;
SET next_date = DATE_ADD(NOW(),INTERVAL 1 MONTH);
set next_year = year(next_date);
set next_month = month(next_date);
set last_day = DAYOFMONTH(LAST_DAY(next_date));
#对于小于10的月份进行填充0，保持工整，对于月份，只需在循环外
if next_month < 10 then
set next_month_str = concat('0', next_month);
end if;
-- select next_year, next_month, next_day;
-- 循环拼接表名，并且建表
while i
#对于小于10的天数进行填充0，保持工整，因为天数需要循环，所以判断放在循环内
if i < 10 then
set next_day_str = concat('0', i);
else
set next_day_str = concat('', i);
end if;
set table_name = concat_ws('', next_year, next_month_str, next_day_str);-- select table_name;-- 拼接建表sql语句
set @create_table_sql = concat('create table user', table_name, '(uid int primary key, username varchar(20), password varchar(20))');
-- FROM后面不能使用局部变量
prepare create_table_stmt from @create_table_sql;-- 预编译sql
execute create_table_stmt; -- 执行sql
deallocate prepare create_table_stmt;--释放预编译
set i = i + 1;
end while;
end $$
delimiter ;
call proc21_auto_create_tables_next_month();