Mysql存储过程实战

一、循环插入数据

#存储过程：批量造dcep_users的用户数据(注意：同步造d_tree的用户数据：钱包表、微信绑定表、D豆树用户表）

drop procedure if EXISTS Decp_users_add;

create procedure Decp_users_add(in i INT)

begin

DECLARE id VARCHAR(7);

declare phone varchar(12);

declare share varchar(5);

declare ip varchar(40);

while 10<=i do

set id = concat('20003',i);

set phone = concat('187805217',FLOOR(10+RAND()*80));

set ip ='117.136.63.163';

set share = concat('5',i,'JH');

INSERT INTO `dcep_share`.`dcep_users`(`id`, `phone`, `enable_pwd`, `password`, `register_time`, `token`, `money`, `ip`, `share`, `area_code`, `level1_parent`, `level2_parent`, `level3_parent`, `level4_parent`, `level5_parent`, `level6_parent`, `create_time`, `modified_time`, `nick`) VALUES ( id, phone, '111111', '96e79218965eb72c92a549dd5a330112', '2020-09-03 14:22:26', NULL, 0.00, ip, share, '0086', 2000050, 0, 0, NULL, NULL, NULL, '2020-09-03 14:22:26', '2020-09-03 14:22:26', NULL);

SET i=i-1;

end while;

end

 

call Decp_users_add(30); #调用

如果报错“Duplicate key”重复的值：排查出错的地方：select * from dcep_users where id = 2000330;

 

 

二、当多个表数据有关联时候，通过创建游标批量造数据

 

#存储过程：批量造d_tree的用户数据

drop procedure if EXISTS Tree_users_add;

create procedure Tree_users_add()

begin

 

DECLARE uid int(7);

declare share1 varchar(5);

declare create_time1 varchar(30);

declare modified_time1 varchar(30);

DECLARE done INT DEFAULT FALSE;#创建游标开始标识

declare cur CURSOR for select id,share,create_time,modified_time from dcep_share.dcep_users where share like '5%'; #筛选条件，创建游标，

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; #创建游标结束标识

open cur;#打开游标

read_loop: LOOP #遍历游标

FETCH cur into uid,share1,create_time1,modified_time1;#获取游标的值

if done then LEAVE read_loop; #当游标为结束标识即done为true的时候，退出遍历

end if;

INSERT INTO `d_tree`.`tree_user_info`(`uid`, `create_user`, `share`, `union_id`, `level1_parent`, `level2_parent`, `level3_parent`, `certification_status`, `alipay_bind_status`, `activate_status`, `wx_account`, `qq_account`, `alipay_account`, `user_name`, `social_master_visible`, `social_apprentice_visible`, `create_datetime`, `modified_datetime`) VALUES (uid, 1, share1, concat('olF4J06Oj1NdHzJ76BJ0ZOhgfK',FLOOR(RAND()*100)), 2000050, 0, 0, 0, 0, 0, '', '', '', '', 1, 1, create_time1, modified_time1); #将获取到的数据执行sql插入操作

END LOOP;

close cur;

end

 

call Tree_users_add();

 

 

注意：声名插入的变量值一定要区别于筛选查的值，否则为空

如： select id,share,create_time,modified_time from dcep_share.dcep_users where share like '5%';

在声名变量时候用declare create_time1 varchar(30);

declare modified_time1 varchar(30);

 

三、查询放在本地的位置，可通过此查看