欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-29 20:12:54
来,考考大家一个问题,在 mysql 中当某一列设置为 int(0) 时会发生什么 ? 为了演示这个问题,我们先要创建一个表 drop table if exi...

来,考考大家一个问题,在 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 会将值裁剪到列数据类型范围的上下限值并存储
    • 当超出范围的值分配给整数列时,mysql 会存储表示列数据类型范围的相应端点的值
    • 当为浮点或定点列分配的值超出指定(或默认)精度和比例所隐含的范围时,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 模式有关…

总结

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