JSON型から正規化するためのデータ移行INSERT文 #MySQL

はじめに

個人開発でjson型便利じゃね？？と長らく使ってきたけど、
これはさすがに正規化すべきだな。。という部分の正規化を実施したところ、
なかなかに大変だったので、メモとして残しておきます。

正規化したテーブル

実施前

CREATE TABLE board (
  id int(11) NOT NULL AUTO_INCREMENT,
  memo_ids json DEFAULT NULL, -- ❗️ここ！！！
  title varchar(255) NOT NULL,
  content json NOT NULL
);

CREATE TABLE memo (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  content text NOT NULL
);

boardテーブルにmemoテーブルを関連付けするために、
memoのidをjson型で複数持つようにしてました。
（明らかにアンチパターンですが勉強を兼ねて試してました。）

memo_idsの値はこんな感じ。
JSON型といいつつ、このカラムは配列で格納してました。

SELECT id, memo_ids, JSON_LENGTH(memo_ids) FROM board WHERE JSON_TYPE(memo_ids) != 'NULL' AND JSON_LENGTH(memo_ids) != 0;
-- ちなみにJSON型は`IS NOT NULL`で`NULL`を弾けないのも地味にハマります。
+----+----------------------------------------------------+-----------------------+
| id | memo_ids                                           | JSON_LENGTH(memo_ids) |
+----+----------------------------------------------------+-----------------------+
|  9 | [837, 828, 851, 819]                               |                     4 |
| 17 | [837, 828, 819, 848, 851]                          |                     5 |
| 22 | [845, 309, 844]                                    |                     3 |
| 23 | [606]                                              |                     1 |
| 26 | [114, 761, 735, 729, 702, 699, 691, 687, 686, 680] |                    10 |
+----+----------------------------------------------------+-----------------------+

実施後

boardテーブルからmemo_idsカラムを削除。
中間テーブルを作成して正規化。

CREATE TABLE board (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  content json NOT NULL
);

CREATE TABLE IF NOT EXISTS board_memo (
  id int(11) NOT NULL AUTO_INCREMENT,
  board_id int(11) NOT NULL,
  memo_id int(11) NOT NULL,
  sort int(11) NOT NULL
);

移行のために作ったSQL文

-- 区切り文字を "//" に変更。
-- 関数を定義する際に「;」で処理が発火されないように一時的に変更している。
delimiter //
-- ストアドプロシージャー定義
CREATE PROCEDURE loop_insert_board_memo()
  begin
    SET @start_idx = 0;
    SET @end_idx=(SELECT count(*) FROM board WHERE JSON_TYPE(memo_ids) != 'NULL' AND JSON_LENGTH(memo_ids) != 0);
    WHILE @start_idx < @end_idx DO
      PREPARE CREATE_TEMP FROM "CREATE TEMPORARY TABLE filter_board_temp SELECT id, memo_ids, JSON_LENGTH(memo_ids) FROM board WHERE JSON_TYPE(memo_ids) != 'NULL' AND JSON_LENGTH(memo_ids) != 0 LIMIT 1 OFFSET ?";
      EXECUTE CREATE_TEMP USING @start_idx;
      SET @board_id = (SELECT id FROM filter_board_temp);
      SET @memo_ids = (SELECT memo_ids FROM filter_board_temp);
      SET @json_length = (SELECT JSON_LENGTH(memo_ids) FROM filter_board_temp);
      SELECT @board_id, @memo_ids, @json_length;

      -- 対象のレコードの、memo_idを１つずつ出力
      SET @start_memo_idx = 0;
      WHILE @start_memo_idx < @json_length DO
        PREPARE CREATE_MEMO_IDX_TEMP FROM "CREATE TEMPORARY TABLE current_memo_id_temp SELECT JSON_EXTRACT(memo_ids, CONCAT('$[',?,']')) as memo_id FROM board WHERE id = ?";
        SET @start_memo_idx_for_json = CAST(@start_memo_idx AS CHAR);
        EXECUTE CREATE_MEMO_IDX_TEMP USING @start_memo_idx_for_json, @board_id;
        SET @current_memo_id = (SELECT memo_id FROM current_memo_id_temp);
        SELECT @current_memo_id;

        -- 今回の目的であるINSERTを実行！！
        INSERT INTO board_memo
        SELECT
          null,
          @board_id,
          @current_memo_id,
          @start_memo_idx + 1
        FROM
          board
        WHERE
          id = @board_id;

        SET @start_memo_idx = @start_memo_idx + 1;
        DROP TABLE current_memo_id_temp;
      END WHILE;

      SET @start_idx = @start_idx + 1;
      DROP TABLE filter_board_temp;
    END WHILE;
  end
-- ストアドプロシージャー定義終了
//
-- DELIMITER -- 区切り文字を ";" に戻す。
delimiter ;
-- ストアドプロシージャー呼び出し
call loop_insert_board_memo();

ハマったポイント

LIMIT句に変数を使えないので、prepared statementを使った
memo_idsカラムからindexを指定して値を取得するために、CONCATを使った。
- こうしないとprepared statementの「?」として認識されない。
- 「Cannot create a JSON value from a string with CHARACTER SET 'binary'.」が発生したのでIntをCHARにキャスト。

おわりに

安易にJSON型は使わないべし・・！！
とはいえ、今回のデータ移行でめちゃくちゃSQLの勉強になりました。笑
どなたかの参考になれば幸いです。