为什么SELECT ... WHERE id = a如果值是0则返回结果 [英] Why does SELECT ... WHERE id = a returns a result if value is 0

问题描述

Table preview 
Note: 'id' is an integer column (offcourse)

+-------------+
| id | value  |
+----+--------+
| 0  | abc    |
| 1  | def    |
+-------------+

查询:
1.返回abc:SELECT value FROM table WHERE id = 0
2.返回def:SELECT value FROM table WHERE id = 1
3.不返回任何内容:SELECT value FROM table WHERE id = 2
4.返回abc:SELECT value FROM table WHERE id = 'a'

Queries:
1. Returns abc: SELECT value FROM table WHERE id = 0
2. Returns def: SELECT value FROM table WHERE id = 1
3. Returns nothing: SELECT value FROM table WHERE id = 2
4. Returns abc: SELECT value FROM table WHERE id = 'a'

问题:为什么查询4返回abc?应该没有结果吧?

The question: Why does query 4 returns abc? It should give no results right?

推荐答案

MySQL在数字上下文中默默地将字符串转换为数字.

MySQL silently converts strings to numbers in a numeric context.

它是通过转换前导数字字符(例如数字，减号和小数点)来实现的.

It does so by converting the leading numeric characters (such as digits, minus sign, and decimal point).

如果没有数字，它将停止.所以:

If there are no digits, it stops. So:

where id = 'a'

被解释为:

where id = 0

道德:不要在数字常数(或应为数字的常数)周围使用单引号.

Moral: Never use single quotes around numeric constants -- or constants that should be numbers.

这篇关于为什么SELECT ... WHERE id = a如果值是0则返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！