mysql 排除行,MYSQL：如果一个连接的行匹配条件,则排除多个行

我有两张桌子.一个是用户,其中包含字段uid和名称.另一个表是users_roles,其中包含字段uid和rid(role id).

我想要检索没有任何一组提供的角色的用户列表.

例如：

uid | name

----------

1 | BOB

2 | DAVE

3 | JOHN

USERS_ROLES：

uid | rid

---------

1 | 1

1 | 2

1 | 3

2 | 1

3 | 1

我希望能够只查询没有一组rids的用户.例如,排除rid 2和3的查询应该返回DAVE和JOHN,或者排除rids(1,3)的查询应该返回nobody.

解决方法:

使用反连接模式的查询有时是最有效的：

SELECT u.uid

, u.name

FROM users u

LEFT

JOIN users_roles r

ON r.uid = u.uid

AND r.rid IN (2,3)

WHERE r.uid IS NULL

反连接模式是LEFT [outer] JOIN user_roles表以拉回所有匹配的行,AND来从没有匹配行的用户获取行. “技巧”是在WHERE子句中使用谓词排除所有匹配行,从而消除具有匹配项的用户的所有行.

使用NOT EXISTS相关子查询可以获得等效的结果集：

SELECT u.uid

, u.name

FROM users u

WHERE NOT EXISTS

( SELECT 1

FROM users_roles r

WHERE r.uid = u.uid

AND r.rid IN (2,3)

)

另一种方法是使用NOT IN,尽管有时效率较低.性能取决于许多因素.优化器可以为每个查询生成不同的执行计划.

在任何情况下,为了获得最佳性能,您需要一个索引… ON users_roles(uid)或ON users_roles(uid,rid).

跟进：

我的MySQL 5.1.34服务器上的性能测试表明,反连接查询的速度几乎是等效的NOT EXISTS和NOT IN查询的两倍. (1.091秒对2.066秒和2.020秒)

-- setup and populate test tables

CREATE TABLE t_users

( uid INT UNSIGNED NOT NULL PRIMARY KEY

, `name` VARCHAR(50)

) ENGINE=INNODB DEFAULT CHARSET=latin1 ;

CREATE TABLE t_users_roles

( uid INT UNSIGNED NOT NULL

, rid INT UNSIGNED NOT NULL

, PRIMARY KEY (uid,rid)

) ENGINE=INNODB DEFAULT CHARSET=latin1;

ALTER TABLE t_users_roles ADD CONSTRAINT FK_t_users_roles_t_users FOREIGN KEY (uid) REFERENCES t_users (uid);

CREATE INDEX t_users_ix1 ON t_users (uid,`name`);

CREATE INDEX t_users_roles_ix1 ON t_users_roles (rid,uid);

INSERT INTO t_users (uid,`name`)

SELECT d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1 AS uid

, CONCAT('NAME',LPAD(d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1,8,'-')) AS `name`

FROM (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) o

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) h

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) u

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d

;

-- 1000000 row(s) affected.

INSERT INTO t_users_roles (uid,rid)

SELECT d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1 AS uid

, r.rid

FROM (SELECT 1 AS rid UNION ALL SELECT 2 UNION ALL SELECT 3) r

CROSS

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) o

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) h

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) u

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e

JOIN (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d

WHERE (d.d*100000+e.d*10000+u.d*1000+h.d*100+t.d*10+o.d+1) % 100000 <> 0

;

-- 2999970 row(s) affected

OPTIMIZE TABLE t_users;

OPTIMIZE TABLE t_users_roles;

SHOW STATUS LIKE 'Qcache_hits' ;

-- Variable_name Value

-- ------------- ---------

-- Qcache_hits 1117342

SHOW VARIABLES LIKE 'version' ;

-- Variable_name Value

-- ------------- ------------

-- version 5.1.53-log

-- table size from the file system

$du -sh DATA/test/t_users*.ibd

72M DATA/test/t_users.ibd

133M DATA/test/t_users_roles.ibd

-- anti-join query

SELECT SQL_NO_CACHE u.uid

, u.name

FROM t_users u

LEFT

JOIN t_users_roles r

ON r.uid = u.uid

AND r.rid IN (2,3)

WHERE r.uid IS NULL ;

-- Exec: 1.095 sec

-- Exec: 1.090 sec

-- Exec: 1.091 sec

-- Exec: 1.087 sec

-- Exec: 1.090 sec

-- avg 5 executions: 1.091 sec

-- not exists query

SELECT SQL_NO_CACHE u.uid

, u.name

FROM t_users u

WHERE NOT EXISTS

( SELECT 1

FROM t_users_roles r

WHERE r.uid = u.uid

AND r.rid IN (2,3)

) ;

-- Exec: 2.071 sec

-- Exec: 2.066 sec

-- Exec: 2.059 sec

-- Exec: 2.065 sec

-- Exec: 2.070 sec

-- avg 5 executions: 2.066 sec

-- not in query

SELECT SQL_NO_CACHE u.uid

, u.name

FROM t_users u

WHERE u.uid NOT IN

( SELECT r.uid

FROM t_users_roles r

WHERE r.uid IS NOT NULL

AND r.rid IN (2,3)

) ;

-- Exec: 2.022 sec

-- Exec: 2.023 sec

-- Exec: 2.014 sec

-- Exec: 2.026 sec

-- Exec: 2.016 sec

-- avg 5 executions: 2.020 sec

SHOW STATUS LIKE 'Qcache_hits' ;

-- Variable_name Value

-- ------------- ---------

-- Qcache_hits 1117342

EXPLAIN output for three statements:

-- ANTI JOIN

id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------------------ ------ -------------- ------------------------- ----------- ------- ----- ------- -------- ------------------------------------

1 SIMPLE u index t_users_ix1 57 1000423 100.00 Using index

1 SIMPLE r ref PRIMARY,t_users_roles_ix1 PRIMARY 4 u.uid 1 100.00 Using where; Using index; Not exists

-- NOT EXISTS

id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------------------ ------ -------------- ------------------------- ----------- ------- ----- ------- -------- --------------------------

1 PRIMARY u index t_users_ix1 57 1000423 100.00 Using where; Using index

2 DEPENDENT SUBQUERY r ref PRIMARY,t_users_roles_ix1 PRIMARY 4 u.uid 1 100.00 Using where; Using index

-- NOT IN

id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------------------ ------ -------------- ------------------------- ----------- ------- ------ ------- -------- --------------------------

1 PRIMARY u index t_users_ix1 57 1000423 100.00 Using where; Using index

2 DEPENDENT SUBQUERY r index_subquery PRIMARY,t_users_roles_ix1 PRIMARY 4 func 1 100.00 Using index; Using where

标签：sql,mysql,drupal

来源： https://codeday.me/bug/20190725/1535731.html