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との絆を一層深める一助に本エントリーがなれば幸いである。

◆MySQL関連エントリー
・これだけは覚えておきたい!!MySQL の６つの自動変換(本エントリの元ネタ)
・勝手に補足:これだけは覚えておきたい!!MySQL の６つの自動変換(木村デービー木村氏の補足エントリー）

・MySQL 5.5 の Semi-sync. を Windows 上で試してみた
・MySQL に関するつぶやきを一挙に見られる『MySweet』を公開しました
・早速MySQLメインサイトからダウンロードページへのリンクがなくなった
・知らなかった。mysql の -o オプション
・６月31日はいったい何日なのか。

.

*1:セッションではなくグローバルな値を見るには SHOW GLOBAL VARIABLES 文を使う