在 MySQL 中选择浮点数
我正在尝试根据标识符和价格对表进行 SELECT 匹配，例如:
I am trying to do a SELECT match on a table based upon an identifier and a price, such as:
SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';
上面返回零行，而如果您删除 price='101.31' 位，它会返回正确的行.
The above returns zero rows, while if you remove the price='101.31' bit it returns the correct row.
做...
SELECT * FROM `table`;
返回与上面相同的行，并且非常清楚地指出 price='101.31'.然而 select 无法匹配它.将 = 更改为 <= 使其工作 - 但这并不是一个完全解决方案.
Returns the same row as above and quite clearly states that price='101.31'. Yet select fails to match it. Changing = to <= makes it work - but this is not exactly a solution.
有没有办法在对其执行操作之前将 MySQL 浮点数转换为 2 位数字，从而使上述 SELECT 工作(或其他解决方案)?
Is there a way of casting the MySQL float to 2 digits before the operation is performed on it, thus making the above SELECT work (or some other solution)?
谢谢！
推荐答案
转换为小数对我有用:
SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);
但是，您可能首先要考虑将 price 列设为 DECIMAL.DECIMAL 通常被认为是处理货币值时使用的最佳类型.
However, you may want to consider just making the price column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.
相关文章