当前位置：首页 > news >正文

将 Excel 中的行政区域数据快速导入 MySQL

news 2026/4/19 22:11:22

将 Excel 中的行政区域数据快速导入 MySQL，关键在于利用现成的工具链和标准化数据源，避免手动拼接 SQL 的繁琐过程。这里提供从“极速上手”到“专业批量”的完整方案。

最佳方案：SQLyog/Navicat 直接导入（推荐）

这是最直接、最稳定、最高效的方法，尤其适合处理成千上万行的行政区域数据。

表结构如下：

CREATE TABLE IF NOT EXISTS area(

id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',

code VARCHAR(12) NOT NULL COMMENT '行政区划代码',

name VARCHAR(50) NOT NULL COMMENT '名称',

full_name VARCHAR(100) COMMENT '全称',

parent_code VARCHAR(12) COMMENT '父级代码',

level TINYINT NOT NULL COMMENT '层级: 1-省, 2-市, 3-区县, 4-乡镇,5-村',

longitude DECIMAL(10, 6) COMMENT '经度',

latitude DECIMAL(10, 6) COMMENT '纬度',

is_active TINYINT(1) DEFAULT 1 COMMENT '是否启用: 1-是, 0-否',

PRIMARY KEY (id),

UNIQUE KEY uk_code (code)

) ENGINE=INNODB COMMENT='行政区划表';

准备 Excel 文件

确保第一行是字段名（如 id,code,name,name,parent_code,level）。

数据规范：行政区划代码应为文本格式（如 110101），而非数字，防止开头的 0 被 Excel 自动过滤。

数据举例：下载地址http://www.zrscsoft.com/sitepic/12174.html
在 SQLyog 中操作

右键目标数据库 ->导入 ->导入外部数据。

在向导中选择Excel 文件 格式，并上传你的文件。

设置字段映射：确保 Excel 列与数据库表的字段一一对应，并将“区划代码”列的格式映射为 TEXT或 VARCHAR。

点击执行，数据将直接物理导入，无需生成中间 SQL。

在 Navicat 中（类似操作）

右键目标表 ->导入向导 -> 选择 Excel 文件。

设置映射后，在最后一步选择“导入模式”为“追加”。

方案二：使用 Python 脚本（自动化、可复用）

如果你的 Excel 结构复杂，或有清洗、校验的需求，几行 Python 代码是最灵活的解决方案。

import pandas as pd

import pymysql

from sqlalchemy import create_engine

# 1. 读取 Excel

df = pd.read_excel('行政区域.xlsx', dtype={'区划代码': str}) # 强制代码为文本

# 2. 数据清洗（按需）

df['区划代码'] = df['区划代码'].str.zfill(6) # 补全6位代码

df['上级代码'] = df['上级代码'].str.zfill(6)

# 3. 连接并写入 MySQL

# 使用 sqlalchemy 引擎

engine = create_engine('mysql+pymysql://用户名:密码@localhost:3306/数据库名?charset=utf8mb4')

# 写入数据库，如果表存在则追加

df.to_sql('area_info', con=engine, if_exists='append', index=False)

print("导入完成！")

优势：可处理任意大小的 Excel 文件，并可加入自动化的清洗逻辑（如代码补零、去重、格式转换）。

方案三：导出 CSV 并用 MySQL 命令导入（极速大批量）

当 Excel 文件超过 50 万行时，这是最快的方案，利用 MySQL 的 LOAD DATA命令。

Excel 另存为 CSV：在 Excel 中点击“文件” -> “另存为”，选择格式为CSV UTF-8。

使用 MySQL 命令行导入：

-- 登录 MySQL

mysql -u root -p

-- 使用你的数据库

USE your_database;

-- 执行导入命令

LOAD DATA LOCAL INFILE '/path/to/你的文件.csv'

INTO TABLE area_info

CHARACTER SET utf8mb4

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS -- 忽略 CSV 标题行

(code, name, parent_code, level); -- 对应表中的字段

注意：如果遇到 LOCAL权限问题，可去掉 LOCAL并将文件放在 MySQL 服务器可访问的路径。

方案四：生成 SQL 文件再导入（传统、通用）

如果你必须生成 SQL 文件，可使用在线工具或 Power Query 批量生成 INSERT语句，然后用 SQLyog 运行该 .sql文件。

快速生成 SQL：访问如magickit.ai 的“Excel 转 SQL”功能，粘贴 Excel 数据，选择“插入模式”，即可得到完整的 INSERT语句文本。

在 SQLyog 中执行：打开该 .sql文件，按 F9或点击执行图标。

关键注意事项

字符集：确保 MySQL 表、连接、文件的字符集为 utf8mb4，以支持所有汉字和特殊字符。

代码补零：Excel 中的代码（如 110101）必须以文本格式存储，防止开头的 0 丢失。在导入前，可先在 Excel 中将该列格式设置为“文本”。

数据去重：导入前，建议在 MySQL 表上为“区划代码”字段设置 UNIQUE约束，或在导入脚本中加入去重逻辑。

决策指南

数据规模	推荐方案	原因
< 10 万行	SQLyog/Navicat 直导	图形化操作，最省心
10 万 ~ 100 万行	Python 脚本	可控性强，支持清洗逻辑
> 100 万行	CSV + LOAD DATA	原生命令，性能极致
需生成可分享的脚本	在线工具转 SQL 文件	通用格式，便于交接