本篇文章給大家分享的是有關MySQL 5.7 NOT EXISTS的用法是什么,小編覺得挺實用的,因此分享給大家學習,希望大家閱讀完這篇文章后可以有所收獲,話不多說,跟著小編一起來看看吧。
成都創新互聯是專業的普蘭店網站建設公司,普蘭店接單;提供網站建設、成都網站設計,網頁設計,網站設計,建網站,PHP網站建設等專業做網站服務;采用PHP框架,可快速的進行普蘭店網站開發網頁制作和功能擴展;專業做搜索引擎喜愛的網站,專業的做網站團隊,希望更多企業前來合作!
在MySQL中沒有MINUS語句,要想實現MINUS語句,可以使用NOT EXISTS語句。
查看測試表中的數據
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)
查看執行計劃
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)
通過執行計劃,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)
由于兩張表的主鍵不是相同列,創建索引進行測試,發現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的用法是什么,小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注創新互聯行業資訊頻道。
文章名稱:MySQL5.7NOTEXISTS的用法是什么
標題來源:http://m.newbst.com/article0/jheeio.html
成都網站建設公司_創新互聯,為您提供App設計、用戶體驗、網站策劃、網站排名、品牌網站設計、面包屑導航
聲明:本網站發布的內容(圖片、視頻和文字)以用戶投稿、用戶轉載內容為主,如果涉及侵權請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網站立場,如需處理請聯系客服。電話:028-86922220;郵箱:631063699@qq.com。內容未經允許不得轉載,或轉載時需注明來源: 創新互聯