MySQL 从具有多个 where 子句和 find_in_set 的多个表中进行选择

Question

我在尝试使用 MySQL 查询避免部分重复结果时遇到问题。我承认我真的是 MySQL 的新手，但我从对 SO 的研究中了解到，我将在下面为您布置的架构绝对可以做得更好(用户表的 linked_users 列应该是一个单独的表).但是，我现在无法更改它的设置方式。

我正在尝试返回分配给 t2 中每个项目的用户 ID 的用户名或链接到这些用户的用户的用户名。但是，查询为每个项目返回两组名称。我认为发生这种情况是因为它对它们进行了两次搜索，并且我试图阅读 this tutorial关于从多个表返回多个值的问题，但我似乎无法全神贯注于 JOINS 和 UNIONS 等等。

我的问题有两个方面:

1. 如何在不更改数据库设置方式的情况下解决此问题？
2. 应如何更改数据库以便将来更好地支持此类查询？

感谢您的宝贵时间。

架构:

create table users (user_id int, user_name varchar (55), linked_users varchar (55));
insert into users( user_id, user_name, linked_users)values(1, 'user1', '2,154,4,45');
insert into users( user_id, user_name, linked_users)values(2, 'user2', '13,1,200');
create table t2 (t2_id int, user_id int);
insert into t2( t2_id, user_id)values(1, 2);
insert into t2( t2_id, user_id)values(2, 1);
insert into t2( t2_id, user_id)values(3, 1);
insert into t2( t2_id, user_id)values(4, 2);
insert into t2( t2_id, user_id)values(5, 3);

查询:

SELECT t.*, u.user_name 
FROM t2 t, users u 
WHERE t.user_id = u.user_id
OR find_in_set(t.user_id, u.linked_users) > 0

fiddle :http://sqlfiddle.com/#!9/c5540/10

Answer 1

您绝对应该更改表的结构。正确的结构将使用两个表，一个用于实体(“用户”)，另一个用于它们之间的关系:

create table users (
    user_id int not null primary key auto_increment,
    user_name varchar(55)
);

create table userlinks (
    user_id int not null references users(user_id),
    linked_user_id int not null references users(user_id)
);

创建表 t2 ( t2_id 整数， user_id int not null 引用 users(user_id));

您的结构有几个明显的缺陷:

• 您将整数值存储为它们的字符串表示形式。
• 您将列表存储在字符串中，而 SQL 几乎不支持逗号分隔列表。
• 您没有定义主键或外键。

使用此结构，您可以使用 exists 获取给定“t”值的用户列表，没有重复且没有 distinct:

select u.user_id
from users u cross join
     (select user_id from t2 where t2.tid = 1) t
where u.user_id = t.user_id or
      exists (select 1
              from ul
              where ul.user_id = t.user_id and
                    u.user_id = ul.linked_user_id
             );

查询说:“获取用户 ID 与 t2 中相应 ID 匹配或用户链接到 t2< 中的 user_id 的所有用户。”

请注意，您还可以通过以下方式对您的结构执行此操作:

select u.user_id
from users u cross join
     (select user_id from t2 where t2.tid = 1) t
where u.user_id = t.user_id or
      find_in_set(u.user_id,
                  (select u.linked_users from users u2 where u2.user_id = t.user_id)
                 ) > 0;