在业务数据管理中，根据用户所在地的地理位置信息自动为数据行设置所属大区是常见需求，既可以提升数据录入效率，也能保证大区划分规则的统一性。实现该需求的核心逻辑是先明确用户所在地的地理标识，再匹配预设的大区划分规则，最终将大区信息写入对应数据行。
基础实现思路
实现自动设置大区的核心步骤分为三步：第一步获取用户的地理位置信息，比如省份、城市或者经纬度坐标；第二步建立地理位置与大区的映射规则；第三步在写入数据时通过SQL逻辑自动匹配大区信息。
基于行政区域的映射方案
如果大区是按照省份、城市等行政区域划分的，最简单的方式是建立一张区域映射表，存储每个行政区域对应的所属大区，写入数据时通过关联查询匹配大区信息。
首先创建区域映射表：
-- 创建区域与大区映射表
CREATE TABLE region_mapping (
id INT PRIMARY KEY AUTO_INCREMENT,
province VARCHAR(50) NOT NULL COMMENT '省份名称',
city VARCHAR(50) DEFAULT NULL COMMENT '城市名称，为空时表示整个省份归属该大区',
region_name VARCHAR(50) NOT NULL COMMENT '所属大区名称',
UNIQUE KEY uk_province_city (province, city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行政区域与大区映射表';
-- 插入示例映射数据
INSERT INTO region_mapping (province, city, region_name) VALUES
('广东省', '广州市', '华南大区'),
('广东省', '深圳市', '华南大区'),
('广东省', NULL, '华南大区'),
('浙江省', NULL, '华东大区'),
('江苏省', NULL, '华东大区'),
('四川省', NULL, '西南大区');
当用户数据写入时，假设用户表包含user_province和user_city字段，插入数据时可以关联映射表获取大区信息：
-- 插入用户数据并自动设置所属大区
INSERT INTO user_info (user_name, user_province, user_city, belong_region)
SELECT
'张三',
'广东省',
'深圳市',
rm.region_name
FROM region_mapping rm
WHERE rm.province = '广东省'
AND (rm.city = '深圳市' OR rm.city IS NULL)
ORDER BY rm.city DESC -- 优先匹配城市级别的规则
LIMIT 1;
基于经纬度的地理空间判断方案
如果用户所在地只有经纬度坐标，没有明确的行政区域信息，可以使用数据库的地理空间函数判断坐标所属的大区范围。以MySQL为例，首先创建大区范围表，存储每个大区的地理边界多边形：
-- 开启地理空间扩展支持
CREATE TABLE region_polygon (
id INT PRIMARY KEY AUTO_INCREMENT,
region_name VARCHAR(50) NOT NULL COMMENT '大区名称',
polygon GEOMETRY NOT NULL COMMENT '大区边界多边形',
SPATIAL INDEX idx_polygon (polygon)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='大区地理范围表';
-- 插入示例大区范围，这里用简化的矩形范围演示
INSERT INTO region_polygon (region_name, polygon) VALUES
('华南大区', ST_GeomFromText('POLYGON((113 20, 115 20, 115 25, 113 25, 113 20))')),
('华东大区', ST_GeomFromText('POLYGON((118 27, 122 27, 122 31, 118 31, 118 27))'));
写入用户数据时，通过ST_Contains函数判断用户经纬度是否在大区范围内：
-- 插入用户数据并根据经纬度匹配大区
INSERT INTO user_info (user_name, user_lng, user_lat, belong_region)
SELECT
'李四',
114.05,
22.55,
rp.region_name
FROM region_polygon rp
WHERE ST_Contains(rp.polygon, ST_GeomFromText(CONCAT('POINT(', 114.05, ' ', 22.55, ')')))
LIMIT 1;
进阶优化方案
使用触发器自动填充大区
如果希望所有写入用户表的数据都自动设置大区，不需要每次插入都写关联逻辑，可以创建触发器，在插入数据前自动匹配大区信息。
-- 创建触发器，插入用户数据时自动设置大区
DELIMITER //
CREATE TRIGGER before_user_insert
BEFORE INSERT ON user_info
FOR EACH ROW
BEGIN
DECLARE v_region VARCHAR(50);
-- 优先按城市匹配，再按省份匹配
SELECT region_name INTO v_region
FROM region_mapping
WHERE province = NEW.user_province
AND (city = NEW.user_city OR city IS NULL)
ORDER BY city DESC
LIMIT 1;
-- 如果匹配到规则，设置大区字段
IF v_region IS NOT NULL THEN
SET NEW.belong_region = v_region;
END IF;
END //
DELIMITER ;
使用CASE表达式快速判断简单规则
如果大区划分规则非常简单，比如只有几个固定省份归属固定大区，也可以直接使用CASE表达式在SQL中完成判断，不需要额外创建映射表。
-- 使用CASE表达式判断大区
INSERT INTO user_info (user_name, user_province, belong_region)
VALUES (
'王五',
'浙江省',
CASE
WHEN user_province IN ('广东省', '广西壮族自治区', '海南省') THEN '华南大区'
WHEN user_province IN ('浙江省', '江苏省', '上海市') THEN '华东大区'
WHEN user_province IN ('四川省', '重庆市', '贵州省') THEN '西南大区'
ELSE '其他大区'
END
);
注意事项
- 映射表的规则需要定期维护，当大区划分规则调整时，及时更新映射数据，保证大区匹配的准确性。
- 使用地理空间函数时，需要确认当前使用的数据库版本是否支持相关函数，不同数据库的地理空间函数语法存在差异。
- 如果业务中存在同一个地点归属多个大区的特殊情况，需要在映射规则中明确优先级，避免匹配结果冲突。
- 触发器的逻辑会增加数据写入的开销，如果数据写入量非常大，建议评估性能后再决定是否使用触发器方案。