SQL 語法 REPLACE

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

REPLACE 也是新增 (同 INSERT) 資料時使用,但他會先檢查該筆新增的資料其 PRIMARY KEY 或是 UNIQUE index 是否在 資料庫.表 已存在 (重覆資料) ,這時候他會更新該筆資料而不是新增資料.

REPLACE 為非標準 SQL 語法,透過 INSERT … ON DUPLICATE KEY UPDATE Syntax 一樣可以達到效果,請參考 – http://benjr.tw/102189 .

先建立測試用資料,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 – REPLACE

要更新同事 Ben , Afa 薪水了,但裡面摻雜了新同事 Bendy 的資料,這時候就可以使用 REPLACE 取代 INSERT

MariaDB [testdb]> REPLACE INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101001','Ben','Testing','Engineer','48000');
Query OK, 2 rows affected (0.00 sec)

MariaDB [testdb]> REPLACE INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20190101002','Afa','Power','Engineer','52000');
Query OK, 2 rows affected (0.01 sec)

MariaDB [testdb]> REPLACE INTO employee(UserID ,Name ,Dept ,JobTitle ,Salary) VALUES ('20191018001' ,'Bendy','Testing','Manager','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)

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 – http://benjr.tw/97834 來轉換.

選擇 CSV – 用逗號 (,) 將值隔開的檔案格式 (在 Microsoft Excel 或是 Linux 下的 LibreOffice 都可以直接將檔按轉換成 csv)

勾選 使用檔案取代資料表資料 (Replace table data with file), 選擇 欄位分隔符號,內容分隔符號, 內容跳脫符號 ,如果在資料的第一行為資料表名稱時,需要勾選 檔案第一行包含資料表的欄位名稱 (若未選此項,首行將被認為是資料),按下執行即可完成匯入.

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料