SQL阵列搜寻 [英] SQL Array Search

问题描述

我的网站上具有群组功能，该功能允许1个用户将其任何朋友的用户ID放入被邀请的用户列.我需要执行SQL搜索以确定登录用户的ID是否已由其朋友存储在任何邀请用户列中. 如何通过sql搜索做到这一点?

I have a group feature on my website that allows 1 user to put the userids of any of his or her friends into an invitedusers column. I need to do an SQL search to determine whether the logged-in user's ID has been stored in any invitedusers columns by his or her friends. How can I do this with an sql search?

EX:我的专栏是受邀请的用户，并且在该专栏中存储了以下内容(234,394,479) Userid 234登录后，我需要进行搜索以查看是否有任何列的用户ID 234存储在被邀请的用户列中.

EX: My column is invitedusers, and inside that column is stored the following, (234,394,479) When Userid 234 has logged in, I need to search to see whether any columns have userid 234 stored inside the invitedusers column.

SELECT userid, name... FROM mytable WHERE invitedusers = ?

*我与FIND_IN_SET一起查看了SQL IN运算符，但都没有帮助.*

*I looked at the SQL IN operator along with FIND_IN_SET but no help with either.*

推荐答案

假定您确实使用234,394,479作为一列的值(至少应该使用,234,394,479,能够在您的查询)，您应该重建用户表，删除字段invited_users并创建表，如下所示:

Assuming that you're really using 234,394,479 as value of one column (you at least should use ,234,394,479, to be able to do WHERE invited LIKE '%,234,%' in your query) you should rebuild your user tables, remove field invited_users and create table like this:

CREATE TABLE invited_users (
  id INT AUTO_INCREMENT,
  owner_id INT, -- Who's input it is
  target_id INT, -- What's the target user
  PRIMARY KEY ( id),
  UNIQUE ( owner_id, target_id),
  -- Indexes (FOREIGN KEYs!) to users table
);

并不仅仅是选择邀请用户234查询的用户列表:

And than just select list of users who invited user 234 with query:

SELECT users.id, users.name
FROM invited_users
INNER JOIN users ON invited_users.owner_id = users.id
GROUP BY users.id
WHERE invited_users.target_id = 234

这篇关于SQL阵列搜寻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！