MysqlExists与in_在MySQL里，有个和in一样的东东叫做exists，但是它比in更牛叉，你会么？...

我们在学习Yii2的时候&＃xff0c;一定接触过这样的where输入

$query->where(["exists",xxxx]);

User::find()->where(["exists",xxxx])->all();

是的&＃xff0c;这是MYSQL的exists关键词&＃xff0c;今天我们就来说说这个exist&＃xff0c;为了给大家更清楚的讲解&＃xff0c;先给大家说下本文目录&＃xff1a;

什么是exists

exists和in的区别和使用场景

使用Yii2的Query Builder实现一个exists语句

要自己看哈。

提前准备

为了大家学习方便&＃xff0c;北哥在这里面建立两张表并为其添加一些数据

一张会员表&＃xff0c;一张会员下单表。

会员表数据

id

user

email

1

abei

abei&＃64;nai8.me

2

wh

abei&＃64;maige123.com

3

liuhuan

267765&＃64;qq.com

订单表

id

user_id

create_time

...

1

1

1489579802

...

2

2

1489579802

...

3

1

1489579802

...

4

3

1489579802

...

5

2

1489579802

...

6

1

1489579802

...

我们将用这两张表做演示。

什么是exists

exists表示存在&＃xff0c;它常常和子查询配合使用&＃xff0c;例如下面的SQL语句

SELECT * FROM &＃96;user&＃96;

WHERE exists (SELECT * FROM &＃96;order&＃96; WHERE user.id &＃61; order.user_id)

exists用于检查子查询是否至少会返回一行数据&＃xff0c;该子查询实际上并不返回任何数据&＃xff0c;而是返回值True或False。

当子查询返回为真时&＃xff0c;则外层查询语句将进行查询。

当子查询返回为假时&＃xff0c;外层查询语句将不进行查询或者查询不出任何记录。

因此上面的SQL语句旨在搜索出所有下过单的会员。需要注意的是&＃xff0c;当我们的子查询为 SELECT NULL 时&＃xff0c;MYSQL仍然认为它是True。

exists和in的区别和使用场景

是的&＃xff0c;其实上面的例子&＃xff0c;in这货也能完成&＃xff0c;如下面SQL语句

SELECT * FROM &＃96;user&＃96;

WHERE id in (SELECT user_id FROM &＃96;order&＃96;)

那么&＃xff01;in和exists到底有啥区别那&＃xff0c;要什么时候用in&＃xff0c;什么时候用exists那&＃xff1f;接下来阿北一一教你。

我们先记住口诀再说细节&＃xff01;“外层查询表小于子查询表&＃xff0c;则用exists&＃xff0c;外层查询表大于子查询表&＃xff0c;则用in&＃xff0c;如果外层和子查询表差不多&＃xff0c;则爱用哪个用哪个。”

In关键字原理

SELECT * FROM &＃96;user&＃96;

WHERE id in (SELECT user_id FROM &＃96;order&＃96;)

in()语句只会执行一次&＃xff0c;它查出order表中的所有user_id字段并且缓存起来&＃xff0c;之后&＃xff0c;检查user表的id是否和order表中的user_id相当&＃xff0c;如果相等则加入结果期&＃xff0c;直到遍历完user的所有记录。

in的查询过程类似于以下过程

$result &＃61; [];

$users &＃61; "SELECT * FROM &＃96;user&＃96;";

$orders &＃61; "SELECT user_id FROM &＃96;order&＃96;";

for($i &＃61; 0;$i <$users.length;$i&＃43;&＃43;){

for($j &＃61; 0;$j <$orders.length;$j&＃43;&＃43;){

// 此过程为内存操作&＃xff0c;不涉及数据库查询。

if($users[$i].id &＃61;&＃61; $orders[$j].user_id){

$result[] &＃61; $users[$i];

break;

}

}

}

我想你已经看出来了&＃xff0c;当order表数据很大的时候不适合用in&＃xff0c;因为它最多会将order表数据全部遍历一次。

如&＃xff1a;user表有10000条记录,order表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.

再如&＃xff1a;user表有10000条记录,order表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

exists关键字原理

SELECT * FROM &＃96;user&＃96;

WHERE exists (SELECT * FROM &＃96;order&＃96; WHERE user.id &＃61; order.user_id)

在这里&＃xff0c;exists语句会执行user.length次&＃xff0c;它并不会去缓存exists的结果集&＃xff0c;因为这个结果集并不重要&＃xff0c;你只需要返回真假即可。

exists的查询过程类似于以下过程

$result &＃61; [];

$users &＃61; "SELECT * FROM &＃96;user&＃96;";

for($i&＃61;0;$i

if(exists($users[$i].id)){// 执行SELECT * FROM &＃96;order&＃96; WHERE user.id &＃61; order.user_id

$result[] &＃61; $users[$i];

}

}

你看到了吧&＃xff0c;当order表比user表大很多的时候&＃xff0c;使用exists是再恰当不过了&＃xff0c;它没有那么多遍历操作,只需要再执行一次查询就行。

如:user表有10000条记录,order表有1000000条记录,那么exists()会执行10000次去判断user表中的id是否与order表中的user_id相等.

如:user表有10000条记录,order表有100000000条记录,那么exists()还是执行10000次,因为它只执行user.length次,可见B表数据越多,越适合exists()发挥效果.

但是&＃xff1a;user表有10000条记录,order表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

因此我们只需要记住口诀&＃xff1a;“外层查询表小于子查询表&＃xff0c;则用exists&＃xff0c;外层查询表大于子查询表&＃xff0c;则用in&＃xff0c;如果外层和子查询表差不多&＃xff0c;则爱用哪个用哪个。”

Yii2使用exists

我想我只需要写一个Query Builder的用法&＃xff0c;其他你应该能举一反三了吧

$query &＃61; new Query();

$query->from("user")

->where(["exists",(new Query())->select(&＃39;user_id&＃39;)->from(&＃39;order&＃39;)->where([&＃39;user.id&＃39; &＃61;> &＃39;order.user_id&＃39;])]);