使用存储过程生成数据
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;
欢迎大家评论哟!如果本文对您有帮助,请点个赞,您的点赞对我很重要!这次一定!感谢!!!
转发请注明出处呦!感谢!!!