MySQL中高效插入多行資料的方法【適合初學者解說】

1. 簡介

MySQL 是許多 Web 應用程式和資料庫管理系統中使用的資料庫管理系統之一。為了有效管理資料，適當的插入（INSERT）操作是不可或缺的。特別是處理大量資料時，一行一行插入的方法可能會花費過多時間和資源。

本文將詳細說明在 MySQL 中一次插入多行資料的方法。使用此方法，可以提升資料插入的效率，並大幅改善系統效能。此外，為了讓初學者也能輕鬆理解，我們將從基礎到應用逐步說明。

以下內容特別適合有以下問題的人：

• 「想有效使用 INSERT 語句」
• 「想縮短資料插入時間」
• 「想學習大量資料的處理方法」

接下來的各節中，我們將結合具體的程式碼範例和注意事項，全面說明在 MySQL 中插入多行資料的最佳方法。下一節中，我們首先掌握單行插入方法的基本知識。

2. 基本的 INSERT 語句的語法

MySQL 中插入資料時，首先理解基本的單行 INSERT 語句非常重要。此語法雖然非常簡單，但卻是熟悉 MySQL 操作的第一步。在此，我們將說明插入單行資料的基本語法及具體範例。

INSERT 語句的基本語法

插入單行資料至資料表時，基本語法如下：

INSERT INTO 資料表名稱 (欄位1, 欄位2, ...)
VALUES (值1, 值2, ...);

• 資料表名稱: 插入資料的目標資料表的名稱。
• 欄位1, 欄位2，…: 指定儲存插入值的資料表內欄位名稱。
• 值1, 值2，…: 指定對應各欄位的值。

基本範例：插入客戶資訊

例如，假設有如下「customers」資料表。

在此資料表中新增客戶資訊的 INSERT 語句如下：

INSERT INTO customers (id, name, email)
VALUES (2, 'Hanako Tanaka', 'hanako@example.com');

執行後，「customers」資料表將變成如下：

省略欄位指定的方法

若要插入值至所有欄位，可省略欄位名稱的指定。此時，值的順序需與資料表的欄位定義順序一致。

INSERT INTO customers
VALUES (3, 'Ichiro Suzuki', 'ichiro@example.com');

注意事項

• 資料型的一致: 插入的值的資料型需與資料表定義的欄位資料型一致。
• NULL 值的處理: 若欄位允許 NULL，可不指定值而插入 NULL。
• 預設值: 若欄位定義了預設值，則不指定值時將插入該預設值。

摘要

正確理解基本的 INSERT 語句，即可順利進行 MySQL 中的資料操作。此單行插入的理解，將成為下一個「一次插入多行資料的方法」的基礎。

3. 一次插入多行資料的方法

在 MySQL 中，可以使用單一 SQL 語句插入多行資料。使用此方法，比重複執行 INSERT 語句更有效率，並能減輕資料庫的負荷。在此，我們將說明多行插入的語法及具體範例。

多行插入的基本語法

當要一次插入多行時，使用以下語法：

INSERT INTO 資料表名稱 (欄位1, 欄位2, ...)
VALUES
(值1_1, 值1_2, ...),
(值2_1, 值2_2, ...),
(值3_1, 值3_2, ...);

• 將每行資料用括號包圍，並以逗號分隔各行。
• 「VALUES」子句只需描述一次。

基本範例：插入多個客戶資訊

在以下範例中，一次將多行資料插入 customers 資料表。

INSERT INTO customers (id, name, email)
VALUES
(4, 'Makoto Kato', 'makoto@example.com'),
(5, 'Sakura Mori', 'sakura@example.com'),
(6, 'Kei Tanaka', 'kei@example.com');

執行後，資料表將如下更新：

高效的部分

• 減輕網路負荷: 因為使用單一 SQL 語句插入多行，因此與伺服器的通訊次數減少。
• 加速: 插入操作只需一次，因此處理更有效率。

注意事項

1. 欄位數與值的數量必須一致

• 範例: 如果欄位有 3 個，每行值也必須有 3 個，否則會發生錯誤。

1. 資料型態必須一致

• 每個值的資料型態必須與資料表中定義的欄位資料型態一致。

1. 避免重複錯誤

• 如果有主鍵或唯一鍵的限制，試圖插入相同鍵的資料時會發生錯誤。

錯誤回避的技巧：IGNORE選項

使用 IGNORE，即使發生錯誤也能跳過並繼續處理。

INSERT IGNORE INTO customers (id, name, email)
VALUES
(7, 'Ryoichi Suzuki', 'ryoichi@example.com'),
(5, 'Duplicate User', 'duplicate@example.com'); -- 這行將被忽略

總結

透過一次插入多行資料，可以有效率地操作資料庫。藉此，可預期縮短處理時間並減輕伺服器負荷。

4. 大量資料的批次插入方法

插入大量資料時，使用一般的 INSERT 語句可能會變得低效。在 MySQL 中，為了有效率地插入大量資料，可以使用「LOAD DATA INFILE」這個指令。此方法在大規模資料集批次儲存到表格時非常有用。

LOAD DATA INFILE 的基本語法

以下是 LOAD DATA INFILE 的基本語法：

LOAD DATA INFILE '檔案路徑'
INTO TABLE 表格名稱
FIELDS TERMINATED BY ',' -- 欄位的分隔符
LINES TERMINATED BY '\n' -- 行的分隔符
(欄位1, 欄位2, ...);

• INFILE: 指定儲存插入資料的檔案路徑。
• FIELDS TERMINATED BY: 指定每個欄位（列）的分隔符（例如：逗號「,」）。
• LINES TERMINATED BY: 指定每行的分隔符（例如：換行「\n」）。
• (欄位1, 欄位2, ...): 指定插入資料的欄位。

基本範例：從 CSV 檔案插入資料

例如，假設有以下的 CSV 檔案（data.csv）：

4,Makoto Kato,makoto@example.com
5,Sakura Mori,sakura@example.com
6,Kei Tanaka,kei@example.com

將此檔案插入 customers 表格時，執行以下指令：

LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email);

LOCAL 選項的使用

如果 CSV 檔案位於客戶端而非伺服器時，使用 LOCAL 選項：

LOAD DATA LOCAL INFILE '/path/to/data.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, email);

效能最佳化的要點

1. 交易的使用

• 將插入處理置於交易內時，發生錯誤可進行回滾。

   START TRANSACTION;
   LOAD DATA INFILE '/path/to/data.csv' INTO TABLE customers;
   COMMIT;

2. 索引的暫時停用

• 插入前停用索引，插入後再啟用，可加速插入處理。

   ALTER TABLE customers DISABLE KEYS;
   LOAD DATA INFILE '/path/to/data.csv' INTO TABLE customers;
   ALTER TABLE customers ENABLE KEYS;

3. 使用 SET 子句加工資料

• 可在插入前加工資料：

   LOAD DATA INFILE '/path/to/data.csv'
   INTO TABLE customers
   FIELDS TERMINATED BY ','
   LINES TERMINATED BY '\n'
   (id, name, @email)
   SET email = LOWER(@email);

注意事項

• 檔案權限: 使用 LOAD DATA INFILE 時，MySQL 伺服器必須擁有檔案的存取權限。
• 安全性: 使用 LOCAL 選項時，需對外部攻擊採取充分的防範措施。

總結

LOAD DATA INFILE 是有效率插入大量資料的強大工具。活用此方法，可大幅提升資料庫的操作效率。

5. 效能最佳化的要點

在 MySQL 中插入資料時，特別是處理大量資料的情況下，提高效率的最佳化非常重要。本節將說明為了最大限度發揮效能的具體方法。

交易的使用

透過使用交易，可以將多個 INSERT 操作合併處理。由此，比起個別提交的情況，可以大幅加速。>範例：使用交易的 INSERT

START TRANSACTION;

INSERT INTO customers (id, name, email)
VALUES (7, 'Haruto Sato', 'haruto@example.com'),
       (8, 'Yuki Aoki', 'yuki@example.com');

COMMIT;

要點：

• 在交易內執行多個 INSERT 陳述式，並在最後一次批量提交，從而減少磁碟 I/O。
• 如果發生錯誤，可以使用ROLLBACK來取消整體。

索引的暫時無效化

在資料插入時，如果索引被更新，處理可能會變慢。透過暫時無效化索引，並在插入後重新有效化，可以提高效率。範例：無效化索引並插入資料

ALTER TABLE customers DISABLE KEYS;

INSERT INTO customers (id, name, email)
VALUES (9, 'ori Tanaka', 'kaori@example.com'),
       (10, 'Shota Yamada', 'shota@example.com');

ALTER TABLE customers ENABLE KEYS;

注意事項：

• 此手法在批量插入大量資料時有效。
• 只能無效化次要索引，無法適用於主鍵。

批次處理的活用

進行將資料小份插入的批次處理，有時可以提高效率。因為一次插入大量資料，會增加記憶體不足或逾時的風險。範例：指定批次大小的 INSERT

-- 每次 INSERT 插入 100 行
INSERT INTO customers (id, name, email)
VALUES
(11, 'Hiroshi Kato', 'hiroshi@example.com'),
(12, 'Miku Yamamoto', 'miku@example.com'),
... -- 新增 98 行
(110, 'Rina Suzuki', 'rina@example.com');

要點：

• 調整批次大小（例如：100 行或 1000 行），以抑制伺服器負荷。
• 請注意記錄的大小和伺服器設定。

緩衝區大小和設定的調整

MySQL 的設定檔案（my.cnf）中，進行為了提高插入效能的調整。建議的設定項目：

• innodb_buffer_pool_size：增加用於在記憶體內有效管理資料的大小。
• bulk_insert_buffer_size：擴大批量插入時使用的緩衝區大小。

範例：設定變更

[mysqld]
innodb_buffer_pool_size=1G
bulk_insert_buffer_size=512M

設定變更後，重新啟動 MySQL 伺服器以反映。

總結

MySQL 中資料插入的效能最佳化，以下方法有效：

1. 利用交易來提高效率。
2. 無效化索引來提高插入速度。
3. 批次處理來分散負荷。
4. 調整伺服器設定來發揮最大性能。

透過組合這些方法，可以有效率地進行大量資料插入。

6. 與其他資料庫的差異

MySQL 中的資料插入操作，與許多資料庫既有相似之處，也有其獨特特徵。本文將說明 MySQL 與其他一般資料庫（例如：PostgreSQL 或 Oracle）在多行資料插入方法上的差異。

MySQL 與 PostgreSQL 的比較

1. 多行插入的語法

• MySQL 和 PostgreSQL 基本上可以使用相同的語法。

MySQL 的範例:

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

PostgreSQL 的範例:

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

差異:

• 在 PostgreSQL 中，可以新增 RETURNING 子句來取得插入的資料。

INSERT INTO customers (id, name, email)
VALUES
(3, 'Sakura Mori', 'sakura@example.com')
RETURNING *;

2. 交易的處理

• 兩者皆支援交易，但 PostgreSQL 在交易隔離層級及資料一致性方面，具有更嚴格的預設設定。

MySQL 與 Oracle 的比較

1. 多行插入的方法

在 Oracle 中，可以使用與 MySQL 不同的「INSERT ALL」語法。MySQL 的方法:

INSERT INTO customers (id, name, email)
VALUES
(1, 'Taro Yamada', 'taro@example.com'),
(2, 'Hanako Tanaka', 'hanako@example.com');

Oracle 的方法（INSERT ALL）:

INSERT ALL
  INTO customers (id, name, email) VALUES (1, 'Taro Yamada', 'taro@example.com')
  INTO customers (id, name, email) VALUES (2, 'Hanako Tanaka', 'hanako@example.com')
SELECT * FROM dual;

差異:

• MySQL 使用單一的 VALUES 子句插入多行，但 Oracle 使用 INSERT ALL 語法可以個別插入多行。
• 在 Oracle 中，有時需要使用名為 dual 的特殊虛擬表格。

其他差異

1. 資料類型的差異

• MySQL 常使用 TEXT 或 BLOB 類型，但 Oracle 或 PostgreSQL 使用不同的資料類型，如 CLOB 或 BYTEA。
• 插入時，需要注意資料類型的差異。

2. 錯誤處理

• MySQL 可以使用 IGNORE 選項來忽略錯誤。

INSERT IGNORE INTO customers (id, name, email)
VALUES (1, '重複使用者', 'duplicate@example.com');

• PostgreSQL 或 Oracle 使用專用的例外處理（例如：EXCEPTION 或 SAVEPOINT）來進行錯誤處理。

3. 批次插入的方法

• MySQL 可以利用 LOAD DATA INFILE，但 PostgreSQL 使用 COPY 指令，Oracle 則使用 SQL*Loader 工具。

總結

MySQL、PostgreSQL、Oracle 之間，在多行插入及資料操作方面，有共通點與差異。透過了解各資料庫的特徵，即可選擇最適當的方法。

7. FAQ

這裡，我們將說明有關在 MySQL 中插入資料的常見問題及其解決方法。透過事先解決讀者可能抱持的疑問，讓您能夠順利進行作業。

Q1: 插入多行時發生錯誤。該如何進行除錯？

A: 插入多行時發生錯誤的情況下，請確認以下事項：

1. 資料類型的符合

• 確認插入每個欄位的值是否符合資料表中定義的資料類型。
• 範例: 確認沒有只插入數字到 VARCHAR 類型中。

1. 值的數量與欄位數量是否符合

   INSERT INTO customers (id, name, email)
   VALUES
   (1, 'Taro Yamada'), -- 錯誤：email 的值不足
   (2, 'Hanako Tanaka', 'hanako@example.com');

3. 限制違反

• 如果未滿足主鍵或唯一鍵的限制，就會發生錯誤。
• 解決方案: 為了避免錯誤，使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE。

Q2: 使用 LOAD DATA INFILE 時的安全性注意事項是什麼？

A:LOAD DATA INFILE 雖然是強大的功能，但也伴隨著安全性風險。請注意以下事項：

1. 檔案的存取權限

• 請設定適當的路徑與權限，讓 MySQL 伺服器能夠存取檔案。
• 確認 SECURE_FILE_PRIV 目錄的設定，並僅使用允許目錄內的檔案。

1. LOCAL 選項的風險

• 使用 LOAD DATA LOCAL INFILE 時，為了防止從遠端伺服器讀取惡意檔案，請僅在可信任的客戶端與伺服器之間使用。

1. 資料的驗證

• 事先驗證檔案內的資料，確認不包含不正當的資料。

Q3: 插入大量資料時，效能降低的原因是什麼？

A: 效能降低的主要原因及其對策如下：

1. 索引的更新

• 插入時索引會被更新，導致處理變慢。
• 對策: 在插入前停用索引，插入後再重新啟用。

1. 交易記錄檔

• 每個插入作業個別提交時，磁碟 I/O 會增加，導致處理速度降低。
• 對策: 使用交易，一次性提交。

1. 緩衝區設定的不足

• innodb_buffer_pool_size 或 bulk_insert_buffer_size 的設定過小時，資料插入可能變慢。
• 對策: 調整設定以確保足夠的記憶體。

Q4: 如果已有現有資料，是否能安全地執行多行插入？

A: 是的，可以使用以下方法，在避免與現有資料衝突的同時進行插入。

1. 使用 ON DUPLICATE KEY UPDATE

   INSERT INTO customers (id, name, email)
   VALUES (1, 'Updated Name', 'updated@example.com')
   ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email);

2. 使用 REPLACE INTO

   REPLACE INTO customers (id, name, email)
   VALUES (1, 'Replaced Name', 'replaced@example.com');

Q5: 進行批次處理時的最佳大小是多少？

A: 最佳批次大小會因以下要素而異：

• 伺服器的記憶體與 CPU 效能。
• 資料表結構（索引的有無、限制）。
• 資料量與記錄的大小。

一般而言，請以 100 行至 1000 行左右為基準進行調整。進行效能測試，找出最佳大小非常重要。

總結

在 FAQ 中，我們提供了在 MySQL 中進行資料插入時容易遇到的問題或疑問的具體解決方案。透過活用這些資訊，您將能夠更有效率且安全地執行插入作業。

8. 總結

MySQL 中的資料插入，從基本操作到進階技巧，有許多選擇。本文特別聚焦於「多行插入」，說明了高效且實用的方法。

文章要點

1. 基本的 INSERT 語句的語法

• 單行插入是 MySQL 的基本操作，資料型態與欄位的一致性很重要。

1. 將多行資料一次插入的方法

• 透過一次 SQL 語句插入多行，可以減輕網路負荷並提升效能。

1. 大量資料的批次插入

• 活用 LOAD DATA INFILE，可以有效率地插入大量資料。不過，需要注意安全性與設定。

1. 效能最佳化的要點

• 介紹了交易、索引無效化、批次處理、伺服器設定的調整等，提升插入效率的各種方法。

1. 與其他資料庫的差異

• MySQL 的插入方法相較於 PostgreSQL 或 Oracle 較為簡單，但理解各資料庫特性相應的手法很重要。

1. FAQ

• 具體呈現常見疑問與錯誤的解決方案，提供實際作業中有用的資訊。

最後

MySQL 中有效率地插入資料，在資料庫運作中非常重要。實踐本文內容，不僅能提升資料插入的效率，也能帶來系統整體效能的提升。

作為下一步，推薦以下實踐：

• 實際執行文章中介紹的 SQL 語句，確認動作。
• 選擇適合自身專案的插入手法，嘗試效能最佳化。
• 為了獲得更詳細的知識，參考 MySQL 官方文件或相關書籍。

MySQL 使用的資料操作請有效率化，連結到業務或專案的成功。