【MySQL】ENUM型のカラムの値に自由入力の値を追加したくなった時の改修方法

　ENUM型は文字列による読みやすさと省スペースなデータ量と高速さを併せ持つ型です。ENUM型を使うことによって限られた種類の値を扱いやすくなります。

MySQL ：： MySQL 8.0 リファレンスマニュアル ：： 11.3.5 ENUM 型

　ENUM型は便利ですが、限られた種類の値しか扱えないという制約があります。この制約はプログラムで取り扱うデータ上でよく出るためぴったりなのですが、その様なデータは時として自由入力も可能にしたいという要望が出てきます。そうなった際にENUM型をどう改造すると便利なままでいられるのかを紹介します。

　例として次のようなENUM型のカラムを持つテーブルがあるとします。

CREATE TABLE posts
(
    post_id   INT AUTO_INCREMENT PRIMARY KEY,
    file_type ENUM ('text', 'image', 'video') NOT NULL
    -- 実際に使う時はファイルパス等、色々なカラムも追加
);

　ファイルのおおまかな種類を file_type カラムに持っています。こうすると予期せぬ値が格納されることもなく扱いやすいです。

　プログラムを運用しているとファイルの種類は自由入力もできるようにすることになったとします。多くの場合は既存の種類に含まれますが「その他」が増えた形です。対応方法の一つとしてENUMをやめて次の様に文字列型にする方法があります。

ALTER TABLE posts MODIFY COLUMN file_type VARCHAR(255) NOT NULL;
-- ↑を実行すると↓のようになります
CREATE TABLE posts
(
    post_id   INT AUTO_INCREMENT PRIMARY KEY,
    file_type VARCHAR(255) NOT NULL
    -- 実際に使う時はファイルパス等、色々なカラムも追加
);

　互換性も保たれますし、そこまで悪い形ではありません。とはいえ種類単位による集計クエリを作る際など、しばしば面倒を引き起こしやすくもなってしまう形でもあります。

　そこでENUMと自由入力を兼ね備えた形を作ります。これは次のようになります。

-- ファイルの種類のENUMにその他を表す other を追加します。
ALTER TABLE posts  MODIFY COLUMN file_type ENUM('text', 'image', 'video', 'other') NOT NULL;
-- other のための自由入力列を追加します。
ALTER TABLE posts  ADD COLUMN file_type_other VARCHAR(255) DEFAULT NULL;
-- 検索のためにファイルの種類の表示用文字列をまとめた列を追加します。
-- STOREDにしておくとインデックスを列につけられて高速化しやすいです。
ALTER TABLE posts
    ADD file_type_display VARCHAR(255) AS (
        CASE
            WHEN file_type = 'text' THEN 'テキスト'
            WHEN file_type = 'image' THEN '画像'
            WHEN file_type = 'video' THEN '動画'
            WHEN file_type = 'other' THEN file_type_other
            ELSE file_type END
        )
    STORED;
-- ↑を実行すると↓のようになります
CREATE TABLE posts
(
    post_id         INT AUTO_INCREMENT PRIMARY KEY,
    file_type       ENUM ('text', 'image', 'video', 'other') NOT NULL,
    file_type_other VARCHAR(255)                             NULL,
    file_type_display VARCHAR(255) AS (
        CASE
            WHEN file_type = 'text' THEN 'テキスト'
            WHEN file_type = 'image' THEN '画像'
            WHEN file_type = 'video' THEN '動画'
            WHEN file_type = 'other' THEN file_type_other
            ELSE file_type END
        ) STORED
);

　こうすると検索クエリは次のように読みやすいもので済みます。

-- ファイルの種類に属するレコードの数を集計します。
-- これだけで「その他」のファイル群も1レコードにまとまります。
SELECT file_type, COUNT(file_type) as c
FROM posts
GROUP BY file_type
ORDER BY c DESC;
-- ファイルの種類の前方一致検索をします。
SELECT *
FROM posts
WHERE file_type_display LIKE 'P%';
-- ファイルの種類の検索をします。
-- アプリケーションの中でその他以外の種類が要求されているとわかるならば
-- この検索をすると早いです。
SELECT *
FROM posts
WHERE file_type = 'image';

　こんな感じでENUM型のカラムを改造すると、自由入力をサポートしつつデータの整合性、データのやり取りの速度、開発速度を維持しやすいです。