如何确定WHERE子句中的哪个条件失败? [英] How can I determine which condition on WHERE clause fails?

问题描述

我有一个这样的表:

// cookies
+----+---------+-------------------+------------+
| id | user_id |       token       |   expire   |
+----+---------+-------------------+------------+
| 1  | 32423   | dki3j4rf9u3e40... | 1467586386 |
| 2  | 65734   | erhj5473fv34gv... | 1467586521 |
| 3  | 21432   | 8u34ijf34t43gf... | 1467586640 |
+----+---------+-------------------+------------+

我有这个查询:

SELECT 1
FROM cookies
WHERE user_id = :id AND
      token   = :token

注意:结果始终为 one 行或 zero 行. (令牌列为unique)

Note: Always the result is either one row or zero row. (token column is unique)

当有匹配的行时，很好，但是，当没有匹配的行时，我想了解为什么?

When there is a matched row, ok, all fine, but when there isn't any matched row, I want to understand why?

• user_id存在，但token不存在
• token存在，但user_id不存在
• 所有这些列都不存在

• user_id exists but token doesn't
• token exists but user_id doesn't
• non of those columns doesn't exist

如何确定未选择任何行(匹配)"的原因?

编辑:这是所有可能的输出:

Here is all possible outputs:

1. 行存在
2. 行不存在，因为user_id = :id为 false
3. 行不存在，因为token = :token为 false
4. 行不存在，因为user_id = :id和token = :token均为 false
5. 行不存在，因为user_id = :id和token = :token是 true ，但不在同一行中.

1. row exists
2. row doesn't exist because user_id = :id is false
3. row doesn't exist because token = :token is false
4. row doesn't exist because both user_id = :id and token = :token are false
5. row doesn't exist because user_id = :id and token = :token are true but not in the same row.

推荐答案

这总是返回一行.如果匹配，则为1表示匹配，否则为0.它还显示cookie表中是否存在userID或令牌.使用此方法，您可以确定where子句失败的原因.

This always returns one row. 1 for matched if there is a match otherwise 0. It also shows if the userID or the token exists in the cookies table. Using this method you can determine why the where clause failed.

SELECT (  select count(*) > 0 matched from cookies where user_id = :id  and token   = :token ) matched
, ( select count(*) > 0 userIdExists from cookies where user_id = :id ) userIdExists
, ( select count(*) > 0 tokenExists from cookies where token = :token ) tokenExists

这篇关于如何确定WHERE子句中的哪个条件失败?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！