mysql float format_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).使用likemysql> 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，如资金数据。