在 MySQL 数据库中搜索标签 [英] Searching a MySQL database for tags
问题描述
我正在努力创建一个系统,允许用户上传项目并为其添加标签",以帮助他们在搜索中可见.目前,我有一个这样工作的数据库:
I'm working on creating a system to allow users to upload items and add 'tags' to them to help them be visible in searches. Currently, I have a database that works like this:
id|title|tags
其中 tags
是用户自己输入的以逗号分隔的标签列表.我读到这是一种糟糕的方法,但是拥有一个标签表并将每个 ID 与项目记录一起存储基本上是一样的.
Where tags
is a comma-separated list of tags the user has entered themself. I've read that this is a terrible way to do it, but having a tags table and storing each ID along with the item record is basically the same thing.
我如何运行搜索以首先返回最相关的结果?我目前正在使用它,它有效,但不按相关性排序: SELECT * FROM items WHERE tags LIKE '%$tag%' LIMIT 0,20";
where $tag
只是一个标签,没有逗号(它在循环中).
How could I run a search to return the most relevant results first? I'm using this at the moment, which works, but doesn't sort by relevancy: SELECT * FROM items WHERE tags LIKE '%$tag%' LIMIT 0,20";
where $tag
is just a tag, no commas (it's inside a loop).
推荐答案
有一个标签表,将每个 ID 与项目记录一起存储基本上是一回事
没有.不.不.这绝对不是一回事.
NO. NO. NO. It's definitely not the same thing.
您使用逗号分隔列表"版本,并尝试提出查询以解决这些问题:
You take your "comma separated listed" version, and try to come up with the queries to accomplish these problems:
- 从所有标题中删除标签 ID #7
- 有多少标题使用标签 #87
使用正确规范化的表格:
With a properly normalized table:
- 从 users_tags 中删除 tag_id=7
- SELECT count(*) FROM users_tags WHERE tag_id = 87
使用您的版本:
- UPDATE users_tags SET tags=.... 在此处插入大量丑陋的字符串操作...
- SELECT count(*) FROM users_tags WHERE tag_id=87 OR tag_id='87,%' OR tag_id LIKE '%,87,%' 或 tag_id LIKE '%,87'
看到区别了吗?
这篇关于在 MySQL 数据库中搜索标签的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!