Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to batch generate MySQL does not repeat the example of mobile phone number, the article is very detailed, has a certain reference value, interested friends must read it!

Generate a script

Generate instructions:

The following uses stored procedures to batch generate a large table containing fields such as user name, mobile phone number, date of birth, etc.

The stored procedure uses uid as the primary key, so a small number of duplicate mobile phone numbers are generated, followed by a filtered SQL script.

If you want to generate non-repeating mobile phone numbers at one time, you can consider modifying the following script to remove uid and use the mobile column as the primary key

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, `day` 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 二、数据填充 call prc_gen_user(1000);Query OK, 1 row affected (1.38 sec)select count(*) from big_table;+----------+| count(*) |+----------+| 1000 |+----------+1 row in set (0.00 sec)select 'Leshami' author,'http://blog.csdn.net/leshami' Blog;+---------+------------------------------+| author | Blog |+---------+------------------------------+| Leshami | http://blog.csdn.net/leshami |+---------+------------------------------+ 三、滤重SQL语句 DELETE FROM big_tableWHERE 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)

IV. Other

This article refers to the following code, the following code implementation is MySQL batch production of user data, name / mobile phone number / birthday / password

DROP PROCEDURE IF EXISTS batchGenerateUsers;DELIMITER $$CREATE PROCEDURE batchGenerateUsers () BEGINDECLARE x INT Default 0; DECLARE p char (11); WHILE x < 10000 DOSET xylene 1; SET p=concat ('1x), cast (3 + (rand () * 10)% 7 as char (1)), right (trim (cast (rand () as char (20)), 11), 9) Insert into my_users (mobile, passwd, name, sex, birthday, updated_time) values (ceiling (rand () * 1000000)) Concat (substring) (Zhao Qian Sun Li Zhou Wu Zheng Feng Chen Zhuwei Jiang Shen Han Yang Zhu Qin Yu Xu Lu Lu Shi Zhang Kong Cao Yanhua Jin Jiang Qi Xie Zou Yu Baishui Su Pan GE Xi Fan Peng Lang Lu Wei Chang Ma Miao Fenghua Fang Yu Yuan Liu Yi Bao Shi Tang Fei Lian he Ni Tang Teng Yin Luo Bi Hao Wu an often happy Fu Pi Qi Kang Wu Yuanbu Gu Mengping Huang and Xiao Yin Yao Shao Yin Yao Qi Mao Di Miao Ming Zang Ji Cheng Dai On Song Maopang Xiong Jishu qu Xiang Zhu Dong Liang du Ruan Min Xi Ji Maqiang Jia Lu Lou Wei Jiang Tong Yan Guo Mei Sheng Lin Diaozhong Xu Qiu Luo Gao Xia Cai Tian Fan Hu Ling Huo Yu Wan Ke Mo Jing dry solution of the house fur should Zong Ding Xuan Ben Deng Yu Dan Hang Hongbao Zuo Shi Cui Ji Niu Gong Floor (1+190*rand ()), 1) Substring (Ming Hua Jianwen Ping Zhiwei Donghaiqiang Xiaoguang Lin Xiaomin Yongjie Jun Jin Jian Yizhong Hong Jiang Fuxiang Zhenyong Yao Chun Da Ningliang Yuxingbao Shaojian Yunxue Rentao Rui Pengan Yazhe Han Dawei Li Min Group Rong Xinfeng long Deqing Bin Hui Liang Yu Jun Li Hao Tian Qing Qing Chief Jiahong Shan Xianyang Le Feng Lefeng Zhi Qing Yueyuan Wu Guangsi Wei Qichang Ming Yingkai Hong Sen Super strong Xu Chuan Kang Following Xiangdong Zhong Quan Qi Li Nanwei Younian Zhen Xin Lei Bing Wanxingjun Lun Shaolin Yuxing Xiyanzhao Guiyuan Jingsheng Huichen Huikai Zhangrun Gao Jiahu Root far into Quan Maoyi Fu Bo Lin Shun Hao Shu and Eun to Daochuan Binbo Lei Ming Bingji Guanhui Jing Xin Tingzhe Baoqiu Jun Jin Xuan Fan Ruo Xun Zu Xi Ji Chongjun Tian Shiyi Pu Yun Fu Bo Shen Xianghao Chengzi Xue Meng Qi Chao Bing Lu Yu Han Zheng humble voyage Shiyao Chengshou Fengge pure weathered Yue Silver Helin Xianhuan Ling Hao Xiyu Baijie Ding Qi Sheng Pei Qi Hong such as Jing Yat Yue Yue Yan Yue Tong Hsiao Xian Meng Ying Yue Yue Pei Sung Dong Kui Yang Shuangkun Town Chu Shui Tiexi Di and Bin Bang Xianchong Chao Shanfei Hengjin Rudan for the morning show Yan Chen Yang Yulan Yi Ling Qi Meiqi also Shu Jing Zhen Shuang Ya Yi Static chess is suitable for male Weizhi, Feluna, Shanwen Shu, Manping, Zhu Qin, Su Mei, Linglei, Linglei, brilliant Purple, Beautiful Rhythm, Qianxi, Yan, Bifen, er, Lan Ting, Junie, Yuan Lianjuan. Floor (1+400*rand ()), 1) Substring (Ming Hua Jianwen Ping Zhiwei Donghaiqiang Xiaoguang Lin Xiaomin Yongjie Jun Jin Jian Yizhong Hong Jiang Fuxiang Zhenyong Yao Chun Da Ningliang Yuxingbao Shaojian Yunxue Rentao Rui Pengan Yazhe Han Dawei Li Min Group Rong Xinfeng long Deqing Bin Hui Liang Yu Jun Li Hao Tian Qing Qing Chief Jiahong Shan Xianyang Le Feng Lefeng Zhi Qing Yueyuan Wu Guangsi Wei Qichang Ming Yingkai Hong Sen Super strong Xu Chuan Kang Following Xiangdong Zhong Quan Qi Li Nanwei Younian Zhen Xin Lei Bing Wanxingjun Lun Shaolin Yuxing Xiyanzhao Guiyuan Jingsheng Huichen Huikai Zhangrun Gao Jiahu Root far into Quan Maoyi Fu Bo Lin Shun Hao Shu and Eun to Daochuan Binbo Lei Ming Bingji Guanhui Jing Xin Tingzhe Baoqiu Jun Jin Xuan Fan Ruo Xun Zu Xi Ji Chongjun Tian Shiyi Pu Yun Fu Bo Shen Xianghao Chengzi Xue Meng Qi Chao Bing Lu Yu Han Zheng humble voyage Shiyao Chengshou Fengge pure weathered Yue Silver Helin Xianhuan Ling Hao Xiyu Baijie Ding Qi Sheng Pei Qi Hong such as Jing Yat Yue Yue Yan Yue Tong Hsiao Xian Meng Ying Yue Yue Pei Sung Dong Kui Yang Shuangkun Town Chu Shui Tiexi Di and Bin Bang Xianchong Chao Shanfei Hengjin Rudan for the morning show Yan Chen Yang Yulan Yi Ling Qi Meiqi also Shu Jing Zhen Shuang Ya Yi Static chess is suitable for male Weizhi, Feluna, Shanwen Shu, Manping, Zhu Qin, Su Mei, Linglei, Linglei, brilliant Purple, Beautiful Rhythm, Qianxi, Yan, Bifen, er, Lan Ting, Junie, Yuan Lianjuan. Floor (1+400*rand (), 1)), ceiling (rand () * 10)% 2 (now ()-interval (20+ceiling (rand () * 100)% 40) year), concat ('2015 rand () * 100),' -', 1+ceiling (rand () * 100)% 28) ON DUPLICATE KEY UPDATEupdated_time=now () END WHILE;END $$# call batchGenerateUsers ()

And modify it appropriately.

The above is all the contents of the article "how to batch generate MySQL without repeating mobile phone numbers". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.