萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

MySQL INSERT 語法全解析:從基礎插入到高效批次寫入策略

掌握 INSERT 所有變體,避開常見陷阱

INSERT 是最基礎也最高頻的寫入操作。MySQL 提供多種語法變體,適用不同場景。本文系統梳理語法細節與效能優化要點。

標準語法形式

-- 1. 明確指定欄位 (推薦,防結構變更出錯)
INSERT INTO tbl_name (col1, col2, ...) VALUES (val1, val2, ...);

-- 2. 全欄位插入 (須按建表順序,不推薦生產用)
INSERT INTO tbl_name VALUES (val1, val2, ...);

-- 3. 多列單語句批次插入 (高效,單次網路往返)
INSERT INTO tbl_name (col1, col2) VALUES
  (1, 'a'), (2, 'b'), (3, 'c'), ...;

-- 4. INSERT ... SELECT (表間複製/轉換)
INSERT INTO target_tbl (col1, col2)
SELECT colA, colB FROM source_tbl WHERE condition;

-- 5. INSERT ... ON DUPLICATE KEY UPDATE (Upsert 核心)
INSERT INTO tbl (id, val) VALUES (1, 'new')
ON DUPLICATE KEY UPDATE val = VALUES(val), updated_at = NOW();

效能優化鐵律

  1. 批次插入:單條 `INSERT` 開銷極大 (網路、解析、鎖、刷盤)。**每批 500~5000 行**,單次 SQL 長度 < `max_allowed_packet` (預設 4MB,可調大)。
  2. 關閉索引/外鍵檢查 (大量載入時):
    SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;
    -- 大量 INSERT
    SET FOREIGN_KEY_CHECKS=1; SET UNIQUE_CHECKS=1;
  3. 主鍵順序插入:InnoDB 聚簇索引按主鍵順序存儲,亂序主鍵 (如 UUID) 導致頁分裂、碎片、寫入放大。**推薦自增整數或有序 UUID (UUID v7/COMB)**。
  4. 使用預處理語句:`PreparedStatement` 減少解析開銷,防注入。
  5. 調大 `innodb_log_file_size` / `innodb_buffer_pool_size`** 承接寫入突發。

常見陷阱與解法

現象原因解法
Duplicate entry '0' for key 'PRIMARY'自增主鍵耗盡或顯式插入 0檢查主鍵類型 (BIGINT)、重置自增值、避免插入 0
Data truncated for column數值超出範圍/字串過長修正數據或調整欄位定義
Lock wait timeout exceeded行鎖衝突、長事務縮短事務、優化索引、調大 `innodb_lock_wait_timeout`
Packet too large批次 SQL 超過 `max_allowed_packet`拆分批次或調大參數 (需重啟)

LOAD DATA INFILE:極速大量載入

超過 10 萬行時,`LOAD DATA INFILE` 比批次 INSERT **快 10~20 倍**:

LOAD DATA INFILE '/path/data.csv'
INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES (col1, col2, ...);

需 `FILE` 權限且檔案在伺服器端 (或 `LOCAL` 關鍵字從客戶端讀取)。

copyright © 萬盛學電腦網 all rights reserved