导入前准备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 |
更稳妥的流程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.