phpmyadmin创建mysql的存储过程
程序员文章站
2024-01-24 11:38:58
通过phpmyadmin ,创建procedure, 用于生成测试数据。 随机的用户名及手机号。 调用: call sp_insert_test_users(10); ......
通过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);
上一篇: PHP:判断年龄是否符合注册要求
下一篇: 谈谈序列化和反序列化