がるの健忘録

エンジニアでゲーマーで講師で占い師なおいちゃんのブログです。

大きなIDをどうやってPHPからMySQLに渡す?:詳細:MySQL編

とりあえず「思考の流れと調査の流れ」をそのまま書くので、読みにくいのはごめんなさいまし。


まずはいくつか検証テーブルを用意…する前に、MySQL自体の動きの確認を。
直近、参考にさせていただいたのは、hnwさんのこちらのサイト。
http://d.hatena.ne.jp/hnw/20120405


例題にある、以下のSQLを実際に流して、動きの確認をしました。
確認したのは、Server version: 5.6.10。ちょいと古いんだけど、ちょうど年末の整理でつぶす直前のサーバだったので、気兼ねなくw

create table decimal_test(id integer auto_increment primary key, a decimal(20));
insert into decimal_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996);
select * from decimal_test where a='9007199254740993';

mysql> select * from decimal_test where a='9007199254740993';
+----+------------------+
| id | a |
+----+------------------+
| 1 | 9007199254740992 |
| 2 | 9007199254740993 |
+----+------------------+
2 rows in set (0.00 sec)

うん、記述通り。

create table bigint_test(id integer auto_increment primary key, a bigint);
insert into bigint_test(a) values(9007199254740992),(9007199254740993),(9007199254740994),(9007199254740995),(9007199254740996);
select * from bigint_test where a='9007199254740993';

mysql> select * from bigint_test where a='9007199254740993';
+----+------------------+
| id | a |
+----+------------------+
| 2 | 9007199254740993 |
+----+------------------+
1 row in set (0.00 sec)

こちらも記述通り。


とりあえず、考察の前提が「AUTO_INCREMENTでの値」のお話なんで
・計算はしない
・型はint系(っていうかぶっちゃけるとbigint unsigned一択)
で、以降お話をすすめます。


さて。
MySQLのマニュアルにも、これに近しいお話は書いてありまして。

文字列カラムと数字との比較では、MySQL はカラム上のインデックスを使用して、値をすばやく検索できません。str_col がインデックスの付いた文字列カラムである場合は、次のステートメントで検索を実行するときに、そのインデックスを使用できません。
-略-
その理由は、'1'、' 1'、'1a' のように、値 1 に変換できるさまざまな文字列があるためです。
このような数字は不正確であるため、浮動小数点数 (または浮動小数点数に変換される値) を使用する比較は概算になります。これにより、整合性のない結果が表示される可能性があります。

ってなわけで、書いてある内容を試してみます。


書いてあるのは

mysql> SELECT '18015376320243458' = 18015376320243458;
-> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
-> 0

実験。

mysql> SELECT '18015376320243458' = 18015376320243458;
+-----------------------------------------+
| '18015376320243458' = 18015376320243458 |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT '18015376320243459' = 18015376320243459;
+-----------------------------------------+
| '18015376320243459' = 18015376320243459 |
+-----------------------------------------+
| 1 |
+-----------------------------------------+
1 row in set (0.00 sec)

………まてこら。話が違う!!
ただ、実はちょいと別の角度から、怪しい動きをします。
数値をちょいと変えるよ & 大体「カラム=値」で聞くのと、カラムのほうが数値型なので、順番も入れ替え。

mysql> SELECT 9223372036854775808 = '9223372036854775808';
+---------------------------------------------+
| 9223372036854775808 = '9223372036854775808' |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = '9223372036854775809';
+---------------------------------------------+
| 9223372036854775808 = '9223372036854775809' |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = '9223372036854775810';
+---------------------------------------------+
| 9223372036854775808 = '9223372036854775810' |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec)


ほら(悪い方に)あたった。丸め誤差出てる。

ほかのすべてのケースでは、引数は浮動小数点 (実) 数として比較されます。

って記述があるので、大体状況の予想はできる。


…んと

mysql> SELECT '9223372036854775808' + 0;
+---------------------------+
| '9223372036854775808' + 0 |
+---------------------------+
| 9.223372036854776e18 |
+---------------------------+
1 row in set (0.08 sec)

mysql> SELECT '9223372036854775809' + 0;
+---------------------------+
| '9223372036854775809' + 0 |
+---------------------------+
| 9.223372036854776e18 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT '9223372036854775810' + 0;
+---------------------------+
| '9223372036854775810' + 0 |
+---------------------------+
| 9.223372036854776e18 |
+---------------------------+
1 row in set (0.00 sec)

こーゆー感じ。
倍精度ったって限界はあるので。このあたりの数値で「1違う」値って、誤差で吸収されて「同じ値」になっちゃったりするのだよねぇ。
………じゃぁ「select * from bigint_test where a='9007199254740993';」がうまくいった、丸め誤差に巻き込まれなかった理由はなにか?


確定できるわけではないのですが、こんな風に組み替えると、こんな風に結果が変わります。

mysql> SELECT 9223372036854775808 = CAST('9223372036854775808' as UNSIGNED);
+---------------------------------------------------------------+
| 9223372036854775808 = CAST('9223372036854775808' as UNSIGNED) |
+---------------------------------------------------------------+
| 1 |
+---------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT 9223372036854775808 = CAST('9223372036854775809' as UNSIGNED);
+---------------------------------------------------------------+
| 9223372036854775808 = CAST('9223372036854775809' as UNSIGNED) |
+---------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT 9223372036854775808 = CAST('9223372036854775810' as UNSIGNED);
+---------------------------------------------------------------+
| 9223372036854775808 = CAST('9223372036854775810' as UNSIGNED) |
+---------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)


なんとなし「実数(浮動小数点)変換」のケースと「整数変換」のケースがあるんじゃないかなぁ、という予想が、少しばっかり*1
上述を踏まえて、少し「実際にテーブル作って」挙動の確認をしてみたいかなぁ、と思います。


下準備。INSERT、まぁしないのですが「文字と数値」で、両方の値を入れてみましょう(少し蛇足な実験w)。
あとは、本来的にターゲットであるAUTO_INCREMENT。

DROP TABLE IF EXISTS test;
CREATE TABLE test (
i bigint unsigned
)ENGINE=InnoDB;
INSERT INTO test VALUES (9223372036854775806),(9223372036854775807),(9223372036854775808),(9223372036854775809),(9223372036854775810);
INSERT INTO test VALUES ('9223372036854775806'),('9223372036854775807'),('9223372036854775808'),('9223372036854775809'),('9223372036854775810');


DROP TABLE IF EXISTS test_auto;
CREATE TABLE test_auto (
i bigint unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (i)
)ENGINE=InnoDB auto_increment = 9223372036854775806;
INSERT INTO test_auto VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);


下確認

mysql> SELECT * FROM test;
+---------------------+
| i |
+---------------------+
| 9223372036854775806 |
| 9223372036854775807 |
| 9223372036854775808 |
| 9223372036854775809 |
| 9223372036854775810 |
| 9223372036854775806 |
| 9223372036854775807 |
| 9223372036854775808 |
| 9223372036854775809 |
| 9223372036854775810 |
+---------------------+
10 rows in set (0.00 sec)

すんなりと入ってます。


さて、比較演算子を実験。

mysql> SELECT * FROM test WHERE i = 9223372036854775809;
+---------------------+
| i |
+---------------------+
| 9223372036854775809 |
| 9223372036854775809 |
+---------------------+
2 rows in set (0.00 sec)


mysql> SELECT * FROM test WHERE i = '9223372036854775809';
+---------------------+
| i |
+---------------------+
| 9223372036854775809 |
| 9223372036854775809 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test_auto WHERE i = 9223372036854775809;
+---------------------+
| i |
+---------------------+
| 9223372036854775809 |
+---------------------+
1 row in set (0.00 sec)


mysql> SELECT * FROM test_auto WHERE i = '9223372036854775809';
+---------------------+
| i |
+---------------------+
| 9223372036854775809 |
+---------------------+
1 row in set (0.00 sec)

ふむぅ。
単純に「SELECTに比較演算子を与えて出力」ん時は丸め誤差巻き込んでtrue返してたのに、実際のWHERE句になると挙動が違う。
どっちかってぇと「CASTした時」に近いような印象を受ける感じですねぇ。


まぁ、ここについてはhnwさんのほうでも

マッチしたのは1レコードだけでした。このことから、BIGINT型の値と文字列リテラルとを比較した場合は64ビット整数として比較されていることがわかります。
-壮絶に中略-
MySQLの数値型と文字列型の比較は浮動小数点数比較になるようにマニュアルに書いてありますが、整数比較になる例外ケースがいくつか見つかりました。具体的には、BIGINT型と文字列リテラル、インデックスつきのDECIMAL型と文字列リテラル、という2組が整数比較されているとわかりました。

って書かれてますし。
細かい話をすると「WHERE句だけなんかなぁ?」とも思うのですが、まぁ、使うし使いたいのはWHERE句で、なんで、気にしないw


また、MySQLのマニュアルのほうにも
https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html

BIGINT カラムについて注意の必要な点は、次のとおりです。
-中略-
文字列を使用して格納すると、いつでも正確な整数値を BIGINT カラムに格納できます。この場合、MySQL は、中間倍精度表現を含まない文字列から数値に変換します。

とあるので。
上述は「格納」ですが、比較についてもなんとなし「中間倍精度表現を含まない文字列から数値に変換」ってロジックが流れている可能性が想起されるような気が、びしばしとするのでございます。


このあたりを踏まえたうえで、じゃぁ次は「PHPからcallするとどうなるの?」っていう、疑問のメインをあさってみたいと思います。

*1:余談ですが。CASTで「実数への変換」って、ないんですかねぇ? いや「ちゃんとマニュアル読み切れてないだけ」な気もするのですが