mysql generates random name, mobile number, date

生成随机姓名

CREATE DEFINER=`root`@`%` FUNCTION `generateUserName`() RETURNS varchar(255) CHARSET utf8mb4
DETERMINISTIC
BEGIN
    DECLARE xing varchar(2056) DEFAULT 'Zhao money sun zhou li city Feng Chen disappear who Jiang Shen Han Yang l. cot what Mr Xu lu shi Zhang Kong Cao Yan joaquin Wei Tao ginger sinus ZhangYunSu qi Xie Yubai water pan ge XiFanPeng LangLuWei chang Ma Miao phoenix flower Fang Yu ren Yuan Liu Feng bao shi tang FeiLian cen Xue Leihe NiShang teng ying luo bi hao WuAn often enjoy when skin JiKangWu fu yuan Gu Mengping yellow and jose Xiao Yin Yao Shaozhan Wang Qimao yu DE m BeiMing Zang meter fell into wearing about Song Mao PangXiong JiShu flexor item ZhuDongLiang du ruan blue fujian seats season strong ma jia road LouWei Jiang Tong YanGuoMei ShengLin diao zhong Xu Qiu Locke high Xia Caitian FanHuLing HuoYu wan ke Zan tubes lu mo after QiuMiao dry solution should ZongDingXuan kwok-bun Deng Yu single HangHong package the glutaeus maximus Cui Ji button Gong Cheng Ji slide PeiLuRong xing weng';

    DECLARE ming varchar(2056) DEFAULT 'Jiayi Yucheng Yixuan Ye Weiyuan Bo Weize Yitong Hong Xuan Bo Tao Ye Lin Ye Hua Yuqi Zhi Chen Zhenghao Hao Ran Mingjie Cheng Li Xuan Li Hui Hong Wen Yi Tong Hong Xuan Ye Lin Zhe Han Xin PengZhiyuan Junchi Yuze Ye Leisheng Rui Tianyou Wen Hao Xiujie Li Xin Yuanhang Xu Yao Hongtao Wei Qixuan Yue Ze Haoyu Jin Yu Haoxuan Qing Cang Qing Yu Zhi Ze Rui Yuan Kai Ruixuan Hong Wen Zhe Han Yu Ze Xin Lei MengQi Yizhi's peach, Mu Qing, asked Lan Erlan, Yuanxiang, early summer, Peihan, Aoshan, Manwen, Leling, crazy Shan, hatred, Yuxiwen, fragrance, cold, soft language, Ronghai, An Yerong, Hanbai, water peach, drunk, blue spring, children's language, Qin Congtong, Aoqing languageLan Youling, Bitong, Yuanshuang, pity, dream, Zihan MiaotongPeishan, cold cloud, ice rotation, Waner, green, true hopeEasy to visit Xuan Yi Yu Ling Xuan interview Hui Huai Yi Xiao Lan Chun Cui Jing Bai Ye Lei Bing Xia Meng Song Shu Xue Le Feng Nian Wei Jing Yan seeks spring hate mountains from the cold memories Xiang Bo Jing Man Fan Xuan Yi also reads Lu Zhi Lei QianLanxinbodai Zhenxinlei Yanyu Lenghui Zishan Qianqin hates the sky Aofupan Mountain Huaidi Binglan Mountain Bai Cuixuan Le Dancui Rou Valley Mountain Yao Bingluer Zhengu Xuele Xuan Han Han Hailian Ao Lei Qing Huai DongEr Yimeng cherishes the softness of the snow Wanhai Xia Qingyi Yao Miao Han Chun Bamboo Xiujie Wei Cheng Jianhui Jin Peng Tian Lei Shao Hui Ze Yang Ming Xuan Jian Bai Xuan Hao Qiang Wei Chen Bo Chao Jun Hao Zi Qian Ming Hui Peng Tao Yan BinHe Xuan Yue Bin Feng Hua Jing Qi Ming Cheng Gao Ge Guang Hua Guo Yuan Yu Han Yu Han Run Han Fei Han Hai Hao Gan Hao Bo and An Hong Bo Hong Lang Hua Ao Hua Can Jia Mu Jian Bing Jian Ming Jin Xin Jin Cheng Jin Yu Peng Jing Fu Jing Tong Jing QiJun Hao Jun Ming Ji Tong Kai Ji Kai An Kang Cheng Le Language Diligence Good Philosophy Qun Mao Yan Min Bo Ming Da Peng Zepeng Peng Zepeng Ju Cun Pu Xin Pu Yu Pu Ze Qi Sui Xiang Rong Xuan';
    
    DECLARE I_xing int DEFAULT LENGTH(xing) / 3;
    DECLARE I_ming int DEFAULT LENGTH(ming) / 3;
    DECLARE return_str varchar(2056) DEFAULT '';
    
    SET return_str = CONCAT(return_str, substring(xing, floor(1 + RAND() * I_xing), 1));#substring(str,pos,len):由 <str> 中的第 <pos> 位置开始,选出接下去的 <len> 个字元.
    SET return_str = CONCAT(return_str, substring(ming, floor(1 + RAND() * I_ming), 1));
    
    IF RAND() > 0.400 THEN
        SET return_str = CONCAT(return_str, substring(ming, floor(1 + RAND() * I_ming), 1));
    END IF;
    
    RETURN return_str;
END

生成随机手机号

CREATE FUNCTION `generatePhone`() RETURNS char(11) CHARSET utf8
DETERMINISTIC
BEGIN
    DECLARE head VARCHAR(100) DEFAULT '000,156,136,176';
    DECLARE content CHAR(10) DEFAULT '0123456789';
    DECLARE phone CHAR(11) DEFAULT substring(head, 1+(FLOOR(1 + (RAND() * 3))*4), 3);#注意sql下标从1开始
    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

生成随机日期

CREATE FUNCTION `generateDate`() RETURNS timestamp
DETERMINISTIC
BEGIN
    RETURN date(from_unixtime(unix_timestamp('2018-01-01') +floor(rand() * ( unix_timestamp('2023-01-01') -unix_timestamp('2018-01-01') + 1 )))); #产生2018-2022年之间的日期
END;

执行

CREATE PROCEDURE generateSampleDataForSalary()
    begin
    declare i int;
    set i=0;
    while i<10000 do
        #Insert statement here
        insert into employee(name, phone, salary, entry_time)
        values((select generateUserName()), (select generatePhone()), floor(rand()*(100000 - 10000) + 10000), )
    set i=i+1;
    end while;
end;
call generateSampleDataForSalary();