SQLアンチパターン ジェイウォークから正規化するためのデータ移行INSERT文

はじめに

個人開発でアンチパターンと言われているジェイウォークを使用してしまっていたので正規化しました。
データ移行も対応したのでその際に作成したINSERT文をメモとして残しておきます。

正規化したテーブル

正規化前

CREATE TABLE english (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  mean text NOT NULL,
  source varchar(255), -- ❗️ここ 
  PRIMARY KEY (id)
);

englishテーブルは学習した英単語や英文を保存しておくためのテーブルです。
sourceカラムには参考元を保存します。たとえば「金フレ」とか。
なんですが、いろんな単語帳を使っているうちに「金フレ,銀フレ」みたいな感じで、
値を複数格納したくなってしまい、ほぼ無意識のうちに「,」区切りで登録してしまっていました😂

これでも、一覧画面で絞り込みできたのでOKではあるんですが、
「金フレ」と「金のフレーズ」が混在してしまったり、
ラベル名を変える際にも１つ１つ更新かけないといけなかったり、なにかと勝手がよくないです。
ってことで今回正規化することに決めました。

正規化後

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

CREATE TABLE source (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE english_source (
  id int(11) NOT NULL AUTO_INCREMENT,
  english_id int(11) NOT NULL,
  source_id int(11) NOT NULL,
  PRIMARY KEY (id)
);

移行のために作ったSQL文

sourceテーブルにデータ移行

-- 区切り文字を "//" に変更。
-- 関数を定義する際に「;」で処理が発火されないように一時的に変更している。
delimiter //
-- ストアドプロシージャー定義
CREATE PROCEDURE loop_insert_english_source()
  begin
    -- source を重複排除した状態で仮想テーブル作成。element_countで「,」の数をカウント。
    CREATE TEMPORARY TABLE distinct_english_source SELECT DISTINCT source, char_length(source) - char_length(replace(source,',','')) as element_count, is_deleted, created_by, updated_by FROM english WHERE source IS NOT NULL AND source != '';
    SET @start_idx = 0;
    SET @end_idx=(SELECT count(*) FROM distinct_english_source);
    SELECT @start_idx, @end_idx;

    -- sourceの数分WHILEで回す
    WHILE @start_idx < @end_idx DO
      -- 現在扱っているレコードの行だけを仮想テーブルを作って絞りこみ
      PREPARE CREATE_CURRENT_ENGLISH_SOURCE_TEMP FROM "CREATE TEMPORARY TABLE current_english_source SELECT * FROM distinct_english_source LIMIT 1 OFFSET ?";
      EXECUTE CREATE_CURRENT_ENGLISH_SOURCE_TEMP USING @start_idx;
      SET @source = (SELECT source FROM current_english_source);
      SET @element_count = (SELECT element_count FROM current_english_source);
      SELECT @start_idx, @source, @element_count;

      IF @element_count = 0
      THEN 
        INSERT INTO source
        SELECT
          null,
          source
        FROM
          distinct_english_source
        WHERE
          source = @source;
      END IF;

      IF @element_count > 0
      THEN
        SET @start_element_idx = 0;
        WHILE @start_element_idx < @element_count DO
          -- 値が存在しない場合のみINSERT
          IF (SELECT count(*) FROM distinct_english_source WHERE source = SUBSTRING_INDEX(@source, ',', @start_element_idx + 1)) = 0
          THEN
            INSERT INTO source
            SELECT
              null,
              SUBSTRING_INDEX(@source, ',', @start_element_idx + 1)
            FROM
              distinct_english_source
            WHERE
              source = @source;
          END IF;
          SET @start_element_idx = @start_element_idx + 1;
        END WHILE;
      END IF;

      SET @start_idx = @start_idx + 1;
      DROP TABLE current_english_source;
    END WHILE;

    DROP TABLE distinct_english_source;
  end
-- ストアドプロシージャー定義終了
//
-- 区切り文字を ";" に戻す。
delimiter ;
-- ストアドプロシージャー呼び出し
call loop_insert_english_source();

english_sourceテーブルにデータ移行

-- english_and_source に移動
DROP PROCEDURE IF EXISTS loop_insert_english_and_source;
DROP TABLE IF EXISTS current_english;
delimiter //
CREATE PROCEDURE loop_insert_english_and_source()
  begin

    SET @start_idx = 0;
    SET @end_idx=(SELECT count(*) FROM english WHERE source IS NOT NULL AND source != '');
    SELECT @start_idx, @end_idx;

    WHILE @start_idx < @end_idx DO
      PREPARE CREATE_CURRENT_ENGLISH_TEMP FROM "CREATE TEMPORARY TABLE current_english SELECT * FROM english WHERE source IS NOT NULL AND source != '' LIMIT 1 OFFSET ?";
      EXECUTE CREATE_CURRENT_ENGLISH_TEMP USING @start_idx;

      SELECT id, source FROM current_english;

      SET @element_count = (SELECT char_length(source) - char_length(replace(source,',','')) FROM current_english);
      SET @source = (SELECT source FROM current_english);

      IF @element_count = 0
      THEN
        INSERT INTO english_source
        SELECT
          null,
          id,
          (SELECT id FROM source WHERE name = @source)
        FROM
          current_english;
      END IF;

      IF @element_count > 0
      THEN
        SET @start_element_idx = 0;
        SET @current_source_name = (SELECT SUBSTRING_INDEX(@source, ',', @start_element_idx + 1));
        SET @source_id = (SELECT id FROM source WHERE name = SUBSTRING_INDEX(@source, ',', @start_element_idx + 1));
        SELECT @current_source_name, @source_id;
        WHILE @start_element_idx < @element_count DO
          -- 値が存在しない場合のみINSERT
          INSERT INTO english_source
          SELECT
            null,
            id,
            @source_id
          FROM
            current_english;
        SET @start_element_idx = @start_element_idx + 1;
        END WHILE;
      END IF;

      SET @start_idx = @start_idx + 1;
      DROP TABLE current_english;
    END WHILE;

  end
//
delimiter ;
call loop_insert_english_and_source();

おわりに

こういうDB定義の見直しに伴うデータ移行を経験すると、DB設計の重要性に気付きます。

また、このSQL文をつくる前にJSON型の正規化も対応していたので、余裕だろうと甘く見積もっていましたが、
ジェイウォークの正規化の方がJSON型より大変でした。。
どなたかの参考になれば幸いです。

以下、前記事。
JSON型から正規化するためのデータ移行INSERT文