ある一定の範囲内でランダムな値を持つテーブルをさくっと作りたい。

環境

MariaDB [test]> status
--------------
mysql  Ver 15.1 Distrib 10.6.7-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Connection id:          2445
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.6.7-MariaDB-2ubuntu1.1 Ubuntu 22.04
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /run/mysqld/mysqld.sock
.
.
.

作戦

最初からランダムなデータを insert できるかな... と思ったが、insert 構文は一行ごとにデータを指定しないといけないようだ。ストアドプロシージャでループしたりするのもメンドクサイ。なので以下の作戦でいくことにした。

ランダムでない行を insert してからランダムに update するのが良い
- insert ~ select で自分自身を倍々に増やすのが最もお手軽
- ユニークキーの値だけ気をつける
update はフォーマットに応じてrnd関数を加工する

結局、以下に示されている方法そのままになった。

「MySQLで簡単にランダムなテストデータを作成する方法」
https://qiita.com/tayasu/items/c5ddfc481d6b7cd8866d

初期状態のテーブルが以下のようにある。


MariaDB [test]> select * from 会員成績;
+------+---------------------+--------------+--------+
| stid | 日時                | 会員番号     | 成績   |
+------+---------------------+--------------+--------+
|    1 | 0000-00-00 00:00:00 | 4            |      0 |
|    2 | 0000-00-00 00:00:00 | 6            |      0 |
|    3 | 0000-00-00 00:00:00 | 6            |      0 |
|    4 | 0000-00-00 00:00:00 | 1            |      0 |
|    5 | 0000-00-00 00:00:00 | 8            |      0 |
+------+---------------------+--------------+--------+
5 rows in set (0.001 sec)

これを倍倍倍倍で80行にする。
auto_increment である stid 以外のフィールドをいちいち指定する。

MariaDB [test]> INSERT INTO 会員成績(日時,会員番号,成績) SELECT 日時,会員番号,成績 FROM 会員成績;
Query OK, 5 rows affected (0.020 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [test]> INSERT INTO 会員成績(日時,会員番号,成績) SELECT 日時,会員番号,成績 FROM 会員成績;
Query OK, 10 rows affected (0.017 sec)
Records: 10  Duplicates: 0  Warnings: 0

MariaDB [test]> INSERT INTO 会員成績(日時,会員番号,成績) SELECT 日時,会員番号,成績 FROM 会員成績;
Query OK, 20 rows affected (0.015 sec)
Records: 20  Duplicates: 0  Warnings: 0

MariaDB [test]> INSERT INTO 会員成績(日時,会員番号,成績) SELECT 日時,会員番号,成績 FROM 会員成績;
Query OK, 40 rows affected (0.018 sec)
Records: 40  Duplicates: 0  Warnings: 0

update でそれらしい値にする。


MariaDB [test]> UPDATE 会員成績 SET
   会員番号 = CEIL(RAND() * 5),
   成績 = CEIL(RAND() * 100),
   日時 = ADDTIME(
            CONCAT_WS(' ',
                      '2012-01-01' + INTERVAL RAND() * 3653 DAY, '00:00:00'),
                      SEC_TO_TIME(FLOOR(0 + (RAND() * 86401))));
:
Query OK, 80 rows affected (0.019 sec)
Rows matched: 80  Changed: 80  Warnings: 0

日時は 2012年〜2022年のいずれかとしています。
CEIL で切り上げなので、会員番号は 1 〜 5の間のいずれか、成績は1〜100までのいずれかです。
レンジにゼロを入れる場合はちょっと変える必要があります。

MariaDB [test]> select * from 会員成績;
+------+---------------------+--------------+--------+
| stid | 日時                | 会員番号     | 成績   |
+------+---------------------+--------------+--------+
|    1 | 2019-02-18 12:14:06 | 4            |     36 |
|    2 | 2015-09-08 06:48:52 | 3            |     52 |
|    3 | 2018-07-04 02:19:29 | 2            |     72 |
|    4 | 2014-09-12 05:27:12 | 3            |     38 |
.
.
.
|   96 | 2020-10-15 14:53:29 | 2            |     60 |
|   97 | 2021-07-05 08:15:31 | 3            |     47 |
|   98 | 2021-05-26 18:31:07 | 5            |     31 |
+------+---------------------+--------------+--------+
80 rows in set (0.001 sec)

うまくいったみたいですね。

MariaDB でランダムなテストデータを作成

環境

作戦