phpmyadmin创建mysql的存储过程
通过phpmyadmin ,创建procedure,
用于生成测试数据。
随机的用户名及手机号。
DELIMITER $$ CREATE PROCEDURE `sp_insert_test_users`(IN `para_count` INT) BEGIN DECLARE p_username varchar(50); DECLARE p_countryCallingCode varchar(10) default '86'; DECLARE p_phone varchar(20); DECLARE p_all_phone varchar(20); DECLARE p_create_time datetime; DECLARE p_index int default 0; DECLARE p_userid int default 0; IF para_count > 0 THEN SET p_create_time = NOW(); while p_index < para_count do select concat( substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1), substring('abcdefghijklmnopqrstuvwxyz', rand()*26 , 1) ) into p_username; select concat( '139', substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1), substring('1234567890', rand()* 10 , 1) ) into p_phone; if not exists ( select 1 from users where username = p_username or phone = p_phone ) and length(p_username) = 8 and length(p_phone) = 11 then set p_all_phone = concat(p_countryCallingCode,';',p_phone); INSERT INTO `users` (`username`, `countryCallingCode`, `phone`, `_phone`, `group`, `created_at`, `updated_at`) VALUES (p_username,p_countryCallingCode,p_phone,p_all_phone, 'user', p_create_time, p_create_time); select @@IDENTITY into p_userid; INSERT INTO `users_test_mock` (`userid`,`username`, `countryCallingCode`, `phone`) VALUES (p_userid,p_username,p_countryCallingCode,p_phone); SET p_index = p_index + 1; end if; end while; END IF; END$$ DELIMITER ;
调用:
call sp_insert_test_users(10);
posted on 2019-03-01 17:57 freeliver54 阅读(519) 评论(1) 编辑 收藏 举报
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步