关于MySQL的小数表示，浮点数与定点数，float,doube,decimal

矫情吗；* 2022-02-19 10:06 215阅读 0赞

浮点数类型：float单精度，double双精度  
定点数类型：decimal，默认decimal(10,0)

float占4bytes，double占8bytes，  
而decimal的存储要求，整数和小数部分分别计算，每9位数字占用4bytes，而不足9位的，占用情况如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70]  
需要注意的是，如果小数位不够，采用四舍五入的方法存储，如：

mysql> desc test;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | a     | float(3,2)   | YES  |     | NULL    |       |
    | b     | double(3,2)  | YES  |     | NULL    |       |
    | c     | decimal(3,2) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    
    mysql> insert into test values (1.236,1.236,1.236);
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    mysql> show warnings;
    +-------+------+----------------------------------------+
    | Level | Code | Message                                |
    +-------+------+----------------------------------------+
    | Note  | 1265 | Data truncated for column 'c' at row 1 |
    +-------+------+----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from test;
    +------+------+------+
    | a    | b    | c    |
    +------+------+------+
    | 1.24 | 1.24 | 1.24 |
    +------+------+------+
    1 row in set (0.00 sec)

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zOTAwNDkwMQ_size_16_color_FFFFFF_t_70]: /images/20220219/05fcd5b92dbf4b6fb9f62a1ebc986812.png