mysql 存储过程中 游标使用遇到问题了
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条数据,几秒就运行完了, 不知道是不是哪写的不对? 高人上座,求指教!
------解决方案--------------------
MYSQL要转到MYSQL版块的,知道MSSQL的路过。
------解决方案--------------------
探讨
引用:
MYSQL要转到MYSQL版块的,知道MSSQL的路过。
姐姐,俺不会转呀,第一次发帖,见谅! 有问题,求指导!
------解决方案--------------------
管理菜单,移动,找MYSQL版块。要不我给你移过去?
------解决方案--------------------
MY SQL 也有存储过程的啊?
微信 赏一包辣条吧~
支付宝 赏一听可乐吧~