MySQL数值类型溢出的处理方法
作者:一入码坑深似海 发布时间:2024-01-19 12:03:34
来,考考大家一个问题,在 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 模式有关…
来源:https://www.jianshu.com/p/fe903f61f483
猜你喜欢
- 最近在使用Go语言搞一个用户登录&注册的功能,说到登录&注册相关,我们油然会产生一种增加验证码的想法,因此着手实现,后来在G
- 我一般是不看别人写的代码的,为啥?累!而且这位同志给的还是经过压缩的!汗。。。考我是不是?还有,这位同志也不给个示例的代码,只说是代码没有问
- PHP Too few arguments to function的解决过去自定义函数的时候如果参数不足,则会抛出一个警告,但是在7.1开始
- 现在同类型的网站数不胜数,网站的功能或服务日趋同质化,大的方面看不出什么差别,差别就体现在细节上。“窥斑见豹”,细节成为网站最有力的表现形式
- 前言最近学习了python,感觉挺多地方能用到它的。打包 测试 上传 爬电影....而且代码量是真少。人生苦短,我用python。而今天写的
- 在 Python 中,列表是一种非常常见且强大的数据类型。但有时候,我们需要从一个列表中删除特定元素,尤其是当这个元素出现多次时。本文将介绍
- RSS 是一个可用多种扩展来表示的缩写:“RDF 站点摘要(RDF Site Summary)”、“真正简单的辛迪加(Really Simp
- 摘要: 每到情人节、七夕节,不少小伙伴大伙伴们都会遇到这样一个世纪问题——怎么给女朋友/老婆一个与众不同的节日惊喜。今天给大家分享一个独特的
- 1)在service层和mapper层中写一个插入方法和查询方法;我们先写一个日志类;定义属性;并且要在数据库中建好表;package en
- 在python3中使用dict.keys()返回的不在是list类型了,也不支持索引,我们可以看一下下面这张图片那么我们应该怎么办呢,其实解
- 内码问题.用这段代码的吧,我已经改得很精简了.使用XMLHTTP抓取地址并进次内容处理Function GetBody(Url) Dim o
- 在网上搜索了半天,最简单的办法是在新的数据库中创建和原名字一样的数据库,然后把.frm 文件拷贝进去就OK了。 可是,有些时候这样不行,查询
- Python装饰器语法糖代码示例####装饰器的固定格式##普通版本def timer(func): def inner(*ar
- 以前大家谈了很多有关打开数据库连接安全的问题,现在我再提出一种思路:使用activex dll来保护你的代码。(既可以不用为使用共享的加密软
- Python 是由吉多·范罗苏姆(Guido Van Rossum)在 90 年代早期设计。 它是如今最常用的编程语言之一。它的语法简洁且优
- 首先给大家分享一篇js中int和string数据类型互相转化实例https://www.aspxhome.com/article/15474
- Python单例模式的两种实现方法方法一 import threading class Singleton(object): &
- 今天大概弄懂了partition by和group by的区别联系。1. group by是分组函数,partition by是分析函数(然
- 简介python可以做很多事情,虽然它的强项在于进行向量运算和机器学习、深度学习等方面。但是在某些时候,我们仍然需要使用python对外提供
- python中,A object = B object 是一种赋值操作,赋的值不是一个对象在内存中的空间,而只是这个