mysql选择数组中的数组

Question

我正在做一个有多个键盘或标签的表格，像这样:

id |   keyboards
===+================================================
1  |     picture,mountain,animals 
2  |     water,mountain
3  |     sound, mountain
4  |     water, picture, mountain, space, other

我想做一个选择。

SELECT id from table where (mountain,picture,space) in keyboards;

结果必须是:1 行

编号

4.

请帮助我。

Answer 1

你的数据库规范化很差。最好的方法是创建一个表 Keyboards

CREATE TABLE Keyboards
(
    Keyboard_ID INT AUTO_INCREMENT,
    KeyBoardName VARCHAR(25),
    CONSTRAINT kbrd_pk PRIMARY KEY (Keyboard_ID),
    CONSTRAINT kbrd_uq UNIQUE(KeyboardName)
)

CREATE table Tags
(
    ID INT AUTO_INCREMENT,
    Keyboard_ID int,
    CONSTRAINT tags_pk PRIMARY KEY (ID),
    CONSTRAINT tags_pk FOREIGN KEY (keyboard_ID) REFERENCES keyboards(keyboard_ID)
)

然后在 tags 表中引用 keyboards.id。

然后你可以做这个查询，

SELECT id,
FROM   tags a
       INNER JOIN keyboards b
           ON a.keyboard_id = b.keyboard_ID
WHERE  b.keyboardName IN ('mountain','picture','space')
GROUP BY ID
HAVING COUNT(DISTINCT b.keyboardName) = 3

但要回答你的问题，你可以简单地这样做

select *
from tags
where keyboards like concat('%','mountain','%') AND
      keyboards like concat('%','picture','%') and
      keyboards like concat('%','space','%')

SQLFiddle Demo