SQL 語法 INSERT INTO SELECT FROM DUAL WHERE NOT EXISTS

測試環境為 CentOS 8 x86_64 (虛擬機)

INSERT 時會檢查 PRIMARY KEY 或是 UNIQUE Index 欄位是否有重覆 (Duplicate),進而確保資料的唯一性,但當我們資料欄位非 PRIMARY KEY 或是 UNIQUE Index 時,怎麼檢查資料是否有重覆的問題.

可以使用 SQL 語法 – INSERT INTO SELECT FROM DUAL WHERE NOT EXISTS

如果是已存在 (重覆資料) 只更新該筆資料而不是新增資料的方式.請參考.

1. 使用 INSERT … ON DUPLICATE KEY UPDATE 語法 – https://benjr.tw/102189
   他會先檢查該筆新增的資料其 PRIMARY KEY 或是 UNIQUE index 是否在 資料庫.表 已存在 (重覆資料) ,這時候他會更新該筆資料而不是新增資料.
2. REPLACE 也具由相同功能,但他為非標準 SQL 語法,請參考 – https://benjr.tw/102179

先建立一個資料庫 (testdb) , 與 資料表 (Apple) 格式為 Name VARCHAR(20) 20 個字元.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE Apple (Name VARCHAR(20));
Query OK, 0 rows affected (0.004 sec)

MariaDB [testdb]> DESCRIBE Apple;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.002 sec)

一般 INSERT 的方式.

MariaDB [testdb]> INSERT INTO Apple (Name) VALUES ('iPhone11') ;
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT * FROM Apple;
+----------+
| Name     |
+----------+
| iPhone11 |
+----------+
1 row in set (0.001 sec)

先檢查資料是否重覆再來決定是否 INSERT ,語法如下:

INSERT INTO Apple (Name) 
SELECT 'iPhone12' FROM DUAL
WHERE NOT EXISTS (
SELECT 1 FROM Apple WHERE Name='iPhone12' LIMIT 1
);

說明:

1. 一般 INSERT 後面接著是 VALUE() ,這語法後面接著 SELECT ,這 SELECT 的回傳值直接等於 INSERT 的 VALUE() 值.
2. SELECT FROM DUAL , DUAL 是一種特別的只有 One-Row , One-Column 的資料表 (Table). 一定要使用 FROM DUAL ,如果從其他 TABLE 讀取資料時,會依據回傳資料(行數 , Column)的行數來決定前面要 INSERT 幾筆資料.
3. EXISTS 與 NOT EXIST 會依據期回傳值為 True 或是 False,用來決定該敘述是否要執行. NOT EXISTS 就是當不存在 (FALSE) 時,才會執行前面的 INSERT.
4. LIMIT 1 只需第一筆資料來確認是否有資料即可.

MariaDB [testdb]> INSERT INTO Apple (Name) SELECT 'iPhone12' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM Apple WHERE Name='iPhone12' LIMIT 1);
Query OK, 1 row affected (0.003 sec)
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Apple;
+----------+
| Name     |
+----------+
| iPhone11 |
| iPhone12 |
+----------+
2 rows in set (0.001 sec)

第二次相同的資料就不會再執行 INSERT 了.

MariaDB [testdb]> INSERT INTO Apple (Name) SELECT 'iPhone12' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM Apple WHERE Name='iPhone12' LIMIT 1);
Query OK, 0 rows affected (0.001 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM Apple;
+----------+
| Name     |
+----------+
| iPhone11 |
| iPhone12 |
+----------+
2 rows in set (0.001 sec)