mySQL在数组中搜索id

I want to filter my SQL query with some array of IDs.

我想用一些ID数组过滤我的SQL查询。

so I have IDs for example: 2,3,4 and activity_meta.value 1,2,5; And I want to find every activity where it has id in activity_meta.value

所以我有ID例如：2,3,4和activity_meta.value 1,2,5;我想在activity_meta.value中找到它具有id的每个活动

SELECT DISTINCT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
FROM wp_bp_activity a
LEFT JOIN wp_users u
ON a.user_id = u.ID
INNER JOIN wp_bp_activity_meta
ON (a.id = wp_bp_activity_meta.activity_id)
WHERE a.is_spam = 0
AND a.hide_sitewide = 0
AND a.type != 'activity_comment'
AND  (wp_bp_activity_meta.meta_key = 'activity_tagz' )
DESC LIMIT 0, 20

I want to add AND (1,2,3 IN wp_bp_activity_meta.meta_value) I just dont know how to treat serialized array;

我想添加AND（1,2,3 IN wp_bp_activity_meta.meta_value）我只是不知道如何处理序列化数组;

1 个解决方案

#1

The value of activity_meta.value is not normalized. It would be a better choice, to create a second table, where you assign the meta tags to your element.

activity_meta.value的值未规范化。这是一个更好的选择，创建第二个表，您可以在其中为元素分配元标记。

So, it would look like:

所以，它看起来像：

wp_bp_activity_id | meta
1              | 1
1              | 2
1              | 3

let's cal this table "meta_relation". Then you simple could either use a subselect, like this:

让这个表“meta_relation”。然后你就可以使用subselect，就像这样：

... WHERE wp_bp_activity.id in (SELECT wp_bp_activity_id FROM meta_relation WHERE meta in (1,2,5))

Or you could implement this with another join, like

或者你可以用另一个连接实现这个，比如

... INNER JOIN meta_relation ON wp_bp_activitiy.id = meta_relation.wp_bp_activity_id

(will return 3 rows then for 3 matching tags)

（将返回3行，然后返回3个匹配的标签）

... WHERE meta in (1,2,5)
... GROUP BY wp_bp_activity.id

(will remove any duplicate result and unwanted tags)

（将删除任何重复的结果和不需要的标签）

For your current scenario you could use a workaround. However this requires to build up the query programmatically:

对于您当前的方案，您可以使用解决方法。但是，这需要以编程方式构建查询：

For each "Tag", you want to find (i.e. 1,2,5) you need to add another or-condition.

对于每个“标签”，您要查找（即1,2,5）您需要添加另一个或条件。

To make sure you are not matching the 2 within 125 you can sourround it by ,.
为了确保你不匹配125中的2，你可以使它变得更好。
To make sure, you are not missing the FIRST or LAST item (which has no leading / trailing , you need to concatenate the column with 2 more , in the first place:
为了确保，您不会错过FIRST或LAST项目（没有前导/尾随，您需要将列连接到另外2个，首先：

Query then contains the following additional criterias.

然后查询包含以下附加标准。

SELECT
   ....
WHERE 
   ... 
AND
(
   CONCAT(CONCAT(",", activity_meta.value ), ",") LIKE "%,1,%" OR
   CONCAT(CONCAT(",", activity_meta.value ), ",") LIKE "%,2,%" OR
   CONCAT(CONCAT(",", activity_meta.value ), ",") LIKE "%,5,%"
)

if you want all 3 tags to appear, use AND instead of OR.

如果要显示所有3个标记，请使用AND而不是OR。

in your example, this will match :

在您的示例中，这将匹配：

,2,3,4, against ,1, 
,2,3,4, against ,2, //match
,2,3,4, against ,5,

Order and / or gaps don't matter with this approach.

订单和/或差距与此方法无关。

(Depending on whether its a 10 User Website or a Million-Customer-Portal, you can use either. Preferred Solution is to normalize your table.)

（根据其是10个用户网站还是百万客户门户网站，您可以使用其中任何一个。首选解决方案是规范化您的表格。）

#1