如何搜索逗号分隔的值 [英] How to search for a comma separated value

问题描述

你好，我有一个字符串，存储在我的数据库中，用逗号分隔 例如:(新南威尔士州，昆士兰州等) 知道我的问题是，当我尝试搜索昆士兰州时，我无法获得结果，但是当我尝试搜索新的南威尔士州时，我得到了记录.

Hello i have a string that is stored in my database separated by comma eg: (new south wales,Queensland,etc,etc) Know my problem is when i try to search Queensland i am not able to get the result but when i try to search for new south wales i get the record.

但是当我尝试搜索皇后等时，我想得到结果. 我是php新手，请帮助...

But i want to get the result when i try to search for queen or etc. I am new to php so please help...

推荐答案

短期解决方案

使用 FIND_IN_SET函数:

WHERE FIND_IN_SET('Queensland', csv_column)

...因为在两端使用带通配符的LIKE是有风险的，具体取决于匹配项的数量/匹配程度(并且还可以确保对表进行扫描).两端都带有通配符的LIKE的性能与REGEXP相当-这意味着效果很差.

...because using LIKE with wildcards on either end is risky, depending on how much/little matches (and it also ensures a table scan). Performance of LIKE with wildcards on either side is on par with REGEXP--that means bad.

不要存储逗号分隔的值-使用适当的多对多关系，涉及三个表:

Don't store comma separated values -- use a proper many-to-many relationship, involving three tables:

• thing_id(主键)

• State_id(主键)
• 州名

• thing_id(主键，THINGS表的外键)
• State_id(主键，AUSTRALIAN_STATES表的外键)

• thing_id (primary key, foreign key to THINGS table)
• State_id (primary key, foreign key to AUSTRALIAN_STATES table)

您将需要JOIN来从三个表中获取数据，但是如果您想知道诸如与某个特定状态或两个特定状态相关联的数据之类的信息，那么这就是正确的模型.

You'll need JOINs to get data out of the three tables, but if you want to know things like how many are associated to a particular state, or two particular states, it's the proper model.

这篇关于如何搜索逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！