在mysql表中搜索标签的更好方法 [英] A better way to search for tags in mysql table

问题描述

假设我有一个表格，其中一列是标题标签，数据以逗号分隔，就像这样.

Say I have a table and one of the columns is titled tags with data that is comma separated like this.

"tag1,tag2,new york,tag4"

如您所见，有些标签会有空格.

As you can see, some of tags will have spaces.

查询表中任何等于new york"的标签的最佳或最准确的方法是什么?

Whats the best or most accurate way of querying the table for any tags that are equal to "new york"?

过去我使用过:

SELECT id WHERE find_in_set('new york',tags) <> 0 

但是当值有空格时 find_in_set 不起作用.

But find_in_set does not work when the value has a space.

我目前正在使用这个:

SELECT id WHERE concat(',',tags,',') LIKE concat(',%new york%,') 

但我不确定这是否是最好的方法.

But I'm not sure if this is the best approach.

你会怎么做?

推荐答案

当 Item A 可以与许多 item B 相关联，并且 item B 可以与许多 item A 相关联时，这称为 多对多关系

When Item A can be associated with many of item B, and item B can be associated with many of item A. This is called Many to many relationship

具有这些关系的数据应存储在单独的表中，并且仅在查询时连接在一起.

Data with these relationship should be stored in separate table and join together only on query.

示例

表一

| product_uid | price | amount |
|      1      | 12000 |  3000  |
|      2      | 30000 |   600  |

表 2

| tag_uid | tag_value |
|    1    |   tag_01  |
|    2    |   tag_02  |
|    3    |   tag_03  |
|    4    |   tag_04  |

然后我们使用一个连接表将它们关联起来

Then we use a join table to relate them

表 3

| entry_uid | product_uid | tag_uid |
|    1      |     1       |     3   |
|    2      |     1       |     4   |
|    3      |     2       |     1   |
|    4      |     2       |     2   |
|    5      |     4       |     2   |

查询将是(如果您想选择第一项和标签)

The query will be (If you want to select item one and the tag)

SELECT t1.*, t2.tag_value 
FROM Table1 as t1,
JOIN Table3 as join_table ON t1.product_uid = join_table.product_uid
JOIN Table2 as t2 ON t2.tag_uid = join_table.tag_uid
WHERE t1.product_uid = 1

这篇关于在mysql表中搜索标签的更好方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！