MySQL数值类型溢出的处理方法
来，考考大家一个问题，在MySQL中当某一列设置为int(0)时会发生什么？
为了演示这个问题，我们先要创建一个表
DROPTABLEIFEXISTS`na`; CREATETABLE`na`( n1INT(0)NOTNULLDEFAULT'0', n2INT(11)NOTNULLDEFAULT'0' );
然后我们使用下面的语句往na表中插入一些数据
mysql>INSERTINTO`na`VALUES(520,520),(5201314,5201314); QueryOK,2rowsaffected(0.02sec) Records:2Duplicates:0Warnings:0
最后我们读取出来看看
mysql>SELECT*FROMna; +---------+---------+ |n1|n2| +---------+---------+ |520|520| |5201314|5201314| +---------+---------+ 2rowsinset(0.00sec)
对的，好像什么都不会发生，没什么问题才是对的，我就怕有什么问题…哈哈
我们这一章节来讲讲整型溢出问题。
MySQL数值类型溢出处理
当MySQL在某个数值列上存储超出列数据类型允许范围的值时，结果取决于当时生效的SQL模式
- 如果启用了严格的SQL模式，则MySQL会根据SQL标准拒绝带有错误的超出范围的值，并且插入失败
- 如果没有启用任何限制模式，那么MySQL会将值裁剪到列数据类型范围的上下限值并存储
- 当超出范围的值分配给整数列时，MySQL会存储表示列数据类型范围的相应端点的值
- 当为浮点或定点列分配的值超出指定（或默认）精度和比例所隐含的范围时，MySQL会存储表示该范围的相应端点的值
这个，应该很好理解吧？
我们举一个例子，假设t1表的结构如下
CREATETABLEt1( i1TINYINT, i2TINYINTUNSIGNED );
如果启用了严格的SQL模式，超出范围会发生一个错误
mysql>SETsql_mode='TRADITIONAL';--首先设置严格模式 mysql>INSERTINTOt1(i1,i2)VALUES(256,256); ERROR1264(22003):Outofrangevalueforcolumn'i1'atrow1 mysql>SELECT*FROMt1; Emptyset(0.00sec)
当严格模式被禁用，值可以插入，但会被裁剪，并且引发一个警告
mysql>SETsql_mode='';--禁用所有模式 mysql>INSERTINTOt1(i1,i2)VALUES(256,256); mysql>SHOWWARNINGS; +---------+------+---------------------------------------------+ |Level|Code|Message| +---------+------+---------------------------------------------+ |Warning|1264|Outofrangevalueforcolumn'i1'atrow1| |Warning|1264|Outofrangevalueforcolumn'i2'atrow1| +---------+------+---------------------------------------------+ mysql>SELECT*FROMt1; +------+------+ |i1|i2| +------+------+ |127|255| +------+------+
如果未启用严格SQL模式，对于ALTERTABLE，LOADDATAINFILE，UPDATE和多行INSERT等语句会由于裁剪而发生的列分配转换并且引发一个警告。
而如果启用了严格模式，这些语句会直接失败，并且未插入或更改部分或全部值，具体取决于表是否为事务表和其他因素。
数值表达式求值过程中的溢出会导致错误，例如，因为最大的有符号BIGINT值是9223372036854775807，因此以下表达式会产生错误
mysql>SELECT9223372036854775807+1; ERROR1690(22003):BIGINTvalueisoutofrangein'(9223372036854775807+1)'
为了在这种情况下使操作成功，需要将值转换为unsigned
mysql>SELECTCAST(9223372036854775807ASUNSIGNED)+1; +-------------------------------------------+ |CAST(9223372036854775807ASUNSIGNED)+1| +-------------------------------------------+ |9223372036854775808| +-------------------------------------------+
从另一方面说，是否发生溢出取决于操作数的范围，因此处理前一个表达式的另一种方法是使用精确值算术，因为DECIMAL值的范围大于整数
mysql>SELECT9223372036854775807.0+1; +---------------------------+ |9223372036854775807.0+1| +---------------------------+ |9223372036854775808.0| +---------------------------+
整数数值之间的减去，如果其中一个类型为UNSIGNED，默认情况下会生成无符号结果。如果为负，则会引发错误
mysql>SETsql_mode=''; QueryOK,0rowsaffected(0.00sec) mysql>SELECTCAST(0ASUNSIGNED)-1; ERROR1690(22003):BIGINTUNSIGNEDvalueisoutofrangein'(cast(0asunsigned)-1)'
这种情况下，如果启用了NO_UNSIGNED_SUBTRACTIONSQL模式，则结果为负
mysql>SETsql_mode='NO_UNSIGNED_SUBTRACTION'; mysql>SELECTCAST(0ASUNSIGNED)-1; +-------------------------+ |CAST(0ASUNSIGNED)-1| +-------------------------+ |-1| +-------------------------+
如果此类操作的结果用于更新UNSIGNED整数列，则结果将裁剪为列类型的最大值，如果启用了NO_UNSIGNED_SUBTRACTION则裁剪为0。但如果启用了严格的SQL模式，则会发生错误并且列保持不变。
后记
一切都是套路，套路….基本都和SQL模式有关…
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对毛票票的支持。
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。