MySQL道普請便り
第64回 SQLモードについて［その2］
これまで，
SQLモードの設定方法や変更の方法は，
ALLOW_
実際に存在しない日付を挿入することができます。2018年2月31日といった日付は存在しません。そういう日付を挿入しようとすると，STRICT_やSTRICT_が有効になっている場合は，STRICT_有効なため，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
このような存在しない日付を保存したい場合などに，2018年14月1日のような日付を挿入しようとすると0000-00-00が挿入されます。前述の通りSTRICT_やSTRICT_が有効になっている場合は，
続けてSQLモードにALLOW_だけを指定した場合で試してみます。
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)
存在しない日付を利用したいということはあまり無いとは思うのですが，
注意することとしては，
NO_
このSQLモードを使うと，\記号が通常の文字と同様に扱われます。
どういうことかといいますと，\記号はC言語のようにエスケープに使用されています。たとえば，\nのように改行を表す文字や，'でテキストを囲んだ場合に文中で'文字を使いたい場合などに，
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_を設定した状態で実行をしてみます。
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から移行してきた場合に，\記号を使っていなかった場合などで使われることがあります。SQL中で\記号がそのまま文字として使われていて，\をこのSQLモードを有効にしておくと，
このモードが非常に有効な例としては，%記号を文字として扱いたい場合等です。また，ESCAPSE句にエスケープしたい文字を1文字設定しないとエラーになるのにも注意してください。
PAD_
こちらのSQLモードを有効にすると，
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_
このSQLモードを有効にすると，(記号の間にスペースを入れられるようになります。そのため，
まとめ
今回は4つのSQLモードについて説明を行いました。これらのモードは，