今天遇到一个问题,格式化浮点数的问题,用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,如资金数据。