mysql图片对应多标签的设计,mysql – 按多个标签选择照片

我想你将不得不四次将标签表加入到照片表中……非常难看.

SELECT Photos.*

FROM

Photos

JOIN (

Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

) t1 ON (t1.photo = Photos.id)

JOIN (

Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

) t2 ON (t2.photo = Photos.id)

JOIN (

Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

) t3 ON (t3.photo = Photos.id)

JOIN (

Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

) t4 ON (t4.photo = Photos.id)

WHERE

(t1.name = 'dirty' AND t2.name = 'road')

AND (t3.name = 'light.front' OR t3.name = 'light.side')

AND (t4.name = 'perspective.two-point')

子查询可能会更快：

SELECT *

FROM Photos

WHERE

Photos.id IN (

SELECT Tagspohotos.photo

FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

WHERE Tags.name = 'dirty'

)

AND Photos.id IN (

SELECT Tagspohotos.photo

FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

WHERE Tags.name = 'road'

)

AND Photos.id IN (

SELECT Tagspohotos.photo

FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

WHERE Tags.name = 'light.front' OR Tags.name = 'light.side'

)

AND Photos.id IN (

SELECT Tagspohotos.photo

FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag)

WHERE Tags.name = 'perspective.two-point'

)