如何使用逗号分隔值查询字段中没有(数组值)的SQL行?

I have a SQL table, which looks like this:

我有一个SQL表，它是这样的:

id_question (int) | tags (varchar)
where "tags" field is
- either empty : NULL
- or is filled with one value (Ex: 1) (not numeric)
- or is filled with several comma separated values (ex: 273,2308,24) (not numeric)

id_question (int) |标签(varchar)，其中“标记”字段为空:NULL，或填充了一个值(Ex: 1)(不是数字)，或者填充了多个逗号分隔值(Ex: 273,2308,24)(不是数字)

id_question (int) | tags (varchar)
1 | 1,373
2 | 283,4555,308,12
3 | 283,25,3

id_question (int) |标签(varchar) 1 | 1373 2 | 283,4555,308,12 3 | 283,25,3。

ANd i have a blacklisted_tags array. I would like to retrieve id_questions of all questions whose tags field do not have a blacklisted $tags_blacklist value.

我有一个blacklisted_tags数组。我想检索所有的问题的id_questions，这些问题的标签字段没有黑名单$tags_blacklist值。

For example:
$tags_blacklist = array (1,3)
=> I should get 2
and not 1 because it has 1 in its tags field
and not 3 because it has 3 in its tags field.

例如:$tags_blacklist = array(1,3) =>我应该得到2而不是1，因为它的tags字段中有1，而不是3，因为它的tags字段中有3。

What should my SQL query look like?

我的SQL查询应该是什么样子?

2 个解决方案

#1

your database design violates law of normalization #1: NEVER STORE COMMA-SEPARATED LISTS.

您的数据库设计违反了规范化规则#1:永远不要存储逗号分隔的列表。

What you should have instead is this:

相反，你应该拥有的是:

id | tag
id |标签
1 | 1
1 | 1
1 | 373
1 | 373
2 | 283
2 | 283
2 | 4555
2 | 4555
2 | 308
2 | 308

etc.

等。

This way your query becomes as easy as

这样，您的查询就变得非常简单

SELECT DISTINCT id 
FROM YourTable
WHERE tag NOT IN (1, 3)

#2

first suggestion, change your tables to the following:

第一个建议，将你的表格改为:

question
---------
id

question_tag
------------
question_id
tag

blacklist
----------
tag

#1