MySQLの自動変換を丁重にお断りするためのたった１種類の呪文

sakaikの日々雑感。技術やお仕事モード以外は別日記に引っ越しました

2010/03/03

■[mysql]MySQLの自動変換を丁重にお断りするためのたった１種類の呪文

　先日の『これだけは覚えておきたい!!MySQL の６つの自動変換』

http://d.hatena.ne.jp/sakaik/20100225/mysqlautochange

にはたくさんの反響をいただいた。

　時にこちらの意図と違っちゃうこともあるけれどもケナゲに気を使ってくれる MySQL が、これほどに皆さんにも愛されていることが判り、MySQLファンの一人として嬉しい限りである。

　さて、そのエントリの最後に、

　なお、「SQL モード」を指定するとこれらの動作を変更することができる。SQL モードについては気が向いたらいつか紹介してみたい。

　と書いたところ、速攻でキムラデービーの木村明治氏が補足エントリーを書いてくださった。

　　　○キムラデービーブログ

　　　[勝手に補足]これだけは覚えておきたい!!MySQL の６つの自動変換

　　　http://blog.kimuradb.com/?eid=838516

　対応の素早さと内容の的確さ、そしてコンパクトにズバっとまとめる技法にシビれた。私はこのようには書けない。木村氏のフォローに感謝したい。

　木村氏の手法とは異なるが、ここでは私なりの解説を試みたい。

　さて、本エントリの主題である「気配りMySQLちゃんに "それは頑張らなくていいよ" と伝えてあげる方法」（意訳）だ。これには、たった一種類の呪文を知っていればいい（「１個」ではなく「１種類」である点が、ややくすぐったいが）。

　その呪文とは、木村氏も紹介してくれている「SQL モード」だ。SQL モードの呪文を唱えれば MySQL の気配りを自分好みにすることができる。

　木村氏のエントリでは、MySQLの再起動なくすぐにその場で試せる方法として、

mysql>SET sql_mode='TRADITIONAL'

　という記法が紹介されている。色々と値を変えて実験してみる際には便利だが mysqld 再起動時にこの設定は消失してしまう。再起動時にも設定値を保持できるようにするために、ここでは my.cnf ファイルへの記述を推奨したい。

　ではひとつひとつ見ていこう。

１. [数値] 範囲外の数値は頭を押さえつけられる

　これは SQL_MODE に 'STRICT_ALL_TABLES' を指定することで、範囲外の数字をエラーにすることができる。my.cnf（多くの場合 /etc/my.cnf）の [mysqld] セクションに以下の記述を追加して mysqld を再起動しよう。

sql_mode='STRICT_ALL_TABLES'

　この状態で、前回MySQLがとっさの判断(?)で値を小さくしてしまった INSERT 文をお願いしてみる。

mysql> INSERT INTO numtest VALUES (2200000000);
ERROR 1264 (22003): Out of range value for column 'a' at row 1

　sql_mode の呪文のうち、STRICT_ALL_TABLES というのは謂わば「勝手にテキトーな事はしないでね」という意味である。MySQLはこの言いつけをしっかりと守り「それはいくらなんでも範囲を超えていますわ、ご主人さま...」とエラーで教えてくれるのである。なんと従順な子であろうか。

　ちなみに、現在のセッションで使用されている sql_mode は SHOW VARIABLES で確認することができる*1。（あるいは @@sql_mode 変数の値として参照することもできる）

mysql> SHOW VARIABLES LIKE "%sql_mode%";
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| sql_mode      | STRICT_ALL_TABLES |
+---------------+-------------------+

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+

２．[数値] 小数は勝手に丸められる（warningも出ない）

　整数型のカラムに小数を入れようとすると自動変換して整数値として登録されるというのは、MySQLに限らず多くのDBMSで共通の動作のようだ。残念ながら現在のMySQLではこの動作は変更できない。(10/3)のような値も 3.333..... ということで "3" として登録される。

　ただし、(10/0) のようにゼロで割った場合の動作は変更可能だ。MySQL標準の状態ではゼロでの除算はエラーにならず NULL となる（warningも出ない）。これをエラーにするには SQL_MODE に ERROR_FOR_DIVISION_BY_ZERO を指定する。複数のSQL モードを指定する場合はコンマ区切りだ。my.cnfに書く場合は以下のようになる。

sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO'

　これで、ゼロでの割り算もエラーになってくれる。

mysql> INSERT INTO numtest VALUES (10/0);
ERROR 1365 (22012): Division by 0

３．[数値] 数字以外が入っていると分かるところまでを登録してくれる

　これは　１　で指定した STRICT_ALL_TABLES というSQL モードにより、すでに「ちゃんと」やってくれるようになっている。

mysql> INSERT INTO numtest VALUES ("12B345");
ERROR 1265 (01000): Data truncated for column 'a' at row 1

　やはり、無理なものは無理と言ってくれるほうがお互い良い関係を築けそうである。

４．[文字列] 長さを超えると勝手にカットされる

　これも STRICT_ALL_TABLES SQL モードにより、勝手にカットされないようになっている。

mysql> INSERT INTO strtest VALUES ("ABCDEFGHIJKLMN");
ERROR 1406 (22001): Data too long for column 'a' at row 1

５．[文字列] 「文字列連結」のつもりでうっかり + を使うな!!

　木村氏も述べている通りそもそも「+」は文字列を連結するものではない。SQL標準では文字列連結は || だ。MySQLでは標準では || は OR の意味で使用されるため、文字列連結には CONCAT() 関数を使用するのが唯一の方法だったが、これもSQL モードによって動作を他のDBMSと同じになるように変更することができる。PIPES_AS_CONCAT だ。

sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT'

mysql> INSERT INTO strtest VALUES ("ABC"||"123");
Query OK, 1 row affected (0.04 sec)

６．[日付] 年を２ケタで指定する際の変換ルール

　表題の「年を２桁で指定する際」については、前エントリーで紹介したような変換仕様を変更することはできない。繰り返しになるが、年部分は４桁で与える習慣をつけたい。

　日付時刻型に対し、単に "2010" という値を入れようとして 0000-00-00 として登録されてしまっていた事象は、実は SQL_MODE のSTRICT_ALL_TABLESを指定したことですでにエラーを吐くようになっている。

mysql> INSERT INTO dttest VALUES ("2010");
ERROR 1292 (22007): Incorrect datetime value: '2010' for column 'a' at row 1

　一方で、MySQLの日付値に関する広い許容力をある程度制限することは、できる。前回のエントリでも「0000-00-00」という日付値が許されることは紹介したが、驚くことに実はMySQLの標準では年月日どの部分でもゼロが許されている。

mysql> INSERT INTO dttest VALUES ("0000-00-00");
mysql> INSERT INTO dttest VALUES ("2010-00-00");
mysql> INSERT INTO dttest VALUES ("2010-02-00");
mysql> INSERT INTO dttest VALUES ("2010-00-30");

mysql> SELECT * FROM dttest;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
| 2010-02-00 00:00:00 |
| 2010-00-00 00:00:00 |
| 2010-00-30 00:00:00 |
+---------------------+

　これらの日付を許さなくするのが NO_ZERO_DATE(0000-00-00を認めない),NO_ZERO_IN_DATE(年月日どこか１箇所でもゼロがあるのは認めない)である。

これら２つのSQL モードを追加した状態の my.cnf 中のSQL_MODEの指定は以下のようになっている：

sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,
NO_ZERO_DATE,NO_ZERO_IN_DATE'

　この指定により、ゼロを含む日付をきちんとエラーにしてくれるようになった。

mysql> INSERT INTO dttest VALUES ("2010-00-00");
ERROR 1292 (22007): Incorrect datetime value: '2010-00-00' for column 'a' at row 1

今回紹介したSQL モードのまとめ

　今回、以下のSQL モードを紹介した。

STRICT_ALL_TABLES

ERROR_FOR_DIVISION_BY_ZERO

PIPES_AS_CONCAT

NO_ZERO_DATE

NO_ZERO_IN_DATE

　実はこれらを含むいくつかのSQL モードをまとめて、ひとつのSQL モードとして名前が付けられているものがある(この機能に特に定まった名前はないようだが、私は「SQL モードセット」と呼びたい）。

　SQL モードとして (SQL モードセットの) 'TRADITIONAL' を指定すると、以下のSQL モードが指定されたことになる。*印は今回紹介したものだ。

ERROR_FOR_DIVISION_BY_ZERO  (*)
NO_AUTO_CREATE_USER
NO_ZERO_DATE                (*)
NO_ZERO_IN_DATE             (*)
STRICT_ALL_TABLES           (*)
STRICT_TRANS_TABLES

　STRICT_TRANS_TABLE は、STRICT_ALL_TABLES の動作をトランザクショナルなテーブルにのみ適用するものなので、あとは NO_AUTO_CREATE_USER が自分の環境での邪魔にならないことが確認（マニュアルを参照ください）した上で：

sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
PIPES_AS_CONCAT,NO_ZERO_DATE,NO_ZERO_IN_DATE'

　という長いSQL モードの指定は

sql_mode='TRADITIONAL,PIPES_AS_CONCAT'

　という非常に短いモード指定で表せることになる。SQL モードセット、便利である。

　SQL モードセットで使用したものは、内部ではそれぞれのSQL モードに展開されていることが以下の結果からわかる。

mysql> SELECT @@SQL_MODE;

PIPES_AS_CONCAT,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

　このように自分好みに改造できちゃうMySQL。どうだろう、益々あなたのお気に入りになったのではないだろうか。

　SQL モードはここで紹介した以外でも面白いものがたくさんある。改めて紹介できればと思っている。

　MySQLは世界中の重要なシステムの裏側でひっそりと日々、気をつかいながら、しかし堅牢にたいせつなデータを守ってくれている。SQL モードを使ってコミュニケーションし、MySQLとの絆を一層深める一助に本エントリーがなれば幸いである。