mysqlで手軽に100万件のテストデータを追加する方法 #MySQL

create database test_database;
use test_database;

CREATE TABLE data(
  id INT(11) NOT NULL AUTO_INCREMENT,
  status TINYINT(1) NOT NULL,
  name VARCHAR(50) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

insert into data value(null,0,"test000000000",now(),now());

select * from data;
+----+--------+---------------+---------------------+---------------------+
| id | status | name          | created_at          | updated_at          |
+----+--------+---------------+---------------------+---------------------+
|  1 |      0 | test000000000 | 2018-01-12 05:17:44 | 2018-01-12 05:17:44 |
+----+--------+---------------+---------------------+---------------------+

以下x 20実行

insert into data(name,status) 
SELECT CONCAT ('test', @rownum := @rownum + lpad(FLOOR( (RAND() * 100000000)),9,'0')),
FLOOR( (RAND() * 2)) FROM data AS s1, (SELECT @rownum := 0) AS v;

自分自身のデータを取得して'test'とランダムな数字を組み合わせたものを
nameとして使用する。
stautsは0 or 1　のいずれかが入るようにする。

select * from data limit 10;
+----+--------+---------------+---------------------+---------------------+
| id | status | name          | created_at          | updated_at          |
+----+--------+---------------+---------------------+---------------------+
|  1 |      0 | test000000000 | 2018-01-12 05:17:44 | 2018-01-12 05:17:44 |
|  2 |      1 | test15062706  | 2018-01-12 05:17:46 | 2018-01-12 05:17:46 |
|  3 |      1 | test51218631  | 2018-01-12 05:17:47 | 2018-01-12 05:17:47 |
|  4 |      0 | test88808289  | 2018-01-12 05:17:47 | 2018-01-12 05:17:47 |
|  6 |      0 | test53235853  | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
|  7 |      1 | test126410172 | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
|  8 |      1 | test137599580 | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
|  9 |      1 | test191629493 | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
| 13 |      1 | test25863700  | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
| 14 |      0 | test31004826  | 2018-01-12 05:17:48 | 2018-01-12 05:17:48 |
+----+--------+---------------+---------------------+---------------------+

select count(*) from data;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+