先写个简单的存储过程实现循环插入,表不创建了,看看样式就行,mysq8.0,实现批插入:
delimiter //
drop procedure if exists test;
create procedure test()
begin
declare i int;
set @s = "insert into student(id,name,code,age) values " ;
set i = 0;
while i < 100000 do
set @s = CONCAT(@s,"(",i,",","CONCAT('name","',",i,"),",i,",25),");
set i = i + 1;
end while;
set @s = CONCAT(@s,"(",i,",","CONCAT('name","',",i,"),",i,",25)");
-- 准备执行sql
PREPARE stmt FROM @s;
-- 执行sql
EXECUTE stmt;
end
//
call test();
使用游标,给表中的每一个用户都插入一条数据,表加入user_id字段
这里演示游标,其实可以用insert into...select
-- 给每一个历史用户插入新的对账类型
DELIMITER //
drop procedure if exists test; -- 如果存在名字为test的procedure则删除
create procedure test() -- 创建一个存储过程
begin
declare uid varchar(30); -- 声明用户变量
declare flag int default 0; -- 循环退出标志
-- 将查询结果放到游标中
declare s_list cursor for select distinct user_id from `test`;
-- 为下面while循环建立一个退出标志,当游标遍历完后将flag的值设置为1
declare continue handler for not found set flag=1;
-- 拼接更新数据
set @sql = "INSERT INTO `test`(`id`, `code`, `name`, `user_id`) VALUES ";
open s_list; -- 打开游标
-- 将游标中的值赋给定义好的变量,实现for循环的要点
fetch s_list into uid;
-- 拼接sql,先提出来一个设置格式
set @sql = concat(@sql,"(UUID(), 'code', 'name',",uid,"),");
fetch s_list into uid;
while flag <> 1 do
-- 拼接sql
set @sql = concat(@sql,"(UUID(), 'code', 'name', ",uid,")");
fetch s_list into uid;
end while;
close s_list; # 关闭游标
-- select @sql;
-- 准备执行sql
PREPARE stmt FROM @sql;
-- 执行sql
EXECUTE stmt;
end;
//
DELIMITER ;
call test();