场景:把表格灌进数据库Scenario: Load a Spreadsheet into a Database
当你有一份 Excel 导出的 CSV,需要把它导入 MySQL、PostgreSQL 或 SQLite 时,最省事的办法是生成一批 INSERT 语句,然后在数据库客户端里执行。这样无需安装数据库驱动,也不依赖图形导入向导。
When you have a CSV exported from Excel and need it in MySQL, PostgreSQL or SQLite, the easiest path is to generate a batch of INSERT statements and run them in your DB client. No drivers or GUI import wizards required.
生成 INSERT 的关键点Key Points When Generating INSERTs
- 表名与列名:用 CSV 表头作为列名,注意保留字(如
order)需要加反引号或双引号。 - 字符串要加引号:文本值用单引号包裹,且内部的单引号要写成两个单引号来转义。
- 数字与布尔:数值不要加引号;布尔可写为 0/1 或 TRUE/FALSE。
- 日期:保持
YYYY-MM-DD或YYYY-MM-DD HH:MM:SS格式。
- Table & column names: Use CSV headers as columns; quote reserved words (e.g.
order) with backticks or double quotes. - Quote strings: Wrap text in single quotes and escape inner single quotes by doubling them.
- Numbers & booleans: Numerics stay unquoted; booleans can be 0/1 or TRUE/FALSE.
- Dates: Keep
YYYY-MM-DDorYYYY-MM-DD HH:MM:SS.
INSERT INTO users(name, city) VALUES ('张三', '北京');
INSERT INTO users(name, city) VALUES ('李四', '上海');INSERT INTO users(name, city) VALUES ('Zhang San', 'Beijing');更稳妥的批量写法A Safer Batch Form
把多行合并成一条多值 INSERT,能大幅减少语句条数、提升导入速度:INSERT INTO users(name, city) VALUES ('张三','北京'),('李四','上海');。同时先在数据库里建好表结构,并确认列顺序与 CSV 一致。
Merge rows into one multi-value INSERT to cut statement count and speed up import: INSERT INTO users(name, city) VALUES ('Zhang San','Beijing'),('Li Si','Shanghai');. First create the table and make sure the column order matches the CSV.
避坑清单Pitfall Checklist
- Garbled Chinese: use UTF-8 for both DB and connection (see garbled-text fix).
- Duplicates: remove duplicate rows before import.
- Big files: for millions of rows, batch or chunk first.
想马上试试?打开在线工具,拖入 CSV 即可生成 SQL,全程本地处理。
Want to try now? Open the toolkit, drop in your CSV to generate SQL — everything runs locally.