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

　MySQLはとても気ぃつかい屋さんである。我々が投げる多少あいまいな指示も頑張って解釈し、なんとか文句を言わずに実行してみようと挑戦してみてくれる。
　今日はそんなMySQLがケナゲに解釈してくれる自動変換について紹介しようと思う。この自動変換、ケナゲなMySQLの奥ゆかしさ故、出した指示と異なる動作をされたことに気がつかないことがある。ここで紹介する６つの自動変換をしっかり脳ミソにたたき込んでおけば、無用なトラブルにハマる時間も減るかもしれない。

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

　intやsmallint、bigintなどの数値型には、扱える範囲が決まっている。例えばint型なら最大21億ちょっとだ（unsignedの場合は43億弱）。これより大きい数字を登録するよう指示を出すとMySQLはどうするか。そう、頑張って入れられるところまで入れてくれるのである。「入れられるところまで」といっても制限を超えられるわけではない。ちゃんとした値を入れないで仕事を終えたことにしてしまうのだ。遠慮がちに「1264:Out of range value for column 'col1' at row 1」とつぶやきながら。

　具体例を挙げよう。

mysql> CREATE TABLE numtest (a INT) ENGINE=InnoDB;
mysql> INSERT INTO numtest VALUES (1);
Query OK, 1 row affected (0.06 sec)

　これは正常なケースの実行例だ。1行を登録したと返事が返ってきている。次に INT 型の範囲を超える値を登録依頼してみる。

mysql> INSERT INTO numtest VALUES (2200000000);
Query OK, 1 row affected, 1 warning (0.05 sec)

　OKと返ってきた。たださっきと違うのはその後に「1 warning」とあることだ。これを見落とすと痛いことになる。しっかりMySQLサーバからの返事はチェックするようにしたい。
　ここで返ってくるワーニングの内容はこんな感じ。範囲外だよ、と言っている。

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+

　範囲外ではあるが登録自体はされていることに注意したい。

mysql> SELECT * FROM numtest;
+------------+
| a          |
+------------+
|          1 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

　そう。登録をお願いした2200000000とは違う値として。
お願いしたものと違う値をとりあえず登録して処理をエラーで停めない気配り。それがMySQLである。

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

　次は小数だ。INTのような整数型カラムに小数値を登録するよう依頼するとどうなるか。なんとMySQLは「整数カラムなんだから小数が来るわけない」と気を利かせて、一番近い整数の値として登録してしまうのだ。つまり四捨五入だ。しかも奥ゆかしさ故か一切の文句を言わない（ワーニングを出さない）

　以下が実行例だ。

mysql> INSERT INTO numtest VALUES (0.1);
mysql> INSERT INTO numtest VALUES (12.345);
mysql> INSERT INTO numtest VALUES (24.5);

mysql> SELECT * FROM numtest;
+------+
| a    |
+------+
|    0 | 
|   12 | 
|   25 | 
+------+

　これは値を与える時に開発者が気をつけなければならない。登録をお願いしている立場からすれば「無理しないで言ってくれればいいのに・・・・」であるのだが。。。

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

　数値型のカラムへ、"123AB" や "123AB456" のように数字以外のものが入っている値を登録依頼すると、MySQLは前から見ていって数値としてわかるところまでを登録してくれる。この例では「123」まで見たところで「A」という数字以外の文字に突き当たるので "123" という数字とみなして登録してくれるわけだ。ただし一応ぶつぶつ文句は言ってくる。

mysql> INSERT INTO numtest VALUES ("123B45");
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 |
+---------+------+----------------------------------------+

　値はしっかりと、わかったところまでを登録してくれている。

mysql> SELECT * FROM numtest;
+-------------+
| a           |
+-------------+
|         123 |
+-------------+

　また、先頭から見て1文字も数字を見ないうちに数字以外の文字にあたってしまった場合、さすがのMySQLもギブアップする。・・・かとと思いきやこの場合も頑張ってしまうのである。いやはやケナゲ。もちろんいくら頑張ったところで頑張った結果の値は「ゼロ」である。

mysql> INSERT INTO numtest VALUES ("AB123");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: 'AB123' for column 'a' at row 1 | 
+---------+------+----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM numtest;
+------+
| a    |
+------+
|    0 | 
+------+

　ここまでで紹介した数値に関する３つのワーニング。エラーコードがそれぞれ違ことに気づいただろうか。優秀なエンジニアたるもの、常にエラーコードには目を光らせておきたい。

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

　次は文字列だ。カラム長を越えた文字列の登録を依頼すると入るところまでを登録し、あとは捨ててしまう。一応ブツブツ言うので聞き漏らさないようにしたい。

mysql> CREATE TABLE strtest (a VARCHAR(10)) ENGINE=innodb;
mysql> INSERT INTO strtest VALUES ("123456789ABCDE");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'a' at row 1 | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

　truncateして（切り捨てて）登録したと言っている。数値型への登録に文字を与えたときと同じエラーコードであることに気づいただろうか。MySQLが自らの判断で与えられた値の一部を捨ててしまった時にはこのコードのワーニングが出る。
　入るところまでは精一杯登録されていることが以下より確認できる。

mysql> SELECT * FROM strtest;
+------------+
| a          |
+------------+
| 123456789A | 
+------------+

　なおCREATE TABLE時に VARCHAR(長さ）で定義した長さは「文字数」なので改めてここで確認しておきたい。いまや、昔から言われているような「日本語1文字は英語2文字ぶん」などとは別の常識を持つ世界なので気をつけておこう。

mysql> INSERT INTO strtest VALUES ("あいうえおかきくけこさし");
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> SELECT * FROM strtest;
+--------------------------------+
| a                              |
+--------------------------------+
| あいうえおかきくけこ           |
+--------------------------------+

　このように VARCHAR(10) に対しては10文字（5文字ではない!!）までに切りつめられた文字列が登録されることがわかる。

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

　これは数値型と文字型の自動変換の合わせ技だ。
　MySQLでは「+」記号は、数値の和算として働く。文字列を連結するために使うのは CONCAT() 関数だ。
　+を使うとどうなるか、実例で見てみよう。

mysql> INSERT INTO strtest VALUES (123);
mysql> INSERT INTO strtest VALUES (123+27);
mysql> INSERT INTO strtest VALUES ("123"+27);
mysql> INSERT INTO strtest VALUES ("123"+"27");

　これらはすべて、文字列型のカラムへの挿入処理だが、すべて数値として処理されている点に注目だ。特に4番目の例は文字列どうしを連結している（とおそらくこのクエリの書き手は考えている）のに、実際には一旦数値化して計算されていることに、MySQLの気配りが感じられる。

　処理結果：

mysql> SELECT * FROM strtest;
+--------------------------------+
| a                              |
+--------------------------------+
| 123                            |
| 150                            |
| 150                            |
| 150                            |
+--------------------------------+

　ここまで見てきた方なら、以下のSQL文によって実施に何が登録されるのか、すぐにおわかりだろう。

mysql> INSERT INTO strtest VALUES ("image"+"37"+".png");

　これは正しくは以下のように書くべきなのである。

mysql> INSERT INTO strtest VALUES (CONCAT("image","37",".png"));

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

　MySQL の日付(または日付時刻)型は懐が広く、様々なフォーマットで与えられた「日付らしきもの」を理解しようと最大限の努力をしてくれる。日付時刻のうち日付だけに焦点を当てて見ると、YYYY-MM-DD や YYYY/MM/DD, YYYYMMDD のような表記の揺れを受け付けてくれるばかりか、実は「年は２桁でも良い」というルールもある。 YYYYMMDD の代わりに YYMMDD と与えても良い、というわけだ。
　このMySQLの気づかいがどういうことを引き起こすか見てみよう。もうワーニングについてはいちいち紹介しない。ワーニングが出るものも出ないものもあるので自身で試してもらいたい。

CREATE TABLE dttest (a DATETIME) engine=innodb;

INSERT INTO dttest VALUES (null);
INSERT INTO dttest VALUES ("");
INSERT INTO dttest VALUES ("2010");
INSERT INTO dttest VALUES ("201001");
INSERT INTO dttest VALUES ("20100123");

　nullを登録すればnullのまま登録されるのは良いとして、ブランクや2010のように日付型の要件を満たしていない場合は、なんと 0000-00-00 として登録されてしまう。日付型の変数値として 0000-00-00 を認めていない言語ではMySQLからデータを取得した途端にエラー（または例外発生）となるのでこの値は厄介だ。
　最後の 20100123 は、2010年1月23日として登録されるのは予想通りだろう。曲者なのが "201001" だ。なんとなく「2010年01月」のつもりだったようにも見える。だが年月日は３点セットであり、年月だけを与えるということはあり得ないのだ。ここで「年は２桁でも良い」ルールが発動する。　なんとMySQLはこの数字を YYMMDD 形式で与えられたものと解釈して、「2020年10月01日」として登録するのだ。これはびっくり。

　ところで日付型についてマニュアルにはこうある：

「サポートされている範囲は '1000-01-01' から '9999-12-31' です。」

　とはいえ、実際には西暦1000年以前も登録できてしまうので、いったい何を信用して良いのやら悩んでしまうところではある。

mysql> INSERT INTO dttest VALUES ("645-02-25");
mysql> SELECT * FROM dttest;
+---------------------+
| a                   |
+---------------------+
| 0645-02-25 00:00:00 |
+---------------------+

　話を戻して、年を2桁で与えた時の自動変換だが、これは「69年までは2000年代、70年からは1900年代」として解釈される。

mysql> INSERT INTO dttest VALUES ("69-02-25");
mysql> INSERT INTO dttest VALUES ("70-02-25");
mysql> SELECT * FROM dttest;
+---------------------+
| a                   |
+---------------------+
| 2069-02-25 00:00:00 |
| 1970-02-25 00:00:00 |
+---------------------+

　年を2桁ではなく必ず4桁で与える習慣が最善だが、そうでない場合、うっかりするとエラーにならず思っていたのと違う日付が登録されてしまうので気をつけたい。

　以上、MySQLでハマりやすい自動変換について紹介た。MySQLの気配りと上手に付き合い、みなさんのMySQLライフの一助となれば幸いである。
　なお、「SQLモード」を指定するとこれらの動作を変更することができる。SQLモードについては気が向いたらいつか紹介してみたい。

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