业务开发

将github数据库文件转换成city表步骤

下载sql文件

navicat新建sqlLite数据库

执行sql

CREATE TABLE "cms_city" (
      "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
      "province_id" integer(20),
      "province_name" text(64),
      "city_id" integer(20),
      "city_name" text(64),
      "county_id" integer(20),
      "county_name" text(64),
      "town_id" integer(20),
      "town_name" text(64),
      "village_id" integer(20),
      "village_name" text(64)
    );


    INSERT INTO cms_city(village_id,village_name,town_id,county_id,city_id,province_id,town_name,county_name,city_name,province_name) SELECT * FROM (

    (SELECT
    	vsac.*,
    	province.`name` AS province_name 
    FROM
    	(
    		(
    		SELECT
    			vsa.*,
    			city.`name` AS city_name 
    		FROM
    			(
    				(
    				SELECT
    					vs.*,
    					area.`name` AS county_name
    				FROM
    					(
    						(
    						SELECT
    							village.`code` AS village_id,
    							village.`name` AS village_name,
    							village.streetCode AS town_id,
    							village.areaCode AS county_id,
    							village.cityCode AS city_id,
    							village.provinceCode AS province_id,
    							street.`name` AS town_name
    						FROM
    							village
    							LEFT JOIN street ON village.streetCode = street.`code` 
    						) AS vs 
    					)
    					LEFT JOIN area ON area.`code` = vs.county_id 
    				) AS vsa 
    			)
    			LEFT JOIN city ON city.`code` = vsa.city_id 
    		) AS vsac 
    	)
    	LEFT JOIN province ON province.`code` = vsac.province_id) as city

    );

    update cms_city set city_id = city_id *100000000,county_id = county_id*1000000,town_id = town_id*1000;

导出cms_city

在mysql创建city表

CREATE TABLE `city` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `province_id` bigint(20) unsigned DEFAULT NULL COMMENT '省编号',
      `province_name` varchar(64) DEFAULT NULL COMMENT '省名称',
      `city_id` bigint(20) unsigned DEFAULT NULL COMMENT '城市编号',
      `city_name` varchar(64) DEFAULT NULL COMMENT '城市名称',
      `county_id` bigint(20) unsigned DEFAULT NULL COMMENT '区编号',
      `county_name` varchar(64) DEFAULT NULL COMMENT '区名称',
      `town_id` bigint(20) unsigned DEFAULT NULL COMMENT '镇\\县编号',
      `town_name` varchar(64) DEFAULT NULL COMMENT '镇\\县名称',
      `village_id` bigint(20) unsigned DEFAULT NULL COMMENT '街道编号',
      `village_name` varchar(64) DEFAULT NULL COMMENT '街道名称',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `province_id` (`province_id`) USING BTREE,
      KEY `city_id` (`city_id`) USING BTREE,
      KEY `county_id` (`county_id`) USING BTREE,
      KEY `town_id` (`town_id`) USING BTREE,
      KEY `village_id` (`village_id`) USING BTREE,
      KEY `id` (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=604852 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='省市县镇村数据';

在mysql数据库还原

城市数据查询优化

通过代码生成器拖拽城市控件自动生成城市业务代码。

通过城市管理手动生成city.json文件，可以减少城市的数据查询效率问题

ms.http.get("/static/json/city.json").then(function (data) {

   if (data.result) {

     that.provinces = data.data;

   }

});