Shulou(Shulou.com)05/31 Report--

The purpose of this article is to share with you what the usage of MySQL 5.7NOT EXISTS is. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

There is no MINUS statement in MySQL. To implement the MINUS statement, you can use the NOT EXISTS statement.

View the data in the test table

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)

Query for a column of MSISDN that exists in card but does not exist in person.

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)

View the execution plan

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)

By executing the plan, the card table uses a full table scan and the person table uses a primary key index

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)

Since the primary keys of two tables are not the same column, an index is created for testing, and it is found that index scanning can never be used for card tables.

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)

The above is what the usage of MySQL 5.7NOT EXISTS is, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.