前言
在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
二、數據填充
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_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);
四、其它
本文參考了以下代碼，下面代碼實現是MySQL批量造用戶數據，姓名/手機號/生日/密碼
DROP PROCEDURE IF EXISTS batchGenerateUsers;
DELIMITER $$
CREATE PROCEDURE batchGenerateUsers()
BEGIN
DECLARE x INT Default 0;
DECLARE p char(11);
WHILE x 10000 DO
SET 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 UPDATE
updated_time=now();
END WHILE;
END $$
#call batchGenerateUsers();
并對其進行適當修改。
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。
您可能感興趣的文章:- MySQL 大表添加一列的實現
- MySQL如何優雅的刪除大表實例詳解
- 淺談MySQL大表優化方案
- MySQL大表中重復字段的高效率查詢方法
- MySQL 刪除大表的性能問題解決方案
- mysql 大表批量刪除大量數據的實現方法
