mysql循环建表_mysql存储过程批量建表

#mysql

DELIMITER $$

CREATE

PROCEDURE `createTables`()

BEGIN

DECLARE i INT;

DECLARE table_name VARCHAR(20);

DECLARE table_pre VARCHAR(20);

DECLARE sql_text VARCHAR(2000);

SET i=0;

SET table_name='';

SET table_pre='user_convenient_';

SET sql_text='';

WHILE i<100 DO

IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);

ELSE SET table_name=CONCAT(table_pre,i);

END IF;

SET sql_text=CONCAT('CREATE TABLE ', table_name, '(

user_id bigint(20) NOT NULL DEFAULT 0 COMMENT \'用户ID\',

operate_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT \'数据更新时间\',

PRIMARY KEY (user_id)

) ENGINE=INNODB DEFAULT CHARSET=utf8');

SELECT sql_text;

SET @sql_text=sql_text;

PREPARE stmt FROM @sql_text;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

SET i=i+1;

END WHILE;

END$$

DELIMITER ;

#drop PROCEDURE createTables; 删除存储过程

#call createTables(); 执行存储过程

#show procedure status; 列出当前的存储过程

#show create procedure createTables 显示存储过程的具体内容