EXISTS 條件句防止插入重復記錄

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);