MySQL批量插入测试数据的几种方式

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL批量插入测试数据的几种方式

MySQL批量插入测试数据的几种方式

前言

在开发过程中我们不管是用来测试性能还是在生产环境中页面展示好看一点, 又或者学习验证某一知识点经常需要一些测试数据, 这个时候如果手敲的话, 十行二十行还好, 多了就很死亡了, 接下来介绍两种常用的MySQL测试数据批量生成方式

  • 存储方式+函数
  • Navicat的数据生成

一、表

准备了两张表

  • 角色表:
  • id: 自增长
  • role_name: 随机字符串, 不允许重复
  • orders: 1-1000任意数字
  • 用户表:
  • id: 自增长
  • username: 随机字符串, 不允许重复
  • password: 随机字符串, 允许重复
  • role_id: 1-10w之间的任意数字
  • 建表语句:


CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL COMMENT '用户名',
  `role_id` int(11) DEFAULT NULL COMMENT '角色id',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `salt` varchar(255) DEFAULT NULL COMMENT '盐',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(255) DEFAULT NULL COMMENT '角色名',
  `orders` int(11) DEFAULT NULL COMMENT '排序权重\r\n',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

二、使用函数生成

通过存储过程快速插入, 通过函数保证数据不重复

设置允许创建函数

查看 MySQL是否允许创建函数


SHOW VARIABLES LIKE 'log_bin_trust_function_creators';

image.png

结果如图所示, 我们使用以下命令将创建函数功能打开(global-所有session都生效)


SET GLOBAL log_bin_trust_function_creators=1;

image.png

这个时候再一次查询就会显示已打开

image.png

产生随机字符串


-- 随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN    
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO  
    SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));  
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END $$
-- 假如要删除
-- drop function rand_string;

产生随机数字


-- 用于随机产生区间数字
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN   
 DECLARE i INT DEFAULT 0;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+1));
RETURN i;  
END$$
-- 假如要删除
-- drop function rand_num;

三、创建存储过程

插入角色表


-- 插入角色数据
DELIMITER $$
CREATE PROCEDURE insert_role(max_num INT)
BEGIN  
  DECLARE i INT DEFAULT 0;   
  SET autocommit = 0;    
  REPEAT  
    SET i = i + 1;  
    INSERT INTO role ( role_name,orders ) VALUES (rand_string(8),rand_num(1,5000)); 
    UNTIL i = max_num  
  END REPEAT;  
  COMMIT;  
END$$
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_role;

插入用户表


-- 插入用户数据
DELIMITER $$
CREATE PROCEDURE  insert_user(START INT, max_num INT)
BEGIN  
  DECLARE i INT DEFAULT 0;   
  SET autocommit = 0;    
  REPEAT  
    SET i = i + 1;  
    INSERT INTO user (username, role_id, password, salt ) VALUES (rand_string(8) ,rand_num(1,100000), rand_string(10), rand_string(10));  
    UNTIL i = max_num  
  END REPEAT;  
  COMMIT;  
END$$
-- 删除
-- DELIMITER ;
-- drop PROCEDURE insert_user;

四、执行存储过程


-- 执行存储过程,往dept表添加10万条数据
CALL insert_role(100000); 
-- 执行存储过程,往emp表添加100万条数据,编号从100000开始
CALL insert_user(100000,1100000);

小结

执行用时 10w数据差不多半分钟, 100w数据超过了20分钟, 同时 user的存储还卡死很久…

最后都成功新增, 但是自动递增值和行数不一致, 这个我也不知道因为啥…

image.png

数据展示

  • role表

image.png

  • user表

image.png

五、使用 Navicat自带的数据生成

接下来我们使用 Navicat的数据生成

image.png

image.png

直接下一步, 然后选择对应的两张表生成行数和对应的生成规则, 基于之前的执行速度, 这次 role生成 1w数据, user生成 10w数据

对于字符串类型的字段, 我们可以设置他的随机数据生成器, 根据需要进行选择

image.png

例如角色名称, 选择了 职位名称 还可以进行是否包含 null 的选择等

image.png

但是如果是 姓名 那么就会让你选择是否唯一

image.png

数字的话会让你选择范围, 默认值等

image.png

等确定好了, 我们就可以点击右下角进行生成随机测试数据

image.png

通过结果可以看到生成十一万测试数据一共用时十一秒, 比第一种方法速度快很多, 推荐使用


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
4月前
|
缓存 NoSQL 关系型数据库
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
美团面试:MySQL有1000w数据,redis只存20w的数据,如何做 缓存 设计?
|
2月前
|
SQL 人工智能 关系型数据库
如何实现MySQL百万级数据的查询?
本文探讨了在MySQL中对百万级数据进行排序分页查询的优化策略。面对五百万条数据,传统的浅分页和深分页查询效率较低,尤其深分页因偏移量大导致性能显著下降。通过为排序字段添加索引、使用联合索引、手动回表等方法,有效提升了查询速度。最终建议根据业务需求选择合适方案:浅分页可加单列索引,深分页推荐联合索引或子查询优化,同时结合前端传递最后一条数据ID的方式实现高效翻页。
139 0
|
1月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
144 10
|
17天前
|
存储 测试技术 API
数据驱动开发软件测试脚本
今天刚提交了我的新作《带着ChatGPT玩转软件开发》给出版社,在写作期间跟着ChatGPT学到许多新知识。下面分享数据驱动开发软件测试脚本。
26 0
|
2月前
|
SQL 存储 缓存
MySQL 如何高效可靠处理持久化数据
本文详细解析了 MySQL 的 SQL 执行流程、crash-safe 机制及性能优化策略。内容涵盖连接器、分析器、优化器、执行器与存储引擎的工作原理,深入探讨 redolog 与 binlog 的两阶段提交机制,并分析日志策略、组提交、脏页刷盘等关键性能优化手段,帮助提升数据库稳定性与执行效率。
|
5月前
|
关系型数据库 MySQL Linux
在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾
以上就是在Linux环境下备份Docker中的MySQL数据并传输到其他服务器以实现数据级别的容灾的步骤。这个过程就像是一场接力赛,数据从MySQL数据库中接力棒一样传递到备份文件,再从备份文件传递到其他服务器,最后再传递回MySQL数据库。这样,即使在灾难发生时,我们也可以快速恢复数据,保证业务的正常运行。
275 28
|
4月前
|
存储 SQL 缓存
mysql数据引擎有哪些
MySQL 提供了多种存储引擎,每种引擎都有其独特的特点和适用场景。以下是一些常见的 MySQL 存储引擎及其特点:
138 0
|
6月前
|
存储 SQL 关系型数据库
【YashanDB知识库】MySQL迁移至崖山char类型数据自动补空格问题
**简介**:在MySQL迁移到崖山环境时,若字段类型为char(2),而应用存储的数据仅为&#39;0&#39;或&#39;1&#39;,查询时崖山会自动补空格。原因是mysql的sql_mode可能启用了PAD_CHAR_TO_FULL_LENGTH模式,导致保留CHAR类型尾随空格。解决方法是与应用确认数据需求,可将崖山环境中的char类型改为varchar类型以规避补空格问题,适用于所有版本。
|
5月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
103 0
|
6月前
|
SQL 关系型数据库 MySQL
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB
本文探讨了在使用YMP 23.2.1.3迁移MySQL Server字符集为latin1的中文数据至YashanDB时出现乱码的问题。问题根源在于MySQL latin1字符集存放的是实际utf8编码的数据,而YMP尚未支持此类场景。文章提供了两种解决方法:一是通过DBeaver直接迁移表数据;二是将MySQL表数据转换为Insert语句后手动插入YashanDB。同时指出,这两种方法适合单张表迁移,多表迁移可能存在兼容性问题,建议对问题表单独处理。
【YashanDB知识库】字符集latin1的MySQL中文数据如何迁移到YashanDB

推荐镜像

更多