集計系などをやっているといつもこの文の構文の原理を忘れるのと、案外ググっても出てこないので他の人たちのためのメモ
環境
$ mysql --version
mysql Ver 14.14 Distrib 5.7.17, for osx10.12 (x86_64)
例と解説
前提と安直な失敗例
仮に以下のようなデータのテーブルがあったとする
ただidとnameを出すだけのクエリ
SELECT id , name FROM users;
結果
+----+--------+
| id | name |
+----+--------+
| 1 | Taro |
| 2 | Ziro |
| 3 | Saburo |
+----+--------+
3 rows in set (0.00 sec)
ここに name_length というかたちで文字数を出すとすると以下
nameの長さを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 にフラグを立てたい」となると以下のようになる.
長さを条件式に当てはめて、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文字以上の数をカウントしたい」とする、そうした場合に安直に考えると以下になる
安直に条件式でCOUNTしてみるクエリ
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 の集計条件をセットで使うと意図した結果が導き出せる
従来なら条件式にあてはまらなければ0だが、それがNULLになるクエリ
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)
意図した結果を得ることができる。