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 の設定内容によって挙動が変化することがある。
参考資料