我遇到一个MySQL语句的问题,该语句一直返回null,尽管我能够弄清楚它,但原因使我有些困惑。
这是有问题的查询的简化版本:
SELECT id FROM users WHERE id = id = 2;
发生该错误是因为id =
重复出现,从而消除了已id =
解决的问题之一(因为存在ID为2的用户)。但是令我有些困惑的是,它如何“默默失败”,返回0行,而不是给出错误。
我在SQL Server上测试了类似的查询,并收到错误消息Incorrect syntax near '='
(类似于我对MySQL的预期)。
最初,我认为MySQL是在前两个字段之间进行比较,而最后一个是比较的结果(0代表false,1代表true)。像这样:
SELECT id FROM users WHERE (id = id) = 2;
但是后来我提出了一些与之矛盾的查询。让我详细说明一下。
想象一下这个表(称为“用户”):
id | username
----|----------------
0 | anonymous
1 | root
2 | john
3 | doe
如果这样做SELECT * FROM users WHERE id = id = 1
,我将获得所有4个用户。而且SELECT * FROM users WHERE id = id = 0
,我什么也没得到。这似乎证实了比较理论。但是,如果我做这样的事情,事情就会变得混乱:
SELECT * FROM users WHERE id = username = 1;
SELECT * FROM users WHERE id = username = 0;
没有一条记录具有与用户名相同的ID(它们甚至都不具有相同的类型:int(11)
和varchar(25)
),但是对于第一个记录,我得到一个结果:“匿名”。在第二个中,我得到了除“匿名”之外的所有用户。为什么会这样呢?我看到它与id
0有关,因为如果我将“匿名” ID从0替换为4,那么我将不会在第一个查询中得到它(它在第二个查询中显示)。
我猜想这与MySQL在与数字比较时将字符串/ varchars转换为0有关。但是,为什么允许在同一子句中进行链式比较呢?比较它们时遵循什么顺序?
当这样的查询有效时,事情变得很有趣,并且实际上返回(意外的?)值。例如:
SELECT * FROM users WHERE id = id = id = username = username = id = username = 1;
返回ID为2和3的记录,但没有ID为0和1的记录。为什么?
tl; dr版本:为什么具有链接比较操作的查询有效?比较中遵循的顺序是什么?这是错误还是预期的行为?
首先,谢谢您提出这个有趣的问题。我很喜欢玩这个。让我们开始回答:
这很明显,但是即使您并不真正了解SQL的工作原理,我也会为每个程序员提供答案。一个SELECT-WHERE
查询,基本上是:
where_condition
使用该特定行的值评估。where_condition
结果为TRUE
,则会列出。伪代码可以是:
for every row:
current values = row values # for example: username = 'anonymous'
if (where_condition = TRUE)
selected_rows.append(this row)
除非字符串是数字('1'
,,)或字符串以数字开头'423'
,'-42'
否则其他所有字符串都等于0
(或FALSE
)。“数字字符串”等于其等效数字,“起始数字字符串”等于其初始数字。提供一些例子:mysql> SELECT'a'= 0;
+---------+
| 'a' = 0 |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)
。
mysql> SELECT 'john' = 0;
+------------+
| 'john' = 0 |
+------------+
| 1 |
+------------+
1 row in set, 1 warning (0.00 sec)
。
mysql> SELECT '123' = 123;
+-------------+
| '123' = 123 |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
。
mysql> SELECT '12a5' = 12;
+-------------+
| '12a5' = 12 |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
链式比较是一个接一个地解决的,首先要有括号的优先选择,从左开始直到aTRUE
或FALSE
剩下。
因此,例如1 = 1 = 0 = 0
将跟踪如下:
1 = 1 = 0 = 0
[1] = 0 = 0
[0] = 0
[1]
Final result: 1 -> TRUE
我将跟踪最后一个查询,该查询被认为是最复杂但最漂亮的解释:
SELECT * FROM users WHERE id = id = id = username = username = id = username = 1;
首先,我将显示where_condition
每个行变量:
id = id = id = username = username = id = username = 1
0 = 0 = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
1 = 1 = 1 = 'root' = 'root' = 1 = 'root' = 1
2 = 2 = 3 = 'john' = 'john' = 2 = 'john' = 1
3 = 3 = 3 = 'doe' = 'doe' = 3 = 'doe' = 1
现在,我将跟踪每一行:
0 = 0 = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
[1] = 0 = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
[0] = 'anonymous' = 'anonymous' = 0 = 'anonymous' = 1
[1] = 'anonymous' = 0 = 'anonymous' = 1
[0] = 0 = 'anonymous' = 1
[1] = 'anonymous' = 1
[0] = 1
[0] -> no match
1 = 1 = 1 = 'root' = 'root' = 1 = 'root' = 1
[1] = 1 = 'root' = 'root' = 1 = 'root' = 1
[1] = 'root' = 'root' = 1 = 'root' = 1
[0] = 'root' = 1 = 'root' = 1
[1] = 1 = 'root' = 1
[1] = 'root' = 1
[0] = 1
[0] -> no match
2 = 2 = 3 = 'john' = 'john' = 2 = 'john' = 1
[1] = 3 = 'john' = 'john' = 2 = 'john' = 1
[0] = 'john' = 'john' = 2 = 'john' = 1
[1] = 'john' = 2 = 'john' = 1
[0] = 2 = 'john' = 1
[0] = 'john' = 1
[1] = 1
[1] -> match
3 = 3 = 3 = 'doe' = 'doe' = 3 = 'doe' = 1
[1] = 3 = 'doe' = 'doe' = 3 = 'doe' = 1
[0] = 'doe' = 'doe' = 3 = 'doe' = 1
[1] = 'doe' = 3 = 'doe' = 1
[0] = 3 = 'doe' = 1
[0] = 'doe' = 1
[1] = 1
[1] -> match
因此,带有id
2
和的行与3
匹配where_condition
,这就是查询所显示的行的原因。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句