mysql在列中的逗号分隔值上选择行

I've a product table with three columns:

我有一个包含三列的产品表:

product_id
product_name
product_keywords

And it contains values something like that:

它包含类似的值:

product_id | product_name | product_keywords
1 |Computer |pc, computer, laptop, personal computer, desktop computer
------------------------------------------------------------------------------------------------------------------'
2 |Mobile |Smart Phone, Phone, Nokia, Sony
--------------------------------------------------------------------------------------------------------------------'

product_id | product_name | product_keywords 1 |电脑|电脑,电脑,笔记本电脑,个人电脑,台式电脑---------------------------------- -------------------------------------------------- ------------------------------'2 |手机|智能手机,手机,诺基亚,索尼------- -------------------------------------------------- -------------------------------------------------- ---------”

Now I need an SQL query that if any customer search for pc or personal computer it will return product_name = Computer with product_id = 1 and so on.

现在我需要一个SQL查询,如果任何客户搜索pc或个人计算机,它将返回product_name = Computer with product_id = 1,依此类推。

3 个解决方案

#1

Although my first instinct would be to re-design the schema to have a keywords table with unique values and a mapping table between the products and the keywords, MySQL actually offers an elegant way to query comma-delimited strings with the find_in_set function:

虽然我的第一直觉是重新设计模式以使关键字表具有唯一值以及产品和关键字之间的映射表,但MySQL实际上提供了一种使用find_in_set函数查询逗号分隔字符串的优雅方法:

SELECT product_id, product_name
FROM   products
WHERE  FIND_IN_SET ('pc', keywords) > 0 OR
       FIND_IN_SET ('personal computer', keywords) > 0

#2

In it's simplest form you could write something like this.

在它最简单的形式你可以写这样的东西。

SELECT product_id, product_name 
FROM    test
WHERE   product_keywords like '%computer%';

But this is not that safe because you could have keywords on multiple rows that contain the letters 'computer'.

但这不是那么安全,因为你可以在包含字母'computer'的多行上设置关键字。

If you can strip the spaces out of your keywords so it reads:

如果您可以从关键字中删除空格,则会显示:

pc,computer,laptop,personal computer,desktop computer

电脑,电脑,笔记本电脑,个人电脑,台式电脑

then you can use the following code and be guaranteed the correct result:

那么您可以使用以下代码并保证正确的结果:

SELECT product_id, product_name 
FROM    test
WHERE   CONCAT(',',product_keywords,',') like CONCAT(',%','computer','%,');

just replace 'computer' with a variable containing the users input. The comma's help to ensure to search on the full keyword and not just part of it.

只需将'computer'替换为包含用户输入的变量即可。逗号帮助确保搜索完整关键字而不仅仅是其中的一部分。

#3

You can use find_in_set.

您可以使用find_in_set。

select * from `table` where find_in_set(' Computer',product_name)

But storing values as csv is very bad database design.

但是将值存储为csv是非常糟糕的数据库设计。

#1

SELECT product_id, product_name
FROM   products
WHERE  FIND_IN_SET ('pc', keywords) > 0 OR
       FIND_IN_SET ('personal computer', keywords) > 0

#2