MySQL道普請便り

第64回　SQLモードについて［その2］

MySQL, MySQLモード, エスケープ文字

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

これまで，第18回　MySQL5.7のデフォルトのSQLモードを確認してみると第60回　SQLモードについて［その1］で，SQLモードについて触れました。今回も引き続きSQLモードについて紹介していきたいと思います。

SQLモードの設定方法や変更の方法は，第60回ですでに説明を行っているので，改めて説明を行いません。

ALLOW_INVALID_DATES

実際に存在しない日付を挿入することができます。「⁠にしむくさむらい」と言うように，2月，4月，6月，9月，11月はそれ以外の月に比べて日付が少ないことが知られています。たとえば2018年2月31日といった日付は存在しません。そういう日付を挿入しようとすると，STRICT_TRANS_TABLESやSTRICT_ALL_TABLESが有効になっている場合は，以下のようにエラーになってしまいます。5.7はデフォルトでSTRICT_TRANS_TABLES有効なため，吊るしの状態で動かそうとするとエラーになります。また，それらのSQLモードが有効でない場合には0000-00-00が入ります。

mysql> create table test (d date);

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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO test (d) VALUE ('2018-01-01');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test (d) VALUE ('2018-02-31');
ERROR 1292 (22007): Incorrect date value: '2018-02-31' for column 'd' at row 1

このような存在しない日付を保存したい場合などに，このSQLモードが使えます。このモードを使用すると，月が1～12の間であることと，日が1～31までであることだけを検査します。注意する点としては，月は1～12までしか無いため，2018年14月1日のような日付を挿入しようとすると0000-00-00が挿入されます。前述の通りSTRICT_TRANS_TABLESやSTRICT_ALL_TABLESが有効になっている場合は，上記の様にエラーになってしまいます。

続けてSQLモードにALLOW_INVALID_DATESだけを指定した場合で試してみます。

mysql> delete from test;
mysql> SET SESSION sql_mode='ALLOW_INVALID_DATES';

mysql> INSERT INTO test (d) VALUE ('2018-14-01');
mysql> INSERT INTO test (d) VALUE ('2018-02-31');

mysql> SELECT * FROM test;
+------------+
| d          |
+------------+
| 0000-00-00 |
| 2018-02-31 |
+------------+
2 rows in set (0.00 sec)

存在しない日付を利用したいということはあまり無いとは思うのですが，たとえば，サーバのバリデーションや機能が正しく動いているかチェックするためのテスト用の値をMySQL上で保存したい場合などに使うことができます。

注意することとしては，DATE型とDATETIME型のカラムには適用されますが，TIMESTAMP型のカラムには適用されないので注意しましょう。

NO_BACKSLASH_ESCAPES

このSQLモードを使うと，名前のとおりに\記号が通常の文字と同様に扱われます。

どういうことかといいますと，MySQLでは\記号はC言語のようにエスケープに使用されています。たとえば，\nのように改行を表す文字や，'でテキストを囲んだ場合に文中で'文字を使いたい場合などに，制御文字ではなくて文字ですと表明するために使います。このSQLモードが有効でない場合，以下のように評価されていることがわかります。

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

mysql> SELECT '\a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)

INSERTした時の値とSELECTをした時の値が違っていることがわかります。

続いてNO_BACKSLASH_ESCAPESを設定した状態で実行をしてみます。

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

mysql> SELECT '\a';
+----+
| \a |
+----+
| \a |
+----+
1 row in set (0.00 sec)

以上のようになります。このモードは他のRDBMSから移行してきた場合に，移行元となるRDBMSのエスケープで\記号を使っていなかった場合などで使われることがあります。SQL中で\記号がそのまま文字として使われていて，しかもプログラムの各所にハードコードされてしまっていて箇所が特定できないような場合に，SQLを最終的には書き換える事になると思いますが，一時的に問題となるクエリに対してそのままにしておきたい場合があります。そういったクエリに対して\をこのSQLモードを有効にしておくと，ただの文字として一時的に利用できます。

このモードが非常に有効な例としては，LIKE検索などクエリ中で%記号を文字として扱いたい場合等です。また，このモードを有効にしたときにはLIKE検索中でESCAPSE句にエスケープしたい文字を1文字設定しないとエラーになるのにも注意してください。

PAD_CHAR_TO_FULL_LENGTH

こちらのSQLモードを有効にすると，CHAR型の場合第41回　MySQLのCHAR型とVARCHAR型との違いを理解するで説明したように，SELECTを実行した場合に末尾についていた空白文字列は削除されます。しかし，このSQLモードを利用するとCHAR型で保存されている文字列がそのまま取得できます。以下のように試してみましょう。

mysql> create table char_test (c char(10));
mysql> INSERT INTO char_test (c) VALUES ('a');
mysql> SET SESSION sql_mode='';

mysql> SELECT *,length(c) FROM char_test;
+------+-----------+
| c    | length(c) |
+------+-----------+
| a    |         1 |
+------+-----------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode='PAD_CHAR_TO_FULL_LENGTH';
mysql> SELECT *,length(c) FROM char_test;
+------------+-----------+
| c          | length(c) |
+------------+-----------+
| a          |        10 |
+------------+-----------+
1 row in set (0.00 sec)

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

このようにスペースをそのまま取得できるのですが，元の文字列の末尾に何個スペースが入っていたかについては知ることはできません。このモードが有効な場合としては，プログラムのメモリー管理で固定長を抑えてしまったほうが楽な場合などになるかと思いますが，あまり利用することは無いのではないかと思われます。

IGNORE_SPACE

このSQLモードを有効にすると，関数と(記号の間にスペースを入れられるようになります。そのため，逆に関数と同じ名前のテーブルを作ろうとしたときなどに，それがテーブル名なのか，カラム名なのかはたまた関数なのかがMySQLにはわからなくなってしまいます。この問題を避けるためには関数と同じ名前のテーブルなどを避ける命名規則にするか，もしくはバッククォート記号で囲って引用符とするかどちらかの対応をする必要があります。基本的に，予約語と同名のテーブル名やカラム名を設定するのは筋が良くない気もしますが，やむを得ない場合には引用符で囲うようにしましょう。

まとめ

今回は4つのSQLモードについて説明を行いました。これらのモードは，最近のMySQLだけを利用している場合にはあまり考えることが無いと思いますが，そういった設定ができるということを知っているのと知らないのとでは大きな違いがあります。一度試してみてはいかがでしょうか？

MySQL, MySQLモード, エスケープ文字

著者プロフィール

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

株式会社オプティム 技術統括本部のエンジニア。最近はミドルウェア・インフラ周りのことも少しずつ学習しています。趣味は将棋。好きな戦法は四間飛車。

Twitter：@kk2170