Skip to main content
 首页 » 数据库

mysql批量建表

2022年07月18日30三少

需求:分表时,需要批量建表

# 第一步:创建存储过程
CREATE PROCEDURE create_64_table(in val_s int, in val_e int)
begin
declare i int;
set i=val_s;
while i<=val_e do

set @sql_create_table = concat(
"CREATE TABLE IF NOT EXISTS xxx_task_", i,
"(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`player_id` int(11) DEFAULT '0' COMMENT '玩家id',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表';
");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;

set @sql_create_table = concat(
"CREATE TABLE IF NOT EXISTS xxx_record_", i,
"(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='记录表'
");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;

set @sql_create_table = concat(
"CREATE TABLE IF NOT EXISTS xxx_achievement_", i,
"(
`id` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='配置';
");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;

set @sql_create_table = concat(
"CREATE TABLE IF NOT EXISTS xxx_record_", i,
"(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='信息表';
");
PREPARE sql_create_table FROM @sql_create_table;
EXECUTE sql_create_table;
set i=i+1;
end while;
end

# 第二步:执行存储过程
call create_64_table(0,63);

# 第三步:删除存储过程
DROP PROCEDURE IF EXISTS create_64_table

最后的说明:某些版本对 ""  或  ‘’ 有严格要求,报错的话,注意字符串的拼接。


本文参考链接:https://www.cnblogs.com/maohuidong/p/15048295.html