こんにちは！新卒3年目エンジニアのtakadaです。
本日はWedding Parkの一部サービスが5.7系だったのをMySQL8系にアップデートした際に知ったtipsを紹介します！
① RANKが予約語に
MySQL8系からRANKが新しく予約語になりました。
予約語とは、言語の仕様で使い方が決められている単語のことでDB名・カラム名・エイリアスなどにて、そのまま記載すると下記のようにエラーが生じてしまいます。
もし既存でRANKを使用しているテーブルなどがありましたら注意が必要ですね…!
解消するためには、バッククォートで囲む必要があります。
今回はよく使いそうなRANKに着目致しましたが、他にも8系から予約語として追加されたものが多くあります。
詳しくは下記のドキュメントよりご確認ください！
https://dev.mysql.com/doc/refman/8.0/ja/keywords.html
② ウィンドウ関数にRANK()・DENCE_RANK()が追加
RANKが予約語となった理由でもありますが、
MySQL8.0.2よりRANK()・DENCE_RANK()というウィンドウ関数が追加されました。
どちらも対象のカラムの中で何番目の値かを返すことができる関数ですが、
RANK()は同じ値があった際に次の順位の値を飛ばした値にするのに対して、DENSE_RANK()は連続した値を用いて順位付けをします。
実際に下記のようなテーブルを使って見ていきます。
それぞれの商品において、値段より順位付けをしたい場合にRANK()・DENSE_RANK()を使います。
クエリ結果を見て頂くとわかりますが、
値段が同じ場合において順位の違いがRANK()とDENSE_RANK()にはあります。
同じ順位のものが複数ある場合、その後の順位に影響が出るか出ないかの違いとなります。
この2つ以外にも順位付けに使用できるROW_NUMBER()がありますので次に説明します。
③ ウィンドウ関数にROW_NUMBER()が追加
こちら同じく順位付けに使用できるROW_NUMBER()。
上記のようなテーブルがあり、それぞれの商品において値段の安い順に連番を振りたい。
その際、下記クエリ文のように ROW_NUMBER()を使用することでシンプルに記載することができる。
また、カテゴリー毎に連番を振ることも可能でありクエリ文を記載すると以下のようになる。
RANK()・DENSE_RANK()と違い、同率の順位があったとしても同じ順位として結果を返さないようになっております。partition byなどを上手く使いながら活用していきたいですね！
④ DATE型 (DATETIME型)と文字列の比較方法の変更
MySQL8.0.16よりDATE型(DATETIME型)と文字列の比較方法が変更されました。
MySQL8.0.15以下の比較手順
・比較する文字列をDATE or DATETIMEに変換して比較
・変換ができない場合、DATEを文字列に変換して比較
MySQL8.0.16からの比較手順
・文字列をDATE or DATETIME型に変換して比較
・上記ができない場合エラーが発生 (具体的なエラー文を下記に記載)
今までは、DATEを文字列に変換して比較することで上手くいっていましたが
比較手順の変更により上記のクエリ文のようにエラーが生じてしまいます…!
DATE型の条件を入れる際は、記載方法に注意しましょう！！
⑤ GROUP BYによる暗黙のソートがされなくなる
今まではGROUP BYをすることで暗黙に一定の並び順でソートされてましたが、
ソートがされなくなります。
具体的に示すと下記クエリ文のようにgroup byでソートした場合、
今までであればidは昇順で出力されていたが暗黙にソートされなくなってしまいます。
もし既存のコードで、GROUP BYのソートに頼ってしまっているものがあれば注意が必要です！
(GROUP BYをした後にfirst()でデータを取得しているなどなど、、)
もし順番を固定したい場合、ORDER BYで指定する必要があります。
⑥ CHECK制約による不正データ防止
MySQL8.0.16からCHECK制約が導入されました。
この制約は、データをINSERT・UPDATEする際に予めCHECK制約にて設定した条件を満たすかを検証する。満たさない場合はエラーとなるといった制約です。
例えば以下のテーブルがあったとします。
・上記テーブルにおいて、値段が1000円以上の商品が挿入されることを防ぐようにしたい
・そのために、下記のようなCHECK制約をいれる
その後、1000円以上するデータをINSERTしようとすると、、、
データを弾いてくれます！
これがあることで、もしアプリケーション側でデータを弾くことができなかった場合においてもSQL側でデータを弾くことができます！
⑦ WITH句が追加
WITH句はサブクエリと似ていますが、
実行結果を一時的に仮想的なテーブルとして作成し、それをメインテーブルで
使用することができるといったものです。
例えば、下記テーブルにおいて各カテゴリー毎の売り上げを出力したい場合
下記のように、サブクエリを用いて記載するよりも可読性が高くなっています。
サブクエリによってクエリ文が複雑になっている箇所に、ぜひ活用していきたいですね！
⑧ カラム毎にJSON型を扱えるように
テーブル作成時にデータ型をJSONと記載することで、非構造化データをカラム毎に扱うことができます。
ここでは、基本的なテーブル作成・挿入・更新について記載します。
まずテーブル作成時は下記のようにデータ型をJSONに指定。
次にデータを挿入します。
上記のようにjson型でデータが挿入できていることが確認できます。
キー名検索・更新したい際は下記のようなクエリ文を記載。
その他できることがたくさんありますので、興味があれば是非調べてみてください！JSON型が上手く活用できれば、不必要なリレーション定義などがなくなり様々な所で活用できそうです！
⑨ デフォルトのログイン認証方式変更
MySQL8.0.4より、デフォルトのログイン認証方式が下記のように変更されました。
mysql_native_password ⇨ caching_sha2_password
caching_sha2_passwordは、暗号方式SHA-256を使った認証方式でキャッシュを使用することで再認証を高速化できます。
何度もmysqlにログインする場合、嬉しいですね！
ただ、注意して欲しいのは、caching_sha2_passwordに対応してない
言語を使用するときです。
PHP7.2.4より前のバージョンにおいては、caching_sha2_passwordに対応していないため、認証方式を設定する必要があります。
下記はクエリ文による認証方式の確認と更新方法です。
➉ 不可視プライマリーキー機能が追加
MySQL8.0.30より、不可視プライマリーキーという機能が導入されました。
不可視プライマリーキーの設定をしている場合、
主キーが必須となり、ない場合は見えないカラムとして自動的にAUTO_INCREMENTのPKカラムを追加してくれます。
設定方法の確認↓
※ デフォルトは0になっています。
オンにする場合は、下記クエリ文実行
基本的に、設定がオンの場合はNOT NULL + UNIQUE KEYがカラムに存在しても(主キーとなれるようなデータ構造だとしても)エラーが生じます。
※ SourceでOFF・ReplicaでONの場合はプライマリーキーなしでテーブルを作成してもReplicaでは設定されません！
以上がMySQLを8系へバージョンアップ時に知った主なTipsとなります！
MySQL8系に関してシェアできるナレッジがまた出てきましたら、こちらのブログにて記載致します！
まだまだMySQL8系にて変更されたことが多くありますので、
是非是非調べてみてください〜！
