如何高效构建和管理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工具导入数据。