mysql替换数据_mysql 替换数值

CREATE PROCEDURE key_replace(add_value int)

BEGIN

DECLARE spec_line,tran_line text DEFAULT '';

DECLARE done,lp_cur,lp_len int default 0;

DECLARE cur1 cursor for select a from table;

DECLARE CONTINUE HANDLER for NOT FOUND SET done = 1;

BEGIN

OPEN cur1;

do_loop: LOOP

SET done = 0;

FETCH cur1 INTO spec_line;

IF done = 1 THEN

LEAVE do_loop;

END IF;

SET lp_cur = 0;

SET lp_len = char_length(spec_line) - char_length(replace(spec_line,';',''));

SET tran_line = '';

inn_loop: LOOP

IF lp_cur > lp_len THEN

LEAVE inn_loop;

END IF;

SET spec_key = substring_index(substring_index(spec_line,';',lp_cur),';',-1);

SET lp_cur = lp_cur + 1;

SET tran_line = CONCAT(tran_line,substring_index(spec_key,':',1),':',CAST(substring_index(spec_key,':',-1) AS SIGNED) + add_value,';');

END LOOP inn_loop;

UPDATE table SET a = tran_line WHERE a = spec_line;

END LOOP do_loop;

CLOSE cur1;

END