MySQL5.6->MySQL8.0という大幅なバージョンアップで、コード修正しなければならない部分が多数あったので、今回対応した4つの変更点について書きます。
MySQL5.7以降の変更
distinct使用時、selectリストにない項目でORDER BY すると怒られるようになった
例えば以下のように注文テーブル、ユーザーテーブルがあるとします。
ORDERTABLE
| orderid(主キー) | orderdate | fixdate | userid |
|---|---|---|---|
| OD0001 | 2022-07-10 12:34:56 | 2022-07-20 12:34:56 | 0001 |
| OD0002 | 2022-07-12 12:34:56 | 0002 | |
| OD0003 | 2022-07-11 12:34:56 | 0001 |
USERTABLE
| userid(主キー) | username |
|---|---|
| 0001 | 山田太郎 |
| 0002 | 田中花子 |
「fixdateが入っていない」レコードから「orderid, username」を「orderdate」順に取得したい場合、下記のようなSQLになります。
SELECT distinct o.orderid,u.username
FROM ORDERTABLE o INNER JOIN USERTABLE u ON o.userid = u.userid
WHERE o.fixdate IS NULL
ORDER BY o.orderdate;
MySQL5.7以降だとこのSQLで怒られます。
PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'db_name.o.orderdate' which is not in SELECT list; this is incompatible with DISTINCT
これは、MySQL設定の ONLY_FULL_GROUP_BY モードが有効になっているから。
MySQL5.7から設定デフォルト値として「ONLY_FULL_GROUP_BY」が追加になったため、設定を変えなければこのエラーに遭遇すると思います。
対策としては下記が挙げられます。
- selectリストにORDER BY項目を追加
- ORDER BY項目にselectリストのカラムを追加
- ONLY_FULL_GROUP_BY モードを無効にする
- サブクエリでORDER BY項目ありのselect distinctをし、外側でORDER BYする
今回はselect項目を増やしたくなかったので、以下のサブクエリで対応しました。
SELECT t1.orderid,t1.username
FROM (
SELECT distinct o.orderid,o.orderdate,u.username
FROM ORDERTABLE o INNER JOIN USERTABLE u ON o.userid = u.userid
WHERE o.fixdate IS NULL
) AS t1
ORDER BY t1.orderdate;
なお、今回distinct項目に「o.orderdate」が追加されていますが、同テーブルの主キー（ユニークキー）である「o.orderid」も入っているので、この変更によって取得行数が増えることはありません。（o.orderid,u.usernameが同じ かつ o.orderdate が異なることはあり得ない）
参考
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.20.3 MySQL での GROUP BY の処理
- MySQLでdistinctが効かない。mysqlバージョン5.7からの変更。｜ prophet STAFF BLOG
MySQL8.0以降の変更
「GROUP BYによる暗黙のソート」がされなくなる
今までGROUP BYで暗黙のソートが行われていましたが、MySQL8からソートしなくなったので取得順が一定でなくなりました。
プロダクトコードで暗黙のソートに依存してしまっている場合はもちろん、ユニットテストで並び順を検証している場合もテスト実行結果が変わってくるので対応が必要になります。
公式リファレンスより：
以前に GROUP BY ソートに依存していたクエリーでは、以前の MySQL バージョンとは異なる結果が生成される場合があります。 特定のソート順序を生成するには、ORDER BY 句を指定します。
対応策としては公式の通り、ソートしたいときはちゃんとORDER BYしてねというところですね。
参考
- MySQL :: MySQL 8.0 リファレンスマニュアル :: 2.11.4 MySQL 8.0 での変更
- 日々の覚書: MySQL 8.0では「GROUP BYによる暗黙のソート」がされなくなるよ
datetime(date)型に変換できない文字列とdatetime型を比較するSQLが不正になった
datetime型カラムと空文字と比較しているSQLでエラーが出るようになりました。
datetime型カラムにはそもそも空文字は登録できないので、比較している所を削除することで対応しました。（なんで比較してるんだって感じですが）
参考
Rankが予約語になった
これは何とも…「Rank」って普通にカラム名とかで使うような気がするんですが…困っちゃいますね…
対応策としてはエスケープするか、予約語になっていない「〇〇_RANK」とかにカラム名変えるかしかないですね。
今回対応したときはカラムの使用箇所が少なかったので、カラム名変更で対応しました。