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工具导入数据。