MySQL/MariaDB 指令匯入 CSV 資料 for Linux

2022-04-05 腳印網頁資訊設計

在 Linux 使用 MySQL/MariaDB 指令來匯入 .csv 檔的資料，並詳細說名每個參數的用途。

要匯入的檔案必須放在 /var/lib/mysql-files/ 目錄下：

vim /var/lib/mysql-files/process_item.csv

"id",name,note,process_item_category_id
001,裁線,NULL,"1"

登入 MySQL：

mysql -u jacky -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45485939
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE mes_one;
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
mysql>

匯入 CSV 資料：

LOAD DATA INFILE '/var/lib/mysql-files/process_item.csv'    -- 要匯入的 CSV 檔案路徑
INTO TABLE process_item                                     -- 資料匯入的資料表 (table)
FIELDS TERMINATED BY ','                                    -- 欄位名稱終止 (分隔) 符號
ENCLOSED BY '"'                                             -- 欄位名稱括起來的符號
LINES TERMINATED BY '\n'                                    -- 每一列 (row) 換行終止符號
IGNORE 1 ROWS;                                              -- 匯入的資料乎略第一列 (row) 的欄位名稱

mysql> LOAD DATA INFILE '/var/lib/mysql-files/process_item.csv'
    -> INTO TABLE process_item
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS;
Query OK, 1 row affected (0.04 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

MySQL