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 会将值裁剪到列数据类型范围的上下限值并存储
    • 当超出范围的值分配给整数列时,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 模式有关…

总结

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

(0)

相关推荐

  • mysql常用日期时间/数值函数详解(必看)

    1.日期时间函数 时间转化秒函数:time_to_sec MySQL> select time_to_sec('01:01:01'); +-------------------------+ | time_to_sec('01:01:01') | +-------------------------+ | 3661 | +-------------------------+ 1 row in set (0.00 sec) 秒转化时间函数:sec_to_time mysql> select se

  • MySQL整型数据溢出的解决方法

    今天接到一个朋友电话说是觉的数据库被别人更改了,出现数据不对的问题 .经过很久的排查是数据类型溢出了(发生问题的版本是MySQL 5.1).后来通过给朋友那边把MySQL 5.1升级到MySQL 5.5去解决这个问题. 这也让我有兴趣去了解一下MySQL不同版本数据类型溢出的处理机制. 先看一下MySQL支持的整型数及大小,存储空间: pe Storage Minimum Value Maximum Value 存储大小   (Bytes) (Signed/Unsigned) (Signed/U

  • 关于MySQL的整型数据的内存溢出问题的应对方法

    今天接到一个朋友电话说是觉的数据库被别人更改了,出现数据不对的问题 .经过很久的排查是数据类型溢出了(发生问题的版本是MySQL 5.1).后来通过给朋友那边把MySQL 5.1升级到MySQL 5.5去解决这个问题. 这也让我有兴趣去了解一下MySQL不同版本数据类型溢出的处理机制. 先看一下MySQL支持的整型数及大小,存储空间: 另外请记着mysql的数据处理会转成bigint处理,所以这里就用bigint几个测试: SELECTCAST(0ASUNSIGNED)-1; SELECT922

  • mysql unsigned 用法及相减出现补数溢出解决方法

    unsigned 既为非负数,用此类型可以增加数据长度! 例如如果 tinyint最大是127,那 tinyint unsigned 最大 就可以到 127 * 2 unsigned 属性只针对整型,而binary属性只用于char 和varchar. 类型 说明 tinyint 非常小的整数 smallint 较小整数 mediumint 中等大小整数 int 标准整数 bigint 较大整数 float 单精度浮点数 double 双精度浮点数 decimal 一个串的浮点数 每种数值类型的

  • 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

  • 总提示[Microsoft][ODBC Excel Driver] 数值字段溢出官方解决方法

    使用Excel作数据库进行查询并显示字段值时老是提示错误:[Microsoft][ODBC Excel Driver] 数值字段溢出!最终在Microsoft网站上查得知是由于一些字段中的数据类型不一致造成的.才想起来Excel中原有的身份证字段是文本类型,后来手工添加的几条记录都被自动转成了数值型,统一成文本后问题随之解决. Microsoft详细说明:http://support.microsoft.com/kb/815277/zh-cn

  • mysql中datetime类型设置默认值方法

    通过navicat客户端修改datetime默认值时,遇到了问题. 数据库表字段类型datetime,原来默认为NULL,当通过界面将默认值设置为当前时间时,提示"1067-Invalid default value for 'CREATE_TM'",而建表的时候,则不会出现这个问题,比如建表语句: CREATE TABLE `app_info1` ( `id` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID', `a

  • numpy判断数值类型、过滤出数值型数据的方法

    numpy是无法直接判断出由数值与字符混合组成的数组中的数值型数据的,因为由数值类型和字符类型组成的numpy数组已经不是数值类型的数组了,而是dtype='<U11'. 1.math.isnan也不行,它只能判断float("nan"): >>> import math >>> math.isnan(1) False >>> math.isnan('a') Traceback (most recent call last):

  • MySQL datetime类型与时间、日期格式字符串大小比较的方法

    目录 一.前提 1.MySQL版本信息: 2.表字段: 二.使用>.<比较 1.  日期格式比较: 2. 时间格式 三.between and 1. 日期格式 四.总结 一.前提 1.MySQL版本信息: MySQL版本:8.0.27 注意:其他版本(主要5.x版本未验证) 2.表字段: 定义列 CREATE TABLE IF NOT EXISTS `user` ( id INT NOT NULL PRIMARY KEY auto_increment COMMENT 'id', user_na

  • Go语言strconv包实现字符串和数值类型的相互转换

    在实际开发中我们往往需要对一些常用的数据类型进行转换,如 string.int.int64.float 等数据类型之间的转换,Go语言中的 strconv 包为我们提供了字符串和基本数据类型之间的转换功能. strconv 包中常用的函数包括 Atoi().Itia().parse 系列函数.format 系列函数.append 系列函数等,下面就来分别介绍一下. string 与 int 类型之间的转换 字符串和整型之间的转换是我们平时编程中使用的最多的,下面就来介绍一下具体的操作. Itoa

  • MySQL数字类型自增的坑

    在进行表结构设计时,数字类型是最为常见的类型之一,但要用好数字类型并不如想象得那么简单,比如: 怎么设计一个互联网海量并发业务的自增主键?用 INT 就够了? 怎么设计账户的余额?用 DECIMAL 类型就万无一失了吗? 以上全错! 数字类型看似简单,但在表结构架构设计中很容易出现上述"设计上思考不全面"的问题(特别是在海量并发的互联网场景下) 数字类型 整数类型 MySQL 数据库支持 SQL 标准支持的整型类型:INT.SMALLINT.此外,MySQL 数据库也支持诸如 TINY

  • C++标准库bitset类型的简单使用方法介绍

    std::bitset是STL的一部分,准确地说,std::bitset是一个模板类,它的模板参数不是类型,而整形的数值(这一特性是ISO C++2003的新特性),有了它我们可以像使用数组一样使用位. #include<bister> using std::bitset; 一句话定义:可自定义位数,用作记录二进制的数据类型. 一,定义和初始化 bitset<n> b;                           //b有n位,每位都为0; bitset<n>

  • Yii统计不同类型邮箱数量的方法

    本文实例讲述了Yii统计不同类型邮箱数量的方法.分享给大家供大家参考,具体如下: 效果图: 控制器: //查询邮箱 public function actionEmail() { /* //查询所有邮箱数据(1种) $arr=Users::find("select * from users")->asArray()->all(); //var_dump($data);die; $data=array(); //取出邮箱类型 foreach($arr as $key=>

  • Go语言基础知识总结(语法、变量、数值类型、表达式、控制结构等)

    一.语法结构 golang源码采用UTF-8编码.空格包括:空白,tab,换行,回车. - 标识符由字母和数字组成(外加'_'),字母和数字都是Unicode编码. - 注释: 复制代码 代码如下: /* This is a comment; no nesting */ // So is this. 二.字面值(literals)类似C语言中的字面值,但数值不需要符号以及大小标志: 复制代码 代码如下: 23 0x0FF 1.234e7类似C中的字符串,但字符串是Unicode/UTF-8编码的

随机推荐