mysql格式化小数与Mysql中float列的等值查询

今天遇到一个问题，格式化浮点数的问题，用format（col,2）保留两位小数点，出现一个问题，例如

 SELECT FORMAT(12562.6655,2);

结果：12,562.67

 查看文档：Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的。

 mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
       -> '12,332'

没有达到预期结果，想要的结果不要以逗号分隔，

 select truncate(4545.1366,2);

 结果：4545.13，直接截取不四舍五入，还是有问题。

 select convert(4545.1366,decimal(10,2));

结果：4545.14，达到预期。

或    CAST

 SELECT CAST('4545.1366' AS DECIMAL(10,2));

convert、cast的用法

字符集转换 :   CONVERT(xxx  USING   utf-8)
类型转换和SQL Server一样,就是类型参数有点点不同  : CAST(xxx  AS   类型)  ,   CONVERT(xxx,类型)，类型必须用下列的类型：
可用的类型　 
  二进制,同带binary前缀的效果 : BINARY  
  字符型,可带参数 : CHAR()   
  日期 : DATE   
  时间: TIME   
  日期时间型 : DATETIME   
  浮点数 : DECIMAL    
  整数 : SIGNED   
  无符号整数 : UNSIGNED

 

 

mysql中float类型不定义精度时，对float列的检索可能不正确。

 

1.创建表t3,col1_float定义为float类型不指定精度
mysql> CREATE TABLE `t3` (
  `col1_float` float DEFAULT NULL,
  `col2_double` double DEFAULT NULL,
  `col3_decimal` decimal DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected

 

2.插入数据
mysql中float类型不指定精度时，会按照实际的精度显示
mysql> insert into t3 values(100.123,200.123,300.123);
Query OK, 1 row affected

mysql> select * from t3;
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

 

3.对float类型不指定精度时，默认等值查询结果为空.

如t3表中存在col1_float为100.123的记录，但由于定义col1_float的类型时没有指定float的精度，float精度会在最后的一位或多位丢失，比如15.9实际值为15.899993等

在对float列进行等值查询的时候检索不到记录。

mysql> select * from t3 where col1_float=100.123;
Empty set

mysql> show warnings;
Empty set

4.对double类型，则没有这种情况
mysql> select * from t3 where col2_double=200.123;
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

 

4.对decimal类型，如果不指定精度, 会对小数位进行截断
mysql> select * from t3 where col3_decimal=300;
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

 

部分内容摘自：http://blog.csdn.net/lwei_998/article/details/40979351

Mysql中针对不定义精度的float类型进行等值查询的解决方法：

a).使用like
mysql> select * from t3 where col1_float like 100.123;
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

b).使用format进行转换
mysql> select * from t3 where format(col1_float ,3) = format(100.123 ,3);
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

c). 修改数据类型，指定精度和标度。
mysql> alter table t3 modify col1_float  float(6,3);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t3 where col1_float=100.123;
+------------+-------------+--------------+
| col1_float | col2_double | col3_decimal |
+------------+-------------+--------------+
|    100.123 |     200.123 | 300          |
+------------+-------------+--------------+
1 row in set

 

如果小数点后面位数固定可以用decimal类型.也可以解决问题.精确数据一定要使用decimal，如资金数据。