SQL 語法 INSERT IGNORE

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

當欄位有使用 PRIMARY KEY 或是 UNIQUE Index ,在新增資料時會確認是否重覆 (Duplicate),當重覆時資料無法輸入,如果當時是新增 (INSERT) 多筆資料時,會 系統會 Rolls Back 讓當次所有的資料都無法輸入,有辦法忽略掉已存在資料,只新增新資料嗎?

可以在 SELECT 時 使用 IGNORE ,如果是非 PRIMARY KEY 或是 UNIQUE Index 有辦法在 INSERT 前先確認是否重覆來決定是否輸入資料,請參考 SQL 語法 INSERT INTO SELECT FROM DUAL WHERE NOT EXISTS – https://benjr.tw/102870

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

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

下面參考範例 – https://mariadb.com/kb/en/insert-ignore/

[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

建立要測試用的資料表 t1 ,並設定為 Unique (唯一鍵,資料不能重覆,也可以使用 Primary ).

在資料表中結構裡面的資料欄位中的唯一鍵(資料不能重覆)有 Primary , Unique 他們主要的差別如下.

• Primary
  Primary Key (主鍵) 必須為 not null (不能為空) 並且要 Unique (唯一).資料表只有一個欄位可以為 Primary Key.
• Unique
  Unique Key (唯一鍵) 不能重覆,但是可以為 null (空)

MariaDB [testdb]> CREATE TABLE t1 (x INT UNIQUE);
Query OK, 0 rows affected (0.009 sec)

MariaDB [testdb]> INSERT INTO t1 VALUES(1),(2);
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

可以發現當有兩筆資料要輸入時但資料重覆了,系統會 Rolls Back 這兩筆的資料都無法輸入.

MariaDB [testdb]> INSERT INTO t1 VALUES(2),(3);
ERROR 1062 (23000): Duplicate entry '2' for key 'x'
MariaDB [testdb]> SELECT * FROM t1;
+------+
| x    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.001 sec)

使用 INSERT IGNORE 就可以忽略這個問題了.

MariaDB [testdb]> INSERT IGNORE INTO t1 VALUES(2),(3);
Query OK, 1 row affected, 1 warning (0.002 sec)
Records: 2  Duplicates: 1  Warnings: 1

MariaDB [testdb]> SHOW WARNINGS;
+---------+------+---------------------------------+
| Level   | Code | Message                         |
+---------+------+---------------------------------+
| Warning | 1062 | Duplicate entry '2' for key 'x' |
+---------+------+---------------------------------+
1 row in set (0.000 sec)

MariaDB [testdb]> SELECT * FROM t1;
+------+
| x    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.001 sec)