UNSIGNED の不思議な挙動 #mysqlcasual

かじゅある！

この記事は、MySQL Casual Advent Calendar 2013 4日目です。

3日目を数秒差で @kamipo さんに取られてしまい*1、ネタかぶったらどうしようとドキドキしていましたが、そのようなことはなくこれでようやく安眠できそうです。

MySQL では、UNSIGNED が使える

UNSIGNED は、標準SQL にはないデータ型属性です。*2

通常の INT が -2147483648 から 2147483647 の範囲を表すのに対し、
INT UNSIGNED で定義したカラムは 0 から 4294967295 の範囲となります。

今回は、この UNSIGNED に関する奇妙な挙動のお話。

なお、検証に利用したバージョンは 5.1.69 です。
すでに 5.6 が GA となっている現在からするとやや古いバージョンではありますが、REHL 6.5 で採用されているバージョンでもあるので、これを利用している方はまだ多いのではないでしょうか。

突然の 4294967295

こんなテーブルがあるとしましょう。

CREATE TABLE test(
  id VARCHAR(10) PRIMARY KEY,
  i INT,
  ui INT UNSIGNED
);
INSERT INTO test VALUES
  ('max',2147483647, 4294967295),
  ('min',-2147483648, 0);

SELECT * FROM test;
+-----+-------------+------------+
| id  | i           | ui         |
+-----+-------------+------------+
| max |  2147483647 | 4294967295 |
| min | -2147483648 |          0 |
+-----+-------------+------------+

id は分かりやすさのために付けただけなので、注目すべきは i (INT) と ui (UNSIGNED INT)

それぞれ max には最大値、min には 最小値 が格納されています。

ここで、範囲を超えるような演算をしてみましょう。

UPDATE test SET i=i+1, ui=ui+1 WHERE id='max';
UPDATE test SET i=i-1, ui=ui-1 WHERE id='min';

Warning はでますが、エラーとはならずに実行出来ます。

そして結果がこれ。

SELECT * FROM test;
+-----+-------------+------------+
| id  | i           | ui         |
+-----+-------------+------------+
| max |  2147483647 | 4294967295 |
| min | -2147483648 | 4294967295 |
+-----+-------------+------------+

( ﾟдﾟ)……(つд⊂)ゴシゴシ…… えっ？

なぜか INT UNSIGNED だけ、最小値から減算すると 4294967295 になる。

上記例では 1 を引いてますが、いくつを引いても 4294967295 でした。

UPDATE test SET ui=0 WHERE id='min';          -- 一度 0 に戻して
UPDATE test SET ui=ui-5000 WHERE id='min';    -- 大きい数字を引いても
SELECT * FROM test;
+-----+-------------+------------+
| id  | i           | ui         |
+-----+-------------+------------+
| max |  2147483647 | 4294967295 |
| min | -2147483648 | 4294967295 |
+-----+-------------+------------+

＿人人人人人人人人人人＿
＞　突然の 4294967295　＜
￣Y^Y^Y^Y^Y^Y^Y^Y^Y￣

わけが分からない。

数値を文字列で渡してみる

先ほどのテーブルを一度初期値(最大値/最小値)に戻します。

UPDATE test SET i=2147483647, ui=4294967295 WHERE id='max';
UPDATE test SET i=-2147483648, ui=0 WHERE id='min';
SELECT * FROM test;
+-----+-------------+------------+
| id  | i           | ui         |
+-----+-------------+------------+
| max |  2147483647 | 4294967295 |
| min | -2147483648 |          0 |
+-----+-------------+------------+

今度は、数値をシングルクォテーションで囲って、文字列で渡してみましょう。

UPDATE test SET i=i+'1', ui=ui+'1' WHERE id='max';
UPDATE test SET i=i-'1', ui=ui-'1' WHERE id='min';

前回同様 Warning はでるものの、実行はできます。

シングルクォテーションで囲ったところで数値として解釈されるはず。
なので挙動は変わらないはず……と思って結果を見ると

SELECT * FROM test;
+-----+-------------+------------+
| id  | i           | ui         |
+-----+-------------+------------+
| max |  2147483647 | 4294967295 |
| min | -2147483648 |          0 |
+-----+-------------+------------+

( ﾟдﾟ)……(つд⊂)ゴシゴシ…… えっ？

なぜかこちらは、保存されたデータが変化することはありません。
や、むしろこの方が適切な挙動のように思えます。

けど、なぜ 数値を文字列にするだけで挙動変わる？？？

わけが分からない。

5.7 で試したら

5,7 で試してみたところ、データが書き換わらない動きに統一されているようです。
「突然の 4294967295」となるクエリについては エラーとなりました。(SET sql_mode='' にて)

エラー文言を見る限り、格納される前に演算の部分でエラーとなっているように見えます。

mysql> SELECT version();
+---------------+
| version()     |
+---------------+
| 5.7.2-m12-log |
+---------------+
1 row in set (0.00 sec)

mysql> SET sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test SET i=i-1, ui=ui-1 WHERE id='min';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test`.`ui` - 1)'

とはいえ、5.6 以降はsql_mode にデフォルトで STRICT_TRANS_TABLES が含まれている*3ので、あまり問題にはならないかと思います。
範囲外の値が格納されるケースは全て エラーとなって SQL の実行に失敗しますので。

mysql> SET sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE test SET i=i+1, ui=ui+1 WHERE id='max';
ERROR 1264 (22003): Out of range value for column 'i' at row 1
mysql> UPDATE test SET i=i-1, ui=ui-1 WHERE id='min';
ERROR 1264 (22003): Out of range value for column 'i' at row 1

まとめ

5.1でも STRICT_ALL_TABLES を設定したほうが良いんじゃないかな。

とはいえ、既に動いてるシステムで sql_mode 変えるのはしんどいので、おかしなクエリは発行しないよう注意しましょー。

明日は @yoshi_ken さんです。

かじゅある！

*1:https://twitter.com/kamipo/status/400596752798994432 / https://twitter.com/do_aki/status/400596814052593665

*2:MySQL :: MySQL 5.1 リファレンスマニュアル :: 1.8.4 SQL標準に対するMｙSQL拡張機能 http://dev.mysql.com/doc/refman/5.1/ja/extensions-to-ansi.html

*3:日々の覚書: MySQL5.6が勝手にsql_modeを書き換えてくれる話 http://yoku0825.blogspot.jp/2013/03/mysql56sqlmode.html