MySQL中数值类型的精度探析

2020年2月26日
| 没有评论
摘要：float、double和decimal的比较。

任何数据库里，数值都是最核心的数据类型了，也就只有字符串类型能和它掰掰手腕了。在MySQL中，支持的数值类型也很全面。看看官方说明：

MySQL supports all standard SQL numeric data types. These types include the exact numeric data
types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data
types (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and
the keywords DEC and FIXED are synonyms for DECIMAL.

MySQL支持所有标准SQL中的数值类型，包括严格数值类型（INTEGER、SMALLINT、DECIMAL和NUMERIC），以及近似数值类型（FLOAT、REAL和DOUBLE PRECISION）。INT是INTEGER的同义词，DEC和FIXED是DECIMAL的同义词。

另外，REAL就DOUBLE PRECISION的同义词。DOUBLE PRECISION也简称为DOUBLE。

近似数值类型？相信很多同学都会有点懵住了，难道存进去的值会变化吗？在使用时就不准了？我们一起来测试下。

  • 数值类型均可以用名称加“(M,D)”的方式来表示，“(M,D)”表示该值一共显示M位数字（整数位+小数位），其中D为小数点后的位数。
  • float和double在不指定精度时，默认会按照实际的精度（硬件和操作系统决定）来显示。decimal在不指定精度时，默认的整数位为10，小数位为0。
  • MySQL里的float和double都属于浮点数，decimal属于定点数。

1）创建测试表

root@database-one 23:24:  [gftest]> create table test(v1 float(5,2),v2 double(5,2),v3 decimal(5,2));
Query OK, 0 rows affected (0.00 sec)

root@database-one 23:25:  [gftest]> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| v1    | float(5,2)   | YES  |     | NULL    |       |
| v2    | double(5,2)  | YES  |     | NULL    |       |
| v3    | decimal(5,2) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

 

2）往3个字段中都插入数据123.45

root@database-one 23:25:  [gftest]> insert into test values(123.45,123.45,123.45);
Query OK, 1 row affected (0.02 sec)

root@database-one 23:26:  [gftest]> select * from test;
+--------+--------+--------+
| v1     | v2     | v3     |
+--------+--------+--------+
| 123.45 | 123.45 | 123.45 |
+--------+--------+--------+
1 row in set (0.01 sec)

 

数据都正确地插入了表中。

3）向v1和v2中插入数据123.456，向v3中继续插入123.45

root@database-one 23:27:  [gftest]> insert into test values(123.456,123.456,123.45);
Query OK, 1 row affected (0.00 sec)

root@database-one 23:29:  [gftest]> select * from test;
+--------+--------+--------+
| v1     | v2     | v3     |
+--------+--------+--------+
| 123.45 | 123.45 | 123.45 |
| 123.46 | 123.46 | 123.45 |
+--------+--------+--------+
2 rows in set (0.00 sec)

 

数据都插入到了表中，但v1和v2由于小数位的限制，四舍五入，数据变成了123.46。

4）向v1、v2、v3中都插入123.456

root@database-one 23:29:  [gftest]> insert into test values(123.456,123.456,123.456);
Query OK, 1 row affected, 1 warning (0.07 sec)

root@database-one 23:35:  [gftest]> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'v3' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.01 sec)

root@database-one 23:35:  [gftest]> select * from test;
+--------+--------+--------+
| v1     | v2     | v3     |
+--------+--------+--------+
| 123.45 | 123.45 | 123.45 |
| 123.46 | 123.46 | 123.45 |
| 123.46 | 123.46 | 123.46 |
+--------+--------+--------+
3 rows in set (0.00 sec)

 

数据都插入到了表中，由于小数位的限制，四舍五入，数据变成了123.46。同时因为v3的数据类型是decimal，是严格数值类型，还触发了一个警告，如果是在传统的SQLMode，这条记录是无法插入的。

5）创建个不带精度和标度的新表，再次插入123.456

root@database-one 23:43:  [gftest]> create table testnew(v1 float,v2 double,v3 decimal);
Query OK, 0 rows affected (0.23 sec)

root@database-one 23:43:  [gftest]> insert into testnew values(123.456,123.456,123.456);
Query OK, 1 row affected, 1 warning (0.01 sec)

root@database-one 23:43:  [gftest]> show warnings;
+-------+------+-----------------------------------------+
| Level | Code | Message                                 |
+-------+------+-----------------------------------------+
| Note  | 1265 | Data truncated for column 'v3' at row 1 |
+-------+------+-----------------------------------------+
1 row in set (0.00 sec)

root@database-one 23:44:  [gftest]> select * from testnew;
+---------+---------+------+
| v1      | v2      | v3   |
+---------+---------+------+
| 123.456 | 123.456 |  123 |
+---------+---------+------+
1 row in set (0.01 sec)
root@database-one 23:44:  [gftest]> desc testnew;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| v1    | float         | YES  |     | NULL    |       |
| v2    | double        | YES  |     | NULL    |       |
| v3    | decimal(10,0) | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

 

v1和v2都正常的插入了，v3由于小数位的限制，四舍五入，数据变成了123。同时因为v3的数据类型是decimal，是严格数值类型，还触发了一个警告，如果是在传统的SQLMode，这条记录是无法插入的。

通过这个例子，我们搞明白了为啥float、double会被叫做近似数值类型，因为存入他们的数值如果精度超过了支持的范围，会发生变化，这样就和原始的值产生了偏差。

因此，大家在设计存放数值的字段时，一定要从业务场景出发，选择好数值类型，确保精度和标度都在支持的范围内。

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , , ,