mysql查询结果正确_mysql 查询结果不正确

1.版本

1)操作系统

cat /etc/issue

cat /etc/issue

CentOS release 6.6 (Final)

Kernel \r on an \m

cat /proc/version

cat /proc/version

Linux version 2.6.32-504.el6.x86_64 (mockbuild@c6b9.bsys.dev.centos.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-11) (GCC) ) #1 SMP Wed Oct 15 04:27:16 UTC 2014

2)mysql数据库版本

MySQL --version

MySQL  Ver 14.14 Distrib 5.6.26, for linux-glibc2.5 (x86_64) using  EditLine wrapper

2.问题描述

今天群里一位朋友抛出如下问题：

create table test_pi(a varchar(20));

insert into test_pi values('190000000010378172');

mysql> select a from test_pi where a=190000000010378188;

+--------------------+

| a                  |

+--------------------+

| 190000000010378172 |

+--------------------+

1 row in set (0.00 sec)

##明明不相等，为什么记录会被返回呢？可能很多朋友到会提醒加引号查询，但是不加引号为什么会出现这样不匹配的查询结果呢？

3.问题原因

http://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

官方文档中Type Conversion in Expression Evaluation部分给出了解释。我只解释一下跟本问题相关的部分 ，select a from test_pi where a=190000000010378188;该查询会把a列值和190000000010378188都先转换成浮点数(string和number的比较)，然后比较两个浮点数。

我们来看一下a列值和190000000010378188转换成浮点数后的结果：

mysql> select '190000000010378172' +0.0;

+---------------------------+

| '190000000010378172' +0.0 |

+---------------------------+

| 1.9000000001037818e17 |

+---------------------------+

1 row in set (0.01 sec)

mysql> select 190000000010378188 +0.0

-> ;

+-------------------------+

| 190000000010378188 +0.0 |

+-------------------------+

| 190000000010378188.0 |

+-------------------------+

1 row in set (0.00 sec)##1.9000000001037818e17=1.9000000001037818*10^17  我们看到下面的比上面的多一位

接着我们看看在mysql中比较这两个浮点数会是怎么样的结果：

mysql> select 190000000010378188.0 - 1.9000000001037818e17;

+----------------------------------------------+

| 190000000010378188.0 - 1.9000000001037818e17 |

+----------------------------------------------+

| 0 |

+----------------------------------------------+

1 row in set (0.00 sec)

mysql> select 190000000010378188.0=1.9000000001037818e17;

+--------------------------------------------+

| 190000000010378188.0=1.9000000001037818e17 |

+--------------------------------------------+

| 1 |

+--------------------------------------------+

1 row in set (0.00 sec)##发现mysql认为这两个浮点数是一样的(虽然前面的值多了一位但是比较结果还是一样，这个应该跟浮点数的精度有关)

3.解决方法

mysql官方文档中有如下描述：

If both arguments in a comparison operation are strings, they are compared as strings

If both arguments are integers, they are compared as integers

所以我们要做的就是确保用来比较的两个值类型一致(要么都是整数要么都是string)