MySQL NOT LIKE查询不起作用

I have 2 tables:

我有2张桌子：

posts
帖子
tags
标签

Tags table is structured like this:

标签表的结构如下：

post_id
POST_ID
tag
标签

So for every tag that's given for a post, I create a record in the tags table. If a post has 10 tags, there will be 10 records in tags table with that post_id.

因此，对于为帖子指定的每个标记，我在标记表中创建一条记录。如果帖子有10个标签，则标签表中将有10个记录与该post_id。

I'm now trying to build a search page where users can do a search for posts where tags do not contain the given keywords. This creates a problem though. A query like:

我现在正在尝试构建一个搜索页面，用户可以在其中搜索标签不包含给定关键字的帖子。但这会产生问题。像这样的查询：

SELECT DISTINCT posts.id, posts.title, posts.content
   FROM jobs, tags
   WHERE tags.tag NOT LIKE '%$keywords%' AND posts.id=tags.post_id

doesn't work because if a post has got 6 tags and one of them has got the keyword, it will still be returned because the other 5 records in the tags table don't have that keyword.

不起作用，因为如果一个帖子有6个标签，其中一个有关键字，它仍然会被返回，因为标签表中的其他5个记录没有该关键字。

What's the best way to solve this? Any way other than creating a new column in the posts table which stores all the comma-separated tags used only for search??

解决这个问题的最佳方法是什么？除了在posts表中创建一个新列以外，还有哪些方法可以存储所有用于搜索的逗号分隔标记？

1 个解决方案

#1

Make sure you have indexes or this will perform very badly:

确保你有索引或这将表现非常糟糕：

SELECT posts.id, posts.title, posts.content
FROM posts 
WHERE NOT EXISTS (
  SELECT post_id from tags
  WHERE tags.tag LIKE '%$keywords%' 
    AND posts.id=tags.post_id
)

This gets a list of all posts, excluding those that have a tag matching the tag you specified. (Your orginal query referenced a 'jobs' table. I assumed that was a typo for 'posts'.)

这将获得所有帖子的列表，不包括那些标签与您指定的标签匹配的帖子。（您的原始查询引用了'jobs'表。我认为这是'帖子'的拼写错误。）

Table aliases make this a little cleaner:

表别名使这个更清洁：

SELECT p.id, p.title, p.content
FROM posts p
WHERE NOT EXISTS (
  SELECT t.post_id from tags t
  WHERE t.tag LIKE '%$keywords%' 
    AND p.id=t.post_id
)

Then, I'd create these indexes:

然后，我将创建这些索引：

Posts: id, tag_id
Tags: post_id, tag

Then, run your query with 'explain' to see if it's performing well. Update your question with the results and someone will offer further advice. Index tuning is more trial and error than anything else so testing really is necessary.

然后，使用'explain'运行查询，看看它是否表现良好。用结果更新您的问题，有人会提供进一步的建议。索引调优比其他任何东西都更加试验和错误，所以测试确实是必要的。

#1