はじめに
MySQL5.7.8以降のsql_modeのデフォルト値7つが、設定の有無によってどうのように挙動が異なるのか調査してみました。
まず、MySQL5.7.8以降のデフォルト値は下記のようになっています。
- ONLY_FULL_GROUP_BY
- STRICT_TRANS_TABLES
- NO_ZERO_IN_DATE
- NO_ZERO_DATE
- ERROR_FOR_DIVISION_BY_ZERO
- NO_AUTO_CREATE_USER
- NO_ENGINE_SUBSTITUTION
設定されているsql_modeは下記のように調べられます。
mysql> SELECT @@GLOBAL.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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
設定は下記のコマンドで一時的にsql_modeを変更します。
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
↓ 検証に使用するサンプルとして用意したテーブルはこちらです。
mysql> show columns from sample;
+------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(6) | YES | | NULL | |
| point | int | NO | MUL | NULL | |
| type | smallint | NO | | NULL | |
| flag | tinyint(1) | NO | | NULL | |
| created_at | datetime | YES | | NULL | |
| deleted_at | date | YES | | NULL | |
+------------+------------+------+-----+---------+----------------+
では動作検証していきます！
動作検証
★ONLY_FULL_GROUP_BY
GROUP BY句で指定されていない非集約カラムを、選択リスト、HAVING条件、またはORDERリストが参照するクエリを拒否します。
設定あり🙆♂️
mysql> select type, flag, count(point) from sample;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'index_test.sample.type'; this is incompatible with sql_mode=only_full_group_by
本来であれば集約するときにGROUP BY pointといった書き方をしないとダメですよね。
設定なし🙅♂️
mysql> select type, flag, count(point) from sample;
+------+------+--------------+
| type | flag | count(point) |
+------+------+--------------+
| 1 | 0 | 100000 |
+------+------+--------------+
1 row in set (0.03 sec)
設定を外すとcount()を使えるようになったのですが、typeは5種類、flagは0or1というようなデータなのに、よく分からない集約をされました。
★STRICT_TRANS_TABLES
この設定は厳密モードとも呼ばれInsertやUpdateをした値がテーブルの指定に従っていない場合に、SQLの実行を中止します。
設定あり🙆♂️
mysql> INSERT INTO sample (name, point, type, flag, created_at) VALUES ("hogehuga", 1, 1, 1, NOW());
ERROR 1406 (22001): Data too long for column 'name' at row 1
nameカラムは6文字以下の長さであると宣言されているため、8文字であるhogehugaはそのままでは登録できません。
設定なし🙅♂️
mysql> INSERT INTO sample (name, point, type, flag, created_at) VALUES ("hogehuga", 1, 1, 1, NOW());
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> SELECT * FROM sample ORDER BY id DESC LIMIT 3;
+--------+-------------+-------+------+------+---------------------+
| id | name | point | type | flag | created_at |
+--------+-------------+-------+------+------+---------------------+
| 100001 | hogehu（これ)| 1 | 1 | 1 | 2022-08-20 23:35:32 |
| 100000 | llyxx | 9 | 1 | 1 | 2022-08-08 12:19:38 |
| 99999 | dfrwz | 96 | 4 | 0 | 2022-08-08 12:19:38 |
+--------+-------------+-------+------+------+---------------------+
3 rows in set (0.00 sec)
設定を外すと文字が6桁に切られて保存されていることが分かります。
★NO_ZERO_DATE
これは '0000-00-00'という日付の暗黙的デフォルト値を挿入された時の挙動を決めるための設定です。
- この設定が無効な場合は、日付の暗黙的デフォルト値である
0000-00-00を挿入できます。 - この設定だけ有効な場合は警告を出して、
0000-00-00を挿入します。
設定あり🙆♂️
mysql> INSERT INTO sample (name, point, type, flag, created_at, deleted_at) VALUES ("huga", 1, 1, 1, NOW(), '0000-00-00');
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> SELECT * FROM sample ORDER BY id DESC LIMIT 3;
+--------+--------+-------+------+------+---------------------+------------+
| id | name | point | type | flag | created_at | deleted_at |
+--------+--------+-------+------+------+---------------------+------------+
| 100002 | huga | 1 | 1 | 1 | 2022-08-21 11:44:46 | 0000-00-00 |
| 100001 | hogehu | 1 | 1 | 1 | 2022-08-20 23:35:32 | NULL |
| 100000 | llyxx | 9 | 1 | 1 | 2022-08-08 12:19:38 | NULL |
+--------+--------+-------+------+------+---------------------+------------+
3 rows in set (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | 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 'warning' at line 1 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
確かに0000-00-00のデータは登録されていますが、警告が出ていますね。
設定なし🙅♂️
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql> INSERT INTO sample (name, point, type, flag, created_at, deleted_at) VALUES ("huga2", 1, 1, 1, NOW(), '0000-00-00');
Query OK, 1 row affected (0.02 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> SELECT * FROM sample ORDER BY id DESC LIMIT 3;
+--------+--------+-------+------+------+---------------------+------------+
| id | name | point | type | flag | created_at | deleted_at |
+--------+--------+-------+------+------+---------------------+------------+
| 100003 | huga2 | 1 | 1 | 1 | 2022-08-21 12:36:12 | 0000-00-00 |
| 100002 | huga | 1 | 1 | 1 | 2022-08-21 11:44:46 | 0000-00-00 |
| 100001 | hogehu | 1 | 1 | 1 | 2022-08-20 23:35:32 | NULL |
+--------+--------+-------+------+------+---------------------+------------+
3 rows in set (0.00 sec)
こちらもデータは登録されていますが、警告は出なくなりました。
★NO_ZERO_IN_DATE
2022-00-01, 2022-01-00のような月または日の部分が0である日付を制御します。(0000-00-00は上記にあるNO_ZERO_DATEが適用されます。)
設定がある場合は、0を含む日付は許可されず、エラーになります。
設定あり🙆♂️
mysql> INSERT INTO sample (name, point, type, flag, created_at, deleted_at) VALUES ("huga3", 1, 1, 1, NOW(), '2022-00-01');
ERROR 1292 (22007): Incorrect date value: '2022-00-01' for column 'deleted_at' at row 1
2022-00-01を登録しようとしましたが、正しくない日付としてエラーが発生しました。
設定なし🙅♂️
mysql> INSERT INTO sample (name, point, type, flag, created_at, deleted_at) VALUES ("huga3", 1, 1, 1, NOW(), '2022-00-01');
Query OK, 1 row affected (0.02 sec)
mysql> select * from sample order by id desc limit 3;
+--------+-------+-------+------+------+---------------------+------------------+
| id | name | point | type | flag | created_at | deleted_at |
+--------+-------+-------+------+------+---------------------+------------------+
| 100005 | huga3 | 1 | 1 | 1 | 2022-08-21 13:04:19 | 2022-00-01（これ) |
| 100004 | huga2 | 1 | 1 | 1 | 2022-08-21 12:44:09 | 2022-08-01 |
| 100003 | huga2 | 1 | 1 | 1 | 2022-08-21 12:36:12 | 0000-00-00 |
+--------+-------+-------+------+------+---------------------+------------------+
2022-00-01でもエラーは発生せずに登録できました。
★ERROR_FOR_DIVISION_BY_ZERO
この設定は 0除算(MOD(N, 0)や1/0等)を含むINSERTやUPDATEがあった場合の動作の設定になります。設定が入っていると0で除計するとNULLが入って、かつ警告が表示されます。
設定あり🙆♂️
mysql> SELECT id, name, point, point DIV 5, point DIV 0 from sample ORDER BY point DESC LIMIT 3;
+-------+-------+-------+-------------+-------------+
| id | name | point | point DIV 5 | point DIV 0 |
+-------+-------+-------+-------------+-------------+
| 99701 | aqdpt | 100 | 20 | NULL |
| 99115 | kqnmw | 100 | 20 | NULL |
| 97721 | kpxcr | 100 | 20 | NULL |
+-------+-------+-------+-------------+-------------+
3 rows in set, 3 warnings (0.01 sec)
mysql> show warnings;
+---------+------+---------------+
| Level | Code | Message |
+---------+------+---------------+
| Warning | 1365 | Division by 0 |
| Warning | 1365 | Division by 0 |
| Warning | 1365 | Division by 0 |
+---------+------+---------------+
3 rows in set (0.00 sec)
0を除算したことでpoint DIV 0にはNULLが算出され、
それに対して警告のメッセージが発生していることが分かります。
設定なし🙅♂️
mysql> SET SESSION sql_mode ='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT id, name, point, point DIV 5, point DIV 0 from sample ORDER BY point DESC LIMIT 3;
+-------+-------+-------+-------------+-------------+
| id | name | point | point DIV 5 | point DIV 0 |
+-------+-------+-------+-------------+-------------+
| 99701 | aqdpt | 100 | 20 | NULL |
| 99115 | kqnmw | 100 | 20 | NULL |
| 97721 | kpxcr | 100 | 20 | NULL |
+-------+-------+-------+-------------+-------------+
3 rows in set (0.00 sec)
mysql> show warnings;
Empty set (0.00 sec)
無効にすると警告は出なくなりました。
★NO_AUTO_CREATE_USER
権限の付与とユーザーの作成を同時に行う際の設定です。
有効にすると、存在しないユーザーに対して権限を付加しようとした際に、自動的にユーザーが作成されません。
下記ユーザー一覧に、hogeuserを追加とGRANT権限を付与を同時に行なってみます。
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| localuser | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
6 rows in set (0.04 sec)
設定あり🙆♂️
mysql> GRANT ALL on . to hogeuser@localhost;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
ユーザー作成とGRANT権限付与が同時にできないことが分かります。
設定なし🙅♂️
mysql> GRANT ALL on *.* to hogeuser@localhost;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
あれ、、NO_AUTO_CREATE_USERの設定外したけど、ユーザー作成とGRANT権限付与が同時にできない...
調べてみるとMySQL8以降はsql_modeに関係なく、同時にできない仕様に変わっているようです。(今回の検証環境はMysql8.0でした)
GRANT cannot mix granting both privileges and roles in the same statement. A given GRANT statement must grant either privileges or roles.
★NO_ENGINE_SUBSTITUTION
NO_ENGINE_SUBSTITUTIONを無効にすると、CREATE TABLE については、目的のエンジンが利用できない場合にデフォルトエンジンが使用されて警告が発生します。ALTER TABLE では、警告が発生してテーブルは変更されません。
NO_ENGINE_SUBSTITUTIONを有効にすると、目的のエンジンが利用できない場合にエラーが発生し、テーブルは作成または変更されません。
対応しているエンジンは下記のように確認できます。
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
おわりに
今回はMySQL5.7以降にデフォルト値となっているsql_modeのみを調べました。他にも沢山あるらしいので必要になった時に調べて学んでいきます。