delimiter $DROP FUNCTION IF EXISTS `fun_convert`$
CREATE DEFINER=`root`@`%` FUNCTION `fun_convert`(para varchar(255))
RETURNS varchar(255) CHARSET gbk
BEGIN
declare result varchar(255) default '';
declare cnt int default 0;
declare i int default 0;
declare id BIGINT default 0;
set cnt = length(para) - length(replace(para,',',''))-1;
WHILE i < cnt DO
select c.id into id from company c where c.code = SUBSTRING(para,i*3+2,2);
set result = CONCAT(result, ",",id);
set i = i+1;
end WHILE;
set result = CONCAT(result, ",");
RETURN result;
END
$
delimiter ;
update users u set u.company = fun_convert(u.COMPANY);
