Mysql 存储过程中游标使用遇到问题了

snow-is-my-Love 2012-03-19 10:55:49

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条数据，几秒就运行完了，不知道是不是哪写的不对？高人上座，求指教！

...全文

240 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

snow-is-my-Love 2012-03-23

打赏
举报

还是没有找到问题的原因郁闷，反正换一种方法也实现了，但是这是我的一块心病！

snow-is-my-Love 2012-03-22

打赏
举报

oh shit，原来此文本域有trim（）。对付看吧，对不起各位了。

snow-is-my-Love 2012-03-22

打赏
举报

[Quote=引用 11 楼 acmain_chm 的回复:]

给出你的相关表的create table .. , insert into 等测试数据，这样别人可以直接测试你的代码以分析问题。
[/Quote]
对不起最近忙蒙圈了，这就加上
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数据求指导！大神！