Mysqlでサンプルデータを大量に作る

## 概要
Mysqlでサンプルデータを大量に作る方法。

## 環境

```sql
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.27    |
+-----------+
1 row in set (0.00 sec)
```

## サンプルデータ生成方法

```sql
-- 1..100
SELECT CEIL(RAND() * 100);
-- ランダム文字列 50文字
SELECT SUBSTRING(MD5(RAND()), 1, 50);
-- sample_xxx
SELECT CONCAT('sample_', CEIL(RAND() * 100));
-- From 2001-01-01 to 2019-12-31
SELECT DATE_ADD('2001-01-01', INTERVAL 6938 * RAND() DAY);
-- From 2001-01-01 00:00:00 to 2019-12-31 23:59:59
SELECT ADDTIME(CONCAT_WS(' ','2001-01-01' + INTERVAL RAND() * 6938 DAY, '23:59:59'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));
```


## ハンズオン
### テーブル作成

```sql
CREATE TABLE `sample` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  `description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```

### サンプルデータを作成

```sql
-- 1行作成する
INSERT INTO sample (name, age, description) VALUES(SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50));
-- 以降以下を実行する度、2行、4行、8行、16行、32行とレコードが増えていく
INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
```

実行

```sql
mysql> INSERT INTO sample (name, age, description) VALUES(SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50));
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
+----+----------------------------------+-----+----------------------------------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
|  2 | e25f79237ba7bae536be60edc0638c7d |  84 | c7990703526fd76d66a86f6aa766133d | 2020-11-10 09:43:43 |
+----+----------------------------------+-----+----------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
|  2 | e25f79237ba7bae536be60edc0638c7d |  84 | c7990703526fd76d66a86f6aa766133d | 2020-11-10 09:43:43 |
|  3 | 475113d7d300f9aff2a837a89ab49a1e |  90 | 3d34b1316ab2f2beb99dc6b74157549b | 2020-11-10 09:43:46 |
|  4 | 58b4f00b8067dc6e344f568569c6a596 |  15 | fac2be16ff372e039e51718a7ccc64a7 | 2020-11-10 09:43:46 |
+----+----------------------------------+-----+----------------------------------+---------------------+
4 rows in set (0.00 sec)

mysql> INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
|  2 | e25f79237ba7bae536be60edc0638c7d |  84 | c7990703526fd76d66a86f6aa766133d | 2020-11-10 09:43:43 |
|  3 | 475113d7d300f9aff2a837a89ab49a1e |  90 | 3d34b1316ab2f2beb99dc6b74157549b | 2020-11-10 09:43:46 |
|  4 | 58b4f00b8067dc6e344f568569c6a596 |  15 | fac2be16ff372e039e51718a7ccc64a7 | 2020-11-10 09:43:46 |
|  6 | 12f56e834265a7ecd93fed8f3010e837 |  19 | ca38068e8fdb9aef8d717e16a7a21d01 | 2020-11-10 09:43:52 |
|  7 | dc0f660946c7344dfaa743f3da3bf7a6 |  87 | bf11f139167c5f7270e422e06796f310 | 2020-11-10 09:43:52 |
|  8 | 5a2bb8acd2a8f5f7f42031357d61f2b3 |  45 | 1ee55f0eeaa91d5d8d2bc2de9e993e5b | 2020-11-10 09:43:52 |
|  9 | 6ae44b3fe896eb7b4b36142e26a2a288 |  53 | 18b765661dbd1d1f623862ec352a921a | 2020-11-10 09:43:52 |
+----+----------------------------------+-----+----------------------------------+---------------------+
8 rows in set (0.00 sec)

mysql> INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
|  2 | e25f79237ba7bae536be60edc0638c7d |  84 | c7990703526fd76d66a86f6aa766133d | 2020-11-10 09:43:43 |
|  3 | 475113d7d300f9aff2a837a89ab49a1e |  90 | 3d34b1316ab2f2beb99dc6b74157549b | 2020-11-10 09:43:46 |
|  4 | 58b4f00b8067dc6e344f568569c6a596 |  15 | fac2be16ff372e039e51718a7ccc64a7 | 2020-11-10 09:43:46 |
|  6 | 12f56e834265a7ecd93fed8f3010e837 |  19 | ca38068e8fdb9aef8d717e16a7a21d01 | 2020-11-10 09:43:52 |
|  7 | dc0f660946c7344dfaa743f3da3bf7a6 |  87 | bf11f139167c5f7270e422e06796f310 | 2020-11-10 09:43:52 |
|  8 | 5a2bb8acd2a8f5f7f42031357d61f2b3 |  45 | 1ee55f0eeaa91d5d8d2bc2de9e993e5b | 2020-11-10 09:43:52 |
|  9 | 6ae44b3fe896eb7b4b36142e26a2a288 |  53 | 18b765661dbd1d1f623862ec352a921a | 2020-11-10 09:43:52 |
| 13 | 4f179f20b35a8df63942099272a0f02c |  54 | bdefa22fe6cd6dcc02a590d2d656f362 | 2020-11-10 09:43:56 |
| 14 | 742b35b1c2fbf71b872c282b4bdc1f57 |  72 | 3403f1cb6410806382eb2c329534918c | 2020-11-10 09:43:56 |
| 15 | a9dfc2b181eaff335394c4b4c586c628 |  58 | bc318eb95e20728b7ead48129b33c8e4 | 2020-11-10 09:43:56 |
| 16 | 0be2a6c4ccedfbd3aa62c1edb0aabfd9 |  59 | 96b47e533554c22f1d5c838f411d3162 | 2020-11-10 09:43:56 |
| 17 | 22bdce55181cc561c723231e104e1c0f |  59 | ec79d4e16af45562dcbd70d6639c2a57 | 2020-11-10 09:43:56 |
| 18 | bbd3b4fdbfbf650e3fe15d1d6317f42a |  24 | 9620507953707c2eee67a3fbc9c1f9fd | 2020-11-10 09:43:56 |
| 19 | 464365861c39cca98d4e2443b6be0ff6 |  77 | 6273b0d44e88a860bee8ab3b7977f054 | 2020-11-10 09:43:56 |
| 20 | 37a4f335b9b04450b99f9ce181ef6ca2 |  21 | 3d67b03af999fbfb8315daddc36a734b | 2020-11-10 09:43:56 |
+----+----------------------------------+-----+----------------------------------+---------------------+
16 rows in set (0.00 sec)

mysql> INSERT INTO sample (name, age, description) SELECT SUBSTRING(MD5(RAND()), 1, 50), CEIL(RAND() * 100), SUBSTRING(MD5(RAND()), 1, 50) FROM sample;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> select * from sample;
+----+----------------------------------+-----+----------------------------------+---------------------+
| id | name                             | age | description                      | created_at          |
+----+----------------------------------+-----+----------------------------------+---------------------+
|  1 | 21ca7f5f41144aa979e07d6f149a3b17 |  63 | d4727119e882ffe16116fcb7f4e644f8 | 2020-11-10 09:43:38 |
|  2 | e25f79237ba7bae536be60edc0638c7d |  84 | c7990703526fd76d66a86f6aa766133d | 2020-11-10 09:43:43 |
|  3 | 475113d7d300f9aff2a837a89ab49a1e |  90 | 3d34b1316ab2f2beb99dc6b74157549b | 2020-11-10 09:43:46 |
|  4 | 58b4f00b8067dc6e344f568569c6a596 |  15 | fac2be16ff372e039e51718a7ccc64a7 | 2020-11-10 09:43:46 |
|  6 | 12f56e834265a7ecd93fed8f3010e837 |  19 | ca38068e8fdb9aef8d717e16a7a21d01 | 2020-11-10 09:43:52 |
|  7 | dc0f660946c7344dfaa743f3da3bf7a6 |  87 | bf11f139167c5f7270e422e06796f310 | 2020-11-10 09:43:52 |
|  8 | 5a2bb8acd2a8f5f7f42031357d61f2b3 |  45 | 1ee55f0eeaa91d5d8d2bc2de9e993e5b | 2020-11-10 09:43:52 |
|  9 | 6ae44b3fe896eb7b4b36142e26a2a288 |  53 | 18b765661dbd1d1f623862ec352a921a | 2020-11-10 09:43:52 |
| 13 | 4f179f20b35a8df63942099272a0f02c |  54 | bdefa22fe6cd6dcc02a590d2d656f362 | 2020-11-10 09:43:56 |
| 14 | 742b35b1c2fbf71b872c282b4bdc1f57 |  72 | 3403f1cb6410806382eb2c329534918c | 2020-11-10 09:43:56 |
| 15 | a9dfc2b181eaff335394c4b4c586c628 |  58 | bc318eb95e20728b7ead48129b33c8e4 | 2020-11-10 09:43:56 |
| 16 | 0be2a6c4ccedfbd3aa62c1edb0aabfd9 |  59 | 96b47e533554c22f1d5c838f411d3162 | 2020-11-10 09:43:56 |
| 17 | 22bdce55181cc561c723231e104e1c0f |  59 | ec79d4e16af45562dcbd70d6639c2a57 | 2020-11-10 09:43:56 |
| 18 | bbd3b4fdbfbf650e3fe15d1d6317f42a |  24 | 9620507953707c2eee67a3fbc9c1f9fd | 2020-11-10 09:43:56 |
| 19 | 464365861c39cca98d4e2443b6be0ff6 |  77 | 6273b0d44e88a860bee8ab3b7977f054 | 2020-11-10 09:43:56 |
| 20 | 37a4f335b9b04450b99f9ce181ef6ca2 |  21 | 3d67b03af999fbfb8315daddc36a734b | 2020-11-10 09:43:56 |
| 28 | a4c7b39dcf27da680c936d8e24ba8bd1 | 100 | 9a734bb62e5e1886ca4240fc612c87c0 | 2020-11-10 09:44:00 |
| 29 | a360169fd807ac1fa09bf790962a551f |  56 | 9d796d1a6d0fd4834a23406b04d00867 | 2020-11-10 09:44:00 |
| 30 | a147cda712d26d8ba32ce550a9853818 |  46 | d6861d257cd35d02167f70b2c9bdc0a6 | 2020-11-10 09:44:00 |
| 31 | b43836093222df21e4553bbfbbf3e333 |  54 | 3afd94eb4ccbc8f510c3c568240f2d35 | 2020-11-10 09:44:00 |
| 32 | 3c42c92bd64f5decb65efea3307b1546 |  75 | 28990aa72c4781c916b00954dfff5b94 | 2020-11-10 09:44:00 |
| 33 | c802dd47111d0731f53b9f95d3a42bbe |  88 | b54c8f1b80fa61c66396371bcbcafee6 | 2020-11-10 09:44:00 |
| 34 | 535be6c1879d26737636c911ee99f63d |  67 | d5aaed632923194626794382fe3dea7d | 2020-11-10 09:44:00 |
| 35 | e58b26c315fc48669c06d6651a668907 |  75 | e805ae9ebba85de52b0823a72e64498c | 2020-11-10 09:44:00 |
| 36 | edde8922dc2996a2c36ba34e6753ed1b |  35 | dc9c4d1b0f05e658b7db5047fef01c7b | 2020-11-10 09:44:00 |
| 37 | 69013948544be02fa4bb5f2913d30f62 |  40 | b1afede110f187c80659722ad3b03869 | 2020-11-10 09:44:00 |
| 38 | beb6ce46f4c871ae52bc98be5bee561a |  96 | aa87b2963feedb58657698f2e8a9d00b | 2020-11-10 09:44:00 |
| 39 | 7385bce3314a69099aa88393b0f72243 |  79 | a770217abf5b5a4eaf16f13d83ab2806 | 2020-11-10 09:44:00 |
| 40 | 5f2bc35cbef9f9048a9271ad2226b67e |  95 | 39155118e01ece253643da0abb573a27 | 2020-11-10 09:44:00 |
| 41 | 753e0c39c3e2786771415c2808adc373 |  21 | 9ece0a20a2380e2ce4239bba8a781a76 | 2020-11-10 09:44:00 |
| 42 | c51bd73759cd220fc913f3f71d4d3455 |  80 | 4c5f0cf9ac2e14f763b756d0fa42d629 | 2020-11-10 09:44:00 |
| 43 | 0099c84695e0653ab7b6d8c353dedb0a |  97 | 3f94ecb772f93d8a7711973deaa029b5 | 2020-11-10 09:44:00 |
+----+----------------------------------+-----+----------------------------------+---------------------+
32 rows in set (0.00 sec)
```