• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MySQL数值类型溢出的处理方法

    来,考考大家一个问题,在 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 模式

    这个,应该很好理解吧?

    我们举一个例子,假设 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 TABLE,LOAD DATA INFILE,UPDATE 和多行 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 模式有关…

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

    您可能感兴趣的文章:
    • mysql常用日期时间/数值函数详解(必看)
    • MySQL整型数据溢出的解决方法
    • mysql unsigned 用法及相减出现补数溢出解决方法
    • 关于MySQL的整型数据的内存溢出问题的应对方法
    上一篇:MySql 快速插入千万级大数据的方法示例
    下一篇:MySQL计算两个日期相差的天数、月数、年数
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MySQL数值类型溢出的处理方法 MySQL,数值,类型,溢出,的,