MySQL 测试数据快速生成

一、前言

        在我们进行大数据量的报表测试以及性能测试时，需要数据库中存在大量的基础测试数据，但是依赖于系统本身大多数情况下都无法快速产生大量适合的测试数据，这就需要我们手动生成一些测试数据。

目前生成数据的方式有：

1、使用存储过程实现(需要开发了解PL/SQL语法)

2、多线程 + jdbc批量提交

3、使用性能测试工具(loadrunner或jemter产生，需要了解这两个工具的脚本预言)

4、使用QUEST  Datafactory、TestDataBuilder等，只需简单的配置

二、利用存储过程实现测试数据快速生成

2.1  创建存储过程

-- 清空数据表
truncate table user1;
-- 如果存在存储过程 `add_user1`，先删除再创建
DELIMITER $$
DROP PROCEDURE IF EXISTS `add_user1`;
CREATE PROCEDURE `add_user1` ( IN n INT ) 
BEGIN
	DECLARE i INT DEFAULT 1;
    DECLARE password VARCHAR(100) DEFAULT '';
    DECLARE create_date VARCHAR(255) DEFAULT '';
	start transaction; -- 整个存储过程指定为一个事务
	 WHILE ( i <= n ) DO
        set password =  substring_index( substring_index('123;456;789;321', ';', -FLOOR( rand( ) * 4+1 ) ), ';', 1 );
        set create_date = CONCAT(
		date(from_unixtime(
			unix_timestamp('2019-01-01')
			+ floor(
				rand() * ( unix_timestamp(now()) - unix_timestamp('2019-01-01') + 1 )
			)
		)) ,
		' ',
		LPAD( FLOOR( 9 + ( RAND( ) * 10 ) ), 2, 0 ),-- 时 0-23  LPAD( FLOOR( 0 + ( RAND( ) * 24 ) ), 2, 0 ),; 9-18 LPAD( FLOOR( 9 + ( RAND( ) * 1 ) ), 2, 0 ),
		':',
		LPAD( FLOOR( 0 + ( RAND( ) * 60 ) ), 2, 0 ),-- 分 0-59
		':',
		LPAD( FLOOR( 0 + ( RAND( ) * 60 ) ), 2, 0 ) -- 秒 0-59
	);
	INSERT INTO `user1` VALUES(
		i,
		'oyc',
		'oyc',
		password,
      -- create_date,
		CONCAT(-- 	FLOOR( 1990 + ( RAND( ) * 25 ) ),
			'2019-',-- 年
			LPAD( FLOOR( 1 + ( RAND( ) * 12 ) ), 2, 0 ),-- 月
			'-',
			LPAD( FLOOR( 3 + ( RAND( ) * 8 ) ), 2, 0 ),-- 日
			' ',
			LPAD( FLOOR( 0 + ( RAND( ) * 23 ) ), 2, 0 ),-- 时
			':',
			LPAD( FLOOR( 0 + ( RAND( ) * 59 ) ), 2, 0 ),-- 分
			':',
			LPAD( FLOOR( 0 + ( RAND( ) * 59 ) ), 2, 0 ) -- 秒
		 ) 
		);
	    SET i = i + 1;
	END WHILE;
	commit; -- 必须主动提交
END $$
 
CALL add_user1 (10 );

大量插入数据时，必须加上事务处理，可以提高插入速度。

2.2  执行存储过程

执行可以通过navicat的函数执行，也可以直接执行CALL add_user1 (10 ) 命令。

2.2.1 函数可视化执行：

填入执行函数值，这里我设置为10，点击确定即可完成。

执行完成，耗时0.188s

2.2.2 CALL add_user1 (10 ) 命令

2.3 利用mysql 函数生成一下随机内容

1. 修改引擎
ALTER TABLE user1 ENGINE = INNODB/ MyISAM; 

1. 清空数据表
truncate table user1;

3.从数组中随机获取一个内容
SELECT 
    substring_index(substring_index('学富五车;足智多谋;仗义执言;火眼金睛;见多识广;责任担当;乐观积极', ';',- FLOOR( rand( ) * 7+1 ) ), ';', 1 )
 AS report_content;

4. 从两个数组中随机获取相同索引下的内容
select desc_index, CONCAT(desc_index, 
    '\"desc\": {\"label\":\"', 
    substring_index(substring_index('学富五车;足智多谋;仗义执言;火眼金睛;见多识广;责任担当;乐观积极',';',temp.desc_index),';',1),'\"',
',\"description\": \"', 
substring_index(substring_index('再美的气质，也掩盖不了你才华本质;脑力使用积极分子，非你莫属;你的见解，面面俱到，深入人心;独特的洞察力，总能获悉一切;最美的阅历，就是沿路的风景;先天下之忧而忧,后天下之乐而乐;爱笑的你运气一定不会差',';',temp.desc_index),';',1),
'\"}'
) as report_content from (select -FLOOR(rand()*7+1) as desc_index limit 100000) temp

5.随机年月日 时分秒
SELECT
    CONCAT(
    FLOOR(2010 + (RAND()*10)),-- 年
    '-',
    LPAD(FLOOR( 1 + (RAND() * 12 )), 2, 0),-- 月
    '-',
    LPAD(FLOOR( 3 + (RAND() * 8)), 2, 0),-- 日
    ' ',
    LPAD(FLOOR( 0 + (RAND() * 23)), 2, 0),-- 时
    ':',
    LPAD(FLOOR( 0 + (RAND() * 59)), 2, 0),-- 分
    ':',
    LPAD(FLOOR( 0 + (RAND() * 59)), 2, 0) -- 秒
    )

SELECT FLOOR(7 + (RAND() * 6));     取7到12的随机整数，包括7到12

SELECT FLOOR(12+(rand()*13));   取12到24的随机整数，包括12到24

LPAD(FLOOR(0 + (RAND() * 23)),2,0)  取0-24之间的随机数，不够2位的前补0

格式 ： 2008-12-11 17:43:11

举例：

给用户赋一个随机入职日期，年份为1990-2014

select CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))

结果：1999-10-07

select CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0))

结果：15:39:18

一段时间：

select DATE_ADD(DATE_FORMAT('2019-01-01 00:00:00','%Y-%m-%d %H:%i:%s'),INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(DATE_FORMAT('2019-04-21 23:59:59','%Y-%m-%d %H:%i:%s'))-UNIX_TIMESTAMP(DATE_FORMAT('2019-01-01 00:00:00','%Y-%m-%d %H:%i:%s'))))-1)) SECOND);

结果： 

2019-02-16 13:40:00

存储过程获取一段时间：

DELIMITER $$
CREATE
    FUNCTION `RandomDateTime`(sd DATETIME,ed DATETIME)
    RETURNS DATETIME
    BEGIN
    RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);
    END$$
DELIMITER ;

SELECT RandomDateTime(DATE_FORMAT('2019-01-01 00:00:00','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2019-04-21 23:59:59','%Y-%m-%d %H:%i:%s')) AS t;

获取一段时间：

select CONCAT(date(from_unixtime(
 unix_timestamp('2017-01-01') 
 + floor(
   rand() * ( unix_timestamp('2018-08-08') - unix_timestamp('2017-01-01') + 1 )
 )
)) ,' ', 
LPAD( FLOOR( 9 + ( RAND( ) * 8) ), 2, 0 ),-- 时 0-23  LPAD( FLOOR( 0 + ( RAND( ) * 23 ) ), 2, 0 ),; 9-17 LPAD( FLOOR( 9 + ( RAND( ) * 8 ) ), 2, 0 ),
                ':',
                LPAD( FLOOR( 0 + ( RAND( ) * 59 ) ), 2, 0 ),-- 分 0-59
                ':',
                LPAD( FLOOR( 0 + ( RAND( ) * 59 ) ), 2, 0 ) -- 秒 0-59
);

 

三、利用Datafactory实现测试数据快速生成

3.1 Datafactory简介

        Datafactory（数据工厂）工具是Quest公司(http://www.quest.com)旗下的产品，顾名思义，数据工厂是生产数据的，主要应用领域是性能测试中的大数据量测试， 也就是性能测试数据准备阶段。

        DataFactory 是一种快速的，易于产生测试数据的带有直觉用户介面的工具，它能建模复杂数据关系。DataFactory是一种强大的数据产生器，它允许开发人员和QA很容易产生百万行有意义的正确的测试数据库，该工具支持DB2、Oracle、Sybase、SQL Server数据库，支持ODBC连接方式，无法直接使用MySQL数据库。它能够根据用户定制，产生或导入需要的数据，插入相应的数据表或导出至外部文件。

3.2  Datafactory原理

       通过和数据库进行连接后，对选定表的字段设定一定的插入规则，然后批量插入记录。Datafactory支持各种主流数据库（Oracle、DB2、MS SQL），甚至excel、access等。

3.3 软件安装

3.3.1 DataFactory安装

        下载DataFactory并进行安装，下载地址：链接：https://pan.baidu.com/share/init?surl=0PiqHxa4CpCUKaqAb-n99w 提取码：wq47 。

3.3.2 ODBC驱动安装

        下载MySQL-ODBC驱动并安装，下载安装myodbc32驱动 ： https://dev.mysql.com/downloads/connector/odbc/。

3.3.3 基本配置

        a. 启动DataFactory，通过ODBC连接MySQL数据库，如图所示：

        

        b.选择DBC Administrator对ODBC用户DSN的配置，如下图所示：

       

        c. 进入ODBC数据源管理器，通过点击“添加”来新增用户数据源，页面如下：

       

  • MySQL ODBC 5.3 ANSI Driver：只针对有限的字符集的范围
  • MySQL ODBC 5.3 Unicode Driver：提供了多语言的支持

     d. 配置MySQL连接信息，页面如下：   

       

      e. 配置好数据源后返回到步骤2的页面，此时需要选择数据源，由于新建的数据源此时还无法进行选择，因此取消此安装流程，重新打开一个新的连接流程即可；  

       

        f. 选择相应数据表，页面如下：

       

        g. 选择user1,并进入下一步，指定执行名称为：add_user1，页面如下：

       

        h. 完成配置，页面如下：

       

3.3.4 简单使用

        以上 ，我们已经完成了配置，下面我们可以定义出入数据的规则，完成数据的生成了。选中表的字段，不同类型，提供不同数据产生的设置。

字段为Field.Text时：
        Insert value from a data table：从系统数据表中获取数据插入
        Insert value from an SQL database：编写SQL语句从数据库中获取数据插入
        Insert text from a file：外部文件数据插入
        Insert random characters：随机值插入
        Insert a string constant：常量值插入
        Build a composite field：组合值插入
字段为Field.Numeric时：
        Insert value from a data table：从系统数据表中获取数据插入
        Insert value from an SQL database：编写SQL语句从数据库中获取数据插入
        Insert value from an arithmetic expression：表达式产生数据插入
        Insert sequential values：自增长数据插入
        Insert random values：随机值插入
        Insert a constant value：常量值插入
字段为Field.Date时：
        Insert value from a data table：从系统数据表中获取数据插入
        Insert value from an SQL database：编写SQL语句从数据库中获取数据插入
        Insert sequential values：连续的时间插入
        Insert random values：随机的时间插入
        Insert the curent system data：当前系统时间插入

       1）将id设置为自动生成

            

       2）将name设置成长度为5的随机字符串

            

       3）将username设置成长度为3-8的随机字符串

           

       4）password设置为123456

         

       5）createtime为datetime类型，这里设定设定一个随机的时间，时间跨度为2019-4-21 01:12:58到-2019-4-21 04:12:58。

         

       6）点击Run，提示成功 

       

      7）查看数据，插入了100条数据。

         

注：要修改插入条数，可以修改下图中的ecords。

          

Logo
一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

编译时权限控制

前言 权限控制，对于 MLSQL 而言的重要程度可以说是生命线。 MLSQL 需要面对各式各样的资源访问，比如 MySQL, Oracle,HDFS，Hive，Kafka，Sorl，ElasticSearch，Redis，API，Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。 传统模式是，每个用户都需要有个 proxy user，然后到每个数据源里面给这个 proxy user

avatar Byzer 白泽

Byzer 支持 JDBC 聚合下推

聚合下推PR链接聚合下推 我们知道 Byzer 支持多数据源和联邦查询，可以方便分析师在一个平台上快速的分析来自多种数据源的数据，从而进行灵活的探索式分析。 使用场景和优势 当前 Byzer 中加载数据的方式会拉取明细数据到 spark 中进行聚合计算，对于小数据量或者在分布式文件系统上的数据源来说是常规操作。但是对于 JDBC 数据源或者有分析能力的 OLAP 系统来说拉取明细数据可能就不是最

avatar Byzer 白泽

Byzer 术语表

Byzer-lang Byzer ，又称为 Byzer-lang，一门面向 Data 和 AI 的低代码、云原生的开源编程语言。 Byzer 是一门结合了声明式编程和命令式编程的混合编程语言，其低代码且类 SQL 的编程逻辑配合内置算法及插件的加持，能帮助数据工作者们高效打通数据链路，完成数据的清洗转换，并快速地进行机器学习相关的训练及预测。 Byzer 希望能够提供一套语言、一个引擎，就能覆盖整

avatar Byzer 白泽