MySQL快速生成大量测试数据

需要大量的测试数据用来学习索引，性能优化，分库分表，等等。百度一下发现一个很不错的博文，https://blog.csdn.net/leshami/article/details/84348477，本随笔在此基础上修改。毕竟咱要做个搬运工也得是个有灵魂的搬运工么

一、如果数据库中表存在则删除表

DROP TABLE IF EXISTS user;

二、如果存储过程存在则删除存储过程

DROP PROCEDURE IF EXISTS prc_crt_user;

三、创建表结构

这是MySQL8使用创建表的方式，MySQL5创建表的方式与之不同，需要注意

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

调用存储过程，填入要生成的行数

call prc_crt_user(100000);

去除，这一步可省略

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