CREATE PROCEDURE two
(IN
`mount` integer
)
BEGIN
DECLARE aa INT
(11
);
DECLARE bb INT
(11
);
DECLARE cc VARCHAR
(222
);
set aa
= 1
;
drop TEMPORARY table
if EXISTS temp
;
create TEMPORARY table temp
(`lr` int
(11
) auto_increment,
`id` int
(11
),
`yin` varchar
(255
),PRIMARY key
(`lr`));
insert into temp
(id,yin
) select id,yin from cihai where yin like
'%/%';
mylp:loop
select id,yin into bb,cc from temp where lr
=aa
;
update cihai
set yin
= (select REGEXP_replace
(cc,
'/.*?( |$)',
' ') as yin
) where
id = bb
;
set aa
= aa+1
;
IF aa
>mount THEN
LEAVE mylp
;
END IF
;
end loop mylp
;
drop TEMPORARY table
if EXISTS temp
;
END
由于在一条语句里又查询又更新会报错，只能建临时表做个中转，lr是自增列，再把要修改的数据insert进临时表，再用正则修改数据再update，循环跑完删掉临时表。
更新游标处理方式，效率更高
BEGIN
DECLARE aa INT
(11
);
DECLARE bb INT
(11
);
DECLARE cc VARCHAR
(222
);
DECLARE
done INT
;
DECLARE rs_cursor CURSOR FOR
select id,yin from cihai where yin like
'%/%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done
=1
;
OPEN rs_cursor
;
cursor_loop:LOOP
FETCH rs_cursor INTO bb,cc
; -- 取数据
IF done
=1 THEN
leave cursor_loop
;
END IF
;
-- 更新表
UPDATE cihai SET yin
=(select REGEXP_replace
(cc,
'/.*?( |$)',
' ') as yin
) WHERE id
=bb
;
END LOOP cursor_loop
;
CLOSE rs_cursor
;
END
