ぎょーぼのぶろぐ

IT系の話を書いていくブログです。今はRubyの勉強中。

【MySQL】サーバーのSQLモードを設定する

MySQL sql_mode

前置き

前の記事で、MySQL のテーブルで、日付型の列に、日付Zero値が入ってしまう話を書きましたが、その理由の一つが、MySQL のサーバー設定（SQLモード）が、デフォルトで日付Zero値を許容する設定になっているためです。

他のデータベースのように、日付Zero値を許さないようにするには、SQLモードを適切に設定する必要があります。

Qiita のこちらの記事を全面的に参考にさせてもらっています。
MySQLのSQLモードをstrictモードで設定する。 - Qiita

デフォルトのSQLモード（バージョン8の場合）

MySQL バージョン 8 の SQLモードのデフォルト値は、以下のようになっています。

mysql> SELECT @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

先の Qiita の記事（バージョン 5.7.8）と、デフォルト値が異なっています。STRICT_TRANS_TABLES しか入ってない・・・

SQLモードを変更する

Windows 環境の場合、大きくは以下の手順で SQLモードの変更ができます。

  • C:\ProgramData\MySQL\MySQL Server 8.0 フォルダーにある、my.ini で、sql_mode の値を修正する。
  • 「サービス」を開き、MySQL80 サービスを再起動する。

Windows環境の場合、C:\ProgramData\フォルダー配下のファイルは、管理者権限が必要なので、修正するのに工夫が必要です。

my.ini の内容を修正する

my.ini の内容を変更するのですが、管理者権限でテキストエディターを開く必要があります。普通に my.ini をダブルクリックで開いても、管理者権限がないので、上書き保存ができません。

少し面倒な手順になりますが・・・

  1. メモ帳を管理者権限で開く。
    方法はいろいろありますが「メモ帳」のショートカットを右クリックして、「管理者として実行」するか、コマンドプロンプトを管理者権限で実行して、notepad で起動する、のが簡単な方法かと思います。

  2. メモ帳のメニューから「ファイル - 開く」 を選択して、C:\ProgramData\MySQL\MySQL Server 8.0\my.ini を選択して開く。

としてください。

my.ini を開いたら、sql-mode の値を設定している箇所を探して、値を修正します。

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
sql-mode="TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY"

蛇足ですが、# でコメントアウトできるので、元の行をコピーしてコメントアウトし、残しておきましょう。 ここでは、先の Qiita の記事の通り、TRADITIONAL、NO_AUTO_VALUE_ON_ZERO、ONLY_FULL_GROUP_BY を設定するようにします。

上書き保存したら、「サービス」を開き、MySQL80 サービスを再起動してください。（ここは分かると思うので説明しません・・・）

変更した結果・・・

MySQL にログインし直して、SQLモードがどうなってるか見てみます。

mysql> select @@global.sql_mode \G
*************************** 1. row ***************************
@@global.sql_mode: ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

これも、微妙に変わってて、NO_AUTO_CREATE_USER がなくなってます。

この設定で、日付型の列に、Zero値を入れようとしてみます。

mysql> INSERT INTO datetest(id, date) VALUES (100, '0000-00-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00' for column 'date' at row 1
mysql> INSERT INTO datetest(id, date) VALUES (100, '0000-01-00');
ERROR 1292 (22007): Incorrect datetime value: '0000-01-00' for column 'date' at row 1
mysql> INSERT INTO datetest(id, date) VALUES (100, '0000-01-01');
Query OK, 1 row affected (0.01 sec)

月や日がZeroの日付は、Incorrect datetime value のエラーでINSERTが失敗しています。最後の年がZeroの日付は、日付としては正しいので、INSERTが成功しています。

腑に落ちないこと

MySQL :: MySQL 8.0 Reference Manual :: 5.1.11 Server SQL Modes を読んでいると、腑に落ちない点がいくつか出てきます。。。

デフォルトの sql_mode に STRICT_TRANS_TABLES が入っていること

MySQLリファレンスによると、「STRICT_TRANS_TABLES もしくは STRICT_ALL_TABLES のいずれかが有効であれば、厳密モードである」という記載があるのですが、だとすると最初から「厳密モード」で動いていた、ということになります。

しかし、MySQL 5.7 以降では、NO_ZERO_IN_DATE、NO_ZERO_DATE のSQLモードは非推奨で、厳密モードに含まれる（厳密モードになっていれば有効、という意味だと思います）という記載もあります。

であれば、最初から「厳密モード」だったなら、Zeroを含む日付は入れられないハズなのですが、、、実際は入れられました。InnoDB で生成しているので、実は確認に使ってたテーブルが非トランザクションテーブルだった、なんてこともないハズ・・・

TRADITIONAL の sql_mode に NO_ZERO_IN_DATE、NO_ZERO_DATE が入っていること

TRADITONAL sql_mode を設定すると、SQLモードに NO_ZERO_IN_DATE、NO_ZERO_DATE が普通に入ってきます。厳密モードに含まれるなら、なんでここに出てくるのか、とても疑問です。

実際には廃止になっていなくて、SQL 5.6 と変わってないんじゃ？と疑いたくなる感じです・・・

ONLY_FULL_GROUP_BY の意味

話が日付Zero値の話とずれますが。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.3 MySQL での GROUP BY の処理 に詳細が書かれているのですが、

ONLY_FULL_GROUP_BY SQLモードは、有効でない場合、「GROUP BY句に含まれない列項目を、選択リストや HAVING句、ORDER BY 句で選択することができる」というものです。

仕様の特徴としては、

  • GROUP BY句で指定していない列を選択リストに入れた場合でもエラーにならない。ただし、その列の値は不定値となる。
  • HAVING句の中で、選択リストでつけた「別名」を使用できる。
  • MAX、MIN と組み合わせると、そのグループの最大、最小となる行の項目を取得できる。
  • GROUP BY句の中で、式（SUMとか）を使うことができる。

等々、なのですが・・・どれもけっこう一般的じゃない状況な気がしています。正直、これがデフォルトのSQLモードで有効になっていない理由が良く分かりません。

何より、できるだけ標準SQL に準じてアプリを作っておいた方が、万が一 DB が変わったときにも対応がしやすいので、MySQL の sql_mode はちゃんと確認して、修正しておいた方が良さそうです。