具有多个值的单列匹配多个值sql

I have the table newsarchive in which there is a tags column where I can enter multiple values separated by comma. I have a page in which the news matching the tags specified with the newsarchive.tags will be shown. The tags in the page can also have multiple values.

我有表newsarchive,其中有一个标签列,我可以输入多个以逗号分隔的值。我有一个页面,其中将显示与newsarchive.tags指定的标签匹配的新闻。页面中的标签也可以有多个值。

Now I want to get the records that matches the tags from page with newsarchive.tags. Is there a SQL statement to achieve the result?

现在我想从newsarchive.tags获取与页面中的标签匹配的记录。是否有SQL语句来实现结果?


CREATE TABLE newsarchive ( 
    newsid int(11) NOT NULL auto_increment, 
    headline text NOT NULL, 
    article text NOT NULL, 
    pdate date NOT NULL default '0000-00-00', 
    tags text NOT NULL, 
    PRIMARY KEY (newsid) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=107 ;

Example

newsarchive.tags of record#1="Nepal, Everest, Kathmandu"

newsarchive.tags of record#2="Kathmandu,Pokhara"

newsarchive.tags of record#3="Everest,Pokhara"

tags(in page)="Nepal, Kathmandu"

Result should be:

record#1
record#2

I am using the SELECT statement with IN as

我正在使用带有IN的SELECT语句

SELECT * FROM newsarchive WHERE tags IN ('Nepal','Kathmandu') ORDER BY postdate;

Note:

I will be happy if the record with most tags matched appears first.

如果首先显示匹配最多标签的记录,我将很高兴。

2 个解决方案

#1

Using where tags in isn't going to work for your scenario.

使用where标签不适合您的方案。

The best approach would be to put those tags into a related table linked via foreign key to the record.

最好的方法是将这些标记放入通过外键链接到记录的相关表中。

If you do not have the ability to change the schema and your working with an RDBMS system that supports it you'll want to look into full text searching on MySQL or (SQL Server link here) in order to search that delimited string.

如果您无法更改架构并且无法使用支持它的RDBMS系统,那么您将需要查看MySQL上的全文搜索或(此处为SQL Server链接)以搜索分隔的字符串。

Outside of full text you'll have to split up the strings via their delimiter (a comma in this case) and search them individually via temp table, table variable, or other means. I've seen xml approaches to searching delimited strings that follow that rough concept on SQL Server.

在全文之外,您必须通过分隔符(在本例中为逗号)拆分字符串,并通过临时表,表变量或其他方式单独搜索它们。我已经看到xml方法来搜索在SQL Server上遵循粗略概念的分隔字符串。

On MySQL you may have to write your own string splitting function. Read through responses to linked question to see example approaches to splitting in MySQL.

在MySQL上,您可能必须编写自己的字符串拆分功能。阅读对链接问题的回复,以查看在MySQL中拆分的示例方法。

If you have to store multi value data in a single column (not recommended) then usually XML is a better way to go than delimited strings. MySQL offers some support for xpath queries etc.

如果必须在单个列中存储多值数据(不推荐),那么通常XML比分隔字符串更好。 MySQL为xpath查询等提供了一些支持。

#2

Your data is in a poor structure. You should have a table like:

您的数据结构不佳。你应该有一个像这样的表:

CREATE TABLE newstags ( 
    newstagsid int(11) NOT NULL auto_increment,
    newsid int(11) not null,
    tag text not null 
    PRIMARY KEY (newstagsid)
)

This would have one tag per story. An even better structure would be to store the tags in a separate table, and use tagid here instead of tag.

每个故事都有一个标签。一个更好的结构是将标签存储在一个单独的表中,并在这里使用tagid而不是tag。

But you don't have that. What can you do? You can parse the tags entered by the use in php. Then construct a SQL statement with the following condition:

但你没有那个。你能做什么?您可以解析在php中使用输入的标签。然后使用以下条件构造SQL语句:

(concat(',', tags, ',') like concat('%,', USERTAGS1, '%,') or
 concat(',', tags, ',') like concat('%,', USERTAGS2, '%,') or
 . . .
 concat(',', tags, ',') like concat('%,', USERTAGSn, '%,')
)

Another approach is to build a full text index on the tags, and then to use contains.

另一种方法是在标签上构建全文索引,然后使用contains。

#1