一、问题

发现我们项目中省市区是以前的了，有些县改成区了，编码也变了。数据库需要重新导入，维护新的数据。

二、数据来源

参照：https://blog.csdn.net/qiphon3650/article/details/109241243
找到：http://lbs.tianditu.gov.cn/server/administrative2.html
下载省市县行政区划名称及编码对照表

三、数据整理和导入

1、excel数据整理

截取正确的code

=RIGHT(C2,6)

生成县sql

="INSERT INTO provinces(province_code,province_name)VALUES('"&D2&"','"&B2&"');"

生成市sql

="INSERT INTO cities(city_code,city_name,province_code)VALUES('"&G2&"','"&E2&"','"&D2&"');"

生成区、县sql

="INSERT INTO areas(area_code,area_name,city_code)VALUES('"&J2&"','"&H2&"','"&G2&"');"

去重网站：
https://it.oogao.com/textremoveduplicate/

2、数据库表结构

CREATE TABLE `provinces` (
  `id` int NOT NULL AUTO_INCREMENT,
  `province_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `province_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `longitude` varchar(16) DEFAULT NULL,
  `latitude` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='省份信息表';

CREATE TABLE `cities` (
  `id` int NOT NULL AUTO_INCREMENT,
  `city_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `city_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `province_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `longitude` varchar(16) DEFAULT NULL,
  `latitude` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='行政区域地州市信息表';

CREATE TABLE `areas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `area_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `area_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `city_code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `longitude` varchar(16) DEFAULT NULL,
  `latitude` varchar(16) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2898 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC COMMENT='行政区域县区信息表';

导入生成的sql