1億件(大量)の空レコードを作成する

SQLでテーブルをコピーし続ける

1行のデータを作成して、コピーし続けることでレコード数が2倍に増え続けます。件数を指定できないですが、簡単に増やすことができます。

[highlight_sql] DROP TABLE IF EXISTS test; CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `tmp` VARCHARACTER(1), PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /* 空のレコードを作成 */ INSERT INTO test () VALUES (); /* テーブルをコピーするごとに倍に増える */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 2 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 4 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 8 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 16 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 32 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 64 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 128 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 256 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 512 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 1024 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 2048 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 4096 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 8192 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 16384 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 32768 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 65536 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 131072 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 262144 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 524288 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 1048576 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 2097152 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 4194304 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 8388608 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 16777216 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 33554432 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 67108864 */ INSERT INTO test (tmp) SELECT tmp FROM test; /* 134217728 */ SELECT count(*) FROM test; [/highlight_sql]

ID列の欠番を直す

ID列の採番が飛ぶことがあります。その場合は、振り直すことで欠番のないデータとなります。

[highlight_sql] SET @i = 0; UPDATE `test` SET id = (@i := @i +1); [/highlight_sql]

DevelopSQL

Posted by kidatti