使用 MySQL 高效匯入 CSV 檔案的方法及常見錯誤解決方式

1. 簡介

MySQL 的資料匯入是處理資料庫時非常重要的工作。透過有效率地匯入現有資料，可以迅速進行新系統的移行或從備份還原。本文針對初學者到中級者，清楚說明 MySQL 資料匯入從基礎到應用的內容。也會介紹具體的指令範例、工具的使用方法，以及常見問題的解決方案，請務必閱讀至最後。

2. MySQL 資料匯入的基本步驟

MySQL 中匯入資料時，需要準備資料庫和表格、確認要匯入資料的格式，以及執行實際的命令。以下將具體說明這些步驟。

資料庫和表格的準備

首先，需要目標匯入的資料庫和表格。如果資料庫不存在，可以使用以下 SQL 命令建立。

CREATE DATABASE example_db;
USE example_db;
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

確認要匯入資料的格式

如果要匯入 CSV 格式的資料，請確認資料檔案是正確的格式。例如，以下是常見的格式。

1,John Doe,30
2,Jane Smith,25

• 分隔符: 以逗號（,）分隔。
• 編碼: 建議以 UTF-8 儲存。

LOAD DATA INFILE 命令的使用

使用 MySQL 的 LOAD DATA INFILE 命令，可以有效率地匯入 CSV 資料。以下是其基本語法。

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

• FIELDS TERMINATED BY: 指定資料的分隔字元。
• ENCLOSED BY: 指定包圍值的字元（例如：雙引號）。
• LINES TERMINATED BY: 指定每行的結束字元。

這樣，資料就會匯入到表格中。

3. 使用 GUI 的匯入方法

使用 GUI 工具，即使不熟悉命令列的人也能輕鬆進行匯入作業。在這裡，我們介紹 MySQL Workbench 和 phpMyAdmin 的使用方法。

MySQL Workbench 的使用方法

1. 啟動資料匯入精靈
   開啟 MySQL Workbench，從選單列選擇「Server」→「Data Import」。
2. 選擇檔案
   在「Import from Self-Contained File」中指定要匯入的檔案。
3. 選擇目標資料庫
   在「Default Target Schema」中選擇匯入先的資料庫。
4. 執行
   點擊「Start Import」來開始匯入。

phpMyAdmin 的使用方法

1. 登入 phpMyAdmin
   在瀏覽器中存取 phpMyAdmin 並登入。
2. 選擇資料庫
   從左側選單中選擇匯入先的資料庫。
3. 開啟匯入標籤
   點擊上方的「匯入」標籤。
4. 選擇檔案
   使用「選擇檔案」按鈕指定 CSV 檔案。
5. 指定格式
   選擇檔案格式（例如：CSV）或編碼。
6. 執行
   按下「執行」按鈕來完成匯入。

4. 命令列匯入方法

不依賴 GUI 工具，使用命令列進行作業，即可進行更細部的設定。本節說明使用 mysql 指令與 source 指令的匯入方法。

mysql 指令進行的匯入

使用以下指令，即可將 SQL 檔案匯入至資料庫。

mysql -u root -p example_db < /path/to/example.sql

• -u: 指定使用者名稱。
• -p: 要求輸入密碼的選項。
• example_db: 匯入目標的資料庫名稱。

source 指令的使用

若想在 MySQL 殼層內直接進行匯入，請執行以下步驟。

mysql -u root -p
USE example_db;
SOURCE /path/to/example.sql;

此方法無需退出殼層即可完成操作，因此很方便。

各 OS 的注意事項

• Windows: 檔案路徑請使用反斜線（）。
• Linux/Mac: 檔案路徑使用斜線（/）。

5. CSV 檔案匯入時的注意事項

在 MySQL 中匯入 CSV 檔案時，事先掌握幾個注意點，可以順利進行作業。在這裡，我們將說明匯入時常見的問題，以及預防這些問題的對策。

編碼問題

如果 CSV 檔案的字元編碼與資料庫設定不同，會發生文字亂碼或錯誤。特別是包含日文的資料時，需要注意字元編碼的差異。

• 建議的編碼: MySQL 通常建議使用 UTF-8。
• 使用 Shift-JIS: 在 Windows 環境中建立的 CSV 檔案多為 Shift-JIS，因此預先轉換為 UTF-8 可以防止問題。

編碼確認方法:

• Windows: 使用記事本的「另存新檔」來確認字元編碼。
• Linux/Mac: 可以使用 file 指令來確認檔案的編碼。

file -i example.csv

local_infile的設定

使用 LOAD DATA INFILE 指令時，需要啟用 local_infile 選項。由於預設可能為停用，因此請確認設定。

設定確認與啟用步驟:

1. 確認目前的狀態

   SHOW VARIABLES LIKE 'local_infile';

如果輸出為OFF，請依以下步驟啟用。

2. 在客戶端連線時啟用

   mysql --local-infile=1 -u root -p

3. 在伺服器層級啟用

• 編輯 MySQL 的設定檔案（my.cnf或my.ini），並新增以下行。
  [mysqld]

local_infile=1 設定變更後，請重新啟動 MySQL 伺服器。

CSV 資料的完整性

為了正確匯入資料，請確認以下要點。

1. 欄位數一致
   如果 CSV 檔案的欄位數與資料表的欄位數不一致，會發生錯誤。必要時請編輯 CSV 檔案。
2. NULL 值的處理
   空白儲存格在 MySQL 中會被視為 NULL。如果要將 NULL 轉換為特定值，可以在 LOAD DATA INFILE 中如以下指定。

   LOAD DATA INFILE '/path/to/example.csv'
   INTO TABLE example_table
   FIELDS TERMINATED BY ',' 
   LINES TERMINATED BY '\n'
   (id, name, age)
   SET age = IF(age='', NULL, age);

3. 日期格式
   MySQL 的日期型別（DATE, DATETIME）標準格式為YYYY-MM-DD。如果使用其他格式（例如: MM/DD/YYYY），需要在匯入前轉換資料。

分隔符與包圍文字的設定

如果 CSV 檔案中的分隔符（區隔文字）與 MySQL 預期的格式不同，會發生錯誤。

• 分隔符為 Tab 的情況
  如果 CSV 檔案為 Tab 區隔，請如以下指定。

  FIELDS TERMINATED BY '    '

• 值被包圍文字（雙引號）包圍的情況
  請指定包圍文字。

  FIELDS ENCLOSED BY '"'

6. 匯出與匯入的實踐範例

MySQL 中的資料匯出與匯入，是資料遷移或備份作業中重要的程序。本節將透過實際的指令範例，說明高效的匯出與匯入方法。

資料匯出的步驟

MySQL 的資料匯出時，使用 mysqldump 指令。此指令可輕鬆取得資料庫或表格的備份。

資料庫整體的匯出

以下指令可匯出特定資料庫。

mysqldump -u root -p example_db > example_db.sql

• -u root: 指定使用者名稱。
• -p: 要求輸入密碼的選項。
• example_db.sql: 匯出目的檔案名稱。

特定表格的匯出

若要匯出資料庫整體以外的特定表格，可如以下指定。

mysqldump -u root -p example_db example_table > example_table.sql

多個資料庫的匯出

要備份多個資料庫時，使用 --databases 選項。

mysqldump -u root -p --databases db1 db2 > multiple_dbs.sql

所有資料庫的匯出

要備份伺服器內的所有資料庫時，使用 --all-databases 選項。

mysqldump -u root -p --all-databases > all_databases.sql

資料匯入的步驟

要匯入匯出的資料時，使用 mysql 指令。以下顯示具體範例。

資料庫整體的匯入

要復原匯出的資料庫時，執行以下指令。

mysql -u root -p example_db < example_db.sql

特定表格的匯入

要復原匯出的特定表格資料時，也使用相同方法執行。

mysql -u root -p example_db < example_table.sql

資料庫建立與匯入的組合

若匯入目的資料庫尚未存在，需事先建立資料庫。請參考以下步驟。

1. 建立資料庫：

   CREATE DATABASE example_db;

2. 匯入至建立的資料庫：

   mysql -u root -p example_db < example_db.sql

匯出與匯入的注意事項

1. 匯出檔案的確認
   匯出後，確認檔案內容，驗證是否包含不完整的備份。

   less example_db.sql

2. 編碼的一致性
   匯出時與匯入時，請確認字元編碼一致。預設使用 UTF-8，但視需要可指定 --default-character-set 選項。

   mysqldump --default-character-set=utf8 -u root -p example_db > example_db.sql

3. 權限的設定
   匯入時，請確認使用者擁有適當權限。

   GRANT ALL PRIVILEGES ON example_db.* TO 'user'@'localhost';

7. 疑難排解

MySQL 中匯入資料時，可能會發生各種錯誤或問題。本節將說明常見的問題及其因應方法。

常見錯誤及其因應方法

ERROR 1148 (42000): The used command is not allowed with this MySQL version

• 原因
  LOAD DATA INFILE 指令被無效化時會發生。出於安全原因，許多 MySQL 安裝預設將 local_infile 選項設為無效。
• 因應方法

1. 確認 local_infile 的目前設定。
   SHOW VARIABLES LIKE 'local_infile';
2. 在客戶端連線時啟用。
   mysql --local-infile=1 -u root -p
3. 變更伺服器設定以永久啟用。
   • 在 MySQL 設定檔案（my.cnf 或 my.ini）中新增以下內容：
     [mysqld]

local_infile=1 重新啟動 MySQL 伺服器。

ERROR 1366: Incorrect string value

• 原因
  匯入的資料與資料庫的字元編碼設定不一致時會發生。特別是日文等多位元組字元常是原因。
• 因應方法

1. 確認資料庫與表格的字元編碼。
   SHOW VARIABLES LIKE 'character_set%';
2. 視需要統一字元編碼。以下是設定為 UTF-8 的範例。
   ALTER TABLE example_table CONVERT TO CHARACTER SET utf8mb4;
3. 在匯出與匯入時明確指定字元編碼。
   mysqldump --default-character-set=utf8mb4 -u root -p example_db > example_db.sql mysql --default-character-set=utf8mb4 -u root -p example_db < example_db.sql

ERROR 1062: Duplicate entry

• 原因
  匯入的資料包含違反表格主鍵或唯一限制的重複資料。
• 因應方法

1. 忽略重複資料：
   LOAD DATA INFILE '/path/to/example.csv' INTO TABLE example_table FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' IGNORE;
2. 重複時進行更新：
   INSERT INTO example_table (id, name, age) VALUES (1, 'John Doe', 30) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

ERROR 1045: Access denied for user

• 原因
  缺少存取匯入目標資料庫的權限。
• 因應方法

1. 確認使用者的權限。
   SHOW GRANTS FOR 'user'@'localhost';
2. 授予必要權限。
   GRANT ALL PRIVILEGES ON example_db.* TO 'user'@'localhost'; FLUSH PRIVILEGES;

大量資料匯入時的問題與對策

記憶體不足錯誤

• 原因
  一次匯入大量資料可能導致記憶體不足。
• 因應方法

1. 分割資料進行匯入。在 Linux/Mac 中可以使用 split 指令。
   split -l 1000 large_file.csv part_
2. 依序匯入各部分檔案。

伺服器逾時

• 原因
  大量資料匯入未能在時間內完成，導致逾時發生。
• 因應方法
  變更 MySQL 設定以延長逾時時間。

  SET GLOBAL net_read_timeout = 600;
  SET GLOBAL net_write_timeout = 600;

8. 常見問題 (FAQ)

MySQL 的資料匯入相關，彙整了讀者常提出的問題。針對實際運作中可能遇到的問題，提供簡單易懂的回答。

Q1: 可以只匯入 CSV 檔案的特定欄位嗎？

A: 是的，可以。在使用 LOAD DATA INFILE 指令時，指定要匯入的欄位即可應對。

範例：
以下是將 CSV 檔案的第一欄和第三欄匯入到 example_table 的 id 和 age 欄位中的範例。

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(id, @dummy, age);

如此一來，將不需要的欄位分配給 @dummy 之類的佔位符，即可只匯入特定欄位。

Q2: 大量資料的效率匯入方法是什麼？

A: 大量資料的匯入可採用以下方法。

1. 暫時停用索引
   在匯入前停用索引，匯入完成後重新建立，即可提升效能。

   ALTER TABLE example_table DISABLE KEYS;
   -- 匯入處理
   ALTER TABLE example_table ENABLE KEYS;

2. 活用大量插入
   LOAD DATA INFILE 指令最適合處理大量資料。要進一步提升匯入速度，可使用 LOCAL 選項來載入客戶端檔案。

   LOAD DATA LOCAL INFILE '/path/to/large_file.csv' INTO TABLE example_table;

3. 檔案分割
   在 Linux 或 Mac 上，使用 split 指令將大檔案分割成小單位，依序匯入的方法也很有效。

Q3: 匯入時驗證資料的方法有哪些？

A: 是的，有幾種在匯入時驗證資料的方法。

1. 使用暫存表格
   不是直接匯入到正式表格，而是先匯入到暫存表格後再確認資料。

   CREATE TEMPORARY TABLE temp_table LIKE example_table;
   LOAD DATA INFILE '/path/to/example.csv' INTO TABLE temp_table;
   -- 資料確認後移轉到正式表格
   INSERT INTO example_table SELECT * FROM temp_table;

2. 確認錯誤輸出
   在 LOAD DATA INFILE 指令中附加 ERRORS 選項，即可取得錯誤詳細資訊。

   LOAD DATA INFILE '/path/to/example.csv'
   INTO TABLE example_table
   FIELDS TERMINATED BY ',' 
   LINES TERMINATED BY '
'
   IGNORE 1 LINES
   (@col1, @col2, @col3)
   SET col1 = IF(@col1='', NULL, @col1),
       col2 = @col2,
       col3 = @col3;

Q4: 資料匯入中 MySQL 發生逾時該怎麼辦？

A: 逾時問題可能是由資料量或伺服器設定引起的。可透過以下步驟應對。

1. 延長逾時時間
   調整 MySQL 的逾時設定。

   SET GLOBAL net_read_timeout = 600;
   SET GLOBAL net_write_timeout = 600;

2. 分割檔案後匯入
   將大檔案分割後匯入，即可防止逾時。
3. 確認 MySQL 的記錄
   為了找出逾時原因，請確認錯誤記錄。

Q5: 匯入時如何將空白儲存格視為 NULL 處理？

A: 使用 LOAD DATA INFILE 指令的 SET 選項，即可將空白儲存格處理為 NULL。

範例：
以下指令將空值轉換為 NULL。

LOAD DATA INFILE '/path/to/example.csv'
INTO TABLE example_table
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
'
(name, age)
SET age = IF(age='', NULL, age);

9. 總結

在本篇文章中，我們詳細說明了 MySQL 的資料匯入相關手續、重點以及疑難排解。為了讓初學者到中級使用者都能參考，我們加入了實務性的資訊。以下回顧本篇文章中提到的重點。

文章要點

1. 基本手續的理解

• LOAD DATA INFILE 指令或 mysql 指令來活用，說明了有效率匯入資料的方法。
• 也介紹了使用 GUI 工具（MySQL Workbench、phpMyAdmin）的簡易方法。

1. CSV 檔案的注意事項

• 編碼（建議使用 UTF-8）、欄位數一致、NULL 值的處理等，為了維持資料完整性的注意事項。

1. 匯出與匯入的實務範例

• 提供了使用 mysqldump 指令，從匯出到匯入的具體手續。
• 也可以應用於伺服器間的資料移行或備份作業。

1. 疑難排解

• 詳細說明了常見錯誤（例如：ERROR 1148 或 ERROR 1366）及其因應方法。
• 介紹了大容量資料或逾時的因應最佳實務。

1. 常見問題 (FAQ)

• 以回答讀者疑問的形式，涵蓋了匯入時特定欄位的處理、大容量資料的有效率匯入、資料驗證方法等。

今後參考資源

如果想更詳細學習，請也參考以下資源。

• MySQL 官方文件（英文版）
  MySQL Documentation

下一個步驟

• 活用習得的知識
  在實際專案中試用本篇文章的手續，來強化自己的技能。
• 相關主題的學習
  透過學習資料庫管理中的備份策略和安全性設定，進一步提升實務能力。

MySQL 的資料匯入一開始可能看起來很難，但只要掌握本篇文章說明的重要點，就能順利進行作業。今後也請持續磨練資料庫管理的技能，目標達成更有效率的運作！