exists 条件句防止插入重复记录
exists 和 not exists 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有元素。
差集包含只属于两个集合中的第一个集合的元素
mysql教程> create table books(
-> bookid smallint not null primary key,
-> booktitle varchar(60) not null,
-> copyright year not null
-> )
-> engine=innodb;
query ok, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> insert into books values (12786, 'java', 1934),
-> (13331, 'mysql', 1919),
-> (14356, 'php教程', 1966),
-> (15729, 'perl', 1932),
-> (16284, 'oracle', 1996),
-> (17695, 'pl/sql', 1980),
-> (19264, '网页特效', 1992),
-> (19354, 'www.java2s.com', 1993);
query ok, 8 rows affected (0.03 sec)
records: 8 duplicates: 0 warnings: 0
mysql>
mysql>
mysql> create table authors(
-> authid smallint not null primary key,
-> authfn varchar(20),
-> authmn varchar(20),
-> authln varchar(20)
-> )
-> engine=innodb;
query ok, 0 rows affected (0.09 sec)
mysql>
mysql>
mysql> insert into authors values (1006, 'h', 's.', 't'),
-> (1007, 'j', 'c', 'o'),
-> (1008, 'b', null, 'e'),
-> (1009, 'r', 'm', 'r'),
-> (1010, 'j', 'k', 't'),
-> (1011, 'j', 'g.', 'n'),
-> (1012, 'a', null, 'p'),
-> (1013, 'a', null, 'w'),
-> (1014, 'n', null, 'a');
query ok, 9 rows affected (0.03 sec)
records: 9 duplicates: 0 warnings: 0
mysql>
mysql>
mysql> create table authorbook(
-> authid smallint not null,
-> bookid smallint not null,
-> primary key (authid, bookid),
-> foreign key (authid) references authors (authid),
-> foreign key (bookid) references books (bookid)
-> )
-> engine=innodb;
query ok, 0 rows affected (0.09 sec)
mysql>
mysql>
mysql> insert into authorbook values (1006, 14356),
-> (1008, 15729),
-> (1009, 12786),
-> (1010, 17695),
-> (1011, 15729),
-> (1012, 19264),
-> (1012, 19354),
-> (1014, 16284);
query ok, 8 rows affected (0.03 sec)
records: 8 duplicates: 0 warnings: 0
mysql>
mysql>
mysql> select * from authors;
+--------+--------+--------+--------+
| authid | authfn | authmn | authln |
+--------+--------+--------+--------+
| 1006 | h | s. | t |
| 1007 | j | c | o |
| 1008 | b | null | e |
| 1009 | r | m | r |
| 1010 | j | k | t |
| 1011 | j | g. | n |
| 1012 | a | null | p |
| 1013 | a | null | w |
| 1014 | n | null | a |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from books;
+--------+----------------+-----------+
| bookid | booktitle | copyright |
+--------+----------------+-----------+
| 12786 | java | 1934 |
| 13331 | mysql | 1919 |
| 14356 | php | 1966 |
| 15729 | perl | 1932 |
| 16284 | oracle | 1996 |
| 17695 | pl/sql | 1980 |
| 19264 | javascript | 1992 |
| 19354 | www.java2s.com | 1993 |
+--------+----------------+-----------+
8 rows in set (0.00 sec)
mysql> select * from authorbook;
+--------+--------+
| authid | bookid |
+--------+--------+
| 1009 | 12786 |
| 1006 | 14356 |
| 1008 | 15729 |
| 1011 | 15729 |
| 1014 | 16284 |
| 1010 | 17695 |
| 1012 | 19264 |
| 1012 | 19354 |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> select bookid, booktitle
-> from books as b
-> where not exists
-> (
-> select bookid
-> from authorbook as ab
-> where b.bookid=ab.bookid
-> )
-> order by booktitle;
+--------+-----------+
| bookid | booktitle |
+--------+-----------+
| 13331 | mysql |
+--------+-----------+
1 row in set (0.00 sec)
mysql>
mysql> drop table authorbook;
query ok, 0 rows affected (0.03 sec)
mysql> drop table books;
query ok, 0 rows affected (0.05 sec)
mysql> drop table authors;
query ok, 0 rows affected (0.05 sec)
可以通过使用 exists 条件句防止插入重复记录。
示例一：插入多条记录
假设有一个主键为 client_id 的 clients 表，可以使用下面的语句：
code:
insert into clients
(client_id, client_name, client_type)
select supplier_id, supplier_name, 'advertising'
from suppliers
where not exists (select * from clients
where clients.client_id = suppliers.supplier_id);