MySQL decimal field "Data truncated for column x in row 1"

Question

I have a mysql table with a decimal (16,2) field. It looks like the addition operation with another decimal (16.2) field string may cause a problem Data truncated for column x at row 1that throws an exception in my django project.

I know that the multiplication or division operation of this field can cause this problem, since the result probably does not meet the decimal definition (16.2), but does the addition and subtraction operation the same?

My version of MySQL server is 5.5.37-0ubuntu0.14.04.1. You can reproduce this problem below:

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)

Answer 1

There are two problems:

• , /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));, , cast update t set price = price + cast('0.09' as decimal(16,2));

Answer 2

, 3 , : 0.xxx, , DECIMAL (10,2)

DECIMAL (10,3) , php, , 0.xx DECIMAL (10,2)