集計系などをやっているといつもこの文の構文の原理を忘れるのと、案外ググっても出てこないので他の人たちのためのメモ
環境
$ mysql --version
mysql Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64)
例と解説
前提と安直な失敗例
仮に以下のようなデータのテーブルがあったとする
SELECT id , name FROM users;
+----+--------+
| id | name |
+----+--------+
| 1 | Taro |
| 2 | Ziro |
| 3 | Saburo |
+----+--------+
3 rows in set (0.00 sec)
ここに name_length
というかたちで文字数を出すとすると以下
SELECT id , name , LENGTH(name) as name_length FROM users;
+----+--------+-------------+
| id | name | name_length |
+----+--------+-------------+
| 1 | Taro | 4 |
| 2 | Ziro | 4 |
| 3 | Saburo | 6 |
+----+--------+-------------+
3 rows in set (0.00 sec)
さらに「nameの文字列が5文字以上の場合は is_over_five
にフラグを立てたい」となると以下のようになる.
SELECT id , name , LENGTH(name) as name_length , LENGTH(name) > 5 AS is_over_five FROM users;
+----+--------+-------------+--------------+
| id | name | name_length | is_over_five |
+----+--------+-------------+--------------+
| 1 | Taro | 4 | 0 |
| 2 | Ziro | 4 | 0 |
| 3 | Saburo | 6 | 1 |
+----+--------+-------------+--------------+
3 rows in set (0.00 sec)
そして「nameの文字数が5文字以上の数をカウントしたい」とする、そうした場合に安直に考えると以下になる
SELECT COUNT(LENGTH(name) > 5) FROM users;
ただこうした場合結果は以下のようになる。
+-------------------------+
| COUNT(LENGTH(name) > 5) |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.01 sec)
1がカウントされてほしいのに3になってしまった
原因
COUNTは以下の原理で集計を行う。
COUNT(expr)
SELECT ステートメントで取得された行に含まれる expr の非 NULL 値の数を返します。結果は BIGINT 値になります。
そのため、 LENGTH(name) > 5
の結果は 0 もしくは 1 があてはまるため、いずれもNULLではないので足し上げられてしまう。
解決策
条件式に対して OR NULL
をつける。
MySQLではORは以下のような振る舞いをする
NULL オペランドが 1 つあれば、ほかのオペランドがゼロ以外である場合の結果は 1、それ以外の場合は NULL になります。両方のオペランドが NULL であれば、結果は NULL になります。
つまり 条件式 OR NULL
とすると 条件式の結果が 0 もしくは NULL の場合は NULL 、1 のときは 1 を返す。
この仕様と先程の COUNT
の集計条件をセットで使うと意図した結果が導き出せる
SELECT LENGTH(name) > 5 OR NULL FROM users;
+--------------------------+
| LENGTH(name) > 5 OR NULL |
+--------------------------+
| NULL |
| NULL |
| 1 |
+--------------------------+
3 rows in set (0.01 sec)
こうなるので
SELECT COUNT(LENGTH(name) > 5 OR NULL) AS over_five_count FROM users;
+-----------------+
| over_five_count |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
意図した結果を得ることができる。