MySQL 存储过程批量插入总结

向右看齐 2024-04-23 21:26 189阅读 0赞

**功能需求背景**：今天接到产品经理核心业务表的数据压测功能，让我向核心业务表插入百万级的业务量数据，我首先想到的办法就是**存储过程实现数据的批量**。

由于无法提供核心业务表，本文仅仅提供我刚刚自己创建的表bds\_base\_user 表做相关功能展示。

**表DDL**

CREATE TABLE `bds_base_user`(
        `id` varchar(64) NOT NULL,
        `age` int(11) DEFAULT'O',
        `name` varchar(64) NOT NULL,
        `amount` decimal(20,8) NOT NULL,
        PRIMARY KEY (id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

**存储过程版本1.0**

create procedure proc_batch
    begin
        declare i int default 0;
        declare uid varchar(64) default 
        declare uamount decimal(20，8) default 0;
        declare uage int default 0;
        declare uname varchar(64) default '';
    
        -- 定义SQL异常处理：主键重复
        declare continue handler for 1062 select 'duplicate key error encountered';
    
        -- 开启事务
        start transaction;
        -- 循环遍历
        --组装id
        select concat('1',ceiling(rand() * 9000000000 + 1000000000)) into uid;
        --组装age
        select floor(18 + (rand() * 9)) into uage;
        --组装amount
        select round((10 +(rand() * 1001)), 8) into uamount;
        --组装name
        select concat('测试账户',floor(rand()*11+10)) into uname;
       
        insert into bds _base_user(id, age, name, amount) values(uid, uage, uname, uamount);
    
        set i=i+1;
        if i % 1000 = 0 then
            commit;
        end if;
    end while;
    end

我在自己的座位上正在正常执行向核心业务表的注入数据的存储过程，产品经理火急火燎的跑过来通知我，批量注入的数据质量必须考虑。

**存储过程版本2.0**

目标： 主要解决模拟数据的质量问题，主要体现在：姓名、电话号码、邮箱、公司名称、家庭\\公司住址、字段限定取值范围（涉及字符串和整形）、金额、 时间、系统主键和默认值等相关问题。

解决办法：采用自定义函数实现

**自定义函数总结**

集合生成和判断

-- 生成随机数1-3
    select round(rand()*(3-0) + 0) from dual;
    
    --指定集合随机获取
    select el from(
    select'新增' as el from dual
    union all select '修改' from dual
    union all select '删除' from dual
    union all select '查询' from dual
    )as temp
    order by rand()
    limit 1;
    
    -- 判断指定字符串是否在指定集合范围内
    select ifnull(count(1), 0) from(
    select'新增' as el from dual
    union all select '修改' from dual
    union all select '删除' from dual
    union all select '查询' from dual
    )as temp where el ='测试'

-- 生成随机电话号码
    select  concat('1',
       substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),
       right(left(trim(cast(rand() AS char(50))), 11), 9));

-- 生成随机邮箱
    select CONCAT( FLOOR(UNIX_TIMESTAMP()+10000000000*rand()),'@',ELT(CEILING(RAND( ) * 4) ,  "qq.com","163.com","sina.com","gmail.com"))

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

-- 生成随机省
    SELECT ELT(CEILING(RAND( ) * 34) ,  "河北省","山西省","辽宁省","吉林省","黑龙江省","江苏省","浙江省","安徽省","福建省","江西省","山东省","河南省","湖北省","湖南省","广东省","海南省","四川省","贵州省","云南省","陕西省","甘肃省","青海省","台湾省","内蒙古自治区","广西壮族自治区","西藏自治区","宁夏回族自治区","新疆维吾尔自治区","北京市","上海市","天津市","重庆市","香港特别行政区","澳门特别行政区")
    -- 生成随机市
    SELECT ELT(CEILING(RAND( ) * 269) ,"石家庄","保定市","秦皇岛","唐山市","邯郸市","邢台市","沧州市","承德市","廊坊市","衡水市","张家口","太原市","大同市","阳泉市","长治市","临汾市","晋中市","运城市","晋城市","忻州市","朔州市","吕梁市","呼和浩特","呼伦贝尔","包头市","赤峰市","乌海市","通辽市","鄂尔多斯","乌兰察布","巴彦淖尔","盘锦市","鞍山市","抚顺市","本溪市","铁岭市","锦州市","丹东市","辽阳市","葫芦岛","阜新市","朝阳市","营口市","吉林市","通化市","白城市","四平市","辽源市","松原市","白山市","伊春市","牡丹江","大庆市","鸡西市","鹤岗市","绥化市","双鸭山","七台河","佳木斯","黑河市","齐齐哈尔市","无锡市","常州市","扬州市","徐州市","苏州市","连云港","盐城市","淮安市","宿迁市","镇江市","南通市","泰州市","绍兴市","温州市","湖州市","嘉兴市","台州市","金华市","舟山市","衢州市","丽水市","合肥市","芜湖市","亳州市","马鞍山","池州市","淮南市","淮北市","蚌埠市","巢湖市","安庆市","宿州市","宣城市","滁州市","黄山市","六安市","阜阳市","铜陵市","福州市","泉州市","漳州市","南平市","三明市","龙岩市","莆田市","宁德市","南昌市","赣州市","景德镇","九江市","萍乡市","新余市","抚州市","宜春市","上饶市","鹰潭市","吉安市","潍坊市","淄博市","威海市","枣庄市","泰安市","临沂市","东营市","济宁市","烟台市","菏泽市","日照市","德州市","聊城市","滨州市","莱芜市","郑州市","洛阳市","焦作市","商丘市","信阳市","新乡市","安阳市","开封市","漯河市","南阳市","鹤壁市","平顶山","濮阳市","许昌市","周口市","三门峡","驻马店","荆门市","咸宁市","襄樊市","荆州市","黄石市","宜昌市","随州市","鄂州市","孝感市","黄冈市","十堰市","长沙市","郴州市","娄底市","衡阳市","株洲市","湘潭市","岳阳市","常德市","邵阳市","益阳市","永州市","张家界","怀化市","江门市","佛山市","汕头市","湛江市","韶关市","中山市","珠海市","茂名市","肇庆市","阳江市","惠州市","潮州市","揭阳市","清远市","河源市","东莞市","汕尾市","云浮市","广西省：","南宁市","贺州市","柳州市","桂林市","梧州市","北海市","玉林市","钦州市","百色市","防城港","贵港市","河池市","崇左市","来宾市","海口市","三亚市","乐山市","雅安市","广安市","南充市","自贡市","泸州市","内江市","宜宾市","广元市","达州市","资阳市","绵阳市","眉山市","巴中市","攀枝花","遂宁市","德阳市","贵阳市","安顺市","遵义市","六盘水","昆明市","玉溪市","大理市","曲靖市","昭通市","保山市","丽江市","临沧市","拉萨市","阿里","咸阳市","榆林市","宝鸡市","铜川市","渭南市","汉中市","安康市","商洛市","延安市","兰州市","白银市","武威市","金昌市","平凉市","张掖市","嘉峪关","酒泉市","庆阳市","定西市","陇南市","天水市","西宁市","银川市","固原市","青铜峡市","石嘴山市","中卫市","乌鲁木齐","克拉玛依市")
    -- 生成随机区/县
    SELECT ELT(CEILING(RAND( ) * 23) ,  "朝阳区","海淀区","通州区","房山区","丰台区","昌平区","大兴区","顺义区","西城区","延庆县","石景山区","宣武区","怀柔区","崇文区","密云县","东城区","门头沟区","平谷区","江北区","渝北区","沙坪坝区","九龙坡区","万州区")

-- 生成随机1990 后30年
    SELECT date( CONCAT(CEILING(30*RAND())+1990,'-',CEILING(12*RAND()),'-',CEILING(30*RAND())) )

**创建生成主键函数：**

delimiter //
    create function fun_id()
    	returns varchar(64)
    begin
    		return concat('10000',SUBSTR(md5(UUID()), 5));
    end //
    delimiter ;
    
    select fun_id() from dual;

MySQL 8 执行遇到的报错：

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

造成原因：MySQL 服务中/my.ini 配置中**log\_bin\_trust\_function\_creators** 变量默认为关闭，需要设置为开启。

**方式1**（**临时方案**，MySQL服务重启会失效）  
可以通过SQL语句设置，SQL语句如下：  
**1）查询变量值**

show variables like 'log_bin_trust_function_creators';

![2ce9dd59a762b7b2daefd20a96a40ae6.png][]

**2）设置变量值**

set global log_bin_trust_function_creators = 1;
    
    show variables like 'log_bin_trust_function_creators';

![a3a976d71b82fca9a209f4f692a3a77f.png][]

**方式2**（**永久方案**，需要重启MySQL服务，配置才能生效）  
在 my.cnf 配置文件中添加如下一行配置，然后重启MySQL服务

log_bin_trust_function_creators = 1

![c044f4f86045658323b3d6b61e334c8c.png][]

**创建随机生成家庭/公司地址函数**

新增省、市、区/县和街道数据表。

-- bill.base_province definition
    
    CREATE TABLE `base_province` (
      `id` int NOT NULL AUTO_INCREMENT,
      `code` varchar(100) NOT NULL,
      `name` varchar(100) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- bill.base_city definition
    
    CREATE TABLE `base_city` (
      `id` int NOT NULL AUTO_INCREMENT,
      `code` varchar(64) NOT NULL,
      `name` varchar(64) NOT NULL,
      `province_code` varchar(64) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=343 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- bill.base_area definition
    
    CREATE TABLE `base_area` (
      `id` int NOT NULL AUTO_INCREMENT,
      `code` varchar(64) NOT NULL,
      `name` varchar(64) NOT NULL,
      `city_code` varchar(64) NOT NULL,
      `province_code` varchar(64) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2979 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- bill.base_street definition
    
    CREATE TABLE `base_street` (
      `id` int NOT NULL AUTO_INCREMENT,
      `code` varchar(64) NOT NULL,
      `name` varchar(64) NOT NULL,
      `arae_code` varchar(64) NOT NULL,
      `city_code` varchar(64) NOT NULL,
      `province_code` varchar(64) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=41353 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

数据来源请参考：[Administrative-divisions-of-China][]

核心SQL：

SELECT 
        (select p.name  from base_province p where p.code = t1.province_code) as provinceName,
    	(select c.name  from base_city c where c.code = t1.city_code) as cityName,
    	(select a.name  from base_area a where a.code = t1.arae_code) as areaName,
    	t1.name FROM base_street AS t1 JOIN 
    (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM base_street)-(SELECT MIN(id) FROM base_street ))+(SELECT MIN(id) FROM base_street)) AS id) AS t2 WHERE t1.id >= t2.id  
    ORDER BY t1.id LIMIT 1 ;

效果截图：

![dd7d14e3968e4c1382fe75400d96c682.png][]

最后用随机函数生成号数 ，至此完成随机生成地址信息。

[2ce9dd59a762b7b2daefd20a96a40ae6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/4abb04182b384cb2ac06a7f6c4c9c85b.png
[a3a976d71b82fca9a209f4f692a3a77f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/c6bea6d6629446e7ab288fda7a4bb89e.png
[c044f4f86045658323b3d6b61e334c8c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/7f955008c9de4980837c22f5538de52f.png
[Administrative-divisions-of-China]: https://github.com/modood/Administrative-divisions-of-China?tab=readme-ov-file
[dd7d14e3968e4c1382fe75400d96c682.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/23/5c863f402965483f92b9d8cfcb3a26b3.png