JiaHe's Blog

根据地址检索匹配完整省市区县

发表于 2023-05-09 本文字数： 331 阅读时长 ≈ 1 分钟

步骤

AreaCity 省市区镇，下载ok_data_level4.csv
创建数据库，数据表，导入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `region`;
CREATE TABLE `region`  (
  `id` int(9) UNSIGNED NOT NULL COMMENT '城市编号',
  `pid` int(9) NOT NULL COMMENT '上级ID',
  `deep` int(1) NOT NULL COMMENT '层级深度',
  `name` varchar(255) NOT NULL COMMENT '简短地区名称',
  `pinyin_prefix` varchar(255) NOT NULL COMMENT '拼音前缀',
  `pinyin` varchar(255) NOT NULL COMMENT '完整拼音',
  `ext_id` bigint(13) NOT NULL COMMENT '数据源原始的编号',
  `ext_name` varchar(255) NOT NULL COMMENT '数据源原始的完整名称',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `pid_idx`(`pid`) USING BTREE,
  INDEX `name_idx`(`name`) USING BTREE,
  INDEX `ext_name_idx`(`ext_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

测试SQL

SELECT
	t2.province_name 省, t2.city_name 市, t2.district_name 区, t2.county_name "乡/县/街道"
FROM
	(
		SELECT
			r.id province_code, r.name province_name, r1.name city_name, r2.name district_name, r3.name county_name
		FROM
			region r
			LEFT JOIN region r1 ON r.id = r1.pid AND r1.deep = 1
			LEFT JOIN region r2 ON r1.id = r2.pid AND r2.deep = 2
			LEFT JOIN region r3 ON r2.id = r3.pid AND r3.deep = 3 
		WHERE
			r.deep = 0 
	) t2
WHERE
	-- '亚运村街道' 替换为需要补全或检索的地址关键词
	(
		INSTR ( '亚运村街道', t2.province_name ) 
		OR INSTR ( '亚运村街道', t2.city_name ) 
		OR INSTR ('亚运村街道', t2.district_name) 
		OR  INSTR ('亚运村街道', t2.county_name)
	)
	AND INSTR ( '亚运村街道', t2.county_name)