从 MySQL 数据库中提取类似项目的最佳方法 [英] Best way to pull similar items from a MySQL database

问题描述

我有一个项目数据库(与此相关的文章).
我想要做的是，我想根据两件事提取与特定项目相似的 X 个项目 - 标题，即文章的标题，以及位于另一个表中的标签.

I have a database of items (articles for that matter).
What I'd like to do, is I'd like to pull X items that are similar to a specific item, based on two things - title, which is the title of the article, and tags, which are located in another table.

结构如下(仅相关字段):

The structure is as follows (relevant fields only):

表格:文章
字段:articleid、title

Table: article
Fields: articleid, title

表格:标签
字段:tagid、tagtext

Table: tag
Fields: tagid, tagtext

表格:文章标签
字段:tagid、articleid

Table: articletag
Fields: tagid, articleid

最好的方法是什么?

推荐答案

对于 myisam 表，您可以使用自然语言全文搜索:http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

For myisam tables you can use Natural Language Full-Text search: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

SELECT * FROM article a
LEFT JOIN articletag at ON (at.articleid = a.articleid)
LEFT JOIN tag t ON (at.tagid = t.tagid)
WHERE MATCH (a.title) AGAINST ('some title' IN NATURAL LANGUAGE MODE)
OR MATCH (t.tagtext) AGAINST ('some tag' IN NATURAL LANGUAGE MODE)
GROUP BY a.articleid # if you don't want get duplicates 

您也可以考虑在文章表中的一个字段(例如)中添加有关标签的冗余信息，并在每次添加标签时更新它/移除.这将简化查询，它应该更快:

You can also think about adding redundant information about tags into one field (e.g. <taga><tagb><tagz>) in article table and update it each time tag is added/removed. This will simplify query and it should be faster:

SELECT * FROM article a
WHERE MATCH (a.title, a.tagtext) AGAINST ('some title or tag' IN NATURAL LANGUAGE MODE)

这篇关于从 MySQL 数据库中提取类似项目的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！