本篇文章給大家分享的是有關(guān)MySQL 5.7 NOT EXISTS的用法是什么，小編覺得挺實用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。
成都創(chuàng)新互聯(lián)是專業(yè)的普蘭店網(wǎng)站建設(shè)公司，普蘭店接單;提供網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計,網(wǎng)頁設(shè)計,網(wǎng)站設(shè)計,建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行普蘭店網(wǎng)站開發(fā)網(wǎng)頁制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊,希望更多企業(yè)前來合作!
在MySQL中沒有MINUS語句，要想實現(xiàn)MINUS語句，可以使用NOT EXISTS語句。
查看測試表中的數(shù)據(jù)
mysql> select * from person;
| MSISDN | IMEI | IMSI |
| +3301000000000 | 129980000000000 | 208011000000000 |
| +3301000000001 | 129980000000100 | 208011000000001 |
| +3301000000002 | 129980000000200 | 208011000000002 |
| +3301000000003 | 129980000000300 | 208011000000003 |
| +3301000000004 | 129980000000400 | 208011000000004 |
| +3301000000005 | 129980000000500 | 208011000000005 |
| +3301000000006 | 129980000000600 | 208011000000006 |
| +3301000000007 | 129980000000700 | 208011000000007 |
8 rows in set (0.00 sec)
mysql> select * from card;
| IMSI | MSISDN | IMEI |
| 208011000000000 | +3301000000000 | 129980000000000 |
| 208011000000001 | +3301000000001 | 129980000000100 |
| 208011000000002 | +3301000000002 | 129980000000200 |
| 208011000000003 | +3301000000003 | 129980000000300 |
| 208011000000004 | +3301000000004 | 129980000000400 |
| 208011000000005 | +3301000000005 | 129980000000500 |
| 208011000000006 | +3301000000006 | 129980000000600 |
| 208011000000007 | +3301000000007 | 129980000000700 |
| 208011000000008 | +3301000000008 | 129980000000800 |
| 208011000000009 | +3301000000009 | 129980000000900 |
10 rows in set (0.00 sec)
mysql> select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
| IMSI | MSISDN | IMEI |
| 208011000000008 | +3301000000008 | 129980000000800 |
| 208011000000009 | +3301000000009 | 129980000000900 |
2 rows in set (0.00 sec)
查看執(zhí)行計劃
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 20 | fire.a.MSISDN | 1 | 100.00 | Using where; Using index |
2 rows in set, 2 warnings (0.00 sec)
通過執(zhí)行計劃，card表使用了全表掃描，person表使用了主鍵索引
mysql> show keys from card;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| card | 0 | PRIMARY | 1 | IMSI | A | 12 | NULL | NULL | | BTREE | | |
6 rows in set (0.00 sec)
mysql> show keys from person;
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
| person | 0 | PRIMARY | 1 | MSISDN | A | 8 | NULL | NULL | | BTREE | | |
1 row in set (0.00 sec)
由于兩張表的主鍵不是相同列，創(chuàng)建索引進(jìn)行測試，發(fā)現(xiàn)card表始終無法使用索引掃描
mysql> create index idx_card_msisdn on card(msisdn);
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 20 | fire.a.MSISDN | 1 | 100.00 | Using where; Using index |
2 rows in set, 2 warnings (0.00 sec)
mysql> create index idx_card_msisdn_imsi on card(msisdn,imsi);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 20 | fire.a.MSISDN | 1 | 100.00 | Using where; Using index |
2 rows in set, 2 warnings (0.00 sec)
mysql> create index idx_card_imsi_msisdn on card(imsi,msisdn);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 20 | fire.a.MSISDN | 1 | 100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> explain select * from card a where not exists(select * from person b where a.MSISDN=b.MSISDN);
+----+--------------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 20 | fire.a.MSISDN | 1 | 100.00 | Using where; Using index |
2 rows in set, 2 warnings (0.00 sec)
以上就是MySQL 5.7 NOT EXISTS的用法是什么，小編相信有部分知識點可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
重慶分公司
重慶分公司
