mysql存储过程循环整个表_mysql存储过程，时间循环建表

delimiter //                            #定义标识符为双斜杠

drop procedure if exists test;          #如果存在test存储过程则删除

create procedure test()                 #创建无参存储过程,名称为test

begin

declare tbname varchar(20);

declare i int;                      #申明变量

set i = 1;                          #变量赋值

while i <= 100 do                   #结束循环的条件: 当i大于10时跳出while循环

set tbname = CONCAT('Sys_yhdtInfo_copy',i);

set @t_sql_1 = ' SET NAMES utf8mb4;';

PREPARE temp FROM @t_sql_1;

EXECUTE temp;

set @t_sql_2 = ' SET FOREIGN_KEY_CHECKS = 0;';

PREPARE temp FROM @t_sql_2;

EXECUTE temp;

set @t_sql_3 = CONCAT(' DROP TABLE IF EXISTS ',tbname);

PREPARE temp FROM @t_sql_3;

EXECUTE temp;

set @t_sql_4 = CONCAT(' CREATE TABLE ',tbname,' (

`id` int(11) NOT NULL AUTO_INCREMENT,

`dtsj` datetime(0) NULL DEFAULT NULL COMMENT ''答题时间'',

`tmid` int(255) NULL DEFAULT NULL COMMENT ''题目id'',

`hdda` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''回答答案'',

`zqda` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''正确答案'',

`openid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT ''用户id'',

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;');

PREPARE temp FROM @t_sql_4;

EXECUTE temp;

set @t_sql_5 = 'SET FOREIGN_KEY_CHECKS = 1;';

PREPARE temp FROM @t_sql_5;

EXECUTE temp;

deallocate prepare temp;      -- 释放prepare

set i = i + 1;                  #循环一次,i加一

end while;                          #结束while循环

end

//                                      #结束定义语句

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