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();