最新的省市区三级地区MySQL数据库,附带获取方法

想要直接拿MySQL地区sql文件的,直接跳到文章末尾获取

前言

    之前也写过类似的获取方式,是从国家统计局获取5级地区信息,方法也比较麻烦,在实际使用过程中,也很少遇到要精确到5级的情况,所以,这次就更新一下,如何获取三级地区信息。

1、获取数据

进入高德地图的官网,下载到我们需要的数据:高德地图官网

我当时的获取位置是:开发支持==>web端==>地图JS API,拉到最下面,就能看到相关下载链接,点击后,就是上面图片的位置。

下载后,将得到一个citycode.xlsx的Excel文档,这个文档不能直接使用,需要做一下转换,将Excel变成csv文件,才能做进一步的操作。

2、数据处理

在Windows电脑上,将上面的citycode.xlsx文件打开,另存为csv文件,注意是逗号分隔的;

最后还需要调整一下编码格式,可以用记事本等工具把导出的citycode.csv文件打开,重新保存一下,保存的时候,选择UTF-8的中文编码格式,得到最后的citycode2.csv文件;

做完上述的步骤之后,就可以导入到数据库中了,关于如何导入,有两种方式:

一、参照我之前写的一篇博文:MySQL导入导出csv文件,用命令导入

二、用Navcat工具导入,步骤如下:

1、选择一个数据库,在table选项上右键==>Import Wizard

2、选择CSV File(*.csv),点击Next

3、Improt From选择我们上面的citycode2.csv,编码格式UTF-8(65001)

4、选择行分隔符为CRLF(Windows系统用的是这个,鉴于我们导出的CSV文件也是office导出的,office又是运行在Windows上的,所以这里不会有变化),分隔符用默认的逗号,如果不放心,自己再打一个英文逗号

5、之后就可以一直下一步了,最后确认导入。

6、最后,我们得到一张citycode2的数据库表,如下:

 

3、信息整理

我们拿到的这么一张数据库表,并不能直接使用,像地区等级,上下级等等的信息都没有,所以还需要加工处理一下才能使用

我这边是写了一个函数,将数据全部清理了一遍,导入到新的地区表中了,下面是新建的数据库表,以及我编写的函数:

CREATE TABLE `sys_position` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `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;
drop function if exists create_city;
delimiter $$
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(area_code,5) ="00";
  -- 2循环更新县的数据
  WHILE i <= loopTimes DO
   SET tempPage = i-1;
   -- 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 intCityCode = intProvinceCode+intCityCode;
   -- 2.2插入县的数据
   insert into sys_position (area_name,area_code,city_code,`level`) 
   select area_name,adcode,citycode,3 from citycode2 
   where adcode between intCityCode and intCityCode+100 and substring(adcode,3) != "0000" and substring(adcode,5) !="00";
   -- 2.3补充县的上级地区索引 这里需要循环取出
   SET countyIndex = CONCAT(provinceCode,",",areaCityCode);
   update sys_position set area_index=countyIndex 
   where area_code between intCityCode and intCityCode+100 and substring(area_code,3) != "0000" and substring(area_code,5) !="00";
   SET i = i + 1;
  END WHILE;
  -- 补处理没有市信息,直接是区级别的
  -- 3.1 如果省下面一个市(5-6位是0)也没有 将区级别的直接归为市
  IF loopTimes = 0 THEN
  	insert into sys_position (area_name,area_code,city_code,`level`,area_index) 
  	select area_name,adcode,citycode,2,provinceCode from citycode2 
  	where adcode between intProvinceCode and intProvinceCode+10000 and substring(adcode,3) != "0000";
  -- 3.2 如果是省,取上述循环结束后的地区编码,表格是按照数字增长的,所以将没有上级市的“市”也划为市
  ELSE
  	insert into sys_position (area_name,area_code,city_code,`level`,area_index) 
  	select area_name,adcode,citycode,2,provinceCode from citycode2 
  	where adcode between intCityCode+100 and intProvinceCode+10000 and substring(adcode,3) != "0000";  	
  END IF;
  return 'hello';
end$$ 
delimiter ;

最后执行了一条sql语句:

select create_city(adcode) from citycode2 where substring(adcode,3)="0000" and adcode!="100000" and adcode!="900000"

函数的内容有点复杂,这里就不详细去讲解了,有兴趣的人可以分析一下

另外需要补充的一点是,从原表到新表,少了1条数据,我检查了很久也不知道是哪里出了问题,由于影响很小,我这里也就没管了,有小伙伴看完之后,帮忙修复上述函数的漏洞或者补充出来这1条数据,也是极好的。

最后,大家有什么不懂的或者其他需要交流的内容,也可以进入我的QQ讨论群一起讨论:654331206

文件下载地址:链接:https://pan.baidu.com/s/11MgGykeUup-pv0v4PU5Tqg  密码:0nd5

©️2020 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值