Mysql分表存储过程（指定两个时间段内每个月生成一张表）

papapiu~~

于 2024-07-29 18:11:33 发布

阅读量105

点赞数 1

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_46274588/article/details/140777736

版权

-- 删除存储过程 create_tables
drop procedure if exists create_tables;

-- 创建存储过程
delimiter //
create procedure create_tables(table_name_suffix varchar(32), start_month int, end_month int)
begin
declare current_month int;
declare end_of_cycle int;
-- 初始化变量
set current_month = start_month;
set end_of_cycle = end_month;
-- 循环创建表
while current_month <= end_of_cycle do
set @table_name = concat(table_name_suffix, current_month);
set @sql = concat('
create table ', @table_name, ' (
				id bigint not null auto_increment comment ''自增编号'',
				operation_id bigint not null comment ''操作id'',
				user_name varchar(32) not null comment ''商城账号'',
				wallet_address varchar(64) not null comment ''钱包地址'',
				ip_address varchar(128) not null comment ''ip地址'',
				login_address varchar(64) not null comment ''登录地点（xx省 xx市）'',
				browser varchar(32) not null comment ''浏览器'',
				operation_system varchar(64) not null comment ''操作系统'',
				operation_program tinyint not null comment ''操作程序0钱包后台1商城后台2商家后台3钱包系统4退换货中心5盟非店铺6数字商业公社'',
				operation_function varchar(256) not null comment ''操作功能名称'',
				operation_status tinyint not null comment ''操作状态0成功1失败'',
				content varchar(1024) not null comment ''用户操作内容(xx人物-xx时间-xx地点-xx事件)'',
				req_param varchar(4096) not null comment ''请求参数'',
				source tinyint not null comment ''数据来源1后端2前端'',
				cdate bigint not null comment ''操作时间'',
				primary key (id),
				index index_operation_id (operation_id),
				index index_user_name (user_name),
				index index_operation_program (operation_program),
				index index_wallet_address (wallet_address)
		) engine = innodb
			row_format = compressed
			default charset = utf8mb4
			comment = ''操作日志'';
		');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
-- 增加月份
if month(current_month * 100 + 1) = 12 then
set current_month = (year(current_month * 100 + 1) + 1) * 100 + 1;
else
set current_month = current_month + 1;
end if;
end while;
end //
delimiter ;

-- 调用存储过程创建表
call create_tables('operation_log_', 202407, 202612);

-- 再次删除存储过程 create_tables
drop procedure if exists create_tables;