CODEWITHSUNDEEP
mysqlsqltesting
hiroyuki
hiroyuki

Reputation: 51

Is there an easy way to generate test data in mysql?

I want to generate test data in mysql

Assuming that there is a table like this,

create table person (
  id int auto_increment primary key,
  name text,
  age int,
  birth_day date
);

let me know how to create test data in a simple way.

BTW I know some ways using stdin like

repeat 5 echo 'insert into person (name, age, birth_day) select concat("user", ceil(rand() * 100)), floor(rand()*100), date_add(date("1900/01/01"), interval floor(rand()*100) year);'

or

repeat 5 perl -M"Data::Random qw(:all)" -E 'say sprintf qq#insert into person (name, age, birth_day) values ("user%s", %s,"%s");#,  (int rand(100)), (int rand(100)), rand_date(min => "1900-01-01", max=>"1999-12-31")'

I think the latter may be better because it doesn't use mysql functions.

Upvotes: 4

Views: 36060

Answers (3)

mysql_user
mysql_user

Reputation: 392

This random data generator for MySQL is based on mysql routines and you don't need to really provide anything other than database and table names.

To use it:

  • Download the routines from GitHub
  • mysql < populate.sql
  • mysql> call populate('database','table',1000,'N');

Upvotes: 0

Baptiste Mille-Mathias
Baptiste Mille-Mathias

Reputation: 2169

Personnaly, as sysadmin, I use the library Faker that permit to generate on-the-fly data.

for your database person, you could the following

#!/usr/bin/env python

import random

import mysql.connector             
from mysql.connector import Error  
from faker import Faker

Faker.seed(33422)
fake = Faker()

conn = mysql.connector.connect(host=db_host, database=db_name,
                               user=db_user, password=db_pass)
cursor = conn.cursor()
  
row = [fake.first_name(), random.randint(0,99), fake.date_of_birth()]

cursor.execute(' \                                                                          
     INSERT INTO `person` (name, age, birth_day) \ 
     VALUES ("%s", %d, "%s",);' % (row[0], row[1], row[2])

conn.commit()

Then you can improve the script by looping and at each iteration faker will create random name and birth_date. Faker has an extended list of type (called "provider") of data it can generate, the full list is available at https://faker.readthedocs.io/en/master/providers.html.

Upvotes: 1

Paul Wolbers
Paul Wolbers

Reputation: 29

You can try http://paulthedutchman.nl/datagenerator-online. Also available as offline version to use on your local development environment. It has so much more options than other datagenerators out there. Not suitable for mobile devices because it uses ExtJS, use it on your computer.

The offline version automatically scans your database and table structure.

Upvotes: -1

Related Questions