批量生成MySQL不重复手机号大表

港控/mmm° 2022-04-15 06:54 232阅读 0赞

在MySQL很多测试场景，需要人工生成一些测试数据来测试。本文提供一个构造MySQL大表存储过程，可以生成包含用户名，手机号码，出生日期等字段。也可以通过滤重来使得手机号码不重复，模拟现实场景。

### 一、生成脚本 ###

生成说明：  
以下使用存储过程批量生成包含用户名，手机号，出生日期等字段大表。  
该存储过程使用基于uid作为主键，因此会生成少量重复手机号码，后面有滤重SQL脚本。  
如果想一次性生成不重复手机号码，可以考虑修改以下脚本，去掉uid，基于mobile列作为主键

DROP TABLE IF EXISTS big_table;
    
    DROP PROCEDURE IF EXISTS prc_gen_user;
    
    CREATE TABLE `big_table` (
      `uid` int(11) NOT NULL AUTO_INCREMENT,
      `mobile` char(11) DEFAULT NULL,
      `passwd` varchar(50) DEFAULT NULL,
      `name` varchar(50) DEFAULT NULL,
      `sex` tinyint DEFAULT NULL,
      `birthday` datetime DEFAULT NULL,
      `updated_time` datetime DEFAULT NULL,
      PRIMARY KEY (`uid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE PROCEDURE prc_gen_user(l_cnt int)
    BEGIN
       DECLARE x INT DEFAULT 0;
       DECLARE p char(11);
    
       WHILE x < l_cnt
       DO
          SET x = x + 1;
          SET p =
                 concat('1',
                        substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),
                        right(left(trim(cast(rand() AS char(50))), 11), 9));
    
          INSERT INTO big_table(mobile,
                                passwd,
                                name,
                                sex,
                                birthday,
                                updated_time)
             VALUES (
                       p,
                       md5(ceiling(rand() * 1000000)),
                       concat(
                          substring(
                             '赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金
                              魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任
                              袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康
                              伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈
                              宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛
                              林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗
                              丁宣贲邓郁单杭洪包诸左石崔吉钮龚',
                             floor(1 + 190 * rand()),
                             1),
                          substring(
                             '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                              正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                              敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                              贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                              康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                              源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                              恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                              勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                              裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                              延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                              宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                              先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                              晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                              玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',
                             floor(1 + 400 * rand()),
                             1),
                          substring(
                             '明国华建文平志伟东海强晓生光林小民永杰军金健一忠洪江福祥中
                              正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜
                              敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山
                              贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿森超坚旭政传
                              康继翔栋仲权奇礼楠炜友年震鑫雷兵万星骏伦绍麟雨行才希彦兆贵
                              源有景升惠臣慧开章润高佳虎根远力进泉茂毅富博霖顺信凡豪树和
                              恩向道川彬柏磊敬书鸣芳培全炳基冠晖京欣廷哲保秋君劲轩帆若连
                              勋祖锡吉崇钧田石奕发洲彪钢运伯满庭申湘皓承梓雪孟其潮冰怀鲁
                              裕翰征谦航士尧标洁城寿枫革纯风化逸腾岳银鹤琳显焕来心凤睿勤
                              延凌昊西羽百捷定琦圣佩麒虹如靖日咏会久昕黎桂玮燕可越彤雁孝
                              宪萌颖艺夏桐月瑜沛诚夫声冬奎扬双坤镇楚水铁喜之迪泰方同滨邦
                              先聪朝善非恒晋汝丹为晨乃秀岩辰洋然厚灿卓杨钰兰怡灵淇美琪亦
                              晶舒菁真涵爽雅爱依静棋宜男蔚芝菲露娜珊雯淑曼萍珠诗璇琴素梅
                              玲蕾艳紫珍丽仪梦倩伊茜妍碧芬儿岚婷菊妮媛莲娟一',
                             floor(1 + 400 * rand()),
                             1)),
                       ceiling(rand() * 10) % 2,
                       date(
                            now()
                          - INTERVAL (20 + ceiling(rand() * 100) % 40) YEAR),
                       concat('2018-',
                              1 + ceiling(rand() * 100) % 12,
                              '-',
                              1 + ceiling(rand() * 100) % 28))
          ON DUPLICATE KEY UPDATE updated_time = now();
       END WHILE;
    END

### 二、数据填充 ###

### 三、滤重SQL语句 ###

DELETE FROM big_table
    WHERE mobile IN (SELECT mobile
                         FROM (SELECT u1.mobile
                               FROM big_table u1
                               GROUP BY u1.mobile
                               HAVING count(*) > 1) a)
          AND uid NOT IN (SELECT uid
                          FROM (SELECT min(u2.uid) AS uid
                                FROM big_table u2
                                GROUP BY u2.mobile
                                HAVING count(*) > 1) b);

### 四、其它 ###

本文参考了https://blog.csdn.net/hankersyan/article/details/79973899  
并对其进行适当修改。