MySQL - 如果查询中的任何单词匹配，则在多列中使用REGEX选择一行

Question

I have three tables:

1. post
2. tags
3. past_tags

So, I want to make a simple search to get the post ID using it's title only, related tags only, and title and tag.

To explain my point take this example:

In post table I have columns named (post_id, post_title)

Example: post_id: 1 - post_title: my new super car

In tags table I have columns named (tag_id, tag_name)

Example: tag_id: 5 - tag_name: red

In post_tags I record the post_id of "post" and the tag_id of "tag":

Example: post_id: 1 - tag_id: 5

so each post can has many tags (a simple relationship).

I want to select the post post_id if I enter any of these queries:

1. supper car
2. red car
3. red super car
4. super red car
5. red

It's a kind of merging the results and match the post_id even if the query is not related to one column.

Thank you.

Answer 1

For "word" matching, learn about FULLTEXT indexing. REGEXP is less practical.