MySQL 数据类型（float）的注意事项

我就是我 2022-03-28 07:43 264阅读 0赞

**摘要:**  
      今天左哥问起一个float浮点数类型的问题，这个类型用的不多，所以也不太了解，现在打算测试下。  
  
**知识点：**  
      float：浮点数，单精度，占4字节。  
  
**测试**

root@localhost : test 05:49:32>create table fl(id int,fl float);
    Query OK, 0 rows affected (0.05 sec)
    
    root@localhost : test 05:49:40>insert into fl values(1,123456);
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:49:51>insert into fl values(2,123.456);
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:49:57>insert into fl values(3,1234.567);#7位了
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:50:13>insert into fl values(4,1234567);#7位了
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:50:26>select * from fl;
    +------+-------------+
    | id   | fl          |
    +------+-------------+
    |    1 |      123456 |
    |    2 |     123.456 |
    |    3 |     1234.57 |
    |    4 | 1.23457e+06 |
    +------+-------------+
    4 rows in set (0.00 sec)

**从上面看出：**默认的float类型都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示（id=4），小数位超出则需要四舍五入。那指定float(**m**)的精度呢？

root@localhost : test 05:59:08>create table fl(id int,fl float(20));
    Query OK, 0 rows affected (0.04 sec)
    
    root@localhost : test 05:59:28>show create table fl\G;
    *************************** 1. row ***************************
           Table: fl
    Create Table: CREATE TABLE `fl` (
      `id` int(11) DEFAULT NULL,
      `fl` float DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    root@localhost : test 05:59:35>insert into fl values(1,123456);
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:59:44>insert into fl values(2,123.456);
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:59:46>insert into fl values(3,1234.567);#7位了
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:59:50>insert into fl values(4,1234567);#7位了
    Query OK, 1 row affected (0.00 sec)
    
    root@localhost : test 05:59:53>select * from fl;
    +------+-------------+
    | id   | fl          |
    +------+-------------+
    |    1 |      123456 |
    |    2 |     123.456 |
    |    3 |     1234.57 |
    |    4 | 1.23457e+06 |
    +------+-------------+
    4 rows in set (0.01 sec)

**从上面看出：**和默认的float一样。所以：默认float和float(m)一样 ，**m<=24**； 都是默认float类型，都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示（id=4）；小数位超出则需要四舍五入。那指定他的标度float(m,**d**) 呢？

**从上面看出：**float(m,d)：小数点后位数为d，即整数位数为（m-d），整数位超出则整数为(m-d)个999，小数点后位数为(d)个9999，不用科学计算了；若小数位超出，则需要四舍五入。要是float(m,d)遇到m和d一样呢？

mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(4,4) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.04 sec)
    
    mysql> insert into fl values(1,123456);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> insert into fl values(2,123.45678);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> insert into fl values(3,123.4567);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> insert into fl values(4,12.456789);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from fl;
    +------+--------+
    | id   | fl     |
    +------+--------+
    |    1 | 0.9999 |
    |    2 | 0.9999 |
    |    3 | 0.9999 |
    |    4 | 0.9999 |
    +------+--------+
    4 rows in set (0.00 sec)

**从上面看出：**float(m,d)：若m和d一样，则先把小数位数先满足了，再分配整数位数。所以只有小数点位数，而整数位是0。

之前提过的**m<=24**，要是大于24呢？

mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(25) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show create table fl\G;
    *************************** 1. row ***************************
           Table: fl
    Create Table: CREATE TABLE `fl` (
      `id` int(11) DEFAULT NULL,
      `fl` double DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)

**从上面看出：**在没有标度的float中，从float转换成了double。而有标度的 float(m,**d**)当中，**m>24**呢？

mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(100,3) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show create table fl\G;
    *************************** 1. row ***************************
           Table: fl
    Create Table: CREATE TABLE `fl` (
      `id` int(11) DEFAULT NULL,
      `fl` float(100,3) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8

**从上面看出：**从float还是float，没有受到影响。不过设置这么大，在整数会变的不准；小数的位数还是m-d，位数不足用**0**填充（zerofill）。如下测试：

mysql> show create table fl\G;
    *************************** 1. row ***************************
           Table: fl
    Create Table: CREATE TABLE `fl` (
      `id` int(11) DEFAULT NULL,
      `fl` float(100,3) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    mysql> insert into fl values(5,777777777777777777777777777.5555);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from fl;
    +------+---------------------------------+
    | id   | fl                              |
    +------+---------------------------------+
    |    5 | 777777744225350500000000000.000 |  #不准了
    +------+---------------------------------+
    1 row in set (0.00 sec)
    
    mysql> drop table fl;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(25,23) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> show create table fl\G;
    *************************** 1. row ***************************
           Table: fl
    Create Table: CREATE TABLE `fl` (
      `id` int(11) DEFAULT NULL,
      `fl` float(25,23) DEFAULT NULL
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    1 row in set (0.00 sec)
    
    
    mysql> insert into fl values(5,99.555555555555555);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from fl;
    +------+----------------------------+
    | id   | fl                         |
    +------+----------------------------+
    |    5 | 99.55555725097656000000000 | #小数位被0填充
    +------+----------------------------+
    1 row in set (0.00 sec)

**注意：**当float(m,**d**) 设置了标度之后，小数位一定会存在。当整数位没有被超过，需要用0填充；整数位被超过了，需要用9填充，小数位数不能大于30否则报错：

mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(5,3) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        -> ;
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into fl values(1,123);
    Query OK, 1 row affected, 1 warning (0.00 sec)
    
    mysql> select * from fl;
    +------+--------+
    | id   | fl     |
    +------+--------+
    |    1 | 99.999 |
    +------+--------+
    1 row in set (0.00 sec)
    
    mysql> insert into fl values(2,13);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> insert into fl values(3,3);
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from fl;
    +------+--------+
    | id   | fl     |
    +------+--------+
    |    1 | 99.999 |   #9填充
    |    2 | 13.000 |    #0填充  
    |    3 |  3.000 |
    +------+--------+
    3 rows in set (0.00 sec)

mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(102,100) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    ERROR 1425 (42000): Too big scale 100 specified for column 'fl'. Maximum is 30.   #标度不能大于30，等于30可以建立
    mysql> CREATE TABLE `fl` (
        ->   `id` int(11) DEFAULT NULL,
        ->   `fl` float(52,50) DEFAULT NULL
        -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
    ERROR 1425 (42000): Too big scale 50 specified for column 'fl'. Maximum is 30.

**总结：float存在这么多潜在因素，用的时候需要注意，能避免就避免用。**