mysql查询显示错误结果 [英] mysql query showing wrong result
问题描述
我的数据库表是这样的
+======+===========+============+
| 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'
这篇关于mysql查询显示错误结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!