使用In语句的第一个元素的结果

Question

我有三张桌子

td_product

|--------------|------------------------------------------------------------|
| product_id   |   product_title  |     compatible_model  |                 |
|---------------------------------------------------------------------------|
|      1       |     prod-1       |       1,4,5,6,8       |                 |
|---------------------------------------------------------------------------|
|      2       |     prod-2       |       1,5,6           |                 |
|---------------------------------------------------------------------------|
|      3       |     prod-3       |       4,6             |                 |
|---------------------------------------------------------------------------|

我有

td_model

|--------------|----------------------------------------------------|
|   model_id   |   model_title    |     brand_id  |    model_slug   |
|-------------------------------------------------------------------|
|      1       |     mode-1       |       1       |    1-mode-1     |
|-------------------------------------------------------------------|
|      2       |     mode-2       |       2       |    2-mode-2     |
|-------------------------------------------------------------------|
|      3       |     mode-3       |       4       |    3-mode-3     |
|-------------------------------------------------------------------|
|      4       |     mode-4       |       4       |    4-mode-4     |
|-------------------------------------------------------------------|
|      5       |     mode-5       |       2       |    5-mode-5     |
|-------------------------------------------------------------------|
|      6       |     mode-6       |       4       |    6-mode-6     |
|-------------------------------------------------------------------|
|      7       |     mode-7       |       1       |    7-mode-7     |
|-------------------------------------------------------------------|
|      8       |     mode-8       |       2       |    8-mode-8     |
|-------------------------------------------------------------------|

最后是品牌

|--------------|------------------------------------------------------------|
|   brand_id   |   brand_title    |                                         |
|---------------------------------------------------------------------------|
|      1       |     brnd-1       |                                         |
|---------------------------------------------------------------------------|
|      2       |     brnd-2       |                                         |
|---------------------------------------------------------------------------|
|      3       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      4       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      5       |     brnd-3       |                                         |
|---------------------------------------------------------------------------|
|      6       |     prod-3       |                                         |
|---------------------------------------------------------------------------|

现在我有了一个这样运行的查询

SELECT * FROM td_product,td_model,td_brand
WHERE
td_product.product_id > 0
AND td_model.model_id IN (td_product.compatible_model)
AND td_model.brand_id = td_brand.brand_id
AND td_model.model_slug = '1-mode-1'

显示prod-1和prod-2的

但是这个查询返回空值。

SELECT * FROM td_product,td_model,td_brand
WHERE
td_product.product_id > 0
AND td_model.model_id IN (td_product.compatible_model)
AND td_model.brand_id = td_brand.brand_id
AND td_model.model_slug = '5-mode-5'

我做错什么了？

Answer 1

不要使用IN，而是使用FIND_IN_SET(...)；change：

AND td_model.model_id IN (td_product.compatible_model)

至：

AND FIND_IN_SET(td_model.model_id, td_product.compatible_model) > 0