mysql - 整数值超出范围

Question

如果超过 19 位数字，则整数列类型的超出范围值不会截断为其最大值。在以下示例中，第二行不应有 -1 值。是错误吗？

drop table if exists test;

CREATE TABLE `test` (
  `col1` int(11) NOT NULL,
  `col2` int(11) NOT NULL,
  `col3` int(11) NOT NULL,
  `col4` int(11) NOT NULL,
  `col5` int(11) NOT NULL,
  `col6` int(11) NOT NULL,
  `col7` int(11) NOT NULL,
  `col8` int(11) NOT NULL,
  `lastupdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `lastupdateid` varchar(100) NOT NULL
) ENGINE=InnoDB;


insert into test values('6022','2123456789012345678', '00','00','00','00','00','00', NULL, 'test');

mysql>select * from test;
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| col1 | col2       | col3 | col4 | col5 | col6 | col7 | col8 | lastupdate          | lastupdateid |
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| 6022 | 2147483647 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:30 | test         | 
+------+------------+------+------+------+------+------+------+---------------------+--------------+
1 row in set (0.00 sec)


insert into test values('6022','21234567890123456789', '00','00','00','00','00','00', NULL, 'test');

mysql>select * from test;
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| col1 | col2       | col3 | col4 | col5 | col6 | col7 | col8 | lastupdate          | lastupdateid |
+------+------------+------+------+------+------+------+------+---------------------+--------------+
| 6022 | 2147483647 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:30 | test         | 
| 6022 |         -1 |    0 |    0 |    0 |    0 |    0 |    0 | 2009-10-28 18:20:34 | test         | 
+------+------------+------+------+------+------+------+------+---------------------+--------------+
2 rows in set (0.00 sec)

Answer 1

这不是错误，只是 integer overflow 的一个例子。

如果您需要将字符串中的数字值最大化，您应该在执行插入时或作为触发器添加代码来做到这一点。

有关详细信息，请参阅 http://dev.mysql.com/doc/refman/5.0/en/type-conversion.html。

你也可以尝试小的选择(未经测试，我这里没有 MySQL 访问权限):

SELECT CAST('2123456789012345678' AS INT(11))
SELECT CAST('21234567890123456789' AS INT(11))