SQL 語法 REPLACE

Loading

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

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

REPLACE 為非標準 SQL 語法,透過 INSERT … ON DUPLICATE KEY UPDATE Syntax 一樣可以達到效果,請參考 – https://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 – https://benjr.tw/97834 來轉換.

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

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

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

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

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