MySQL道普請便り
第64回 SQLモードについて[その2]
これまで,
SQLモードの設定方法や変更の方法は,
ALLOW_ INVALID_ DATES
実際に存在しない日付を挿入することができます。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_ BACKSLASH_ ESCAPES
この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_ CHAR_ TO_ FULL_ LENGTH
こちらの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_ SPACE
このSQLモードを有効にすると,(
記号の間にスペースを入れられるようになります。そのため,
まとめ
今回は4つのSQLモードについて説明を行いました。これらのモードは,