使用存储过程生成数据

DROP PROCEDURE IF EXISTS create_data_with_num; CREATE procedure create_data_with_num(in DataCount INT) BEGIN -- DECLARE DataCount INT DEFAULT 100; -- 数据总数 DECLARE CurrentCount INT DEFAULT 1; -- 当前条数 DECLARE Uname NVARCHAR(20) DEFAULT ''; -- 姓名 DECLARE Uage INT(6) DEFAULT 0; -- 年龄 DECLARE IsDELETE INT(2) DEFAULT 0; -- 删除状态 WHILE (CurrentCount<= DataCount) do BEGIN -- 先删除旧数据 delete from tb_data WHERE id = CurrentCount; -- 每5条数据，删除状态为1，其余数据为0 IF (CurrentCount%5=0) then SET IsDELETE = 1; else SET IsDELETE = 0; end if; SET Uage = ceiling(rand() * 100); -- 随机生成1-100之间的数字 INSERT into `tb_data`(`id`,`name`,`age`,`isdelete`) VALUES(CurrentCount,concat('用户',CurrentCount), Uage, IsDelete); SET CurrentCount = (CurrentCount + 1); END; END WHILE; END;

调用存储过程

call create_data_with_num(150);

优化：

使用手动事务提交，将单条操作变为批量操作，提高插入效率!

DROP PROCEDURE IF EXISTS create_data_with_num; CREATE procedure create_data_with_num(in DataCount INT) BEGIN -- DECLARE DataCount INT DEFAULT 100; -- 数据总数 DECLARE CurrentCount INT DEFAULT 1; -- 当前条数 DECLARE Uname NVARCHAR(20) DEFAULT ''; -- 姓名 DECLARE Uage INT(6) DEFAULT 0; -- 年龄 DECLARE IsDELETE INT(2) DEFAULT 0; -- 删除状态 START TRANSACTION; WHILE (CurrentCount<= DataCount) do BEGIN -- 先删除旧数据 delete from tb_data WHERE id = CurrentCount; -- 要求：每隔第5条数据时，删除状态为1，其余数据为0 IF (CurrentCount%5=0) then SET IsDELETE = 1; else SET IsDELETE = 0; end if; SET Uage = ceiling(rand() * 100); -- 随机生成1-100之间的数字 INSERT into `tb_data`(`id`,`name`,`age`,`isdelete`) VALUES(CurrentCount,concat('用户',CurrentCount), Uage, IsDelete); SET CurrentCount = (CurrentCount + 1); END; END WHILE; COMMIT; END;

---

欢迎大家评论哟！如果本文对您有帮助，请点个赞，您的点赞对我很重要！这次一定！感谢！！！ 转发请注明出处呦！感谢！！！