mysql批量生成随机姓名、手机号等数据

-- 开启允许创建函数
set global log_bin_trust_function_creators = 1;
	
-- 创建随机生成姓名函数 rand_name
delimiter $$

create function rand_name(n int) returns varchar(16)
begin
    -- 初始化一个16姓氏字符串，作为姓氏字符库
    declare family_str varchar (128) default '赵钱孙李周吴郑王冯陈蒋沈韩杨朱秦';
    -- 初始化一个32名字字符串，作为名字字符库
    declare name_str varchar (128) default '平书文若山向秋凡白斌绮烟从蕾天曼润又亦从语绮彤之玉凡梅依琴沛槐敏';
    -- 记录当前是第几个
    declare i int default 0;
    -- 记录生成结果
    declare full_name varchar(16) default '';
    -- 随机名字1、2位标记
    declare rand_num int DEFAULT 0;

    while i < n do
	    -- 若获取多个姓名，则用逗号','区分
	    set full_name = if(i > 0, concat(full_name, ','), full_name);
	    -- 随机取姓氏
	    set full_name = concat(full_name, SUBSTR(family_str, floor(1+rand()*16), 1));
	    -- 随机取名字
	    set full_name = concat(full_name, SUBSTR(name_str, floor(1+rand()*16), 1));
	    -- 名字是否为双字
	    set rand_num = rand()*10;
	    set full_name = if(rand_num > 5, concat(full_name, SUBSTR(name_str, floor(1+rand()*16), 1)), full_name);
	    set i = i + 1;
    end while;

    return full_name;

end$$ 
delimiter ; 
-- 调用函数
select rand_name(1);

-- 创建随机生成手机号函数 generatePhone
delimiter $$
CREATE  FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(200) DEFAULT '134,135,136,137,138,139,147,150,151,152,157,158,159,182,183,184,187,188,130,131,132,145,155,156,185,186,199,133,153,180,181,189';
    
    DECLARE content CHAR(10) DEFAULT '0123456789';
    
    DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);
    
    DECLARE i int DEFAULT 1;
    
    DECLARE len int DEFAULT LENGTH(content);
    WHILE i<9 DO
        SET i=i+1;
        SET phone = CONCAT(phone, substring(content, floor(1 + RAND() * len), 1));
    END WHILE;
    
    RETURN phone;
end$$ 
delimiter ; 
-- 查询随机手机号
select generatePhone();



-- 创建表
create table study_user (
	id int not null auto_increment ,
	name varchar(16) default null,
	age int default 0,
	salary int default 0,
	primary key (id)
) engine = innodb default charset = utf8



-- 创建批量生成表数据存储过程
delimiter $$
create PROCEDURE insert_user(in num int)
begin
    -- 记录循环次数
    declare i int default 0;
    -- 关闭自动提交
    set autocommit = 0;
    -- 循环insert
    while i < num do
	    insert into study_user(name, age, salary) values 
	    (rand_name(1), floor(15 + rand()*10), floor(rand()*10000 + rand()*1000));
	    set i = i + 1;
    end while;
    commit;

end$$
delimiter ;

-- 执行存储过程
call insert_user(180000);

-- 截取查询数据
select substr(name,1,1),'*',substr(name,3,4),1,age,'****',substr(salary ,1,4),'个人','广东' from study_user ;