mysql8 regexp_replace 实例:
set contentTagIdMap = '1:7,2:8,3:9,4:10,';
set tag_id_tmp = '2';
set TAG_ID_new_str_tmp =regexp_replace(contentTagIdMap,concat('(.*,)?',tag_id_tmp,':([[:digit:]]+)(,.*)?'),'$2');
该代码解决:新老id数据对应,存放临时表太麻烦!
代码问题:如无匹配则会返回整条字符串,所以代码不可以直接写成 cast( ..... as signed) 否则会报错,后续代码需添加判断
#未获取到则会返回整个 contentTagIdMap字符串,所以进行比对,如果相同则为未获取到新模板id
if TAG_ID_new_str_tmp = contentTagIdMap then
set tag_id_new_tmp = null;
else
set tag_id_new_tmp = cast(TAG_ID_new_str_tmp as signed);
end if;