数据库 拆分存储

Question

现在我有两张表 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的数据量）
如何实现呢？ 辛苦大神们 帮小弟解决下，小弟在此谢过了！

Answer 1

表结构和数据：
[code="sql"]
mysql> desc t1;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| provinceid | varchar(45) | YES | | NULL | |
| cityid | varchar(45) | YES | | NULL | |
| zoneid | varchar(45) | YES | | NULL | |
| province | varchar(45) | YES | | NULL | |
| city | varchar(45) | YES | | NULL | |
| zone | varchar(45) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+

mysql> desc t2;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(45) | YES | | NULL | |
| parentld | varchar(45) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

mysql> select * from t1;
+----+------------+--------+--------+----------+------+------+-------------------------------------------+
| id | provinceid | cityid | zoneid | province | city | zone | address |
+----+------------+--------+--------+----------+------+------+-------------------------------------------+
| 1 | | | | | NULL | NULL | 中国 浙江1 杭州市1 拱墅区1 2路 |
| 2 | NULL | NULL | NULL | | NULL | NULL | 中国 浙江2 杭州市2 拱墅区2 2路 |
+----+------------+--------+--------+----------+------+------+-------------------------------------------+
2 rows in set (0.00 sec)

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]