MySQL双类型比较失败 [英] MySQL double type comparation fails

问题描述

我正在使用 JavaScript 和 php+mysql 开发一个聊天模块，我将聊天数据存储到一个简单的表中:id、member1、member2、message、timestamp.timestamp 列是 double(14,4)，我将时间戳存储在微秒级别，例如:1330522898.3137;

I am developing a chat module with JavaScript and php+mysql and I am storing the chat data into a simple table with: id, member1, member2, message, timestamp. The timestamp column is double(14,4), where I store the timestamp on microseconds level, ex: 1330522898.3137;

我的问题是:当我将此时间戳与另一个时间戳进行比较时，例如

My problem is: When I am comparing this timestamp with another one, like

   SELECT * FROM chat_rows WHERE timestamp >= 1330535168.9548

仅在特定数字上，例如上面的数字，我使用运算符 >= 得到 0 行，尽管我有一行具有完全相同的值；这可以通过由运算符 = 区分的几乎相同的查询来证明:

Only on particular numbers, like this one above, I get 0 rows using operator >= although I have a row with exact same value; This is proved by the almost same query differentiated by operator = :

   SELECT * FROM chat_rows WHERE timestamp = 1330535168.9548

返回一行；

解决方法是用 varchar 替换 double(14,4) 类型，但比 double 慢；

The workaround for this is to replace the double(14,4) type with varchar but is slower than double;

也仅在此服务器上失败(我的开发服务器):Apache/2.2.20 (Ubuntu)MySQL客户端版本:5.1.58

Also fails only on this server ( my development one ): Apache/2.2.20 (Ubuntu) MySQL client version: 5.1.58

在我的本地似乎有效；- 这是窗户Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/0.9.8oVersiunea clientului MySQL: mysqlnd 5.0.8-dev - 20102224 - $Revision: 318113 $

On my local it seems that works; - which is windows Apache/2.2.21 (Win32) mod_ssl/2.2.21 OpenSSL/0.9.8o Versiunea clientului MySQL: mysqlnd 5.0.8-dev - 20102224 - $Revision: 318113 $

有什么想法吗?

提前致谢！

推荐答案

进行浮点数比较的正确方法是首先确定数字之间差异的可接受容差，然后针对容差值进行比较.

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value.

MySQL 浮点比较问题

这篇关于MySQL双类型比较失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！