SQLモードを知らなくて困りかけたので深堀りしてみた

とある日のこと。
いつものようになんちゃってエンジニアをしていた私は出来上がったDB設計書のレビューをしてもらいに行きました。
(記事にしようと思い立ったけど書くのをずっと忘れていたので1年以上前ですね。。)

-- なんちゃってのイメージ(DBはMySQLです)
CREATE TABLE sample(
  id INT NOT NULL AUTO_INCREMENT,
  hoge_id INT NOT NULL, /* 指摘もらったところ */
  hoge_txt VARCHAR(10),
  PRIMARY KEY(id)
);

私「こんな感じの仕様でどうでしょう！」
上司「ここのhoge_id、デフォルト値0で埋めて」
私「(NOT NULL制約入れてるし、デフォルト値0入れちゃうと逆に問題じゃね)分かりました！」

と言われたので、自席にてこんなSQLを投入しました。

INSERT INTO sample(id,hoge_txt) VALUES(1,'test dayo');

hoge_idは
・NOT NULL制約がある
・値指定してない
→ INSERT時にエラーとなるだろう
と予測していました。

ところが結果、INSERTされちゃったんですよね。。。
指摘なかったらそもそもこの事象を知ることができなくて焦ったと思います。
でも、なんでこんな挙動になっているんだろうと疑問に思ったため深堀りした内容になります。

SQLモード

何気無しに使っているSQLですが、実は裏側で様々な構文チェックをしてくれています。
(今回それを初めて知りました)

「SQLモード」と呼ばれるこのチェック機構は実に色々な設定が可能で、
DBに不正な値が入らないように設定してくれているわけです。

試しにmysqlのDockerイメージをpullしてきてデフォルトで入っているSQLモードを確認してみましょう。
執筆当時でMySQLのバージョンは9.1.0 です。

# Docker-hubからMySQLのイメージをインストールする
docker pull mysql

# インストールしたイメージから、コンテナを起動･作成する
# MYSQL_ROOT_PASSWORDにログインする際のパスワードを設定する
docker run -it --rm --name test-mysql -e MYSQL_ROOT_PASSWORD=mysql -d mysql:latest
docker exec -it test-mysql bash -p

# MySQLのコンテナにログインする
mysql -u root -p -h 127.0.0.1

-- 今後の確認用のDBを作成しておいてスイッチしておく
create database my_test;
use my_test;

-- 現在のSQLモードを確認する
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)

以下の6つのモードが設定されていることが分かるかと思います。

• ONLY_FULL_GROUP_BY
• STRICT_TRANS_TABLES
• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• ERROR_FOR_DIVISION_BY_ZERO
• NO_ENGINE_SUBSTITUTION

で、確認したところ今回仕様を作成したDBではこのSQLモードが設定されてませんでした。
いつもはついているから気にしなかったけど、今回仕様を策定したDBだと設定がされていなかったため冒頭のような指摘が飛んできたんだなぁと納得しました。
(まぁ、なんでSQLモード設定していないのかは時代背景がありそうなのでこの際置いとくとしましょう。。)

デフォルトでついている各モードに関して

せっかくなのでデフォルトでついているモードについてもう少し深堀りしてみます。
バージョン古いですが、以下のドキュメントを参照してます。
https://dev.mysql.com/doc/refman/8.0/ja/sql-mode.html

ONLY_FULL_GROUP_BY

リファレンスによると以下のような説明です。

選択リスト、HAVING 条件または ORDER BY リストが、GROUP BY 句で指定されておらず、機能的に GROUP BY カラムに依存しない (一意に決定される) 非集計カラムを参照するクエリーを拒否します。

GROUP BY句以外に書いていない内容をSELECT,HAVING,ORDER BYに書くとエラーとするイメージですね。
例えば以下のような感じ。

-- テスト用のテーブル
CREATE TABLE student(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10),
  sex TINYINT,
  PRIMARY KEY(id)
);


-- 一時的にSQLモードを空にする。
set @@SESSION.sql_mode='';

-- count(sex)以外はgroup byに記載していない内容なので本来はエラーとしたいが通る。
SELECT id,COUNT(sex),NAME FROM student group by sex;

-- ONLY_FULL_GROUP_BYを設定する
set @@SESSION.sql_mode='ONLY_FULL_GROUP_BY';

-- 再度実行。エラーとなる。
SELECT id,COUNT(sex),NAME FROM student group by sex;

ONLY_FULL_GROUP_BYをつけると以下のようなエラーが出力されます。
this is incompatible with sql_mode=only_full_group_byとONLY_FULL_GROUP_BYに関するエラーなのが分かるようになってますね。

mysql> SELECT id,COUNT(sex),NAME FROM student group by sex;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

STRICT_TRANS_TABLES

こちらもリファレンスより。

トランザクションストレージエンジンに対して、および可能な場合は非トランザクションストレージエンジンに対して、厳密な SQL モードを有効にします。 詳細は、厳密な SQL モードを参照してください。

詳細がまた長いのですが簡単に話すと以下のような形になります。

• 有効な場合(モードが設定されている場合)
  • 不正なデータを含むクエリはエラーとなる
• 無効な場合(モードが設定されていない場合)
  • 不正なデータは調整された値に置き換えられる

例えばSTRICT_TRANS_TABLESを無効、有効にして値を入れてみましょう。

-- テスト用のテーブル
DROP TABLE student;
CREATE TABLE student(
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(10) NOT NULL, -- 確認するためにnot nullにする
  PRIMARY KEY(id)
);

-- 一時的にSQLモードを空にする
set @@SESSION.sql_mode='';

-- insert(STRICT_TRANS_TABLESが無効な状態)
-- Query OKとなる
insert into student(id) values(1);

-- STRICT_TRANS_TABLESを有効化
set @@SESSION.sql_mode='STRICT_TRANS_TABLES';

-- insert(STRICT_TRANS_TABLESが有効な状態)
-- ERROR 1364 (HY000): Field 'name' doesn't have a default value となる
insert into student(id) values(2);

-- 試しにselectをしてみるとnameは空白で登録されていることが分かる。
select * from student;
+----+------+
| id | name |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)

置き換えられる調整された値はパターンがいくつかあるようですので、
そちらはリファレンスをご覧ください。

NO_ZERO_IN_DATE

リファレンスより

年の部分は非ゼロであるが月または日の部分が 0 である日付をサーバーが許可するかどうかに影響します。 (このモードは '2010-00-01' や '2010-01-00' などの日付に影響しますが、'0000-00-00' には影響しません。 サーバーが '0000-00-00' を許可するかどうかを制御するには、NO_ZERO_DATE モードを使用してください。) NO_ZERO_IN_DATE の影響は、厳密 SQL モードが有効かどうかにも依存します。

厳密SQLモードというのは「STRICT_TRANS_TABLES」のことですね。
もしやるなら厳密モードが適用されていないときに選択するような運用になると思いますが、このモード単体での使用は非推奨らしいです。

NO_ZERO_DATE

NO_ZERO_DATE モードは、サーバーが '0000-00-00' を有効な日付として許可するかどうかに影響します。 この影響は、厳密 SQL モードが有効かどうかにも依存します。

これも同じように非推奨とのこと。将来的にも消される可能性があるとか。

ERROR_FOR_DIVISION_BY_ZERO

ERROR_FOR_DIVISION_BY_ZERO モードは、MOD(N,0) を含むゼロ除算の処理に影響します。 データ変更操作 (INSERT、UPDATE) の場合、この効果は厳密 SQL モードが有効であるかどうかにもよります。

MySQLでは0による除算を行われたときにNULLを挿入するとのこと。
このとき、モードが有効なら警告を生成するらしいです。
これも同じように非推奨で将来的に消させる可能性があるとのこと。

NO_ENGINE_SUBSTITUTION

CREATE TABLE または ALTER TABLE などのステートメントが無効またはコンパイルされていないストレージエンジンを指定したとき、デフォルトのストレージエンジンの自動置換を制御します。

まず前提のストレージエンジンに触れておきます。
(といってもストレージエンジンというものをあまり気にしていないので詳しくないのですが。。)

よくMySQLにてcreate tableするときにこんな風なサンプルが出てくると思います。

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

ENGINE=InnoDB がストレージエンジンを指定する場所になってまして、
何も気にしないでcreate tableするとInnoDBになっているかなと思います。
InnoDB以外にもメモリに補完する「MEMORY」だったり、CSVファイルに保存する「CSV」があったりします。(他にもたくさん。SHOW ENGINESにて確認できます)

NO_ENGINE_SUBSTITUTION モードに話を戻します。
有効な場合 : サポートされていないストレージエンジンを使用するとエラーとなる
無効な場合 : サポートされていないストレージエンジンを使用するとInnoDBに置き換わる

という挙動をするようになります。

まとめ

何気なしに適用されているSQLモードですが、こういった仕組みでinsert時とかのエラーを判断してくれているんだなということを理解できました。
(SQLモードはつけられるならつけたほうがいいよなぁと思いました)