mysql生成百万测试数据脚本

-- 信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求,当mysql bin-log开启时需要如下设置
set global log_bin_trust_function_creators=TRUE;

-- --------------------------------------------- 创建各类生成随机数据的函数开始 ---------------------------------------------

-- 生成三位数随机姓名函数

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

SET name =
CONCAT(SUBSTRING(last_name, floor(1 + 190 * rand()), 1), SUBSTRING(first_name_1, floor(1 + 400 * rand()), 1),
SUBSTRING(first_name_2, floor(1 + 400 * rand()), 1));
RETURN name;
END;

-- 测试生成随机三位姓名的函数
SELECT generateName();

--
-- 生成11位随机手机号函数

DROP FUNCTION IF EXISTS generatePhone;
CREATE FUNCTION generatePhone() RETURNS CHAR(11) CHARSET utf8
BEGIN
-- 中国电信号段:133、149、153、173、177、180、181、189、199
-- 中国联通号段:130、131、132、145、155、156、166、171、175、176、185、186、166
-- 中国移动号段:134(0-8)、135、136、137、138、139、147、150、151、152、157、158、159、172、178、182、183、184、187、188、198
-- 手机号网络识别号（运营商代码）：3位
DECLARE phone_head VARCHAR(200) DEFAULT '000,133,149,153,173,177,180,181,189,199,130,131,132,145,155,156,166,171,175,176,185,186,166,134,135,136,137,138,139,147,150,151,152,157,158,159,172,178,182,183,184,187,188,198';
-- 手机号其他(归属地区代码+用户代码)：9位
DECLARE phone_content CHAR(10) DEFAULT '0123456789';
DECLARE phone CHAR(11) DEFAULT SUBSTRING(phone_head, 1 + (FLOOR(1 + (RAND() * 43)) * 4), 3);
DECLARE i INT DEFAULT 1;
DECLARE len INT DEFAULT LENGTH(phone_content);
WHILE i < 9 DO
SET i = i + 1;
SET phone = CONCAT(phone, SUBSTRING(phone_content, FLOOR(1 + RAND() * len), 1));
END WHILE;
RETURN phone;
END;

-- 测试生成11位随机手机号函数
SELECT generatePhone();

--
-- 生成随机yyyy-MM-dd生日的函数(1980-01-01到1989-12-31)

DROP FUNCTION IF EXISTS generateDate;
CREATE FUNCTION generateDate() RETURNS CHAR(10) CHARSET utf8
BEGIN
DECLARE date VARCHAR(10);
SET date :=
DATE(
FROM_UNIXTIME(
UNIX_TIMESTAMP('1980-01-01') + FLOOR(
RAND() * (
UNIX_TIMESTAMP('1990-01-01') - UNIX_TIMESTAMP('1980-01-01') + 1
)
)
)
);
RETURN date;
END;

-- 测试生成随机yyyy-MM-dd生日的函数
SELECT generateDate();

-- --------------------------------------------- 创建各类生成随机数据的函数结束 ---------------------------------------------

-- --------------------------------------------- 创建用于测试的业务表建表语句开始 ---------------------------------------------
-- 建表语句 - 教师表
DROP TABLE IF EXISTS tb_teacher;
CREATE TABLE tb_teacher (
t_id int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
t_name varchar(20) NOT NULL COMMENT '姓名',
t_birth date NOT NULL COMMENT '出生年月',
t_sex enum('male','female') DEFAULT NULL COMMENT '性别',
t_phone bigint(20) NOT NULL COMMENT '手机号',
t_height int NOT NULL COMMENT '身高',
PRIMARY KEY (t_id),
UNIQUE INDEX uk_phone(t_phone),
KEY idx_name (t_name),
KEY idx_birth (t_birth)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师信息表';

-- 建表语句 - 教师薪水表
DROP TABLE IF EXISTS tb_teacher_salary;
CREATE TABLE tb_teacher_salary (
t_id int(11) NOT NULL COMMENT '教师编号',
t_name varchar(20) NOT NULL COMMENT '教师姓名',
t_phone bigint(20) NOT NULL COMMENT '手机号',
t_salary int NOT NULL COMMENT '教师薪资',
PRIMARY KEY (t_id),
KEY idx_name (t_name),
UNIQUE INDEX uk_phone(t_phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师薪资表';

-- 建表语句 - 课程表
DROP TABLE IF EXISTS tb_course;
CREATE TABLE tb_course (
c_id varchar(5) NOT NULL COMMENT '课程编号',
c_name varchar(20) NOT NULL COMMENT '课程名称',
t_id int(11) NOT NULL COMMENT '教师编号',
KEY idx_tid (t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

-- --------------------------------------------- 创建用于测试的业务表建表语句结束 ---------------------------------------------

-- --------------------------------------------- 创建生成任意数量数据的存储过程开始 ---------------------------------------------

-- 生成任意数量数据的存储过程(批量插入，效率相对较高)

-- 生成任意数量的tb_teacher数据
DROP PROCEDURE IF EXISTS prod_create_teacher;
CREATE PROCEDURE prod_create_teacher(IN num INT)
BEGIN
DECLARE i INT DEFAULT num;
DECLARE exec_sql VARCHAR(5000) CHARSET utf8mb4 DEFAULT '';
SET exec_sql := 'INSERT INTO tb_teacher(t_name, t_birth, t_sex, t_phone, t_height) VALUES ';
WHILE i > 0 DO
SET @insert_val := CONCAT_WS(',', CONCAT('"', generateName(), '"'), CONCAT('"', generateDate(), '"'), CONCAT('"', IF(i % 2 = 0, 'male', 'female'), '"'),
generatePhone(), 170+i%10);
SET @insert_val := CONCAT('(', @insert_val, ')', ',');
SET exec_sql := CONCAT(exec_sql, @insert_val);
SET i := i - 1;
IF (i % 100 = 0) THEN
SET @exec_sql := LEFT(exec_sql, CHAR_LENGTH(exec_sql) - 1);
PREPARE stmt FROM @exec_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET exec_sql := 'INSERT INTO tb_teacher(t_name, t_birth, t_sex, t_phone, t_height) VALUES ';
END IF;
END WHILE;
END;

-- --------------------------------------------- 创建生成任意数量数据的存储过程结束 ---------------------------------------------

-- --------------------------------------------- 创建100w业务数据开始 ---------------------------------------------
-- 如果创建百万数据，可以多创建一些，然后删除掉部分重复数据，来达到t_phone不重复的目的
-- 临时删除唯一约束，数据处理完成后需要重新加上约束
ALTER TABLE tb_teacher DROP INDEX uk_phone;
-- [2021-01-10 16:20:40] completed in 6 m 24 s 17 ms
CALL prod_create_teacher(1200000);

-- 删除重复的手机号(创建临时的索引，为了加快删除速度，删除完毕后需要将索引删除)
ALTER TABLE tb_teacher ADD INDEX idx_phone(t_phone);
DELETE FROM tb_teacher
WHERE t_phone IN (
SELECT phone.t_phone FROM (
SELECT t_phone FROM tb_teacher
GROUP BY t_phone HAVING COUNT(*) > 1
) phone
);
ALTER TABLE tb_teacher DROP INDEX idx_phone;

-- 查看剩余数据条数
-- 1122890
SELECT COUNT(*) FROM tb_teacher;

-- 删除多余数据，只保留100w条数据
DELETE FROM tb_teacher WHERE t_id >= (SELECT max_id.t_id FROM (SELECT t_id FROM tb_teacher ORDER BY t_id LIMIT 1000000,1) max_id);
-- 核对结果：1000000
SELECT COUNT(*) FROM tb_teacher;

-- 删除重复后修改t_phone为唯一性主键
ALTER TABLE tb_teacher ADD UNIQUE INDEX uk_phone(t_phone);

-- 创建教师薪水表测试数据
INSERT INTO tb_teacher_salary(t_id, t_name, t_phone, t_salary)
SELECT t_id, t_name, t_phone, t_height*100 FROM tb_teacher;

SELECT COUNT(*) FROM tb_teacher_salary;

-- 创建课程表测试数据
INSERT INTO tb_course(c_id, c_name, t_id)
SELECT CASE t_id%10 WHEN 0 THEN '001' WHEN 1 THEN '002' WHEN 2 THEN '003' WHEN 3 THEN '004' WHEN 4 THEN '005' WHEN 5 THEN '006'
WHEN 6 THEN '007' WHEN 7 THEN '008' WHEN 8 THEN '009' WHEN 9 THEN '010' END,
CASE t_id%10 WHEN 0 THEN '语文' WHEN 1 THEN '数学' WHEN 2 THEN '英语' WHEN 3 THEN '物理' WHEN 4 THEN '化学' WHEN 5 THEN '生物'
WHEN 6 THEN '政治' WHEN 7 THEN '历史' WHEN 8 THEN '地理' WHEN 9 THEN '体育' END,
t_id
FROM tb_teacher;

DROP PROCEDURE IF EXISTS prod_create_course;
CREATE PROCEDURE prod_create_course(IN num INT)
BEGIN
DECLARE i INT DEFAULT num;
WHILE i > 0 DO
INSERT INTO tb_course(c_id,c_name,t_id)
VALUES ( CASE i%10 WHEN 0 THEN '001' WHEN 1 THEN '002' WHEN 2 THEN '003' WHEN 3 THEN '004' WHEN 4 THEN '005' WHEN 5 THEN '006'
WHEN 6 THEN '007' WHEN 7 THEN '008' WHEN 8 THEN '009' WHEN 9 THEN '010' END,
CASE i%10 WHEN 0 THEN '语文' WHEN 1 THEN '数学' WHEN 2 THEN '英语' WHEN 3 THEN '物理' WHEN 4 THEN '化学' WHEN 5 THEN '生物'
WHEN 6 THEN '政治' WHEN 7 THEN '历史' WHEN 8 THEN '地理' WHEN 9 THEN '体育' END,
i);
SET i := i - 1;
END WHILE;
END;

-- --------------------------------------------- 创建100w业务数据结束 ---------------------------------------------
-- --------------------------------------------- 创建100w业务数据结束 ---------------------------------------------
-- --------------------------------------------- 创建100w业务数据结束 ---------------------------------------------

DROP TABLE IF EXISTS tb_score;
CREATE TABLE IF NOT EXISTS tb_score (
t_id int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
t_name varchar(20) NOT NULL COMMENT '学生姓名',
c_id varchar(5) COMMENT '课程编号',
c_name varchar(20) COMMENT '课程名称',
s_score int COMMENT '分数',
PRIMARY KEY (t_id),
KEY idx_cname_cid (c_name,c_id),
KEY idx_tname_cid_cname (t_name,c_id,c_name),
KEY idx_score (s_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='分数信息表';

DROP PROCEDURE IF EXISTS prod_create_score;
CREATE PROCEDURE prod_create_score(IN num INT)
BEGIN
DECLARE i INT DEFAULT num;
WHILE i > 0 DO
INSERT INTO tb_score(t_name,c_id,c_name,s_score)
VALUES ( generateName(),CASE i%12 WHEN 0 THEN '001' WHEN 1 THEN '002' WHEN 2 THEN '003' WHEN 3 THEN '004' WHEN 4 THEN '005' WHEN 5 THEN '006'
WHEN 6 THEN '007' WHEN 7 THEN '008' WHEN 8 THEN '009' WHEN 9 THEN '010' WHEN 10 THEN '011' WHEN 11 THEN '012' END,
CASE i%12 WHEN 0 THEN '语文' WHEN 1 THEN '数学' WHEN 2 THEN '英语' WHEN 3 THEN '物理' WHEN 4 THEN '化学' WHEN 5 THEN '生物'
WHEN 6 THEN '政治' WHEN 7 THEN '历史' WHEN 8 THEN '地理' WHEN 9 THEN '体育' WHEN 10 THEN '美术' WHEN 11 THEN '品德' END,
81+i%20);
SET i := i - 1;
END WHILE;
END;

TRUNCATE TABLE tb_score;
CALL prod_create_score(10000);