分库分表-存储过程批量创建、修改表结构

存储过程 tp_create_alert_table 方法说明：

IN `@Num` int(11) ： 表数量

IN `@tableName` varchar(128) ：表名

IN `@sql` varchar(5120) ： SQL内容 (...)

IN `@isCreateTabel` ： 是否是创建表0-非,>0-是

DROP PROCEDURE IF EXISTS tp_create_alert_table; CREATE PROCEDURE tp_create_alert_table(IN `@Num` int(11) , IN `@tableName` varchar(128),IN `@sql` varchar(5120) ,IN `@isCreateTabel` int(2))  BEGIN       DECLARE `@i` int(11);       DECLARE `@sqlstr` varchar(5248);       SET `@i`=0;       WHILE `@i` < `@Num` DO             IF `@isCreateTabel`>0 THEN                   SET @sqlstr = CONCAT("DROP TABLE IF EXISTS ",`@tableName`,'_',`@i`,";");                   prepare stmt from @sqlstr;                   execute stmt ;             END IF;                          SET @sqlstr = replace(`@sql`,`@tableName`,CONCAT(`@tableName`,'_',`@i`));             prepare stmt from @sqlstr;             execute stmt ;             SET `@i` = `@i` + 1;       END WHILE; END  ; call tp_create_alert_table(10,'mall_test',"CREATE TABLE mall_test(`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',          `shop_id` bigint(20) DEFAULT NULL COMMENT '叮当店铺id',          `sku_id` bigint(20) DEFAULT NULL COMMENT '叮当skuId',          PRIMARY KEY (`id`),          KEY `index_pss` (`shop_id`,`sku_id`) ,          KEY `index_sku` (`sku_id`)       ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tpmall新商品库存表';       ",1); call tp_create_alert_table(10,'mall_test',"alter  table mall_test  modify `shop_id` bigint(20) DEFAULT NULL COMMENT '店铺id';",0); call tp_create_alert_table(10,'mall_test',"DROP INDEX `index_sku` ON mall_test;",0); call tp_create_alert_table(10,'mall_test',"CREATE INDEX `index_sku_1` ON mall_test (sku_id);",0); DROP PROCEDURE IF EXISTS tp_create_alert_table;