MYSQL 产生大量数据的过程

CREATE TABLE `personal_identity_info` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `identity_number` char(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '身份证号码',
  `surname` varchar(3) COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓氏',
  `name` varchar(15) COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
  `mobile` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机',
  `sex` char(1) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男;女',
  `birthday` datetime NOT NULL COMMENT '出生日期',
  `age` tinyint unsigned NOT NULL DEFAULT '0',
  `top_education` varchar(2) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '文盲' COMMENT '学历:0 文盲;1 小学,2初中,3 高中,4 大学,5 硕士,6 博士',
  `income_year` int unsigned DEFAULT '0' COMMENT '收入 元',
  `create_time` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `update_time` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  UNIQUE KEY `identity_number_unique` (`identity_number`),
  UNIQUE KEY `mobile_unique` (`mobile`),
  KEY `idx_personal_identity_info_name` (`name`),
  KEY `idx_personal_identity_info_top_education` (`top_education`),
  KEY `idx_personal_identity_info_create_time` (`create_time`),
  KEY `idx_personal_identity_info_mobile` (`mobile`)
) ENGINE=InnoDB AUTO_INCREMENT=1004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='个人身份信息表';

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `prc_gen_user`(l_cnt int)
BEGIN
  DECLARE x INT DEFAULT 0;

   WHILE x < l_cnt
   DO
      SET x = x + 1;

insert into `personal_identity_info` (
  `identity_number`,
  `surname`,
  `name` ,
  `mobile` ,
  `sex` ,
  `birthday`,
  `age`,
  `top_education`,
  `income_year`)   
select  
  concat(floor(1 + rand(1) * (32)) ,right(left(trim(cast(rand() as char(50))),20),16)) as idcard,
  substring(replace('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金
             魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任
             袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康
             伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈
             宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛
             林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗
             丁宣贲邓郁单杭洪包诸左石崔吉钮龚',' ',''), floor(1 + 190 * rand()),1) as "姓氏"  ,

    concat(  
             substring(replace(   
                         '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                          正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                          敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                          贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                          康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                          源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                          恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                          勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                          裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                          延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                          宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                          先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                          晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                          玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',' ',''), floor(1 + 400 * rand()), 1),
              substring(replace( 
                         '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                          正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                          敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                          贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                          康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                          源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                          恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                          勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                          裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                          延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                          宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                          先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                          晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                          玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一'
                          ,' ',''),floor(1 + 400 * rand()),1),                          
              substring(replace(  '边君军俊骏帅柔善文温雯娜基帝德恩师',' ',''), floor(1 + 17 * rand()),1) 
              )  as "名字" ,
                concat('1',substring(cast(3 + (rand() * 10) % 7 as char(50)), 1, 1),right(left(trim(cast(rand() as char(50))), 11), 9)) as mobile,
                (case when round(rand()*10)%2=0 then '女' else '男' end ) as sex,
                date(now()- interval (20 + ceiling(rand() * 100) % 40) year) as birthday,
                floor(1 + rand() * (150)) as age,
                (case when floor(0 + rand() * (7))=0 then '文盲'  
                      when floor(0 + rand() * (7))=1 then '小学'
                      when floor(0 + rand() * (7))=2 then '初中'
                      when floor(0 + rand() * (7))=3 then '高中'
                      when floor(0 + rand() * (7))=4 then '大学'
                      when floor(0 + rand() * (7))=5 then '硕士' 
                      when floor(0 + rand() * (7))=6 then '博士'
                      else '文盲'
                      end) as top_education,
                 floor(1000 + rand() * (90000))  as income_year                   
ON DUPLICATE KEY UPDATE identity_number = concat(floor(1 + rand(1) * (32)) ,right(left(trim(cast(rand() AS char(50))),20),16)) ,
                        mobile =   concat('1',substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),right(left(trim(cast(rand() AS char(50))), 11), 9)) ;             
   END WHILE;
END$$
DELIMITER ;