MySql中float类型的字段的查询

在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）：

 

注意：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函数将小数转成了字符串，可以做到精确查询。
————————————————
版权声明：本文为CSDN博主「赶路人儿」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/liuxiao723846/article/details/108119800