在MySQL中是否有“逆”函数?

The scenario is this: in a table A, I have one column "tags", which is varchar(255). In this column I store numbers, separated by commas, like this:

场景是这样的:在表a中，我有一个列“标记”，即varchar(255)。在这一列中，我存储了数字，用逗号分隔，如下:

2,14,31,33,56

2,14日,31日,33岁,56

etc. there can be none, one, or several.

没有一个，一个或几个。

and I need to make a SELECT query that will return rows that have a certain number in this field. right now I'm using this method (don't be alarmed, I know its a poor way.. that's why I'm asking for help!). for example, let's assume the number I want to check is 33. the query is:

我需要做一个SELECT查询来返回在这个字段中有一定数量的行。现在我正在用这个方法(不用担心，我知道它很糟糕。)这就是我请求帮助的原因!例如，假设我要检查的数字是33。这个查询的方法是:

SELECT * FROM table_a WHERE
tags LIKE "%,33,%" OR tags LIKE "33,%" OR tags LIKE "%,33" OR tags LIKE "33"

I'm no expert but I know this can't be the method. The first question that comes to mind is: is there a command similar to IN() but that works the other way around?

我不是专家，但我知道这不可能是方法。第一个问题是:是否有一个类似于()的命令，但它是反过来的?

I mean, can I tell it "find rows where 'tags' contains value 33" ?

我的意思是，我能告诉它“找到包含值33的行”吗?

When asking this question, I can see that there may be another field type other than varchar(255) to contain this type of data (an array of numbers, after all)

在问这个问题时，我可以看到除了varchar(255)之外，可能还有另一个字段类型(255)来包含这种类型的数据(毕竟是一组数字)

Is there a GOOD and efficient way of doing this? my method works for small tables, yes, but if the table grows.. (say, 10k rows, 50k, 300k ... ) this is obviously a problem.

这样做有什么好的和有效的方法吗?我的方法适用于小桌子，是的，但是如果桌子长了……(比方说，10k行，50k, 300k…)这显然是个问题。

1 个解决方案

#1

The function that you want is find_in_set():

您需要的函数是find_in_set():

SELECT *
FROM table_a
WHERE find_in_set(33, tags) > 0;

You can simplify your like statement to be:

你可以简化你喜欢的语句:

SELECT *
FROM table_a
WHERE concat(',', tags, ',') LIKE '%,33,%';

Neither of these can make use of an index. Having a separate table with one row per entity and per tag is the right way to go (but I think you know this already).

这两种方法都不能使用索引。拥有一个单独的表，每个实体和每个标记都是正确的方法(但我认为您已经知道了)。

#1