1. 首页
  2. IT资讯

MySQL 数值类型溢出处理

推荐阅读吊打面试官!MySQL灵魂100问,你能答出多少?

来,考考大家一个问题,在 MySQL 中当某一列设置为 int(0) 时会发生什么 ?

为了演示这个问题,我们先要创建一个表

   DROP TABLE IF EXISTS `na`;  CREATE TABLE `na` (    n1 INT(0)  NOT NULL DEFAULT '0',    n2 INT(11) NOT NULL DEFAULT '0'  );  

然后我们使用下面的语句往 na 表中插入一些数据

   mysql> INSERT INTO `na` VALUES(520,520),(5201314,5201314);  Query OK, 2 rows affected (0.02 sec)  Records: 2  Duplicates: 0  Warnings: 0  

最后我们读取出来看看

   mysql> SELECT * FROM na;  +---------+---------+  | n1      | n2      |  +---------+---------+  |     520 |     520 |  | 5201314 | 5201314 |  +---------+---------+  2 rows in set (0.00 sec)  

对的,好像什么都不会发生,没什么问题才是对的,我就怕有什么问题…哈哈

我们这一章节来讲讲整型溢出问题。

MySQL 数值类型溢出处理

当 MySQL 在某个数值列上存储超出列数据类型允许范围的值时,结果取决于当时生效的 SQL 模式

  • 如果启用了严格的 SQL 模式,则 MySQL 会根据 SQL 标准拒绝带有错误的超出范围的值,并且插入失败
  • 如果没有启用任何限制模式,那么 MySQL 会将值裁剪到列数据类型范围的上下限值并存储

    1. 当超出范围的值分配给整数列时,MySQL 会存储表示列数据类型范围的相应端点的值

    2. 当为浮点或定点列分配的值超出指定(或默认)精度和比例所隐含的范围时,MySQL 会存储表示该范围的相应端点的值

这个,应该很好理解吧?

我们举一个例子,假设 t1 表的结构如下

   CREATE TABLE t1 (      i1 TINYINT,      i2 TINYINT UNSIGNED  );  

如果启用了严格的 SQL 模式,超出范围会发生一个错误

   mysql> SET sql_mode = 'TRADITIONAL';  -- 首先设置严格模式  mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);  ERROR 1264 (22003): Out of range value for column 'i1' at row 1  mysql> SELECT * FROM t1;  Empty set (0.00 sec)  

当严格模式被禁用,值可以插入,但会被裁剪,并且引发一个警告

   mysql> SET sql_mode = '';  -- 禁用所有模式  mysql> INSERT INTO t1 (i1, i2) VALUES(256, 256);  mysql> SHOW WARNINGS;  +---------+------+---------------------------------------------+  | Level   | Code | Message                                     |  +---------+------+---------------------------------------------+  | Warning | 1264 | Out of range value for column 'i1' at row 1 |  | Warning | 1264 | Out of range value for column 'i2' at row 1 |  +---------+------+---------------------------------------------+  mysql> SELECT * FROM t1;  +------+------+  | i1   | i2   |  +------+------+  |  127 |  255 |  +------+------+  

如果未启用严格 SQL 模式,对于 ALTER TABLELOAD DATA INFILEUPDATE 和多行 INSERT等语句会由于裁剪而发生的列分配转换并且引发一个警告。

而如果启用了严格模式,这些语句会直接失败,并且未插入或更改部分或全部值,具体取决于表是否为事务表和其他因素。

数值表达式求值过程中的溢出会导致错误,例如,因为最大的有符号 BIGINT 值是 9223372036854775807,因此以下表达式会产生错误

   mysql> SELECT 9223372036854775807 + 1;  ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'  

为了在这种情况下使操作成功,需要将值转换为 unsigned

   mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;  +-------------------------------------------+  | CAST(9223372036854775807 AS UNSIGNED) + 1 |  +-------------------------------------------+  |                       9223372036854775808 |  +-------------------------------------------+  

从另一方面说,是否发生溢出取决于操作数的范围,因此处理前一个表达式的另一种方法是使用精确值算术,因为 DECIMAL 值的范围大于整数

   mysql> SELECT 9223372036854775807.0 + 1;  +---------------------------+  | 9223372036854775807.0 + 1 |  +---------------------------+  |     9223372036854775808.0 |  +---------------------------+  

整数数值之间的减去,如果其中一个类型为 UNSIGNED ,默认情况下会生成无符号结果。如果为负,则会引发错误

   mysql> SET sql_mode = '';  Query OK, 0 rows affected (0.00 sec)  mysql> SELECT CAST(0 AS UNSIGNED) - 1;  ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'  

这种情况下,如果启用了 NO_UNSIGNED_SUBTRACTION SQL 模式,则结果为负

   mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';  mysql> SELECT CAST(0 AS UNSIGNED) - 1;  +-------------------------+  | CAST(0 AS UNSIGNED) - 1 |  +-------------------------+  |                      -1 |  +-------------------------+  

如果此类操作的结果用于更新 UNSIGNED 整数列,则结果将裁剪为列类型的最大值,如果启用了 NO_UNSIGNED_SUBTRACTION 则裁剪为 0。但如果启用了严格的 SQL 模式,则会发生错误并且列保持不变。

后记

一切都是套路,套路….基本都和 SQL 模式有关…

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69946007/viewspace-2658397/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/190758.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code