MySQL の SQL Mode について

MySQL には SQL Mode という設定があり、この内容によって、許容される構文やデータの妥当性チェックのルールが変化する。
この記事では SQL Mode の確認方法や設定方法の他、設定内容によって挙動が変化する例を見ていく。
動作確認は MySQL のバージョン8.0.28で行った。

環境構築

まずは動作確認用の MySQL コンテナを用意し起動する。

% docker run --name test_db -dit -e MYSQL_ROOT_PASSWORD=password mysql:8

以下のコマンドを入力するとパスワードを求められるので、先程MYSQL_ROOT_PASSWORDとして設定したpasswordを入力する。

% docker exec -it test_db mysql -p

確認と設定

現在の SQL Mode の設定内容はsql_modeというシステム変数に保持されている。
グローバルとセッションそれぞれ、SELECT @@GLOBAL.sql_mode;やSELECT @@SESSION.sql_mode;で確認できる。

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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.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_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

デフォルトの設定内容はバージョンによって異なる。

設定を更新する方法はいくつかあるが、セッションの場合は例えば、SET SESSION sql_mode = '設定したいモード';で設定できる。
以下は、STRICT_TRANS_TABLESモードを有効にしている様子。

mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+---------------------+
| @@SESSION.sql_mode  |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

カンマ区切りで複数の SQL Mode を設定することもできる。

mysql> SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+----------------------------------+
| @@SESSION.sql_mode               |
+----------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE |
+----------------------------------+
1 row in set (0.00 sec)

SQL Mode によって挙動が変わる例

冒頭に書いた通り、 SQL Mode の設定内容によってクエリ実行時の挙動が変化する。
一例として、IGNORE_SPACEという SQL Mode の有無によって挙動がどのように変わるのか見てみる。

まずは動作確認用にデータを用意する。

mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.01 sec)

mysql> USE sample_db;
Database changed
mysql> CREATE TABLE users (id INT AUTO_INCREMENT, name TEXT NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO users(name) VALUES('Alice');
Query OK, 1 row affected (0.02 sec)

sample_dbというデータベースを作成し、そのなかにusersというテーブルを作成、そしてそこに 1 件のレコードを作成した。

mysql> SELECT * FROM users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
+----+-------+
1 row in set (0.00 sec)

まずは SQL Mode が何も設定されていない状態にしておく。

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

この状態でまずSELECT COUNT( * ) FROM users;を実行する。
そうすると、レコード数を取得できる。

mysql> SELECT COUNT( * ) FROM users;
+------------+
| COUNT( * ) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

だがSELECT COUNT ( * ) FROM users;は、エラーになる。

mysql> SELECT COUNT ( * ) FROM users;
ERROR 1064 (42000): 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 '* ) FROM users' at line 1

これは、COUNTと(の間にスペースがあることで構文エラーになるためである。

IGNORE_SPACEを有効にして再度SELECT COUNT ( * ) FROM users;を実行してみる。

mysql> SET SESSION sql_mode = 'IGNORE_SPACE';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
| IGNORE_SPACE       |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT ( * ) FROM users;
+-------------+
| COUNT ( * ) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

今度はエラーにならない。
これは、IGNORE_SPACEによって関数名と(の間にスペースが入ることが許可されるようになったためである。

このように、同じクエリでも SQL Mode の設定内容によって挙動が変化することがある。

参考資料

• MySQL :: MySQL 8.0 リファレンスマニュアル :: 5.1.11 サーバー SQL モード
• ルーク！MySQLではkamipo TRADITIONALを使え！ | おそらくはそれさえも平凡な日々

『UNIXという考え方―その設計思想と哲学』を読んだ

UNIX やそのツールはどのような考えに基づいて作られているのか解説した本。
UNIX が開発されていくなかで培われていった文化や考え方について書かれている。

www.ohmsha.co.jp

UNIX が具体的にどのように動いているのかではなく、 UNIX はなぜそのように動いているのか、ということが主題。
そのため、 UNIX に限らずソフトウェア開発全般に適用できるような内容になっている。ソフトウェアだけでなく「ものを作る」こと全般に応用できる内容も多いかもしれない。
私も、現時点では UNIX そのものに対する熱意や探究心はあまりないので、 UNIX について知るためではなく開発の参考になる考え方がないかと思って読んだ。

9 つの定理が紹介されているのだが、まず思ったのは、「言うは易く行うは難し」という感じの定理ばかりだなということ。
例えばシンプルに保て、小ささを維持しろ、というのは単純な定理だし、ほとんどの開発者が賛同するはず。
しかし実践するのが難しい。気が付けば余計なことをしているし、いつの間にかなぜか肥大化し複雑になっている。

目の前のタスクに追われているうちに定理を忘れがち、という側面もあるだろうが、単純な定理であっても実践するのは難しいという話なのかもしれない。
プログラムや関数は、たったひとつのやるべきことに専念するべきなのだろうが、その「やるべきこと」が何かを決めるのがまず難しい。
やりたいことを適切に抽象化して「やるべき」ことを導き出すのは、とてつもなく難しいことのように思える。

一つのことをうまくやるようにプログラムを作れないのであれば、恐らく問題をまだ完全には理解していないのだろう

と本書にも書かれているが、まさにそういう話だと思う。適切に設計するためにはまず、解きたい課題を正しく理解しないといけない。

アジャイル開発との共通性も印象に残った。
「できるだけ早く試作する」という定理は、何を作るべきか事前に判断するのは難しい、だからこそ実際に動くものを早く作って顧客からフィードバックをもらえ、試行錯誤を繰り返せ、という内容であり、アジャイル開発そのものだった。
そして本書の最後に、 UNIX の考え方とは変化し続ける世界で未来に向かっていくアプローチだ、という話が出てくるが、これも変化を受け入れるという意味でアジャイル開発の考え方と近いと思う。

それ以外だと、データを作るのはコンピュータではなく人間である、という話が面白くて印象に残った。
ワードプロセッサは自力では何も書けず、書くべき内容は人間の頭の中から生まれる、という話で、確かにという感じだった。
この話の少し前に書かれていた「コンピュータを使って積極的に処理の自動化を行うべきだ、処理に人間を介在させると人間の処理能力がボトルネックになってしまう、それではせっかくのコンピュータの能力を活かせない」という内容と合わせて考えると、コンピュータが得意なことはコンピュータに任せ、人間は人間にしか出来ないことに注力すべき、という話なのかもしれない。
近年はコンピュータが文章やイラストを自動生成してくれるが、あれもあくまでも人間がこれまで積み上げてきたデータを利用して生み出しているだけ、とも言えるかもしれない。
それでも構わない、むしろそのほうが望ましいというケースも当然あるだろうから、そういう分野ではコンピュータの利用が進むのだろうし、一方で人間でなければ生み出せないデータや人間が生み出すことに意味のあるデータも存在するはずなので、人間はそういったデータの生成に注力するようになっていくのだろう。