MySql中float类型的字段的查询

小鱼儿 2021-05-12 11:50 536阅读 0赞

在MYSQL中，字段类型为FLOAT的字段，如果不指定FLOAT的长度和小数点位数，要根据FLOAT字段的值精确查找，结果会是空，这也是初学者经常会犯的一个错误。我们下来看一些例子：

CREATE TABLE `NewTable` (
    `id`  int(11) NOT NULL AUTO_INCREMENT ,
    `c1`  float(3,2) NOT NULL ,
    `c2`  float NOT NULL ,
    `c3`  decimal(10,0) NOT NULL ,
    `c4`  decimal(3,2) NOT NULL ,
    PRIMARY KEY (`id`)
    )
    ENGINE=InnoDB
    DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
    AUTO_INCREMENT=7
    ROW_FORMAT=COMPACT
    ;

c2、c3字段都是没有设置长度、精度，采用默认值。（decimal默认长度是10，精度是0）然后，插入一些数据（0.92、1.1、1、0.01、0）：

![20200820112111810.png][]

注意：c3字段类型是decimal，由于默认精度是0，所以插入的数据都转成了整型。

最后，我们测试一下查询。

**1、c1、c4字段查询：**

mysql> select * from test where c1=0.92;
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  1 | 0.92 | 0.92 | 1  | 0.92 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where c1=1.1;
    +----+-----+-----+----+-----+
    | id | c1  | c2  | c3 | c4  |
    +----+-----+-----+----+-----+
    |  2 | 1.1 | 1.1 | 1  | 1.1 |
    +----+-----+-----+----+-----+
    1 row in set
    
    mysql> select * from test where c1=1;
    +----+----+----+----+----+
    | id | c1 | c2 | c3 | c4 |
    +----+----+----+----+----+
    |  3 |  1 |  1 | 1  | 1  |
    +----+----+----+----+----+
    1 row in set
    
    mysql> select * from test where c1=0.01;
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  4 | 0.01 | 0.01 | 0  | 0.01 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where c1=0;
    +----+----+----+----+----+
    | id | c1 | c2 | c3 | c4 |
    +----+----+----+----+----+
    |  5 |  0 |  0 | 0  | 0  |
    +----+----+----+----+----+
    1 row in set

c1字段是设置了长度、精度的float类型，所以可以精确查询。同样，对于设置了长度、精度的decimal类型的c4字段，也是可以精确查询的。

**2、c2字段查询：**

mysql> select * from test where c2=0.92;
    Empty set
    
    mysql> select * from test where c2=1.1;
    Empty set
    
    mysql> select * from test where c2=1;
    +----+----+----+----+----+
    | id | c1 | c2 | c3 | c4 |
    +----+----+----+----+----+
    |  3 |  1 |  1 | 1  | 1  |
    +----+----+----+----+----+
    1 row in set
    
    mysql> select * from test where c2=0.01;
    Empty set
    
    mysql> select * from test where c2=0;
    +----+----+----+----+----+
    | id | c1 | c2 | c3 | c4 |
    +----+----+----+----+----+
    |  5 |  0 |  0 | 0  | 0  |
    +----+----+----+----+----+
    1 row in set

c2是没有设置精度的float字段，除了1、0之外，对于小数无法精确查询，原因是在 mysql 中float是浮点数，存储的时候是近似值，所以用精确查找无法匹配；但可以用like去匹配，也可以使用format、concat函数去查询。

1）使用format函数：

mysql> select * from test where format(c2,2)=0.92;
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  1 | 0.92 | 0.92 | 1  | 0.92 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where format(c2,2)=0.01;
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  4 | 0.01 | 0.01 | 0  | 0.01 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where format(c2,1)=1.1;
    +----+-----+-----+----+-----+
    | id | c1  | c2  | c3 | c4  |
    +----+-----+-----+----+-----+
    |  2 | 1.1 | 1.1 | 1  | 1.1 |
    +----+-----+-----+----+-----+
    1 row in set
    
    mysql> select * from test where format(c2,2)=1.1;
    +----+-----+-----+----+-----+
    | id | c1  | c2  | c3 | c4  |
    +----+-----+-----+----+-----+
    |  2 | 1.1 | 1.1 | 1  | 1.1 |
    +----+-----+-----+----+-----+
    1 row in set
    
    mysql> select * from test where format(c2,1)=0.01;
    Empty set

这里我们可以得知，format函数第二个参数，可以大于小数点后面尾数，但是小于小数点后面尾数，也是查不出来的。

2）使用concat函数：

mysql> select * from test where concat(c2,'')='0.01';
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  4 | 0.01 | 0.01 | 0  | 0.01 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where concat(c2,'')='0.92';
    +----+------+------+----+------+
    | id | c1   | c2   | c3 | c4   |
    +----+------+------+----+------+
    |  1 | 0.92 | 0.92 | 1  | 0.92 |
    +----+------+------+----+------+
    1 row in set
    
    mysql> select * from test where concat(c2,'')='1.1';
    +----+-----+-----+----+-----+
    | id | c1  | c2  | c3 | c4  |
    +----+-----+-----+----+-----+
    |  2 | 1.1 | 1.1 | 1  | 1.1 |
    +----+-----+-----+----+-----+
    1 row in set

通过concat函数将小数转成了字符串，可以做到精确查询。

[20200820112111810.png]: /images/20210511/5749868cb06e47748b1f77609f842817.png