分表根据模板表生成表名带编号的表
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;
执行结果