MySQL には SQL Mode という設定があり、この内容によって、許容される構文やデータの妥当性チェックのルールが変化する。
この記事では SQL Mode の確認方法や設定方法の他、設定内容によって挙動が変化する例を見ていく。
動作確認は MySQL のバージョン8.0.28で行った。
環境構築
まずは動作確認用の MySQL コンテナを用意し起動する。
% docker run --name test_db -dit -e MYSQL_ROOT_PASSWORD=password mysql:8
以下のコマンドを入力するとパスワードを求められるので、先程MYSQL_ROOT_PASSWORDとして設定したpasswordを入力する。
% docker exec -it test_db mysql -p
確認と設定
現在の SQL Mode の設定内容はsql_modeというシステム変数に保持されている。
グローバルとセッションそれぞれ、SELECT @@GLOBAL.sql_mode;やSELECT @@SESSION.sql_mode;で確認できる。
mysql> SELECT @@GLOBAL.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +-----------------------------------------------------------------------------------------------------------------------+ | @@SESSION.sql_mode | +-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
デフォルトの設定内容はバージョンによって異なる。
設定を更新する方法はいくつかあるが、セッションの場合は例えば、SET SESSION sql_mode = '設定したいモード';で設定できる。
以下は、STRICT_TRANS_TABLESモードを有効にしている様子。
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +---------------------+ | @@SESSION.sql_mode | +---------------------+ | STRICT_TRANS_TABLES | +---------------------+ 1 row in set (0.00 sec)
カンマ区切りで複数の SQL Mode を設定することもできる。
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +----------------------------------+ | @@SESSION.sql_mode | +----------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_DATE | +----------------------------------+ 1 row in set (0.00 sec)
SQL Mode によって挙動が変わる例
冒頭に書いた通り、 SQL Mode の設定内容によってクエリ実行時の挙動が変化する。
一例として、IGNORE_SPACEという SQL Mode の有無によって挙動がどのように変わるのか見てみる。
まずは動作確認用にデータを用意する。
mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.01 sec)
mysql> USE sample_db;
Database changed
mysql> CREATE TABLE users (id INT AUTO_INCREMENT, name TEXT NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO users(name) VALUES('Alice');
Query OK, 1 row affected (0.02 sec)
sample_dbというデータベースを作成し、そのなかにusersというテーブルを作成、そしてそこに 1 件のレコードを作成した。
mysql> SELECT * FROM users; +----+-------+ | id | name | +----+-------+ | 1 | Alice | +----+-------+ 1 row in set (0.00 sec)
まずは SQL Mode が何も設定されていない状態にしておく。
mysql> SET SESSION sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +--------------------+ | @@SESSION.sql_mode | +--------------------+ | | +--------------------+ 1 row in set (0.00 sec)
この状態でまずSELECT COUNT( * ) FROM users;を実行する。
そうすると、レコード数を取得できる。
mysql> SELECT COUNT( * ) FROM users; +------------+ | COUNT( * ) | +------------+ | 1 | +------------+ 1 row in set (0.00 sec)
だがSELECT COUNT ( * ) FROM users;は、エラーになる。
mysql> SELECT COUNT ( * ) FROM users; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* ) FROM users' at line 1
これは、COUNTと(の間にスペースがあることで構文エラーになるためである。
IGNORE_SPACEを有効にして再度SELECT COUNT ( * ) FROM users;を実行してみる。
mysql> SET SESSION sql_mode = 'IGNORE_SPACE'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@SESSION.sql_mode; +--------------------+ | @@SESSION.sql_mode | +--------------------+ | IGNORE_SPACE | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT COUNT ( * ) FROM users; +-------------+ | COUNT ( * ) | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec)
今度はエラーにならない。
これは、IGNORE_SPACEによって関数名と(の間にスペースが入ることが許可されるようになったためである。
このように、同じクエリでも SQL Mode の設定内容によって挙動が変化することがある。
