CASE式みたいな処理って、自分の場合はこれまでアプリケーション内で行うことが多くてMySQLで行うことが少なかったです。
しかし、使う機会があって若干はまったので、備忘録として残します。
やりたかったことは、「NULLか空文字だったら値を0として取得する」ことでした。
CASE式には単純CASE式と検索CASE式があってそれが頭に入っておらず、はまっておりました。
サンプルテーブルを以下に用意します。
START TRANSACTION;
CREATE TABLE member (
id int(11) NOT NULL,
name varchar(64) NOT NULL,
gender enum('male','female') NOT NULL,
age int(11) NOT NULL,
memo mediumtext,
created datetime NOT NULL,
updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE member
ADD PRIMARY KEY (id),
ADD KEY gender (gender),
ADD KEY age (age);
ALTER TABLE member
MODIFY id int(11) NOT NULL AUTO_INCREMENT;
INSERT INTO member (name, gender, age, memo, created) VALUES
('suzuki', 'male', 25, 'イケメン', '2021-04-01 00:00:00'),
('ohtani', 'female', 30, NULL, '2021-05-01 00:00:00'),
('sato', 'female', 35, '', '2021-06-01 00:00:00'),
('nakamura', 'female', 40, 'おもしろい', '2021-07-01 00:00:00'),
('tanaka', 'female', 45, NULL, '2021-08-01 00:00:00'),
('inoue', 'male', 50, '博識', '2021-09-01 00:00:00'),
('ishida', 'female', 55, '優しい', '2021-10-01 00:00:00'),
('matsumoto', 'male', 60, '穏やか', '2021-11-01 00:00:00'),
('sasaki', 'male', 65, '', '2021-12-01 00:00:00'),
('kato', 'female', 70, '', '2022-01-01 00:00:00');
COMMIT;
テーブルの中身はざっとこんな感じ。
| id | name | gender | age | memo | created | updated |
|---|
| 1 | suzuki | male | 25 | イケメン | 2021-04-01 00:00:00 | 2022-05-13 14:18:42 |
| 2 | ohtani | female | 30 | NULL | 2021-05-01 00:00:00 | 2022-05-13 14:18:42 |
| 3 | sato | female | 35 | | 2021-06-01 00:00:00 | 2022-05-13 14:18:42 |
| 4 | nakamura | female | 40 | おもしろい | 2021-07-01 00:00:00 | 2022-05-13 14:18:42 |
| 5 | tanaka | female | 45 | NULL | 2021-08-01 00:00:00 | 2022-05-13 14:18:42 |
| 6 | inoue | male | 50 | 博識 | 2021-09-01 00:00:00 | 2022-05-13 14:18:42 |
| 7 | ishida | female | 55 | 優しい | 2021-10-01 00:00:00 | 2022-05-13 14:18:42 |
| 8 | matsumoto | male | 60 | 穏やか | 2021-11-01 00:00:00 | 2022-05-13 14:18:42 |
| 9 | sasaki | male | 65 | | 2021-12-01 00:00:00 | 2022-05-13 14:18:42 |
| 10 | kato | female | 70 | | 2022-01-01 00:00:00 | 2022-05-13 14:18:42 |
性別が英語で入っているので日本語で取得してみたいと思います。
比較しやすいように名前と英語での性別も取得します。
以下のようにCASEのすぐ後ろにカラム名が来て、そのWHENの値と同値のCASE式を単純CASE式と呼ぶそうです。
# 単純CASE式
SELECT
name,
gender,
CASE gender
WHEN 'male' THEN '男性'
WHEN 'female' THEN '女性'
END AS gender_ja
FROM member;
検索結果
| name | gender | gender_ja |
|---|
| suzuki | male | 男性 |
| ohtani | female | 女性 |
| sato | female | 女性 |
| nakamura | female | 女性 |
| tanaka | female | 女性 |
| inoue | male | 男性 |
| ishida | female | 女性 |
| matsumoto | male | 男性 |
| sasaki | male | 男性 |
| kato | female | 女性 |
maleの場合は「男性」、femaleの場合は「女性」と表示されます。
次は
60歳以上であれば「senior」
40歳以上60歳未満であれば「middle-age」
40歳未満を「young」
と出力するようにします。
検索CASE式ではWHENの後に検索条件が入ります。
CASEの後に列名は必要ありません。
# 検索CASE式
SELECT
name,
age,
CASE
WHEN age >= 60 THEN 'senior'
WHEN age >= 40 && age < 60 THEN 'middle-age'
ELSE 'young'
END AS generation
FROM member;
検索結果
| name | age | generation |
|---|
| suzuki | 25 | young |
| ohtani | 30 | young |
| sato | 35 | young |
| nakamura | 40 | middle-age |
| tanaka | 45 | middle-age |
| inoue | 50 | middle-age |
| ishida | 55 | middle-age |
| matsumoto | 60 | senior |
| sasaki | 65 | senior |
| kato | 70 | senior |
応用が効くのは検索CASE式で単純CASE式は、検索CASE式でも行うことが出来ます。
最初に行った性別を日本語で表示する単純CASE式を検索CASE式で書いてみます。
# 検索CASE式
# 結果は二つ上の表と同じ
SELECT
name,
gender,
CASE
WHEN gender LIKE 'male' THEN '男性'
WHEN gender LIKE 'female' THEN '女性'
END AS gender_ja
FROM member;
今回やりたかったことは、「NULLか空文字だったら値を0として取得する」だったので、検索CASE式を使えば簡単に出来そうです。
CASEの後ろに memo と書いていたので若干はまっていました。
SELECT
name,
memo,
CASE
WHEN (memo IS NULL || memo = '') THEN 0
ELSE 1
END AS memo_exists
FROM member;
検索結果
| name | memo | memo_exists |
|---|
| suzuki | イケメン | 1 |
| ohtani | NULL | 0 |
| sato | | 0 |
| nakamura | おもしろい | 1 |
| tanaka | NULL | 0 |
| inoue | 博識 | 1 |
| ishida | 優しい | 1 |
| matsumoto | 穏やか | 1 |
| sasaki | | 0 |
| kato | | 0 |
めでたしです。
と、最初は上のSQLを書いたのですがCHAR_LENGTHを使えば空文字判定出来そうですね…。
TRIMも必要なら一緒にすれば良さそうです(全角スペースは除かれないので注意)。
SELECT
name,
memo,
CASE
WHEN CHAR_LENGTH(memo) > 0 THEN 1
ELSE 0
END AS memo_exists
FROM member;
上のIF文を使って書くことも可能です。
SELECT
name,
memo,
IF(CHAR_LENGTH(memo) > 0, 1, 0) AS memo_exists
FROM member;
単純CASEで書けるところも検索CASE式で書けるので、検索CASE式 → 単純CASE式の順に学んだ方が覚えやすい気がします。
