SQL: 内部的なコードを人間に分かりやすいラベルに変換して表示する

RDB のスキーマには、たまに対応表などを参照しながらでないと分からないような内部的なコードが使われていることがある。大抵はアプリケーションの中で変換して表示するだろうけど、これを直接 SELECT とかで確認しようとすると分かりにくい。今回は、それを見やすくするためのテクニックについて。

使った環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysqld --version
mysqld  Ver 5.7.18 for osx10.12 on x86_64 (Homebrew)
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using  EditLine wrapper

まずは MySQL のインタラクティブシェルに入る。

$ mysql -u root

そしてサンプル用のテーブルを用意しておく。内部的なコードに対応するのは users.position_code だ。

mysql> DROP TABLE IF EXISTS users;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> CREATE TABLE users (
    ->   id integer,
    ->   name varchar(255),
    ->   age integer,
    ->   position_code integer
    -> );
Query OK, 0 rows affected (0.04 sec)

そして、いくつかレコードを追加しておこう。

mysql> INSERT INTO users
    -> VALUES
    ->   (1, 'Alice', 20, 1),
    ->   (2, 'Bob', 25, 2),
    ->   (3, 'Carol', 30, 3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

こんな感じでデータが入る。この状態では position_code に一体どんな意味があるのか分からない。

mysql> SELECT *
    -> FROM users;
+------+-------+------+---------------+
| id   | name  | age  | position_code |
+------+-------+------+---------------+
|    1 | Alice |   20 |             1 |
|    2 | Bob   |   25 |             2 |
|    3 | Carol |   30 |             3 |
+------+-------+------+---------------+
3 rows in set (0.00 sec)

そこで CASE ~ END を用いて分かりやすいラベルに変換する。条件を WHEN で指定したら、対応する値を THEN で返せば良い。どれにも該当しない場合には ELSE を使う。

mysql> SELECT
    ->   id,
    ->   name,
    ->   CASE
    ->     WHEN position_code = 1 THEN '一般職員'
    ->     WHEN position_code = 2 THEN '主任'
    ->     WHEN position_code = 3 THEN '課長'
    ->     ELSE '不明'
    ->   END as position
    -> FROM users;
+------+-------+--------------+
| id   | name  | position     |
+------+-------+--------------+
|    1 | Alice | 一般職員     |
|    2 | Bob   | 主任         |
|    3 | Carol | 課長         |
+------+-------+--------------+
3 rows in set (0.00 sec)

これで分かりやすくなった。

もちろんこのテクニックはコードの変換だけでなく特定の条件に一致するか、みたいな確認にも使える。次の例は年齢が 30 を超えているかを misoji カラムに表示している。

mysql> SELECT
    ->   id,
    ->   name,
    ->   CASE
    ->     WHEN age >= 30 THEN 'YES'
    ->     ELSE 'NO'
    ->   END as misoji
    -> FROM users;
+------+-------+--------+
| id   | name  | misoji |
+------+-------+--------+
|    1 | Alice | NO     |
|    2 | Bob   | NO     |
|    3 | Carol | YES    |
+------+-------+--------+
3 rows in set (0.00 sec)