測試環境為 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
如果是已存在 (重覆資料) 只更新該筆資料而不是新增資料的方式.請參考.
- 使用 INSERT … ON DUPLICATE KEY UPDATE 語法 – https://benjr.tw/102189
他會先檢查該筆新增的資料其 PRIMARY KEY 或是 UNIQUE index 是否在 資料庫.表 已存在 (重覆資料) ,這時候他會更新該筆資料而不是新增資料. - 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)
沒有解決問題,試試搜尋本站其他內容