mysql 存储过程 循环创建表,Frank的博客

delimiter //

CREATE procedure create_table()

BEGIN

DECLARE `@i` int(11);

DECLARE `@sqlstr` varchar(65000);

SET `@i`=0;

WHILE `@i` < 256 DO

SET @sqlstr = CONCAT(

"CREATE TABLE box_item_pool_",

`@i`,

"(

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`_unique` varchar(64) NOT NULL DEFAULT '' COMMENT '分表后唯一标示',

`user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',

`box_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '盒子ID',

`prod_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID, 目前是product_master.id',

`prod_code` varchar(32) NOT NULL DEFAULT '' COMMENT '商品CODE, 目前是product_master.code',

`product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'product.id',

`created_at` datetime DEFAULT NULL,

`updated_at` datetime DEFAULT NULL,

`deleted_at` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `_unique` (`_unique`) USING BTREE,

KEY `user_box` (`user_id`,`box_id`),

KEY `box` (`box_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"

);

prepare stmt from @sqlstr;

execute stmt;

SET `@i` = `@i` + 1;

END WHILE;

END;

call create_table();

drop procedure create_table;

//