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 模式

如果启用了严格的 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 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 模式有关...

Java 面试宝典是大明哥全力打造的 Java 精品面试题，它是一份靠谱、强大、详细、经典的 Java 后端面试宝典。它不仅仅只是一道道面试题，而是一套完整的 Java 知识体系，一套你 Java 知识点的扫盲贴。

它的内容包括：

大厂真题：Java 面试宝典里面的题目都是最近几年的高频的大厂面试真题。
原创内容：Java 面试宝典内容全部都是大明哥原创，内容全面且通俗易懂，回答部分可以直接作为面试回答内容。
持续更新：一次购买，永久有效。大明哥会持续更新 3+ 年，累计更新 1000+，宝典会不断迭代更新，保证最新、最全面。
覆盖全面：本宝典累计更新 1000+，从 Java 入门到 Java 架构的高频面试题，实现 360° 全覆盖。
不止面试：内容包含面试题解析、内容详解、知识扩展，它不仅仅只是一份面试题，更是一套完整的 Java 知识体系。
宝典详情：https://www.yuque.com/chenssy/sike-java/xvlo920axlp7sf4k
宝典总览：https://www.yuque.com/chenssy/sike-java/yogsehzntzgp4ly1
宝典进展：https://www.yuque.com/chenssy/sike-java/en9ned7loo47z5aw

目前 Java 面试宝典累计更新 400+ 道，总字数 42w+。大明哥还在持续更新中，下图是大明哥在 2024-12 月份的更新情况：

想了解详情的小伙伴，扫描下面二维码加大明哥微信【daming091】咨询

同时，大明哥也整理一套目前市面最常见的热点面试题。微信搜[大明哥聊 Java]或扫描下方二维码关注大明哥的原创公众号[大明哥聊 Java] ，回复【面试题】即可免费领取。

阅读全文