測試環境為 CentOS 7 x86_64 (虛擬機)
有辦法在新增 (INSERT) 資料時,先確認是否資料已存在,有的話更新該筆資料,沒有時才新增資料.可以使用 INSERT … ON DUPLICATE KEY UPDATE
語法來達成.他會先檢查該筆新增的資料其 PRIMARY KEY 或是 UNIQUE index 是否在 資料庫.表 已存在 (重覆資料) ,這時候他會更新該筆資料而不是新增資料.
REPLACE 也具由相同功能,但他為非標準 SQL 語法,請參考 – https://benjr.tw/102179
先建立測試用資料,UserID 欄位為 PRIMARY KEY.
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 99
Server version: 5.5.60-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.00 sec)
MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE TABLE employee (UserID char(20) NOT NULL , Name char(20),Dept char(20),JobTitle char(20),Salary int(11) , PRIMARY KEY (UserID));
Query OK, 0 rows affected (0.01 sec)
MariaDB [testdb]> INSERT INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101001','Ben','Testing','Engineer','45000');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> INSERT INTO employee(USERID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101002','Afa','Power','Engineer','48000');
Query OK, 1 row affected (0.00 sec)
MariaDB [testdb]> SELECT * FROM employee;
+-------------+------+---------+----------+--------+
| UserID | Name | Dept | JobTitle | Salary |
+-------------+------+---------+----------+--------+
| 20190101001 | Ben | Testing | Engineer | 45000 |
| 20190101002 | Afa | Power | Engineer | 48000 |
+-------------+------+---------+----------+--------+
2 rows in set (0.00 sec)
SQL – INSERT ON DUPLICATE KEY UPDATE
要更新同事 Ben , Afa 薪水了,但裡面摻雜了新同事 Bendy 的資料,這時候就可以使用 INSERT ON DUPLICATE KEY UPDATE.
MariaDB [testdb]> INSERT INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101001','Ben','Testing','Engineer','48000') ON DUPLICATE KEY UPDATE Salary='48000';
Query OK, 2 rows affected (0.00 sec)
MariaDB [testdb]> INSERT INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101002','Afa','Power','Engineer','52000') ON DUPLICATE KEY UPDATE Salary='52000';
Query OK, 2 rows affected (0.00 sec)
MariaDB [testdb]> INSERT INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20191018001' ,'Bendy','Testing','Manager','75000') ON DUPLICATE KEY UPDATE Salary='75000';
Query OK, 1 row affected (0.00 sec)
檢視一下資料,更新兩個舊同事資料 (Ben , Afa) ,並新增了 Bendy 新同事.
MariaDB [testdb]> SELECT * FROM employee; +-------------+-------+---------+----------+--------+ | UserID | Name | Dept | JobTitle | Salary | +-------------+-------+---------+----------+--------+ | 20190101001 | Ben | Testing | Engineer | 48000 | | 20190101002 | Afa | Power | Engineer | 52000 | | 20191018001 | Bendy | Testing | Manager | 75000 | +-------------+-------+---------+----------+--------+ 3 rows in set (0.00 sec)
ON DUPLICATE KEY UPDATE 也可以一直更新多筆欄位,如 : ON DUPLICATE KEY UPDATE Dept=’Testing’, JobTitle=’Engineer’, Salary=’48000′;
SQL – LOAD DATA INFILE
如果資料是大筆檔案匯入時要怎麼做?
[root@localhost ~]# vi /etc/salary2019Q4 "20191018002","Cars","Testing","Senior Engineer","45000" "20190101001","Ben","Testing","Engineer","51000" "20190101002","Afa","Power","Engineer","60000" "20191018001","Bendy","Testing","Manager","95000"
這時候可以利用 SQL 語法 LOAD DATA INFILE (Import) 的功能.
MariaDB [testdb]> LOAD DATA INFILE '/etc/salary2019Q4' REPLACE INTO TABLE testdb.employee FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (UserID ,Name ,Dept ,JobTitle ,Salary) ; Query OK, 7 rows affected (0.00 sec) Records: 4 Deleted: 3 Skipped: 0 Warnings: 0
指令說明:
- LOAD DATA INFILE ‘/etc/salary2019Q4’
指定載入檔案位置,需注意權限問題 (Errcode: 2 – Error reading file) – https://dev.mysql.com/doc/refman/5.6/en/global-error-reference.html , 代表 mysql 使用者無法讀取該檔案,這時要注意存放檔案的目錄需要可以進入 (a+x) 的權限,檔案本身至少需要可供讀取 (a+r) 的權限. - REPLACE INTO TABLE testdb.employee
指定資料要輸入的 資料庫.資料表,REPLACE 的功能同前面,新增 (同 INSERT) 資料時使用,但他會先檢查該筆新增的資料其 PRIMARY KEY 或是 UNIQUE index 是否在 資料庫.表 已存在 (重覆資料) ,這時候他會更新該筆資料而不是新增資料. - FIELDS TERMINATED BY ‘,’
csv 資料的分隔字元為 ” , “. - ENCLOSED BY ‘”‘
指定包圍欄位資料的符號. - LINES TERMINATED BY ‘\n’
資料的換行字元,代表接下來的資料是新的一筆紀錄 (Record).
Linux 下的換行 (NewLine) 為 \n – 0A(16進制) 字元. Windows 下的換行為 \r – 0d 0A(16進制) – Carriage Return + \n – NewLine 字元.可以透過 hexdump 來觀看.[root@localhost ~]# hexdump -C /etc/salary2019Q4 00000000 22 32 30 31 39 31 30 31 38 30 30 32 22 2c 22 43 |"20191018002","C| 00000010 61 72 73 22 2c 22 54 65 73 74 69 6e 67 22 2c 22 |ars","Testing","| 00000020 53 65 6e 69 6f 72 20 45 6e 67 69 6e 65 65 72 22 |Senior Engineer"| 00000030 2c 22 34 35 30 30 30 22 0a 22 32 30 31 39 30 31 |,"45000"."201901| 00000040 30 31 30 30 31 22 2c 22 42 65 6e 22 2c 22 54 65 |01001","Ben","Te| 00000050 73 74 69 6e 67 22 2c 22 45 6e 67 69 6e 65 65 72 |sting","Engineer| 00000060 22 2c 22 35 31 30 30 30 22 0a 22 32 30 31 39 30 |","51000"."20190| 00000070 31 30 31 30 30 32 22 2c 22 41 66 61 22 2c 22 50 |101002","Afa","P| 00000080 6f 77 65 72 22 2c 22 45 6e 67 69 6e 65 65 72 22 |ower","Engineer"| 00000090 2c 22 36 30 30 30 30 22 0a 22 32 30 31 39 31 30 |,"60000"."201910| 000000a0 31 38 30 30 31 22 2c 22 42 65 6e 64 79 22 2c 22 |18001","Bendy","| 000000b0 54 65 73 74 69 6e 67 22 2c 22 4d 61 6e 61 67 65 |Testing","Manage| 000000c0 72 22 2c 22 39 35 30 30 30 22 0a |r","95000".| 000000cb
- IGNORE 1 ROWS;
如果當你的資料第一行為欄位名稱,使用該參數即可忽略第一行的資料.
檢視一下,可以看到剛剛輸入的資料,新增了 Cars 使用者資料,並更新了 Ben , Afa , Bendy 資料.
MariaDB [testdb]> SELECT * FROM employee; +-------------+-------+---------+-----------------+--------+ | UserID | Name | Dept | JobTitle | Salary | +-------------+-------+---------+-----------------+--------+ | 20190101001 | Ben | Testing | Engineer | 51000 | | 20190101002 | Afa | Power | Engineer | 60000 | | 20191018001 | Bendy | Testing | Manager | 95000 | | 20191018002 | Cars | Testing | Senior Engineer | 45000 | +-------------+-------+---------+-----------------+--------+ 4 rows in set (0.00 sec)
這指令一樣可以直接在 Linux command Line 或是 Bash script 來執行 (需注意 ENCLOSED BY ‘”‘ 這個地方,因為 Linux script 無法直接使用 ” 須以 \” 來表示).
[root@localhost ~]# mysql -u root -p111111 -e "LOAD DATA INFILE '/etc/salary2019Q4' REPLACE INTO TABLE testdb.employee FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (UserID ,Name ,Dept ,JobTitle ,Salary) ;"
PHPMyAdmin
如果不想用指令,可以用 PHPMyAdmin – https://benjr.tw/97834 來轉換.
選擇 CSV – 用逗號 (,) 將值隔開的檔案格式 (在 Microsoft Excel 或是 Linux 下的 LibreOffice 都可以直接將檔按轉換成 csv)
勾選 使用檔案取代資料表資料 (Replace table data with file), 選擇 欄位分隔符號,內容分隔符號, 內容跳脫符號 ,如果在資料的第一行為資料表名稱時,需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.
沒有解決問題,試試搜尋本站其他內容