MySQL 十进制字段“第 1 行的 x 列的数据被截断”问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23925271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL decimal field 'Data truncated for column x at row 1' issue
提问by yc.
I have a mysql table with a decimal(16,2) field. Seems like the addition operation with another decimal(16,2) field string can cause the Data truncated for column x at row 1issue, which raises exception in my django project.
我有一个带有小数 (16,2) 字段的 mysql 表。似乎与另一个十进制（16,2）字段字符串的加法运算会导致Data truncated for column x at row 1问题，这会在我的 django 项目中引发异常。
I'm aware of multiplication or division operation of that field can cause this issue bacause the result is probably not fit in decimal(16,2) definition, but does the addition and subtraction operation the same?
我知道该字段的乘法或除法运算会导致此问题，因为结果可能不适合十进制（16,2）定义，但是加法和减法运算是否相同？
My MySQL server version is 5.5.37-0ubuntu0.14.04.1. You can reproduce this issue from bellow:
我的 MySQL 服务器版本是5.5.37-0ubuntu0.14.04.1. 您可以从下面重现此问题：
mysql> drop database test;
Query OK, 1 row affected (0.10 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table t(price decimal(16,2));
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t values('2004.74');
Query OK, 1 row affected (0.03 sec)
mysql> select * from t;
+---------+
| price |
+---------+
| 2004.74 |
+---------+
1 row in set (0.00 sec)
mysql> update t set price = price + '0.09';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update t set price = price + '0.09';
Query OK, 1 row affected, 1 warning (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------+
| Note | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t;
+---------+
| price |
+---------+
| 2004.92 |
+---------+
1 row in set (0.00 sec)
回答by I?ya Bursov
There are two problems:
有两个问题：
You are not storing decimal values, you're trying to store string/varchar, which is converted into double value by mysql, for example following code does not give errors
update t set price = price + 0.09;(even executed several times)Anyway this code gives expected warning (note number)
update t set price = price + 0.091;you can change it toupdate t set price = price + cast(0.091 as decimal(16,2));of course with cast you can use string values tooupdate t set price = price + cast('0.09' as decimal(16,2));
你没有存储十进制值，你试图存储字符串/varchar，它被mysql转换成双精度值，例如下面的代码不会出错
update t set price = price + 0.09;（甚至执行了几次）无论如何，这段代码给出了预期的警告（注释编号），您可以使用强制
update t set price = price + 0.091;转换将其更改update t set price = price + cast(0.091 as decimal(16,2));为当然您也可以使用字符串值update t set price = price + cast('0.09' as decimal(16,2));
回答by Tarik
In my case problem occurs when I try to insert a decimal with 3 digits after the the dot like: 0.xxx on a column defined as DECIMAL(10,2)
在我的情况下，当我尝试在点后插入 3 位小数时出现问题，例如：0.xxx on a column defined as DECIMAL(10,2)
I changed it to DECIMAL(10,3) ORused php to enter values like 0.xx on DECIMAL(10,2) table
我将其更改为 DECIMAL(10,3)或使用 php 在 DECIMAL(10,2) 表上输入类似 0.xx 的值
