• MySQL数据生成工具mysql_random_data_load

    在看MySQL文章的时候偶然发现生成数据的工具，此处直接将软件作者的文档贴了过来，说明了使用方式及下载地址

    Random data generator for MySQL

    Many times in my job I need to generate random data for a specific table in order to reproduce an issue.
    After writing many random generators for every table, I decided to write a random data generator, able to get the table structure and generate random data for it.
    Plase take into consideration that this is the first version and it doesn’t support all field types yet!

    NOTICE
    This is an early stage project.

    Supported fields:

    Field typeGenerated values
    tinyint0 ~ 0xFF
    smallint0 ~ 0XFFFF
    mediumint0 ~ 0xFFFFFF
    int - integer0 ~ 0xFFFFFFFF
    bigint0 ~ 0xFFFFFFFFFFFFFFFF
    float0 ~ 1e8
    decimal(m,n)0 ~ 10^(m-n)
    double0 ~ 1000
    char(n)up to n random chars
    varchar(n)up to n random chars
    dateNOW() - 1 year ~ NOW()
    datetimeNOW() - 1 year ~ NOW()
    timestampNOW() - 1 year ~ NOW()
    time00:00:00 ~ 23:59:59
    yearCurrent year - 1 ~ current year
    tinyblobup to 100 chars random paragraph
    tinytextup to 100 chars random paragraph
    blobup to 100 chars random paragraph
    textup to 100 chars random paragraph
    mediumblobup to 100 chars random paragraph
    mediumtextup to 100 chars random paragraph
    longblobup to 100 chars random paragraph
    longtextup to 100 chars random paragraph
    varbinaryup to 100 chars random paragraph
    enumA random item from the valid items list
    setA random item from the valid items list

    How strings are generated

    • If field size < 10 the program generates a random “first name”
    • If the field size > 10 and < 30 the program generates a random “full name”
    • If the field size > 30 the program generates a “lorem ipsum” paragraph having up to 100 chars.

    The program can detect if a field accepts NULLs and if it does, it will generate NULLs ramdomly (~ 10 % of the values).

    Usage

    mysql_random_data_load

    [options...]

    Options

    OptionDescription
    –bulk-sizeNumber of rows per INSERT statement (Default: 1000)
    –debugShow some debug information
    –fk-samples-factorPercentage used to get random samples for foreign keys fields. Default 0.3
    –hostHost name/ip
    –max-fk-samplesMaximum number of samples for fields having foreign keys constarints. Default: 100
    –max-retriesMaximum number of rows to retry in case of errors. See duplicated keys. Deafult: 100
    –no-progressbarSkip showing the progress bar. Default: false
    –passwordPassword
    –portPort number
    –PrintPrint queries to the standard output instead of inserting them into the db
    –userUsername
    –versionShow version and exit

    Foreign keys support

    If a field has Foreign Keys constraints, random-data-load will get up to --max-fk-samples random samples from the referenced tables in order to insert valid values for the field.
    The number of samples to get follows this rules:
    1. Get the aproximate number of rows in the referenced table using the rows field in:

    EXPLAIN SELECT COUNT(*) FROM .

    • 1

    1.1 If the number of rows is less than max-fk-samples, all rows are retrieved from the referenced table using this query:

    SELECT  FROM .

    • 1

    1.2 If the number of rows is greater than max-fk-samples, samples are retrieved from the referenced table using this query:

    SELECT  FROM . WHERE RAND() <=  LIMIT 

    • 1

    Example

    CREATE DATABASE IF NOT EXISTS test;

CREATE TABLE `test`.`t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tcol01` tinyint(4) DEFAULT NULL,
  `tcol02` smallint(6) DEFAULT NULL,
  `tcol03` mediumint(9) DEFAULT NULL,
  `tcol04` int(11) DEFAULT NULL,
  `tcol05` bigint(20) DEFAULT NULL,
  `tcol06` float DEFAULT NULL,
  `tcol07` double DEFAULT NULL,
  `tcol08` decimal(10,2) DEFAULT NULL,
  `tcol09` date DEFAULT NULL,
  `tcol10` datetime DEFAULT NULL,
  `tcol11` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `tcol12` time DEFAULT NULL,
  `tcol13` year(4) DEFAULT NULL,
  `tcol14` varchar(100) DEFAULT NULL,
  `tcol15` char(2) DEFAULT NULL,
  `tcol16` blob,
  `tcol17` text,
  `tcol18` mediumtext,
  `tcol19` mediumblob,
  `tcol20` longblob,
  `tcol21` longtext,
  `tcol22` mediumtext,
  `tcol23` varchar(3) DEFAULT NULL,
  `tcol24` varbinary(10) DEFAULT NULL,
  `tcol25` enum('a','b','c') DEFAULT NULL,
  `tcol26` set('red','green','blue') DEFAULT NULL,
  `tcol27` float(5,3) DEFAULT NULL,
  `tcol28` double(4,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    To generate 100K random rows, just run:

    mysql_random_data_load test t3 100000 --user=root --password=root

    • 1
    mysql> select * from t3 limit 1\G
*************************** 1. row ***************************
    id: 1
tcol01: 10
tcol02: 173
tcol03: 1700
tcol04: 13498
tcol05: 33239373
tcol06: 44846.4
tcol07: 5300.23
tcol08: 11360967.75
tcol09: 2017-09-04
tcol10: 2016-11-02 23:11:25
tcol11: 2017-03-03 08:11:40
tcol12: 03:19:39
tcol13: 2017
tcol14: repellat maxime nostrum provident maiores ut quo voluptas.
tcol15: Th
tcol16: Walter
tcol17: quo repellat accusamus quidem odi
tcol18: esse laboriosam nobis libero aut dolores e
tcol19: Carlos Willia
tcol20: et nostrum iusto ipsa sunt recusa
tcol21: a accusantium laboriosam voluptas facilis.
tcol22: laudantium quo unde molestiae consequatur magnam.
tcol23: Pet
tcol24: Richard
tcol25: c
tcol26: green
tcol27: 47.430
tcol28: 6.12
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    效果良好
    在这里插入图片描述

    How to download the precompiled binaries

    There are binaries available for each version for Linux and Darwin. You can find compiled binaries for each version in the releases tab:

    https://github.com/Percona-Lab/mysql_random_data_load/releases

  • 相关阅读:
    在C代码中找到栈顶的位置并访问栈空间
    利用IP风险画像强化金融行业网络安全防御
    本地浏览器打开远程服务器上的Jupyter Notebook
    【Windows】win10系统重装后桌面及软件安装清单（自己用）
    坚持了 10 年的 9 个编程好习惯
    jsoup框架技术文档--java爬虫--基本概念
    Linux驱动开发——块设备驱动
    自定义协议、序列化与反序列化
    从ReentrantReadWriteLock开始的独占锁与共享锁的源码分析
    9月5日关键点检测学习笔记——人体骨骼点检测：自顶向下
  • 原文地址：https://blog.csdn.net/u014650965/article/details/133779092