MySql中float类型的字段的查询-蒲公英云

MySql中float类型的字段的查询

小鱼儿 2021-05-12 11:50 608阅读 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）：

注意：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