萬盛學電腦網

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

Excel 數據導入 MySQL 完整指南:命令行與 PHP 兩大實戰方案

前言

將 Excel 表格數據匯入 MySQL 是資料庫運維的高頻需求。本文介紹兩種主流方案:原生 LOAD DATA INFILE 命令適合大批量純數據導入;PHP-ExcelReader 庫適合需要邏輯處理、格式轉換的複雜場景。

方案一:CSV/TXT + LOAD DATA INFILE(高效、零依賴)

準備工作

  1. 在 Excel 中確保欄位順序、數量與目標資料表結構完全一致。
  2. 另存為 CSV (逗號分隔) 或 文字檔 (Tab 分隔),編碼建議 UTF-8。
  3. 將檔案放置於資料庫伺服器可讀取的路徑(如 C:\data\import.csv)。

導入命令

-- CSV 格式(欄位以逗號分隔)
LOAD DATA LOCAL INFILE 'C:/data/import.csv'
INTO TABLE `your_table`
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;  -- 跳過標題行

-- TXT 格式(欄位以 Tab 分隔)
LOAD DATA LOCAL INFILE 'C:/data/import.txt'
INTO TABLE `your_table`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

關鍵參數說明:

  • LOCAL:從客戶端讀取檔案,無需檔案置於伺服器。
  • FIELDS TERMINATED BY:指定分隔符。
  • ENCLOSED BY:CSV 中文字段常用雙引號包裹。
  • IGNORE 1 LINES:跳過標題行。

方案二:PHP-ExcelReader(支援 .xls/.xlsx、可寫入邏輯)

環境需求

核心代碼範例

<?php
require_once 'reader.php';

$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('utf-8');  // 輸出編碼
$data->read('data.xlsx');           // 支援 xls / xlsx

$mysqli = new mysqli('localhost', 'root', 'password', 'mydb');
$mysqli->set_charset('utf8mb4');

$sheet = $data->sheets[0];
$rowCount = $sheet['numRows'];
$colCount = $sheet['numCols'];

$stmt = $mysqli->prepare("INSERT INTO `target_table` (col1, col2, col3) VALUES (?, ?, ?)");

for ($i = 2; $i <= $rowCount; $i++) {  // 從第 2 行開始(跳過標題)
    $val1 = $sheet['cells'][$i][1] ?? '';
    $val2 = $sheet['cells'][$i][2] ?? '';
    $val3 = $sheet['cells'][$i][3] ?? '';
    $stmt->bind_param('sss', $val1, $val2, $val3);
    $stmt->execute();
}
$stmt->close();
$mysqli->close();
echo "匯入完成,共處理 $rowCount 筆資料。";

優缺點對比

維度LOAD DATA INFILEPHP-ExcelReader
速度極快(百萬級秒級)中等(受 PHP 處理限制)
格式支援CSV/TXTXLS, XLSX, ODS
資料清洗困難靈活(可在迴圈中處理)
部署依賴無需 PHP 環境與庫檔案

最佳實踐建議

  • 大量純數據匯入 → 首選 LOAD DATA INFILE。
  • 需處理合併儲存格、公式、多工作表、或寫入前驗證 → 使用 PHP 方案。
  • 生產環境務必先在測試庫驗證,確認編碼、欄位對應無誤後再操作正式庫。
copyright © 萬盛學電腦網 all rights reserved