有一个表,Cindex int userid Int, f_month float,另外两个为 字符
1853, 2152, 67.2, '2009-11-07 00:00:00', '2152'
1854, 2152, 67.2, '2009-11-07 00:00:00', '2152'67.2 对应 字段为 float 我用查询语句 : select Cindex from TB_app_CheckIndex where ( F_Month = 67.2);检索结果为空 ?请指教
1853, 2152, 67.2, '2009-11-07 00:00:00', '2152'
1854, 2152, 67.2, '2009-11-07 00:00:00', '2152'67.2 对应 字段为 float 我用查询语句 : select Cindex from TB_app_CheckIndex where ( F_Month = 67.2);检索结果为空 ?请指教
+------------------+
| version() |
+------------------+
| 5.1.40-community |
+------------------+
1 row in set (0.00 sec)mysql> use test;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `t1` (
-> `cindex` int(11) NOT NULL AUTO_INCREMENT,
-> `userid` int(11) DEFAULT NULL,
-> `f_month` float(10,1) NOT NULL DEFAULT '0.0',
-> PRIMARY KEY (`cindex`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=1855 DEFAULT CHARSET=utf8 COLLATE=utf8_uni
code_ci;
Query OK, 0 rows affected (0.02 sec)mysql> insert into t1(cindex,userid,f_month) values(1853,2153,67.2),(1854,2152,6
7.2);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> select * from t1;
+--------+--------+---------+
| cindex | userid | f_month |
+--------+--------+---------+
| 1853 | 2153 | 67.2 |
| 1854 | 2152 | 67.2 |
+--------+--------+---------+
2 rows in set (0.00 sec)mysql> select cindex from t1 where f_month=67.2;
+--------+
| cindex |
+--------+
| 1853 |
| 1854 |
+--------+
2 rows in set (0.00 sec)mysql> alter table t1 modify f_month decimal(65,1) not null default '0';
Query OK, 2 rows affected, 2 warnings (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 2mysql> desc t1;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| cindex | int(11) | NO | PRI | NULL | auto_increment |
| userid | int(11) | YES | | NULL | |
| f_month | decimal(65,1) | NO | | 0.0 | |
+---------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)mysql> select * from t1;
+--------+--------+---------+
| cindex | userid | f_month |
+--------+--------+---------+
| 1853 | 2153 | 67.2 |
| 1854 | 2152 | 67.2 |
+--------+--------+---------+
2 rows in set (0.00 sec)mysql> select cindex from t1 where f_month=67.2;
+--------+
| cindex |
+--------+
| 1853 |
| 1854 |
+--------+
2 rows in set (0.00 sec)
所以浮点数的=比较本身就是不可靠的。一般来说浮点数可以用于> < 的比较。在软件设计中,如果一定要做=比较,一般是用 <一个非常小的数来实现。
mysql> select * from t_ex3206 where abs(F_Month - 67.2)<1e-5;
+--------+---------+
| Cindex | f_month |
+--------+---------+
| 1 | 67.2 |
| 2 | 67.2 |
+--------+---------+
2 rows in set (0.00 sec)mysql>