使用存储过程批量建表建库

创建存储过程

delimiter //

drop procedure if EXISTS `createGroupChatTablesWithIndex`;

create procedure createGroupChatTablesWithIndex()

BEGIN 

DECLARE `@i` int(11);     

DECLARE `@createSql` VARCHAR(2560); 

DECLARE `@createIndexSql1` VARCHAR(2560);     

DECLARE `@createIndexSql2` VARCHAR(2560);

set `@i`=1; 

WHILE  `@i`< 100 DO                 

-- 创建表        

SET @createSql = CONCAT('CREATE TABLE IF NOT EXISTS B_GROUP_MSG_',`@i`,'(

`FUID` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,

`GROUP_ID` bigint(20) NOT NULL,

`SEND_USER_ID` bigint(20) NOT NULL,

`CONTENT` varchar(500) NOT NULL,

`AUDIOTIME` int(11) NULL,

`MSG_TYPE` int(11) NOT NULL,

`SEND_TIME` varchar(19) NOT NULL,

`ADD_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8'

); 

prepare stmt from @createSql; 

execute stmt;

set @createIndexSql1  = CONCAT('create index `IDX_GROUP_ID` on B_GROUP_MSG_',`@i`,'(`GROUP_ID`);');

prepare stmt from @createIndexSql1; 

execute stmt;

set @createIndexSql2  = CONCAT('create index `IDX_ADD_TIME` on B_GROUP_MSG_',`@i`,'(`ADD_TIME`);');

prepare stmt from @createIndexSql2; 

execute stmt;

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

            END WHILE;

end //

delimiter ;

调用存储过程

CALL createGroupChatTablesWithIndex();