mysql - 错误代码 : 1264. 第 1 行 'XXX' 列的值超出范围

Question

我的存储过程有页码的 IN 参数 ( IN page INT )一切都适用于任何值<= 2147483647 .如果值为 > 2147483647我收到错误 1264 .这一切都发生在5.6.26 Community Server compiled for Linux (x86_64) 5.6.24 Community Server compiled for Win64 (x86_64) 也一样以上所有都很好，我理解错误。

但是，在服务器上5.6.30-1+deb.sury.org~xenial+2 (Ubuntu) compiled for debian-linux-gnu (x86_64)这不是转载。无论我向那里传递什么值，输入值都会被截断为 2147483647。

什么设置导致这个“自动最大值上限”？如何在不重新安装的情况下同步不同环境中的设置以获得相同的行为？

谢谢!

Answer 1

检查 5.1.7 Server SQL Modes .

mysql> SELECT @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;
+--------------------------------------------+--------------------------------------------+
| @@GLOBAL.SQL_MODE                          | @@SESSION.SQL_MODE                         |
+--------------------------------------------+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> DELIMITER //

mysql> DROP PROCEDURE IF EXISTS `sp_test`//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `sp_test`(IN `page` INT)
    -> BEGIN
    ->   SELECT `page`;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `sp_test`(2147483647);
+------------+
| `page`     |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL `sp_test`(2147483648);
ERROR 1264 (22003): Out of range value for column 'page' at row 1

mysql> SET @@SESSION.SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@GLOBAL.SQL_MODE, @@SESSION.SQL_MODE;
+--------------------------------------------+------------------------+
| @@GLOBAL.SQL_MODE                          | @@SESSION.SQL_MODE     |
+--------------------------------------------+------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+------------------------+
1 row in set (0.00 sec)

mysql> CALL `sp_test`(2147483648);
+------------+
| `page`     |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------+
| Level   | Code | Message                                       |
+---------+------+-----------------------------------------------+
| Warning | 1264 | Out of range value for column 'page' at row 1 |
+---------+------+-----------------------------------------------+
1 row in set (0.00 sec)