如何高效构建和管理MySQL数据库中的省市区信息?
- 行业动态
- 2024-09-29
- 9
MySQL省市区数据库是一个用于存储和管理中国行政区域信息的MySQL数据库,通常包括省、市、区(县)三级行政区划。
citycode.xlsx的Excel文件不能直接使用,需要另存为csv文件。
表格信息整理
在导入数据之前,需要对获取到的数据进行整理,具体步骤如下:
1、新建数据库表
CREATE TABLEsys_position
(id
int(11) NOT NULL AUTO_INCERMENT,area_name
varchar(255) DEFAULT NULL COMMENT '地区名称',area_code
int(11) DEFAULT NULL COMMENT '地区编码',city_code
varchar(11) DEFAULT NULL COMMENT '城市编码',level
tinyint(1) DEFAULT NULL COMMENT '地区等级',area_index
varchar(255) DEFAULT NULL COMMENT '地区索引', PRIMARY KEY (id
) ) ENGINE=InnoDB AUTO_INCREMENT=3537 DEFAULT CHARSET=utf8mb4;
2、编写函数处理数据
delimiter // drop function if exists create_city; create function create_city(provinceCode varchar(11)) returns varchar(20) begin declare intProvinceCode int(11); declare intCityCode int(11); declare loopTimes int(11) default 0; declare areaCityCode varchar(11); declare i int(11); declare tempPage int(11) default 0; declare countyIndex varchar(36); set intProvinceCode = CONVERT(provinceCode,SIGNED); 当前省下面有多少个市 select COUNT(1) into loopTimes from citycode2 where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000" and substring(adcode,5) ="00"; SET i = 1; 1.1先插入市的数据 insert into sys_position (area_name,area_code,city_code,level
) select area_name,adcode,citycode,2 from citycode2 where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000" and substring(adcode,5) ="00"; 1.2补充市的上级地区索引 update sys_position set area_index=provinceCode where area_code between intProvinceCode and intProvinceCode+10000 and substring(area_code,3) != "0000" and substring(adcode,5) ="00"; 2循环更新县的数据 WHILE i <= loopTimes DO SET tempPage = i1; 2.1获取市的编码 select adcode into areaCityCode from citycode2 where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000" and substring(adcode,5) ="00" limit tempPage,1; SET intCityCode = CONVERT(CONCAT(substring(areaCityCode,3,2),"00"),SIGNED); SET countyIndex = CONCAT(provinceCode,"",LPAD(intCityCode,2,"0")); 2.2插入县的数据 insert into sys_position (area_name,area_code,city_code,level
) select area_name,adcode,countyCode,3 from citycode2 where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000" and substring(adcode,5) != "00" and adcode like CONCAT(countyIndex,"%"); 2.3补充县的上级地区索引 update sys_position set area_index=countyIndex where adcode in (select adcode from citycode2 where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000" and substring(adcode,5) != "00" and adcode like CONCAT(countyIndex,"%")); SET i = i + 1; END WHILE; RETURN "success"; end // delimiter ;
3、调用函数导入数据
call create_city('11'); 北京市的地区代码是11
相关FAQs
Q1: MySql省市区数据库如何获取?
A1: 可以通过高德地图官网的JS API下载三级地区数据,然后将其转换为CSV格式并导入MySQL数据库,具体操作步骤如上所述。
Q2: 如何处理Excel文件中的数据以适应MySQL导入?
A2: 首先将Excel文件另存为逗号分隔的CSV文件,然后在Windows电脑上调整编码格式为UTF8,最后通过MySQL命令或Navicat工具导入数据。