mysql所有的标签,MySQL-选择所有带有多个标签的项目

I'm making hashtag system for my webpage, and have three tables:

items - ID and some others

tags - ID and name

item_tags - junction table with IDitem and IDtag

Selecting all items with given hashtag is pretty easy:

SELECT items.* FROM items

join item_tags on items.ID=item_tags.IDitem

join tags on item_tags.IDtag=tags.ID

where tags.name="something";

Question is, what should I do if I want to select all items with multiple tags, for example, find all items tagged as cat and animal?

I've thought about making temporary table, inserting all items with first tag, then leaving those with second tag, then third, then fourth and so on, but it doesn't look too good and too fast.

解决方案

well you know your list, so that is a simple string. and you know your count. these can be jammed into a mysql Prepared Statement and executed.

But below it is with the list and count plopped in just for demo purposes.

create table items

( id int not null

);

create table tags

( id int not null,

name varchar(50)

);

create table item_tags

( iid int not null,

tid int not null

);

insert items (id) values (1),(2),(3),(4);

insert tags(id,name) values (1,'cat'),(2,'animal'),(3,'has nose');

-- note, everything has a nose so far:

insert item_tags (iid,tid) values (1,1),(1,3),(2,1),(2,3),(3,2),(3,3),(4,1),(4,2),(4,3);

select i.id,count(i.id)

from items i

join item_tags junc

on junc.iid=i.id

join tags t

on t.id=junc.tid and t.name in ('cat','animal')

group by i.id

having count(i.id)=2

-- only item 4 has both cat and animal (note nose is irrelevant)