代码改变世界

MySql中float类型的字段的查询

2021-08-13 19:39  狼人:-)  阅读(507)  评论(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
通过concat函数将小数转成了字符串,可以做到精确查询。
————————————————
版权声明:本文为CSDN博主「赶路人儿」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/liuxiao723846/article/details/108119800