sgykfjsm.github.com

LAST_INSERT_ID()の返り値の型はバージョンによって違う

昨日、MySQLのバージョンを5.5.27から5.6.13に上げてテストしてたらLAST_INSERT_IDを使っているとこでエラーが出た。それで、ドキュメントを見ていたら以下の様な記述があった。

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-idより。

The value has a type of BIGINT UNSIGNED as of MySQL 5.5.29, BIGINT (signed) before that.

5.5.27ではsigned BIGINTが返る模様。

http://dev.mysql.com/doc/refman/5.6/en/information-functions.html#function_last-insert-idより。

The value has a type of BIGINT UNSIGNED as of MySQL 5.6.9, BIGINT (signed) before that.

5.6.13ではunsigned BIGINTが返る模様。

で、これの何が問題になったのかというと、例えばunsignedなBIGINTをjava.lang.Longに変換しようとしたら有効桁数が変わってしまうということ。
ドキュメント1によると、signed BIGINTは

  • min:-9223372036854775808
  • max:9223372036854775807

で、これはjava.lang.Longの定義^2と一致するんだけど、MySQLのunsigned BIGINTは

  • min:0
  • max:18446744073709551615

となる。なので、java.lang.Longじゃなくて、java.math.BigDecimalに変換するようにしないといけないんじゃないかなと。どう対応したかまでは知らないんでアレなんだけど。

以下は一応の検証結果。

mysql [localhost] {msandbox} (test) > select version();
+-----------+
| version() |
+-----------+
| 5.5.27    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t2 select last_insert_id() as c2 from t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > desc t2;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c2    | bigint(21) | NO   |     | 0       |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)


mysql [localhost] {msandbox} (test) > select version();
+-----------+
| version() |
+-----------+
| 5.6.13    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} ((none)) > use test;
Database changed
mysql [localhost] {msandbox} (test) > create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql [localhost] {msandbox} (test) > create table t2 select last_insert_id() as c2 from t1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c2    | bigint(21) unsigned | NO   |     | 0       |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

  1. https://dev.mysql.com/doc/refman/5.6/en/integer-types.html