php 在 MySQL 中选择一个浮点数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1302243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting a float in MySQL
提问by Meep3D
I am trying to do a SELECTmatch on a table based upon an identifier and a price, such as:
我正在尝试SELECT根据标识符和价格对表格进行匹配，例如：
SELECT * FROM `table` WHERE `ident`='ident23' AND `price`='101.31';
The above returns zero rows, while if you remove the price='101.31'bit it returns the correct row.
以上返回零行，而如果删除该price='101.31'位，则返回正确的行。
Doing a...
做一个...
SELECT * FROM `table`;
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.
返回与上面相同的行，并非常清楚地说明price='101.31'. 然而 select 无法匹配它。更改=为<=使其工作 - 但这不完全是解决方案。
Is there a way of casting the MySQL float to 2 digits before the operation is performed on it, thus making the above SELECTwork (or some other solution)?
有没有办法在对其执行操作之前将 MySQL 浮点数转换为 2 位数字，从而使上述SELECT工作（或其他一些解决方案）？
Thanks!
谢谢！
回答by Matt Solnit
Casting to a decimal worked for me:
转换为小数对我有用：
SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);
However, you may want to consider just making the pricecolumn a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.
但是，您可能首先要考虑将price列设为DECIMAL。DECIMAL 通常被认为是处理货币值时使用的最佳类型。
回答by Eric Petroelje
It doesn't work because a float is inherently imprecise. The actual value is probably something like '101.3100000000001' You could use ROUND() on it first to round it to 2 places, or better yet use a DECIMAL type instead of a float.
它不起作用，因为浮点数本质上是不精确的。实际值可能类似于 '101.3100000000001' 您可以先在其上使用 ROUND() 将其四舍五入到 2 个位置，或者最好使用 DECIMAL 类型而不是浮点数。
回答by jcdyer
Don't ever use floats for money.
永远不要使用浮动来赚钱。
回答by hmasif
Today, I also came across the same situation and get resolved just by using FORMAT function of MySQL, It will return the results that exactly match your WHERE clause.
今天，我也遇到了同样的情况，通过使用MySQL的FORMAT函数解决了，它会返回与你的WHERE子句完全匹配的结果。
SELECT * FROM yourtable WHERE FORMAT(col,2) = FORMAT(value,2)
Explanation: FORMAT('col name', precision of floating point number)
解释： FORMAT('col name', precision of floating point number)
回答by Gerson Diniz
Try this:SELECT * FROM tableWHERE pricelike101.31;
试试这个：SELECT * FROM tableWHERE price like 101.31;
回答by Emmanuel Ifebuchechukwu Ossai
I was searching for solution but finally I did it using my previous php number_format() which came in seeing @hmasif's solution.
我正在寻找解决方案，但最后我使用我以前的 php number_format() 来完成它，它看到了@hmasif 的解决方案。
Use this and you'll get your float - mysql match:
使用这个，你会得到你的浮点数 - mysql 匹配：
$floatmatch = number_format((float)$yourfloatvariable,5);
where 5 is five strings from decimal point. e.g. 7.93643
其中 5 是从小数点开始的五个字符串。例如 7.93643
回答by Mark L
Does this work?
这行得通吗？
SELECT * , ROUND( price, 2 ) rounded
FROM table
WHERE ident = 'ident23'
HAVING rounded = 101.31
回答by LorenVS
Perhaps something along these lines:
也许是这样的：
SELECT * FROM table WHERE ident='ident23' AND ABS(price - 101.31) < .01;
