mysql存储过程批量建表
用MySql的存储过程建立100张表
#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 显示存储过程的具体内容