mysql float、decimal类型介绍

Myth丶恋晨 2021-09-01 09:43 615阅读 0赞

### 一、浮点数： ###

float和double类型表示近似数字数据值（浮点数），前者是单精度mysql用4个字节存储，后者是双精度用8个字节存储。

对于浮点数，SQL标准允许在浮点数后跟一个括号来指定精度（以位为单位，而不是指数的范围），例如：float（p）。 mysql也支持此可选的精度规范，float（p）中的精度值仅用于确定存储大小，从0到23的精度导致4字节单精度float列；从24到53的精度导致8字节的双精度double列。

mysql还允许使用非标准语法：float（M，D）或real（M，D）或double PRECISION（M，D）。其中（M，D）表示总共可以存储多达M位的值，其中D位可以在小数点后，M和D又称为精度（precision）和标度（scale）。例如，显示为FLOAT（7,4）的列显示为-999.9999。 MySQL在存储值时执行四舍五入，因此，如果将999.00009插入FLOAT（7,4）列，则近似结果为999.0001。  
从MySQL 8.0.17开始，不建议使用非标准的FLOAT（M，D）和DOUBLE（M，D）语法，并且在将来的MySQL版本中将不再支持该语法。

由于浮点值是近似值而不是作为精确值存储的，因此在比较中尝试将它们视为精确值可能会导致问题。它们还受平台或实现依赖性的约束。接下来，我们就看一些关于浮点数的示例。

**1、默认float类型：**

mysql> show create table t1;
    CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` float DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    
    mysql> insert into t1 (c1) values (12.34);
    Query OK, 1 row affected
    
    mysql> insert into t1 (c1) values (123456);
    Query OK, 1 row affected
    
    mysql> insert into t1 (c1) values (1234567);
    Query OK, 1 row affected
    
    mysql> insert into t1 (c1) values (1.23456);
    Query OK, 1 row affected
    
    mysql> insert into t1 (c1) values (1.234567);
    Query OK, 1 row affected
    
    mysql> select * from t1;
    +----+---------+
    | id | c1      |
    +----+---------+
    |  1 |   12.34 |
    |  2 |  123456 |
    |  3 | 1234570 |
    |  4 | 1.23456 |
    |  5 | 1.23457 |
    +----+---------+
    5 rows in set

*  默认float类型只能存6个数字（包括小数点前后的位数）；
 *  整数部分超过了6位，多出来的部分被截断，最后补了一个0；（整个数据就会错误，例如id=3）；
 *  整数部分没有超出6为，如果是小数部分导致整个数据超出了6位，则小数部分采用四舍五入。

**2、指定float的精度（float(m)）：**

mysql> CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` float(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    mysql> show create table t2;
    CREATE TABLE `t2` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` float DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 
    1 row in set
    
    mysql> insert into t2 (c1) values (1234567);
    Query OK, 1 row affected
    
    mysql> select * from t2;
    +----+---------+
    | id | c1      |
    +----+---------+
    |  1 | 1234570 |
    +----+---------+
    1 row in set

在mysql中float使用了精度和默认float是一样的，最多存储6个数字。

**3、指定float精度、标度（float(m,d)）：**

mysql> CREATE TABLE `t3` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` float(7,4) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected
    
    mysql> insert into t3 (c1) values (123456);
    Query OK, 1 row affected
    
    mysql> insert into t3 (c1) values (123.4567);
    Query OK, 1 row affected
    
    mysql> insert into t3 (c1) values (123.45678);
    Query OK, 1 row affected
    
    mysql> insert into t3 (c1) values (12.345678);
    Query OK, 1 row affected
    
    mysql> insert into t3 (c1) values (1234.56789);
    Query OK, 1 row affected
    
    mysql> select * from t3;
    +----+----------+
    | id | c1       |
    +----+----------+
    |  1 | 999.9999 |
    |  2 | 123.4567 |
    |  3 | 123.4568 |
    |  4 |  12.3457 |
    |  5 | 999.9999 |
    +----+----------+
    5 rows in set

*  对于float(m,d)：整数部分存储m-d位，小数部分存储d位；
 *  如果整数位超出，则整数为(m-d)个999，小数点后位数为(d)个9999；（整个数据存储出错，例如id=1，id=5）
 *  如果小数位超出，则四舍五入；

更为特殊一点，如果m=d呢？

mysql> CREATE TABLE `t4` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c1` float(4,4) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected
    
    mysql> insert into t4 (c1) values (123456);
    Query OK, 1 row affected
    
    mysql> insert into t4 (c1) values (123.456);
    Query OK, 1 row affected
    
    mysql> insert into t4 (c1) values (123.456789);
    Query OK, 1 row affected
    
    mysql> select * from t4;
    +----+--------+
    | id | c1     |
    +----+--------+
    |  1 | 0.9999 |
    |  2 | 0.9999 |
    |  3 | 0.9999 |
    +----+--------+
    3 rows in set

可以看到，存储的数据都会出错。

### 二、定点数： ###

DECIMAL和NUMERIC类型存储精确的数字数据值。当保留精确度很重要时，例如使用货币数据，则使用这些类型。在MySQL中，NUMERIC被实现为DECIMAL，因此以下有关DECIMAL的说明同样适用于NUMERIC。mysql以二进制格式存储DECIMAL值。在DECIMAL列声明中，可以（通常是）指定精度和小数位数。例如：

salary DECIMAL(5,2)

在此示例中，5是精度，2是小数位（这一点通float中定义是一样）。精度表示值存储的有效位数，小数位数表示小数点后可以存储的位数。标准SQL要求DECIMAL（5,2）能够存储任何具有五位数字和两位小数的值，因此可以存储在薪水列中的值的范围是-999.99到999.99。

在标准SQL中，语法DECIMAL（M）等效于DECIMAL（M，0）。同样，语法DECIMAL等效于DECIMAL（M，0），其中允许实现决定M的值。MySQL支持DECIMAL语法的这两种变体形式。 M的默认值为10。如果小数位数为0，则DECIMAL值不包含小数点或小数部分。

DECIMAL的最大位数为65，但是给定DECIMAL列的实际范围可能受给定列的精度或小数位数的限制。当为这样的列分配的值的小数点后的位数比指定刻度允许的位数多时，该值将转换为该刻度。 （精确的行为是特定于操作系统的，但是通常效果是将其截断为允许的位数。）

看一些例子：

mysql> show create table t5;
    CREATE TABLE `t5` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `c1` decimal(7,4) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    1 row in set
    
    mysql> insert into t5 (c1) values (12345678);
    Query OK, 1 row affected
    
    mysql> insert into t5 (c1) values (12345678.67);
    Query OK, 1 row affected
    
    mysql> insert into t5 (c1) values (12345.67);
    Query OK, 1 row affected
    
    mysql> insert into t5 (c1) values (12.34567);
    Query OK, 1 row affected
    
    mysql> insert into t5 (c1) values (12.3456789);
    Query OK, 1 row affected
    
    mysql> select * from t5;
    +----+----------+
    | id | c1       |
    +----+----------+
    |  1 | 999.9999 |
    |  2 | 999.9999 |
    |  3 | 999.9999 |
    |  4 | 12.3457  |
    |  5 | 12.3457  |
    +----+----------+
    5 rows in set

官网资料：

[https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html][https_dev.mysql.com_doc_refman_8.0_en_floating-point-types.html]

[https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html][https_dev.mysql.com_doc_refman_8.0_en_fixed-point-types.html]

参考其他资料：

[https://www.cnblogs.com/zhoujinyi/archive/2013/04/26/3043160.html][https_www.cnblogs.com_zhoujinyi_archive_2013_04_26_3043160.html]

[https://blog.csdn.net/Alen\_xiaoxin/article/details/79802310][https_blog.csdn.net_Alen_xiaoxin_article_details_79802310]

[https_dev.mysql.com_doc_refman_8.0_en_floating-point-types.html]: https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html
[https_dev.mysql.com_doc_refman_8.0_en_fixed-point-types.html]: https://dev.mysql.com/doc/refman/8.0/en/fixed-point-types.html
[https_www.cnblogs.com_zhoujinyi_archive_2013_04_26_3043160.html]: https://www.cnblogs.com/zhoujinyi/archive/2013/04/26/3043160.html
[https_blog.csdn.net_Alen_xiaoxin_article_details_79802310]: https://blog.csdn.net/Alen_xiaoxin/article/details/79802310