本文 AI 產出，尚未審核

MySQL 基本資料 CRUD – INSERT 新增資料

簡介

在資料庫操作的四大核心功能（Create、Read、Update、Delete）中，INSERT 是最常使用、也是最基礎的動作。無論是建立使用者帳號、紀錄交易明細，或是把爬蟲抓取的資料寫入資料庫，最終都會透過 INSERT 來完成「新增」的任務。
對於剛踏入資料庫世界的開發者而言，掌握 INSERT 的語法與最佳實踐，不僅能避免資料錯誤、效能瓶頸，還能為後續的 資料驗證、批次匯入、事務管理 打下堅實基礎。

本篇文章將以 MySQL 為例，從最簡單的單筆插入，到多筆批次插入、使用子查詢、以及結合 ON DUPLICATE KEY UPDATE 的衝突處理，提供完整且實務導向的說明，幫助你在開發過程中快速、正確地寫入資料。

核心概念

1. 基本 INSERT 語法

最基本的 INSERT 語法如下：

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
  • table_name：要插入資料的資料表名稱。
  • column1, column2, ...：指定要寫入的欄位，順序必須與 VALUES 中的值對應。
  • VALUES：一組或多組要插入的資料列。

小技巧：若所有欄位都有值且欄位順序與資料表定義相同，可以省略欄位清單，直接寫 INSERT INTO table_name VALUES (...);，但不建議這樣做，因為未來若欄位結構變動會造成程式碼破壞。

2. 單筆插入與多筆批次插入

2.1 單筆插入

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES ('張', '小明', '2024-10-01', 58000);
  • 只插入一筆資料，最常用於表單送出或 API 新增單筆資源的情境。

2.2 多筆批次插入

一次插入多筆資料可以減少與資料庫的往返次數，提升效能：

INSERT INTO employees (first_name, last_name, hire_date, salary)
VALUES
  ('李', '大華', '2024-09-15', 62000),
  ('王', '美麗', '2024-09-20', 59000),
  ('陳', '志強', '2024-10-02', 61000);
  • 優點：一次執行完成多筆寫入，降低網路延遲與交易開銷。
  • 限制：單條 INSERT 語句的字元長度受 MySQL max_allowed_packet 參數限制，必要時可調整此參數。

3. 使用子查詢插入資料

有時候想把另一張表的資料「搬」過來，直接在 INSERT 中使用子查詢即可：

INSERT INTO archived_orders (order_id, customer_id, total_amount, archived_at)
SELECT order_id, customer_id, total_amount, NOW()
FROM orders
WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);
  • 此方式不需要先把資料讀到應用程式，再寫回資料庫，一次完成搬移，效能更好。
  • 注意子查詢返回的欄位順序與 INSERT 目標欄位必須一致。

4. INSERT 與自動遞增 (AUTO_INCREMENT)

若資料表的主鍵使用 AUTO_INCREMENT，不需要在 INSERT 語句中指定該欄位：

CREATE TABLE products (
  product_id   INT AUTO_INCREMENT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL,
  price        DECIMAL(10,2) NOT NULL,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price) VALUES ('筆記型電腦', 29999.99);
  • 插入成功後，MySQL 會自動產生 product_id，可透過 LAST_INSERT_ID() 取得剛插入的主鍵值，常用於後續的關聯寫入。
SELECT LAST_INSERT_ID();   -- 取得最近一次自動遞增的值

5. 防止重複鍵衝突：ON DUPLICATE KEY UPDATE

在實務上，常見「如果資料已存在就更新」的需求。MySQL 提供 ON DUPLICATE KEY UPDATE 讓 INSERT 同時兼具 UPSERT 功能：

INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES (101, 1, 500)
ON DUPLICATE KEY UPDATE
  quantity = quantity + VALUES(quantity);
  • 若 (product_id, warehouse_id) 為唯一鍵且已存在，則執行 UPDATE，此例中把庫存量 累加。
  • VALUES(column_name) 可以取得本次 INSERT 提供的值，寫法更直觀。

程式碼範例

以下示範 5 個常見且實用的 INSERT 實作，包含完整註解說明。

範例 1：基本單筆插入（含時間戳記）

-- 建立測試表
CREATE TABLE users (
  user_id   INT AUTO_INCREMENT PRIMARY KEY,
  username  VARCHAR(50) NOT NULL UNIQUE,
  email     VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入一筆使用者資料
INSERT INTO users (username, email)
VALUES ('alice', 'alice@example.com');

說明：created_at 欄位使用 DEFAULT CURRENT_TIMESTAMP，不必在 INSERT 時指定，資料會自動帶入插入時間。

範例 2：一次插入多筆資料（批次匯入）

INSERT INTO users (username, email)
VALUES
  ('bob',   'bob@example.com'),
  ('cathy', 'cathy@example.org'),
  ('david', 'david@example.net');

說明：適用於 CSV 匯入或大量測試資料建立，執行效率遠高於逐筆 INSERT。

範例 3：使用子查詢搬移資料

-- 假設有舊的訂單表 orders
CREATE TABLE orders (
  order_id   INT PRIMARY KEY,
  customer_id INT,
  total      DECIMAL(10,2),
  order_date DATE
);

-- 建立新表 archived_orders
CREATE TABLE archived_orders (
  order_id   INT PRIMARY KEY,
  customer_id INT,
  total      DECIMAL(10,2),
  archived_at TIMESTAMP
);

-- 把一年以前的訂單搬到 archived_orders
INSERT INTO archived_orders (order_id, customer_id, total, archived_at)
SELECT order_id, customer_id, total, NOW()
FROM orders
WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

說明：一次完成資料搬移，適合資料清理或分區管理。

範例 4：UPSERT（插入或更新）庫存

CREATE TABLE inventory (
  product_id   INT,
  warehouse_id INT,
  quantity     INT,
  PRIMARY KEY (product_id, warehouse_id)
);

-- 若庫存不存在則新增，若已存在則加總
INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES (2001, 1, 100)
ON DUPLICATE KEY UPDATE
  quantity = quantity + VALUES(quantity);

說明：此語句在高併發的電商系統中尤為重要，可避免「重複下單」導致的庫存不一致。

範例 5：取得自動遞增 ID 並做關聯寫入

-- 建立兩張關聯表
CREATE TABLE orders (
  order_id   INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL
);

CREATE TABLE order_items (
  item_id    INT AUTO_INCREMENT PRIMARY KEY,
  order_id   INT NOT NULL,
  product_id INT NOT NULL,
  qty        INT NOT NULL,
  price      DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入訂單，同時取得產生的 order_id
INSERT INTO orders (customer_id, order_date)
VALUES (12345, CURDATE());

SET @new_order_id = LAST_INSERT_ID();

-- 使用剛剛取得的 order_id 寫入明細
INSERT INTO order_items (order_id, product_id, qty, price)
VALUES
  (@new_order_id, 101, 2, 199.99),
  (@new_order_id, 202, 1, 349.50);

說明：透過變數 @new_order_id 把父表產生的主鍵傳遞給子表，保證資料的完整性。

常見陷阱與最佳實踐

常見問題 為什麼會發生 解決方式或最佳實踐
忘記指定欄位清單 欄位順序變動或新增欄位時程式會錯誤 永遠寫 INSERT INTO table (col1, col2, …)，即使目前所有欄位都有值
插入空字串或 NULL 產生資料不一致 某些欄位允許 NULL，但程式傳入空字串 在 INSERT 前使用 資料驗證，或在欄位上設定 NOT NULL DEFAULT ''
自動遞增主鍵衝突 手動插入已存在的 ID，或複製資料時未排除主鍵 避免手動指定 AUTO_INCREMENT 欄位，若需搬移資料可先關閉自增或使用 INSERT IGNORE
大量批次插入導致 max_allowed_packet 錯誤 單條語句過大超過 MySQL 設定 調整 max_allowed_packet，或把批次切成較小的塊（如每 1000 筆）
重複鍵錯誤未處理 INSERT 時唯一鍵衝突，程式直接拋錯 使用 INSERT IGNORE（略過）或 ON DUPLICATE KEY UPDATE（更新）根據需求處理
忘記提交事務 在 InnoDB 引擎下，未 COMMIT 會導致資料暫存 使用事務（START TRANSACTION … COMMIT）保證多表寫入的一致性
字元編碼不一致 客戶端與資料庫使用不同的編碼，導致中文亂碼 確認連線字元集設定為 utf8mb4，並在建表時指定 CHARACTER SET utf8mb4

最佳實踐總結：

  1. 永遠列出欄位名稱，讓程式更具可讀性與維護性。
  2. 批次插入 時注意 max_allowed_packet 與事務的使用。
  3. 使用參數化查詢（prepared statements）或 ORM，避免 SQL Injection。
  4. 利用 ON DUPLICATE KEY UPDATE 處理唯一鍵衝突，保持資料完整性。
  5. 在需要取得自增 ID 時，立刻使用 LAST_INSERT_ID()，避免其他連線干擾。

實際應用場景

場景 為何需要 INSERT 典型寫法
使用者註冊 新增帳號資料、建立預設設定 INSERT INTO users (username,email,password) VALUES (?,?,?);
電商下單 同時寫入 orders、order_items，必須取得 order_id 先 INSERT INTO orders … → LAST_INSERT_ID() → 多筆 INSERT INTO order_items
日誌/事件追蹤 每次操作寫入 audit_log 表，供日後分析 INSERT INTO audit_log (user_id,action,created_at) VALUES (?,?,NOW());
資料搬移/歸檔 定期把舊資料搬到歷史表，減少主表負載 INSERT INTO archive_table SELECT … FROM main_table WHERE …;
批次匯入外部檔案 CSV、Excel 轉成資料庫記錄 LOAD DATA INFILE 'file.csv' INTO TABLE target FIELDS TERMINATED BY ',';（底層仍是批次 INSERT）

小結：在上述所有情境中，INSERT 的正確使用與效能優化直接影響系統的可靠性與使用者體驗。

總結

  • INSERT 是資料庫 CRUD 中最基礎也是最常見的操作，掌握正確語法與最佳實踐能讓開發更順暢。
  • 從 單筆插入、批次插入、子查詢搬移、自動遞增、到 UPSERT（ON DUPLICATE KEY UPDATE），每種方式都有其適用情境與性能考量。
  • 常見的陷阱（忘記欄位清單、批次過大、唯一鍵衝突等）只要在設計階段做好驗證與事務管理，就能有效避免。
  • 在實務開發中，從 使用者註冊、電商下單、日誌追蹤 到 資料歸檔，INSERT 都扮演著關鍵角色。
  • 最後，別忘了 使用參數化查詢、適時使用事務、以及 設定正確的字元集，這些細節往往是系統穩定性的關鍵。

透過本篇的說明與範例，你已經具備在 MySQL 中安全、有效地新增資料的能力。接下來，只要把這些概念落實在實際的程式碼與專案中，就能讓你的資料庫操作更加可靠且具可維護性。祝開發順利！ 🚀