将int字段改为decimal类型时出现1264 Out of range value的问题分析和处理

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/zengxuewen2045/article/details/53701370

有开发同事在验证环境对某个表将int字段改成decimal(10,4)后,提示 1264 Out of range value报错,咨询dba这个为什么会报错,对数据有什么影响?
我收到这问题后,第一反应就是由于字段类型转换,长度不够发生了数据溢出。
于是,下面模拟该问题进行测试:
1.建测试表
CREATE TABLE `zeng` (
  `_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `a` int(32) DEFAULT NULL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28129 DEFAULT CHARSET=utf8 ;

2.准备测试用数据
insert into zeng(a) values (70000);
insert into zeng(a) values (800000);
insert into zeng(a) values (900000);
insert into zeng(a) values (9000000);
insert into zeng(a) values (99999999);
3.修改字段类型
alter table zeng modify column a decimal(10,4) unsigned;

dba@192.168.74.60:3306 : >alter table zeng modify column a decimal(10,4) unsigned;
Query OK, 5 rows affected, 2 warnings (0.05 sec)  
Records: 5  Duplicates: 0  Warnings: 2

4.查看warnings
dba@192.168.74.60:3306 >show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 4 |
| Warning | 1264 | Out of range value for column 'a' at row 5 |
+---------+------+--------------------------------------------+
2 rows in set (0.00 sec)

5.查询类型更改后的数据
dba@192.168.74.60:3306:>select * from zeng;
+-------+-------------+
| _id   | a           |
+-------+-------------+
| 28129 |  70000.0000 |
| 28130 | 800000.0000 |
| 28131 | 900000.0000 |
| 28132 | 999999.9999 |
| 28133 | 999999.9999 |
+-------+-------------+
5 rows in set (0.00 sec)
第四和第五行的a字段记录发生了溢出。

decimal(10,4)的最大值是999999.9999,decimal(10,4)中的10表示全部位数为10(整数位+小数位),4表示小数位,70000能用decimal(10,4)来表示,表示为70000.0000。
show warnings看到out of range表示超过了这个值 ,建议生产环境在对一个表更改字段前,先对这个表的数据create table as select 来备份一份,若出现out of range时,
该记录的数据也全部变成了9。在更改前可以查询字段a值的最大长度:
select max(length(a)) from zeng;
已确保decimal(M,N)中M-N>=max(length(a))才不会产生溢出。

展开阅读全文

没有更多推荐了,返回首页