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;