MariaDB(MySQL)でストアドプロシージャでテスト用レコードの大量挿入

テスト用データベースとテーブルの作成

説明のためにテスト用データベースとテーブル構造の作成です。

-- データベース作成
CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE db_test;
-- テーブル作成
CREATE TABLE t_kind(kind_id INT, kind_name VARCHAR(100));
CREATE TABLE t_item(item_id INT, kind_id INT, item_name VARCHAR(100));

ER図は以下です。

t_item(商品テーブル)
  item_id                      t_kind(種別テーブル)
  kind_id  ←─────────  kind_id
  item_name                      kind_name
  item_price

テストデータの作成

Bulk Insert（バルクインサート）を使って連番のテスト用レコードを挿入する場合は以下のようなSQLですが10万レコードを挿入するSQLを作成するのは時間がかかります。

-- t_kindテーブルのレコードの削除
TRUNCATE t_kind;
-- t_kindテーブルにレコードを挿入
INSERT INTO t_kind values(1,'種別1'),(2,'種別2');
-- t_itemテーブルのレコードの削除
TRUNCATE t_item;
-- t_itemテーブルにレコードを挿入
INSERT INTO t_item VALUES(1,1,'商品1',1),(2,2,'商品2',2),(3,1,'商品3',3),(4,2,'商品4',4),(5,1,'商品5',5),(6,2,'商品6',6);

ストアドプロシージャを作成して実行すれば少し速くテストデータを作成できます。
CREATE PROCEDURE 作成するストアドプロシージャの名前(引数)
BEGIN
プログラム
END;

以下はSELECT文を実行するストアドプロシージャ名「proc_insert_record」として作成します。

-- 「proc_insert_record」プロシージャが存在していたら削除する
DROP PROCEDURE IF EXISTS proc_insert_record;
DELIMITER //  -- 「;」を「//」に一時的に変更する
CREATE PROCEDURE proc_insert_record()
BEGIN
  -- 変数の宣言
  DECLARE a_item_id INT DEFAULT 1;

  START TRANSACTION;

  -- t_kindテーブルのレコードを削除
  TRUNCATE t_kind;
  -- t_kindテーブルにレコードを挿入
  INSERT INTO t_kind values(1,'種別1'),(2,'種別2');

  -- t_itemテーブルのレコードの削除
  TRUNCATE t_item;
  -- ループの開始
  loop_label: LOOP
    -- t_itemテーブルにレコードを挿入
    INSERT INTO t_item VALUES(a_item_id, MOD(a_item_id-1,2)+1, CONCAT('商品',a_item_id), a_item_id);
    -- 「a_item_id」をインクリメント
    SET a_item_id = a_item_id + 1;
    -- 「a_item_id」が100,000に到達したらループを抜ける
    IF a_item_id > 100000 THEN
        LEAVE loop_label;
    END IF;
  -- ループの終了
  END LOOP;

  COMMIT;

  SELECT COUNT(*) FROM t_item;
END;
//
DELIMITER ;  -- 「//」を「;」に戻す

ストアドプロシージャを実行するコマンドは以下です
CALL ストアドプロシージャ名;

MariaDB [db_test]> CALL proc_insert_record;
+----------+
| COUNT(*) |
+----------+
|   100000 |
+----------+
1 row in set (1 min 32.972 sec)

Query OK, 100002 rows affected (1 min 32.974 sec)

1分33秒で10万レコードの挿入が出来ました。

ちなみに100万レコードの連番のテストレコード挿入の場合は27分40秒かかりました。

MariaDB [db_test]> CALL proc_insert_record;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (27 min 39.863 sec)

Query OK, 1000002 rows affected (27 min 39.866 sec)