MySQL道普請便り

第18回　MySQL5.7のデフォルトのSQLモードを確認してみる

MySQL, SQLモード, 設定

この記事を読むのに必要な時間：およそ 4 分

長い間MySQLを使ってアプリケーションやサービスを提供していると，セキュリティの都合や機能の高性能化によってアップグレードが求められることがあります。アップグレードを行った際にアプリケーションがそのまま動いてくれる場合もあるのですが，SQLモードの設定によって動かなくなってしまうこともあります。

アプリケーションが動かなくなってしまい，仕方がないのでよくわからないけどSQLモードの設定を空にする，なんて事はありませんか？ 今回はそんなSQLモードの確認方法や，デフォルトの設定がどのような意味を持っているのかを紹介していきたいと思います。

デモンストレーション環境

この原稿を書いている時点で最新版である5.7.12を第5回　Dockerで複数バージョンのMySQLを開発環境に用意するで作成した環境で実行して確認していきます。

また，今回使用するデータは第2回　MySQLにはじめてのデータを入れてみるで紹介している郵便番号のテーブルを用いて紹介を行います。

MySQL 5.7のデフォルトのSQLモード

それではまず，現在のSQLモードを確認してみましょう。SELECT @@global.sql_mode;でデータベースに設定されているsql_modeの情報を確認することができます。

$ 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 |
+-------------------------------------------------------------------------------------------------------------------------------------------+

実行結果を見てみると，見やすい表のような形にまとめられています。MySQL5.7では7個のSQLモードがデフォルトで設定されています。以下，各sql_modeの設定について確認してみましょう。

各設定に関してもっと詳しく知りたい場合は，MySQLの公式ドキュメントを参照してください。

ONLY_FULL_GROUP_BY

この設定はGROUP BYに関する設定です。もし有効にした場合，GROUP BY句で名前が指定されていない非集約カラムをSELECT，Having条件，Order条件で指定しているクエリを拒否します。

今回の例では，具体的には以下の様なクエリは拒否されてしまいます。 

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

しかし，SQLモードを変更し，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では規定されておらず，MySQL独自の設定となっています。そのため他のRDBMSで実行しようとすると，エラーになります。5.7からこの設定が有効になったので，この独自拡張を使ってクエリを書いている場合は，アップグレードの時点では一旦無効化した場合でも，クエリの書き換えを行った方が良いと思います。

STRICT_TRANS_TABLES

この設定は厳密モードとも呼ばれInsertやUpdateをした値がテーブルの指定に従っていない場合に，SQLの実行を中止します。例えば，old_zipcodeの文字列が5文字以上であった場合INSERTを中止します。

今回例として使用している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_zipcodeは5文字以下の長さであると宣言されているため，STRICT_TRANS_TABLESが有効になっている場合は以下のようなクエリは実行できません。

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_zipcodeに6文字を設定した結果，old_zipcodeが長すぎるというエラーが発生しました。

続いてSTRICT_TRANS_TABLESを無効にした場合は以下のようになります。

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_zipcodeの値が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/0等)を含むINSERTやUPDATEがあった場合の動作の設定になります。

  • この設定が無効な場合はNULLを挿入します。
  • この設定だけが有効である場合は警告を出力し，NULLを挿入します。
  • この設定と前述のSTRICT_TRANS_TABLESのような厳密モードを指定する設定が有効な場合はエラーとなり挿入がされません。

NO_AUTO_CREATE_USER

この設定は管理用のコマンドの設定なのでアプリケーションには直接影響出ることは少ないと思います。この設定が有効になっていると，以下のようなGRANT文で存在しないユーザに対してGRANTを行った場合にエラーになります。

mysql> GRANT ALL ON *.* TO test;
ERROR 1133 (42000): Can't find any matching row in the user table

この設定を外して実行した場合は，以下のようにGRANTを行うときに自動的にユーザを作成します。

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

この設定は，選択したストレージエンジンがCREATE TABLEやALTER TABLEが無効だった場合や利用できないストレージエンジンだった場合に，エラーを返す設定です。

こちらの設定を無効にすると，利用できないストレージエンジンだった場合はデフォルトに設定されているストレージエンジンが選択されます。

まとめ

今回はMySQL 5.7でデフォルトで適用されているSQLモードを確認しました。もし，アップグレードした際にどうしても動かない場合などは，一時的に設定を無効化してしまうこともあるかもしれません。しかし，どの設定も標準のSQLの仕様に近づけるための設定となっているので，リファクタリングを行ってなるべく追随していけるようにしていきましょう。

MySQL, SQLモード, 設定

著者プロフィール

木村浩一郎（きむらこういちろう）

GMOメディア株式会社 技術推進室所属のWebアプリケーションエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter：@kk2170

コメント

コメントの記入