mysql循环建表_mysql创建存储过程，批量建表分表00到99

--创建存储过程

DELIMITER $$

CREATE PROCEDURE `createTablesWithIndex`()

BEGIN

DECLARE `@i` INT(11);

DECLARE `@createSql` VARCHAR(2560);

DECLARE `@createIndexSql1` VARCHAR(2560);

DECLARE `@createIndexSql2` VARCHAR(2560);

DECLARE `@createIndexSql3` VARCHAR(2560);

DECLARE `@j` VARCHAR(10);

SET `@i`=0;

WHILE `@i`< 100DO

IF `@i`< 10THEN

SET `@j`= CONCAT(0,`@i`);

ELSE

SET `@j`=`@i`;

END IF;--`M_ID` bigint AUTO_INCREMENT PRIMARY KEY NOT NULL,--创建表

SET @createSql= CONCAT('CREATE TABLE IF NOT EXISTS table_',`@j`,'(

`newsid` VARCHAR(255) NOT NULL ,

`classId` VARCHAR(255) DEFAULT NULL,

`kindId` VARCHAR(255) DEFAULT NULL,

`myOther` VARCHAR(255) DEFAULT NULL,

`headTitle` VARCHAR(255) DEFAULT NULL,

`content` VARCHAR(255) DEFAULT NULL,

`connectRealtive` VARCHAR(255) DEFAULT NULL,

`author` VARCHAR(255) DEFAULT NULL,

`editor` VARCHAR(255) DEFAULT NULL,

`newsFrom` VARCHAR(255) DEFAULT NULL,

`top` VARCHAR(255) DEFAULT NULL,

`newsTime` VARCHAR(255) DEFAULT NULL,

`hits` VARCHAR(255) DEFAULT NULL,

`state` VARCHAR(255) DEFAULT NULL,

`tag` VARCHAR(255) DEFAULT NULL

)');

PREPARE stmt FROM @createSql;

EXECUTE stmt;--创建索引

SET @createIndexSql1= CONCAT('create index `newsid` on table_',`@j`,'(`newsid`);');

PREPARE stmt FROM @createIndexSql1;

EXECUTE stmt;

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

END WHILE;

END