MySQLのクエリで疑問に思った事象について
MySQLのクエリで疑問に思った事象を、備忘録として記載。
GROUP BYでカラム指定を省略してSELECT
mauticのインストールで当初MariaDB(10.2.5)を利用していたが、DB・ユーザ設定完了後、ダッシュボードの画面が表示されず、調べてみるとPHPエラーが発生していた。
エラー内容の詳細はこちら。
少し調べると、GROUP BYでカラム指定を省略して(この場合は、ll.name)SELECTしていたためエラーとなっていた。
SELECT
COUNT(t.date_added) AS leads, ll.id, ll.name
FROM lead_lists_leads t
INNER JOIN lead_lists ll
ON ll.id = t.leadlist_id WHERE (ll.is_published = true)
AND (t.date_added BETWEEN "2017-04-02 00:00:00"
AND "2017-05-02 23:59:59")
GROUP BY ll.id
ORDER BY leads DESC
LIMIT 9;
githubで不具合報告されていた(詳細はこちら)。
MariaDBではなくMySQL(5.7)にするとなぜ発生しなかったどうか調べてみたところ、sql_modeでONLY_FULL_GROUP_BYの指定を偶然にも外していたためだった。
sql_modeにONLY_FULL_GROUP_BYを指定すると同様のエラーが発生した。
他のDBに移行するなど、今後の運用を加味すると、はまりそうなので、ONLY_FULL_GROUP_BYは指定したほうが無難かもしれない。
参考資料:13.19.3 MySQL Handling of GROUP BY
GROUP BYなしでHAVINGする場合
GROUP BYといえば、過去になぜか以下のようなクエリをプログラムで実行して、まったく本来の意図通りにならなかった痛い思いをしたことがある。
user_itemテーブルにこんな感じでデータがあるとする。
user_id | item_id |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
2 | 1 |
3 | 2 |
3 | 3 |
SELECT
DISTINCT user_id
FROM user_item
HAVING COUNT(user_id) > 1;
を実行すると、以下となり、user_idに3が表示されない(当然のことだけど)。
+---------+
| user_id |
+---------+
| 1 |
+---------+
本来意図したことを実現するクエリは以下。
SELECT
user_id
FROM user_item
GROUP BY user_id
HAVING COUNT(user_id) > 1;
出力は以下の通り。
+---------+
| user_id |
+---------+
| 1 |
| 3 |
+---------+
なお、stackoverflowで、こちらやこちらでも取り上げられているが、GROUP BYがなくてもHAVINGは指定できるようだ。
CROSS JOINと他のJOINの組み合わせ
テーブルにこんな感じでデータがあるとする。
テーブル名:test
id | category_id | region_id |
---|---|---|
1 | 1 | 1 |
2 | 2 | 2 |
3 | 3 | 3 |
4 | 1 | 3 |
5 | 2 | 2 |
6 | 3 | 1 |
テーブル名:category
id | name |
---|---|
1 | りんご |
2 | バナナ |
3 | パイナップル |
テーブル名:region
id | name |
---|---|
1 | 北海道 |
2 | 青森 |
3 | 岩手 |
下記のようにCROSS JOIN
とINNER JOIN
を1つのSQL文で実行するとエラーとなる。
SELECT
category.name, region.name
FROM
test AS test, category AS category
INNER JOIN
region
ON
test.region_id = region.id
WHERE
test.category_id = category.id AND test.id = 2;
ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'
LEFT JOIN
でも同様にエラーとなる。
SELECT
category.name, region.name
FROM
test AS test, category AS category
LEFT JOIN
region
ON
test.region_id = region.id
WHERE
test.category_id = category.id AND test.id = 2;
ERROR 1054 (42S22): Unknown column 'test.region_id' in 'on clause'
この場合、ON
句ではなく、WHERE
句で条件指定することで解決できる。
SELECT
category.name, region.name
FROM
test AS test, category AS category
INNER JOIN
region
WHERE
test.category_id = category.id AND test.id = 2 AND test.region_id = region.id;
+-----------+--------+
| name | name |
+-----------+--------+
| バナナ | 青森 |
+-----------+--------+
UNIQUE制約とNULL
下記のようなUNIQUE
制約の属性(NOT NULL
は指定しない)を持つテーブルを作成する。
CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`unique_no` int(10) unsigned,
PRIMARY KEY (`id`),
UNIQUE KEY `test_unique_no` (`unique_no`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
以下のようにUNIQUE
制約の属性に対して、同じ値を追加するとERROR 1062 (23000): Duplicate entry '1' for key 'test_unique_no'
が発生する。
insert into test (unique_no) values (1);
insert into test (unique_no) values (1);
しかし、UNIQUE
制約の属性に対して、null
を指定してもエラーにならない。
insert into test (unique_no) values (null);
insert into test (unique_no) values (null);
UNIQUE
属性なのに、NULL
データが複数ある状態が作れる。
mysql> SELECT * FROM test;
+----+-----------+
| id | unique_no |
+----+-----------+
| 12 | NULL |
| 13 | NULL |
| 10 | 1 |
+----+-----------+