MySQL中利用存储过程实现循环批量建表

前言

最近遇到一个在MySQL中循环批量建表的问题，今天抽空记录一下实现过程！

1. 定义循环批量建表的存储过程

# 定义存储过程
DELIMITER $$
CREATE PROCEDURE create_table_batch()
BEGIN
	# 定义循环次数 
	SET @i = 1;
	SET @tablename = ''; 
	SET @sql = ''; 
	 
	WHILE @i <= 31 DO
		SET @tablename = CONCAT('day', CAST(@i AS CHAR));
		IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @tablename) THEN 
			SET @sql = CONCAT('CREATE TABLE ', @tablename,'(
				name VARCHAR(20), 
				num VARCHAR(20), 
				app1 VARCHAR(20), 
				applusetime FLOAT, 
				app2 VARCHAR(20), 
				app2usetime FLOAT, 
				app3 VARCHAR(20), 
				app3usetime FLOAT 
			)');
			PREPARE stmt FROM @sql; 
			EXECUTE stmt;
			DEALLOCATE PREPARE stmt; 
		END IF;
		SET @i = @i + 1; 
	END WHILE;
END$$
DELIMITER;

2. 调用上面我们定义好的存储过程

# 调用存储过程
call create_table_batch();

3. 执行结果