mysql - 在mysql中执行find_in_set()

Question

这是我的屏幕截图，显示了使用命令 find_in_set 执行的查询。

它仅执行特定行中的值集。我想在单个查询中执行表中与 find_in_set 命令匹配的所有值。

看看我的代码:

select * from shirts;
+----+--------------+------------------------------------+
| id | colors       | days                               |
+----+--------------+------------------------------------+
|  1 | 1,2,5,12,15  | monday, friday, thursday           |
|  2 | 1,5,12,15,30 | tuesday,monday                     |
|  3 | 2,5,11,15,28 | monday, friday,wednesday ,thursday |
|  4 | 1,2,7,12,15  | tuesday,monday                     |
|  5 | 2,4,8,12,15  | tuesday,monday                     |
+----+--------------+------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from shirts where find_in_set('5',colors) or find_in_set('30',colors) or find_in_set('30', colors) and find_in_set('monday',days) or  find_in_set('tuesday',days);
+----+--------------+------------------------------------+
| id | colors       | days                               |
+----+--------------+------------------------------------+
|  1 | 1,2,5,12,15  | monday, friday, thursday           |
|  2 | 1,5,12,15,30 | tuesday,monday                     |
|  3 | 2,5,11,15,28 | monday, friday,wednesday ,thursday |
|  4 | 1,2,7,12,15  | tuesday,monday                     |
|  5 | 2,4,8,12,15  | tuesday,monday                     |
+----+--------------+------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from shirts where find_in_set('5',colors) or find_in_set('30',colors) and find_in_set('monday',days) or  find_in_set('tuesday',days);
+----+--------------+------------------------------------+
| id | colors       | days                               |
+----+--------------+------------------------------------+
|  1 | 1,2,5,12,15  | monday, friday, thursday           |
|  2 | 1,5,12,15,30 | tuesday,monday                     |
|  3 | 2,5,11,15,28 | monday, friday,wednesday ,thursday |
|  4 | 1,2,7,12,15  | tuesday,monday                     |
|  5 | 2,4,8,12,15  | tuesday,monday                     |
+----+--------------+------------------------------------+
5 rows in set (0.00 sec)

我在颜色中选择了5和30，在天中选择了星期一、星期五 > 但它只显示 1 和 3 行。

谁能解决我的问题吗？

Answer 1

请记住，在 bool 代数中，AND 的绑定(bind)比 OR 更紧密。

有一个default order of precedence of these operators .

当你写下这个:

select * from shirts where A or B and C or D

就好像您使用了这样的括号:

select * from shirts where A or (B and C) or D

但你希望它像这样:

select * from shirts where (A or B) and (C or D)

因此，您必须使用显式括号编写查询，以覆盖 AND 与 OR 的默认优先顺序。

该问题与使用 find_in_set() 无关。这只是一个 bool 代数错误。