開発メモ：MySQLのINT型で「4294967295」を回避する｜理総研Web＠中学受験専門理科総合研究所

：ユーザー登録はコチラ：

012：MySQLのINT型で「4294967295」を回避する

対象言語：PHP / MySQL

キーワード：PHP MySQL 5.0.75 UNSIGNED 4294967295 INT 0 ゼロマイナス UPDATE CASE SET SQL_MODE NO_UNSIGNED_SUBTRACTION

MySQLのバージョンを4から5にアップしました。文字化けさえ気を付けていれば既存のコードをいじらなくても問題なくデータを移行できるだろう、そう思っていた時期が私にもありました。

で、しばらく運用していたらあるカラムのデータが大量に「4294967295」等とにかく巨大な数字に書き換えられてしまうという不思議。すぐに解決しましたが、もし気付けなかったならば....というドキドキが止まりません。このカラムは次のような構造です。

フィールド	種別	属性	NULL	デフォルト値
point	int(10)	UNSIGNED	いいえ	0

pointカラムのデータは加算されることもあれば減算されることもあります。MySQL4時代では属性にUNSIGNEDを指定し、どれだけ減算されてもマイナスにはならず、最小の値は「0」を維持するようにしていました。つまり負の数になることは無いのです。ただ私が属性：UNSIGNEDの仕様を誤解していたのか、MySQL5になってからこの「0」を維持することができなくなりました。

MySQL4の時代にはpointカラムの減算を以下のコードで実行していました。


$result_lock = mysql_query("LOCK TABLES `user` WRITE", $link) or die("テーブルロックの失敗：". mysql_error());

	$sql = "UPDATE `user` SET `point` = `point` - 1 WHERE `name` = 'darekasan'";
	$result = mysql_query($sql, $link) or die(mysql_error());

$result_lock = mysql_query("UNLOCK TABLES", $link) or die("テーブルロック解除の失敗：". mysql_error());

darekasanのポイントが1減算されるわけです。もし元々のpointカラムの値が「0」ならば、UNSIGNEDが指定されていない場合は「-1」となります。ココではUNSIGNEDを指定しているので-1にはならず、こちらの理想通りそしてMySQL4の仕様として「0」のままとなります。何らかのエラーが出力されるのかと思いきや何もありません。どれだけ減算しても0のままです。

MySQL5に移行して同じ処理を行うと、なんとpointカラムの値が「4294967295」となります。出力しても4294967295です。この数字はINT型で属性にUNSIGNEDを指定した場合の最大値となるもので、0（最小値）から1を引いたら4294967295（最大値）になりました！なんていう事件が起こっていたのです。ちなみに上記コードをもう一度実行すると、pointカラムの値は「4294967294」となり、立派に減算されます。

解決策としては、

パターンＡ： 計算の結果が負となるか正となるかで条件分岐しながらUPDATE！
パターンＢ： UNSIGNEDをMySQL4のときと同じ挙動にする！

があります。あとトリガーを使うなんていう方法もありますけど、コードを短く・処理を少なくをモットーに1行のSQL文にこだわる管理人は、次のようにCASE文を使用することにしました（1だけ減算する場合）。

//	パターンＡ・・・CASE文を使う
//	CASE文： (CASE WHEN ＜条件＞ THEN ＜条件が真の時＞ ELSE ＜条件が偽の時＞ END)

$result_lock = mysql_query("LOCK TABLES `user` WRITE", $link) or die("テーブルロックの失敗：". mysql_error());

	$sql = "UPDATE `user` SET `point` = (CASE WHEN `point` >= 1 THEN `point` - 1 ELSE 0 END) WHERE `name` = 'darekasan'";
	$result = mysql_query($sql, $link) or die(mysql_error());

$result_lock = mysql_query("UNLOCK TABLES", $link) or die("テーブルロック解除の失敗：". mysql_error());

冗長的で好きではありませんが、パターンＢなら次のような感じです。

//	パターンＢ・・・NO_UNSIGNED_SUBTRACTIONを事前にセットする

$result_lock = mysql_query("LOCK TABLES `user` WRITE", $link) or die("テーブルロックの失敗：". mysql_error());
	//　追加
	$sql = "SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION'";
	$result = mysql_query($sql, $link);
	//　以下は変更していない
	$sql = "UPDATE `user` SET `point` = `point` - 1 WHERE `name` = 'darekasan'";
	$result = mysql_query($sql, $link) or die(mysql_error());

$result_lock = mysql_query("UNLOCK TABLES", $link) or die("テーブルロック解除の失敗：". mysql_error());

著者 You Mizuguchi

Name：
Pass：

中学受験専門 理科総合研究所 －理総研－

012：MySQLのINT型で「4294967295」を回避する

中学受験専門　理科総合研究所　－理総研－