mysql 批量建表 存储过程

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; //

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>