数据库拆分存储

现在我有两张表 T1为 company表
address（详细地址） PS：中国浙江杭州市拱墅区...路...号....
provinceId;//省Id
cityId;//市Id
zoneId;//区Id
province;//省
city;//市
zone;//区

T2表为省市区地址及ID
name ps：全国的地址（省市区全有）

parentId ps：每个省市区对应的Id

如何将T1表中的address根据空格拆分出各个省市区存入province，city，zone
在根据T2表找到T1表中province，city，zone找到对应的ID 存入T1表中对应的provinceId，cityId，zoneId

（数据量很大70W的数据量）
如何实现呢？辛苦大神们帮小弟解决下，小弟在此谢过了！

mysql> select * from t2;
+----+------------+----------+
| id | name | parentld |
+----+------------+----------+
| 1 | 浙江1 | p01 |
| 2 | 浙江2 | p02 |
| 3 | 杭州市1 | c01 |
| 4 | 杭州市2 | c02 |
| 5 | 拱墅区1 | z01 |
| 6 | 拱墅区2 | z02 |
+----+------------+----------+
6 rows in set (0.00 sec)
[/code]

存储过程：
[code="sql"]

CREATE FUNCTION func_get_split_string(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare result varchar(255) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
END

CREATE PROCEDURE split_data()
BEGIN
DECLARE tid int DEFAULT 0;

DECLARE address_name VARCHAR(200) DEFAULT "";
DECLARE province_name VARCHAR(200) DEFAULT "";
DECLARE city_name VARCHAR(200) DEFAULT "";
DECLARE zone_name VARCHAR(200) DEFAULT "";

DECLARE province_id VARCHAR(200) DEFAULT "";
DECLARE city_id VARCHAR(200) DEFAULT "";
DECLARE zone_id VARCHAR(200) DEFAULT "";

DECLARE done INT DEFAULT 0;


DECLARE cur CURSOR FOR SELECT id,address FROM t1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


OPEN cur;
address_lb:LOOP
   FETCH cur INTO tid,address_name;
   SELECT func_get_split_string(address_name,' ',2) INTO province_name;
   SELECT func_get_split_string(address_name,' ',3) INTO city_name;
   SELECT func_get_split_string(address_name,' ',4) INTO zone_name;

   SELECT parentld INTO province_id FROM t2 WHERE name = province_name;
   SELECT parentld INTO city_id FROM t2 WHERE name = city_name;
   SELECT parentld INTO zone_id FROM t2 WHERE name = zone_name;

   UPDATE t1
      SET province   = province_name,
          city       = city_name,
          ZONE       = zone_name,
          provinceid = province_id,
          cityid     = city_id,
          zoneid     = zone_id
    WHERE id = tid;

   IF done = 1 THEN
       LEAVE address_lb;
   END IF;
END LOOP address_lb;

CLOSE cur;
commit;

END
[/code]

执行：
[code="sql"]
mysql> call split_data();
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+------------+--------+--------+----------+------------+------------+-------------------------------------------+
| id | provinceid | cityid | zoneid | province | city | zone | address |
+----+------------+--------+--------+----------+------------+------------+-------------------------------------------+
| 1 | p01 | c01 | z01 | 浙江1 | 杭州市1 | 拱墅区1 | 中国浙江1 杭州市1 拱墅区1 2路 |
| 2 | p02 | c02 | z02 | 浙江2 | 杭州市2 | 拱墅区2 | 中国浙江2 杭州市2 拱墅区2 2路 |
+----+------------+--------+--------+----------+------------+------------+-------------------------------------------+
2 rows in set (0.00 sec)
[/code]

什么数据库？你想sql实现？还是java后台实现？

[code="java"]http://topic.csdn.net/t/20060922/14/5041077.html[/code]

[url]http://topic.csdn.net/t/20060922/14/5041077.html[/url]

考虑效率的话，最好写mysql 存储过程，说下思路：
1.遍历T1表。
2.取出address字段，根据空格解析address（详细地址），不会的话搜索下：mysql split。
3.根据2解析的数据，更新T1表。

做关联呀，做关系表就行

数据库 拆分存储

数据库拆分存储