循环创建表
drop procedure IF EXISTS create_table;
DELIMITER //
CREATE procedure create_table()
BEGIN
SET @i=0;
WHILE @i < 10 DO
SET @sqlstr = CONCAT(
"CREATE TABLE `test-user`.teacher_",
@i,
"(
`id` bigint(21) NOT NULL AUTO_INCREMENT,
`number` int(11) NOT NULL ,
`name` varchar(50) DEFAULT NULL,
`phone` bigint(17) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`card` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1; ");
prepare stmt from @sqlstr;
execute stmt;
SET @i = @i + 1;
END WHILE;
END;
//
DELIMITER ;
call create_table();
drop procedure create_table;
循环插入数据
1 drop procedure IF EXISTS insert_user; 2 3 DELIMITER // 4 CREATE procedure insert_user() 5 BEGIN 6 7 SET @i=0; 8 WHILE @i < 10 DO 9 SET @sqlstr = CONCAT( 10 "insert into `test-user`.teacher_", 11 @i, 12 "(`id`, `number`, `name`, `phone`, `address`, `card`) VALUES (1, 345634223, '小郑', 15345544430, '徐州', '11010519491231002X'), 13 (2, 1345634223, '张伟', 15345544431, '徐州', '120102199801015234');"); 14 15 prepare stmt from @sqlstr; 16 execute stmt; 17 18 SET @i = @i + 1; 19 END WHILE; 20 21 END; 22 // 23 DELIMITER ; 24 call insert_user(); 25 drop procedure insert_user;
