mysql查询显示错误结果

本文介绍了mysql查询显示错误结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我的数据库表是这样的

+======+===========+============+ | ID | user Name |user surname| +======+===========+============+ | 100 | name | surname | | 101 | name | surname | | 102 | name | surname | +===============================+

当我运行此查询时，应该不显示任何行，因为不存在具有101foo2值的行:

When i run this query which should show me no rows because there is no row with 101foo2 value :

SELECT * FROM tableName WHERE ID ='101foo2'

SELECT * FROM tableName WHERE ID = '101foo2'

我得到的结果具有相同的ID，但没有foo2字

I am getting a result with same ID without the foo2 word

+======+===========+============+ | ID | user Name |user surname| +======+===========+============+ | 101 | name | surname | +===============================+

如果我的查询是ID = '101foo2'

推荐答案

您正在混合类型. ID是整数(或数字).您正在将其与字符串进行比较.因此，MySQL需要确定用于比较的类型.使用什么类型?好吧，一串?不，一个数字.字符串将使用前导数字转换为数字.因此，它变为101并匹配.

You are mixing types. ID is an integer (or number). You are comparing it to a string. So, MySQL needs to decide what type to use for the comparison. What types gets used? Well, a string? No. A number. The string is converted to a number, using the leading digits. So, it becomes 101 and matches.

您实际上应该只将数字与数字进行比较，将字符串与字符串进行比较.您可以尝试将代码编写为:

You should really only compare numbers to numbers, and strings to strings. You could try to write the code as:

SELECT * FROM tableName WHERE ID = 101foo2

但是，您会得到一个错误.另一种可能性是强制转换为字符串:

However, you would get an error. Another possibility is to force the conversion to a string:

SELECT * FROM tableName WHERE CAST(ID as CHAR) = '101foo2'