mysql int 溢出_MySQL 数值类型溢出处理

> **推荐阅读**：[**吊打面试官！MySQL灵魂100问，你能答出多少？**](http://blog.itpub.net/69946007/viewspace-2656580/)

来，考考大家一个问题，在 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 模式有关…

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