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 VARCHAR(32);
DECLARE address_name VARCHAR(200);
DECLARE province_name VARCHAR(200);
DECLARE city_name VARCHAR(200);
DECLARE zone_name VARCHAR(200);
DECLARE province_id VARCHAR(200);
DECLARE city_id VARCHAR(200);
DECLARE zone_id VARCHAR(200);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id,address FROM tn_company;
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 idcode INTO province_id FROM tl_district WHERE name = province_name and LENGTH(idcode)=2;
SELECT idcode INTO city_id FROM tl_district WHERE name = city_name AND LENGTH(idcode)=4 and parentId=province_id;
SELECT idcode INTO zone_id FROM tl_district WHERE name like zone_name AND LENGTH(idcode)=6 and parentid=city_id LIMIT 1;
UPDATE tn_company
SET address_province = province_name,
address_city = city_name,
address_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 就是将tn_company 表中的字段address 根据空格 分出来 省市区 3个字段,再和tl_district(全国省市区及Id)表中做对
比,去其idcode,存入tn_company表中对应的字段里,不知道为什么,运行没有错误,就是一个字段也插不进去,而且我是70w条数据,几秒就运行完了, 不知道是不是哪写的不对? 高人上座,求指教!
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 VARCHAR(32);
DECLARE address_name VARCHAR(200);
DECLARE province_name VARCHAR(200);
DECLARE city_name VARCHAR(200);
DECLARE zone_name VARCHAR(200);
DECLARE province_id VARCHAR(200);
DECLARE city_id VARCHAR(200);
DECLARE zone_id VARCHAR(200);
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT id,address FROM tn_company;
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 idcode INTO province_id FROM tl_district WHERE name = province_name and LENGTH(idcode)=2;
SELECT idcode INTO city_id FROM tl_district WHERE name = city_name AND LENGTH(idcode)=4 and parentId=province_id;
SELECT idcode INTO zone_id FROM tl_district WHERE name like zone_name AND LENGTH(idcode)=6 and parentid=city_id LIMIT 1;
UPDATE tn_company
SET address_province = province_name,
address_city = city_name,
address_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 就是将tn_company 表中的字段address 根据空格 分出来 省市区 3个字段,再和tl_district(全国省市区及Id)表中做对
比,去其idcode,存入tn_company表中对应的字段里,不知道为什么,运行没有错误,就是一个字段也插不进去,而且我是70w条数据,几秒就运行完了, 不知道是不是哪写的不对? 高人上座,求指教!
tn_company:
+------------------+--------------+------+-----+
| Field | Type | Null | Key |
+------------------+--------------+------+-----+
| id | varchar(32) | NO | PRI |
| provinceId | varchar(32) | YES | |
| cityId | varchar(32) | YES | |
| zoneId | varchar(32) | YES | |
| address_province | varchar(200) | YES | |
| address_city | varchar(200) | YES | |
| address_zone | varchar(200) | YES | |
| address | varchar(500) | YES | |
+------------------+--------------+------+-----+tl_district:
+----------+-------------+------+-----+
| Field | Type | Null | Key |
+----------+-------------+------+-----+
| id | varchar(32) | NO | PRI |
| idcode | varchar(32) | YES | |
| name | varchar(32) | YES | |
| parentId | varchar(32) | YES | |
+----------+-------------+------+-----+tn_company:(address 因为数据不规范,所以有的中国前面也有空格)
+--------------------------------+-----------------------------------------+----------+------+
| id | address |provinceId|cityId|
|402881b835c6a78d0135c6a7da0a0e65|中国 上海市 上海市宝山区殷高西路99号999室| null | null |-------+----------------+------------+-------------+
zoneId |address_province|address_city|address_zone |
null | null | null | null |tl_district:
+---------------------------------+--------+--------+----------+
| id | idcode | name | parentId |
|402881b835e6f5370135e6f5844f00f7 |00001000| 全国 | 00001000 |
|402881b835e6f5370135e6f5844f00f8 | 11 | 北京市 | 00001000 |
|402881b835e6f5370135e6f5847e00f9 | 1101 | 北京市 | 11 |
|402881b835e6f5370135e6f5848e00fa | 110101| 东城区 | 1101 |
|402881b835e6f5370135e6f5849d00fb | 110102| 西城区 | 1101 |
|402881b835e6f5370135e6f5873c0120 | 13 | 河北省 | 00001000 |
|402881b835e6f5370135e6f5874c0121 | 1301 |石家庄市| 13 |
|402881b835e6f5370135e6f5877b0123 | 130102| 长安区 | 1301 |
|402881b835e6f5370135e6f5877b0124 | 130103| 桥东区 | 1301 |数据量太大,tn_company 差不多70w数据 求指导! 大神!