MySQLのテストデータを大量に作成する方法

MySQLでテストデータを手軽に大量にINSERTしたいときに使える方法。
ストアドプロシージャでWHILEを使って繰り返しINSERTすることで実現できるが、
TiDBなどストアドプロシージャに対応していない(2022年6月現在)場合の代替手段として
INSERT ~ SELECT文でレコードを比較的高速にINSERTできる。(もちろんMySQLでもできる)

例えばこんなテーブルがあるとして

CREATE TABLE `idxtest` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `txt` char(255) DEFAULT NULL,
  INDEX(`data`)
)

まず1レコード作る

INSERT INTO idxtest SET txt=SHA2(0,512);

その後INSERT ~ SELECT文で自己参照してレコードを倍々に増やしていく
(SELECTされるテーブルとINSERTされるテーブルが同じというのがポイント)

INSERT idxtest (txt) SELECT UUID() as txt FROM idxtest; # 2レコードになる
INSERT idxtest (txt) SELECT UUID() as txt FROM idxtest; # 4レコードになる
INSERT idxtest (txt) SELECT UUID() as txt FROM idxtest; # 8レコードになる
...
INSERT idxtest (txt) SELECT UUID() as txt FROM idxtest; # 20回繰り返すと2^20 ≒ 100万レコードほどになる

なおTiDBでもある程度同様に実行できるが大量に作成していると下記のようなエラーが出る。

SQL Error [8004] [HY000]: Transaction is too large, size: 104857633

公式ドキュメントによるとどうやら2Phase Commitを内部で行っている関係で下記制限に引っかかっている模様。
(今回のケースではtotal sizeの100MBに引っかかった)

A transaction is limited to 5000 SQL statements (by default)
Each Key-Value entry is no more than 6MB
The total number of Key-Value entry is no more than 300,000 rows
The total size of Key-Value entry is no more than 100MB

回避策としては次のようにLIMITをつけるという手が考えられるが、この方法のうまみが減るので少々微妙かも。

INSERT idxtest (txt) SELECT UUID() as txt FROM idxtest LIMIT 500000;

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル