はじめに
こんにちは。
MySQLには、SQLモードと呼ばれる設定があります。
この記事では、MySQLのSQLモードの概要と、デフォルトで有効な6つのモードの意味を整理します。
実行環境
- MySQL 8.0.46
SQLモードとは
SQLモードは、MySQLの構文チェックやデータ検証の挙動を制御する設定です。
たとえば、不正な日付や範囲外の値をINSERTしようとしたとき、エラーにするか警告だけで通すかをSQLモードで制御できます。
現在のSQLモードを確認する
現在のSQLモードは次のクエリで確認できます。
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
デフォルトでは、次の6つが有効になっています。
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
デフォルトで有効な6つのモード
ONLY_FULL_GROUP_BY
GROUP BYに含まれないカラムをSELECTに書くことを制限します。
SELECT name, city FROM users GROUP BY city;
ONLY_FULL_GROUP_BY が有効な場合、name がGROUP BYに含まれていないためこのクエリはエラーになります。
STRICT_TRANS_TABLES
不正な値の INSERT や UPDATE を警告ではなくエラーにします。
CREATE TABLE items (
name VARCHAR(5) NOT NULL
);
INSERT INTO items (name) VALUES ('abcdefgh');
STRICT_TRANS_TABLES が有効な場合、このクエリのように5文字を超える値をnameに登録しようとするとエラーになります。
無効の場合、値が 'abcde' に切り詰められ、警告だけで INSERT が成功します。
NO_ZERO_IN_DATE
月や日が 0 の日付（例: '2024-00-15'）を不正とみなします。
STRICT_TRANS_TABLES が有効の場合エラーになります。
NO_ZERO_DATE
'0000-00-00' を不正な日付とみなします。
こちらも STRICT_TRANS_TABLES が有効の場合エラーになります。
ERROR_FOR_DIVISION_BY_ZERO
0除算が発生した場合の扱いを制御します。
SELECT 1 / 0;
STRICT_TRANS_TABLES と組み合わせると、INSERT や UPDATE の中で0除算が起きた場合にエラーになります。
無効の場合は NULL が返り、警告だけが出ます。
NO_ENGINE_SUBSTITUTION
CREATE TABLE で指定したストレージエンジンが使えない場合にエラーにします。
無効の場合は、指定したエンジンの代わりにデフォルトエンジンが自動で使われます。
SQLモードの変更方法
セッション単位で変更する
現在の接続だけに適用したい場合は SET SESSION を使います。
SET SESSION sql_mode = CONCAT(@@SESSION.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
グローバルで変更する
新しい接続に適用したい場合は SET GLOBAL を使います。
SET GLOBAL sql_mode = CONCAT(@@GLOBAL.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
SET GLOBAL を変更しても、すでに接続しているセッションには影響せず、変更後に新しく接続したセッションから反映されます。
永続化する
SET GLOBAL は、MySQLを再起動すると元に戻ります。
永続的に設定を変えたい場合は、my.cnf や my.ini といった設定ファイルに記述します。
[mysqld]
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO
MySQL 8.0以降では SET PERSIST を使うこともできます。
SET PERSIST sql_mode = CONCAT(@@GLOBAL.sql_mode, ',NO_AUTO_VALUE_ON_ZERO');
SET PERSIST は設定ファイルを直接編集しなくても、再起動後に設定が残ります。
まとめ
- SQLモードはMySQLの構文チェックやデータ検証の厳しさを制御する設定
- MySQL 8.0のデフォルトでは6つのモードが有効である
お読みいただきありがとうございました。