長い間MySQLを使ってアプリケーションやサービスを提供していると、
アプリケーションが動かなくなってしまい、
デモンストレーション環境
この原稿を書いている時点で最新版である5.
また、
MySQL 5.7のデフォルトのSQLモード
それではまず、SELECT @@global.でデータベースに設定されているsql_
$ mysql -uroot 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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+
実行結果を見てみると、
各設定に関してもっと詳しく知りたい場合は、
ONLY_FULL_GROUP_BY
この設定はGROUP BYに関する設定です。もし有効にした場合、
今回の例では、
mysql> SELECT prefecture, COUNT(*) FROM zipcode; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'zipcode.zipcode.prefecture'; this is incompatible with sql_mode=only_full_group_by
SELECTのリストとprefectureがGROUP BY句で指定がされていないため、
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'zipcode.zipcode.old_zipcode' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
しかし、
mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> SELECT prefecture, COUNT(*) FROM zipcode; +------------+----------+ | prefecture | COUNT(*) | +------------+----------+ | 北海道 | 123866 | +------------+----------+ 1 row in set (0.40 sec)
この非集約カラムを設定できる機能は標準SQLでは規定されておらず、
STRICT_TRANS_TABLES
この設定は厳密モードとも呼ばれInsertやUpdateをした値がテーブルの指定に従っていない場合に、
今回例として使用しているzipcodeテーブルのCREATE TABLE文を再掲します。
mysql> CREATE TABLE zipcode.zipcode(
-> code varchar(12) NOT NULL,
-> old_zipcode varchar(5) NOT NULL,
-> zip_code varchar(7) NOT NULL,
-> prefecture_kana varchar(255) NOT NULL,
-> city_kana varchar(255) NOT NULL,
-> town_kana varchar(255) NOT NULL,
-> prefecture varchar(128) NOT NULL,
-> city varchar(128) NOT NULL,
-> town varchar(128) NOT NULL
-> ) DEFAULT CHARACTER SET= utf8mb4;
以上のようにold_
mysql> INSERT INTO zipcode (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town) VALUES ('00000', '123456', '1234567', 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', 'dummy');
ERROR 1406 (22001): Data too long for column 'old_zipcode' at row 1
old_
続いてSTRICT_
mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> INSERT INTO zipcode (code, old_zipcode, zip_code, prefecture_kana, city_kana, town_kana, prefecture, city, town) VALUES ('00000', '123456', '1234567', 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', 'dummy');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'old_zipcode' at row 1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)
以上のようにINSERT文が実行できてしまいました。こちら中身がどうなっているのか？
SELECT code, old_zipcode FROM zipcode WHERE code = '00000'; +-------+-------------+ | code | old_zipcode | +-------+-------------+ | 00000 | 12345 | +-------+-------------+ 1 row in set (0.21 sec)
old_123456から上記の結果のように12345に切り詰められていることがわかります。このように最悪の場合、
NO_ZERO_DATE
この設定は '0000-00-00'という日付の暗黙的デフォルト値を挿入された時の挙動を決めるための設定です。
- この設定が無効な場合は、
日付の暗黙的デフォルト値である '0000-00-00'を挿入できます。 - この設定だけ有効な場合は警告を出し、
'0000-00-00'を挿入します。 - この設定と前述のSTRICT_
TRANS_ TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
NO_ZERO_IN_DATE
この設定は '2000-00-01'や'2000-01-00'の様な日や月に0が入った値を挿入された時の挙動を決めるための設定です。
- この設定が無効な場合は、
日や月が0の場合に暗黙的デフォルト値である '0000-00-00'を挿入します。 - この設定だけが有効である場合には警告を出力し
'0000-00-00'を挿入します。 - この設定と前述のSTRICT_
TRANS_ TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
ERROR_FOR_DIVISION_BY_ZERO
この設定は 0除算(MOD(N, 0)や1/
- この設定が無効な場合はNULLを挿入します。
- この設定だけが有効である場合は警告を出力し、
NULLを挿入します。 - この設定と前述のSTRICT_
TRANS_ TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。
NO_AUTO_CREATE_USER
この設定は管理用のコマンドの設定なのでアプリケーションには直接影響出ることは少ないと思います。この設定が有効になっていると、
mysql> GRANT ALL ON *.* TO test; ERROR 1133 (42000): Can't find any matching row in the user table
この設定を外して実行した場合は、
mysql> SET 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' mysql> GRANT ALL ON *.* TO test ; Query OK, 0 rows affected, 1 warning (0.00 sec)
サーバの構築を行う際などにはご注意ください。
NO_ENGINE_SUBSTITUTION
この設定は、
こちらの設定を無効にすると、
まとめ
今回はMySQL 5.