创建几个相同结构的表,表名前缀相同,后缀是动态的数字
-
动态删除表
DELIMITER $
DROP PROCEDURE
IF
EXISTS `drop_order_info_1-12`;
CREATE PROCEDURE `drop_order_info_1-12` ( ) BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
i <= 12 DO
SET @sql_drop_table = concat( 'drop table if exists order_info_', i );
PREPARE sql_drop_table
FROM
@sql_drop_table;
EXECUTE sql_drop_table;
SET i = i + 1;
END WHILE;
END $
DELIMITER ;
-
动态创建表
DELIMITER $
DROP PROCEDURE
IF
EXISTS `create_order_info_1-12`;
CREATE PROCEDURE `create_order_info_1-12` ( ) BEGIN
DECLARE
i INT;
SET i = 1;
WHILE
i <= 12 DO
SET @sql_create_table = concat( 'CREATE TABLE order_info_', i, '( id VARCHAR ( 64 ) PRIMARY KEY, business_time datetime, create_time datetime, order_no VARCHAR ( 20 ) );' );
PREPARE sql_create_table
FROM
@sql_create_table;
EXECUTE sql_create_table;
SET i = i + 1;
END WHILE;
END $
DELIMITER ;