在MySQL数据库中搜索标签

Question

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

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.

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).

Answer 1

having a tags table and storing each ID along with the item record is basically the same thing

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:

1. delete tag ID #7 from all titles
2. How many titles use tag #87

With a properly normalized table:

1. DELETE FROM users_tags WHERE tag_id=7
2. SELECT count(*) FROM users_tags WHERE tag_id = 87

With your version:

1. UPDATE users_tags SET tags=.... insert massively ugly string operation here ...
2. SELECT count(*) FROM users_tags WHERE tag_id=87 OR tag_id='87,%' OR tag_id LIKE '%,87,%' or tag_id LIKE '%,87'

see the difference?