【存储过程】分表Mysql存储过程复制任意表的结构和索引

​

 分表根据模板表生成表名带编号的表

DROP PROCEDURE if exists create_tables;
delimiter $$
CREATE PROCEDURE create_tables(IN tableName VARCHAR(100), IN table_num INTEGER(10))
BEGIN
	DECLARE v_tableName VARCHAR(100);
	DECLARE temp_text text default '';
	
	set @temp_table_num = table_num;
	SELECT tableName, table_num;
	
	while table_num >0 DO 
		SET table_num = table_num - 1;
		SET v_tableName =  CONCAT(tableName, "_", table_num);
		#SELECT v_tableName;
		
		SET temp_text = CONCAT('CREATE TABLE IF NOT EXISTS ',v_tableName,' LIKE ', tableName);
		#SELECT temp_text;
		
		SET @sql_text=temp_text;
		PREPARE stmt FROM @sql_text;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	END while;
END $$
delimiter ;
-- 调用存储过程，传入表名和数量
CALL create_tables("axk_orders.orders", 4);
DROP PROCEDURE if exists create_tables;

执行结果

​