全国省市县三级地区数据库设计&为区域添加全称

书接上回，创建省市区三级数据库&jsoup获取数据。
创建了三级地区数据库后，要获取区域的全程仍然非常麻烦。为此，可以在每个区域中直接保存其全称字段，以空间换时间。

1.修改表结构

添加region_full_name字段， varchar 100.

2.通过存储过程为每个区域添加全程

CREATE DEFINER=`root`@`localhost` PROCEDURE `addFullName`()
BEGIN
	# 临时变量，保存循环中的记录id
	declare tmpId2 int;
	declare tmpId3 int;
	
	# 循环结束标志
	declare done boolean default 0;
	
	# 二、三级区域查询结果集的游标
	declare idIndex2 cursor
		for select _id from district where region_level = 2;
	declare idIndex3 cursor
		for select _id from district where region_level = 3;
	
	declare continue handler for sqlstate '02000' set done = 1;
	
	
	# 1. 直接修改一级区域
	update district set region_full_name = region_name where region_level =1;
	
	# 2.循环拼接、更新修改二级区域
	open idIndex2;
	repeat
		fetch idIndex2 into tmpId2;
		# repeat为do-while循环，添加判断防止多执行一次
		IF done != 1 THEN
			set @paFull = (select b.region_full_name from district a, district b where a.region_parent_id = b.region_code and a._id = tmpId2);
			set @child = (select region_name from district where _id = tmpId2);
			#select Concat(@paFull);
			#select Concat(@child);
		
			update district set region_full_name = Concat(@paFull, @child) where _id = tmpId2;
		END IF;
	until done end repeat;
	
	# 重置
	set done = 0;

	# 3.处理三级区域
	open idIndex3;
	repeat
		fetch idIndex3 into tmpId3;
		IF done != 1 THEN
			set @paFull = (select b.region_full_name from district a, district b where a.region_parent_id = b.region_code and a._id = tmpId3);
			set @child = (select region_name from district where _id = tmpId3);
			#select Concat(@paFull);
			#select Concat(@child);
		
			update district set region_full_name = Concat(@paFull, @child) where _id = tmpId3;
		END IF;
	until done end repeat;

	
	close idIndex3;

END

完毕

现在可以直接查询任意区域的全称。