MySQL FIND_IN_SET()无法正常工作 [英] MySQL FIND_IN_SET() not working as expected

问题描述

我有下表:

... | parents_id   | ...
________________________
... | 1, 40, 7     | ...
... | 10, 4, 7, 1  | ...
... | 45, 40, 1, 7 | ...
... | other_rows   | ...

现在，我需要获取这三行，我使用此查询SELECT * FROM products WHERE FIND_IN_SET(1, parents_id) > 0，但是我只得到第一行(1, 40, 7)，有帮助吗?

Now, I need to get these three rows, I use this query SELECT * FROM products WHERE FIND_IN_SET(1, parents_id) > 0, but I only get the first row (1, 40, 7), any help?

推荐答案

根据文档-FIND_IN_SET的第二个参数是逗号分隔的列表.因此，用逗号分隔的值10, 4, 7, 1变为以下4个值:

As per documentation - FIND_IN_SET's second argument is a comma separated list. So the value 10, 4, 7, 1 being split by a comma becomes to the following 4 values:

1. 10
2. 4-空格后跟4
3. 7-空格后跟7
4. 1-空格后跟1

1. 10
2. 4 - space followed by 4
3. 7 - space followed by 7
4. 1 - space followed by 1

它们都不等于1

解决方案:停止使用此方法，并规范您的架构以使用一对多(或多对多).

Solution: stop using this approach and normalize your schema to use one-to-many (or many-to-many).

这篇关于MySQL FIND_IN_SET()无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！