导入前准备Prepare Before Import

  • 表已存在:先建好目标表,列名、类型与 CSV 列一一对应。
  • 编码统一:建议 CSV 用 UTF-8(无 BOM);MySQL 建表时指定 CHARACTER SET utf8mb4。
  • 清洗先行:去重、修日期格式、处理空值,能大幅降低导入报错。
  • Table exists: create the target table first, with columns/types matching the CSV.
  • Unified encoding: prefer UTF-8 (no BOM); in MySQL set CHARACTER SET utf8mb4.
  • Clean first: dedupe, fix dates, handle nulls — it cuts import errors sharply.

MySQL:LOAD DATAMySQL: LOAD DATA

LOAD DATA INFILE '/path/data.csv' INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 LINES;

要点:IGNORE 1 LINES 跳过表头;ENCLOSED BY '"' 处理带引号字段;若报 "secure-file-priv",需把文件放到允许的目录或用 LOAD DATA LOCAL INFILE。

Key points: IGNORE 1 LINES skips the header; ENCLOSED BY '"' handles quoted fields; if you hit "secure-file-priv", move the file to the allowed dir or use LOAD DATA LOCAL INFILE.

PostgreSQL:COPYPostgreSQL: COPY

COPY users FROM '/path/data.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');

PostgreSQL 的 COPY 对 CSV 支持很完善,HEADER true 表示首行是表头,ENCODING 'UTF8' 指定编码。需要超级用户或用 \copy(psql 客户端侧)替代。

PostgreSQL's COPY handles CSV well; HEADER true means the first row is the header, ENCODING 'UTF8' sets encoding. You need superuser rights, or use \copy (client-side in psql).

常见报错与对策Common Errors & Fixes

现象Symptom原因Cause对策Fix
中文变乱码Garbled Chinese编码不一致Encoding mismatch统一 UTF-8,去 BOMUnify UTF-8, strip BOM
列数不符Column count error引号/换行未转义Bad quoting/newline核对引号规则Check quoting rules
日期报错Date error格式非标准Non-standard format先规范为 YYYY-MM-DDNormalize to YYYY-MM-DD
提示:导入前可用 365Lab 在线 CSV 工具 校验编码、去重、规范日期与数字,再导出干净的 CSV,能省去大量排错时间。
Tip: Before importing, use the 365Lab toolkit to verify encoding, dedupe, and normalize dates/numbers, then export a clean CSV — it saves hours of debugging.

更稳妥的流程A Safer Workflow

先用在线工具把 CSV 清洗成"数据库友好"的形态(UTF-8 无 BOM、规范类型、无多余引号),再执行 LOAD DATA / COPY。这样既能避免 90% 的报错,也能保证导入后数据可直接查询。

First clean the CSV into a "database-friendly" shape (UTF-8 no BOM, normalized types, no stray quotes) with the online tool, then run LOAD DATA / COPY. This avoids ~90% of errors and ensures queryable data.

想马上试试?打开在线工具,拖入 CSV 做编码校验与清洗,再导出干净文件用于导入。

Want to try now? Open the toolkit, drop in your CSV to verify encoding and clean it, then export a clean file for import.

打开 CSV 清洗工具 →Open the CSV Cleaner →