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

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 |+---------+------------------------------+

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);

DROP PROCEDURE IF EXISTS batchGenerateUsers;DELIMITER $$CREATE PROCEDURE batchGenerateUsers()BEGINDECLARE x INT Default 0;    DECLARE p char(11);WHILE x < 10000 DOSET x=x+1;        SET p=concat('1', cast(3+(rand()*10)%7 as char(1)), right(left(trim(cast(rand() as char (20))), 11),9));insert into my_users(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('2015-', 1+ceiling(rand()*100)%12,'-',1+ceiling(rand()*100)%28))ON DUPLICATE KEY UPDATEupdated_time=now();END WHILE;END $$#call batchGenerateUsers();