navicat 使用存储过程批量建表

打开Navicat 创建 函数

函数内容:

下面展示一些 内联代码片。

BEGIN
DECLARE i INT;
DECLARE table_name VARCHAR(20);
SET i = 0;
 
WHILE i<128 DO
#为了使表名成为xxx00这样的格式加的条件判断
IF i<10 THEN
SET table_name = CONCAT('user_tourist_00',i);
ELSEIF i<100 THEN
SET table_name = CONCAT('user_tourist_0',i);
ELSE
SET table_name = CONCAT('user_tourist_',i);
END IF;
 
SET @csql = CONCAT(
'CREATE TABLE ',table_name,'(
`id` bigint(20) UNSIGNED NOT NULL auto_increment COMMENT"自增主键",
`tourist_id` bigint(20) unsigned NOT NULL COMMENT "游客id",
`token` char(128) NOT NULL DEFAULT "" COMMENT "游客token",
`user_id` bigint(20) DEFAULT NULL COMMENT "关联用户id",
`salt` char(8) NOT NULL DEFAULT "" COMMENT "密码盐",
`atom` varbinary(512) DEFAULT "" COMMENT "客户端atom信息",
`is_del` tinyint(2) NOT NULL DEFAULT "0" COMMENT "是否删除 0否; 1是",
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(id),
KEY token (token)
)ENGINE=Innodb default charset=utf8;'
);
 
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
SET i = i+1;
END WHILE;
 
END

点击 运行 即可