mysql - 列名称为 boolean 值？这是什么意思？

Question

我正在尝试理解一个 MySQL 语句，我认为它的行为与实际不同。但我感觉像official documentation是 sending我in circles .

<小时/>

这就是我想要的，基本上......

SELECT * FROM table WHERE col1 = 'string' OR col2 = 'string' OR col3 = 'string';

…相反，我想避免重复永不改变的'string'。几天前我发现这有效......

SELECT * FROM table WHERE col1 OR col2 OR col3 = 'string';

…或者，它至少返回了一些行。我假设这两个陈述是相同的 - 乍一看确实如此。

但是今天我发现口译员将其读作……

SELECT * FROM table WHERE (col1 OR col2) OR (col3 = 'string');

现在我想知道 (col1 OR col2) 的实际含义。有人可以启发我吗？我无法在 SO 或 Google 中找到明确的答案。

doc说这个……

Logical OR. When both operands are non-NULL, the result is 1 if any operand is nonzero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is nonzero, and NULL otherwise. If both operands are NULL, the result is NULL.

…但是我不明白id OR name和name OR Owner之间的区别(参见附录)。

附录

MariaDB [test2]> select * from pets;
+----+------------+-------+
| id | name       | owner |
+----+------------+-------+
|  1 | dog        | pete  |
|  2 | cat        | erica |
|  3 | bird       | maria |
|  4 | koala bear | pete  |
|  5 | gorilla    | joe   |
|  6 | chamaelon  | NULL  |
|  7 | NULL       | joe   |
+----+------------+-------+
7 rows in set (0.00 sec)

MariaDB [test2]> select * from pets WHERE name;
Empty set, 6 warnings (0.00 sec)

MariaDB [test2]> select * from pets WHERE name OR owner;
Empty set, 12 warnings (0.00 sec)

MariaDB [test2]> select * from pets WHERE id OR name OR owner;
+----+------------+-------+
| id | name       | owner |
+----+------------+-------+
|  1 | dog        | pete  |
|  2 | cat        | erica |
|  3 | bird       | maria |
|  4 | koala bear | pete  |
|  5 | gorilla    | joe   |
|  6 | chamaelon  | NULL  |
|  7 | NULL       | joe   |
+----+------------+-------+
7 rows in set (0.02 sec)

MariaDB [test2]> select * from pets WHERE id OR name OR owner = 'joe';
+----+------------+-------+
| id | name       | owner |
+----+------------+-------+
|  1 | dog        | pete  |
|  2 | cat        | erica |
|  3 | bird       | maria |
|  4 | koala bear | pete  |
|  5 | gorilla    | joe   |
|  6 | chamaelon  | NULL  |
|  7 | NULL       | joe   |
+----+------------+-------+
7 rows in set (0.00 sec)

MariaDB [test2]> select * from pets WHERE (id OR name);
+----+------------+-------+
| id | name       | owner |
+----+------------+-------+
|  1 | dog        | pete  |
|  2 | cat        | erica |
|  3 | bird       | maria |
|  4 | koala bear | pete  |
|  5 | gorilla    | joe   |
|  6 | chamaelon  | NULL  |
|  7 | NULL       | joe   |
+----+------------+-------+
7 rows in set (0.00 sec)

Answer 1

改用这个:

SELECT * FROM table WHERE 'string' IN (col1, col2, col3)

因为您的 name 列中没有值计算为 1，所以您的情况下的 id OR name 计算结果为 1，这意味着您的查询与以下内容相同

select * from pets WHERE 1

同样，由于 owner 列中没有值计算为 1，name OR Owner 计算结果为 0，这意味着您的查询与

select * from pets WHERE 0

我建议先select-ing表达式，然后再在where子句中使用它，这样您就可以看到将使用什么值。

示例:

select name OR owner