有时需要查询统计类似上表中 ext字段中的序列化后的字符串中某个key对应的值时比较麻烦,普通sql无法直接查询,更无法加条件查询,mysql也没有相关解析方法,故写了如下mysql方法:
先写个小demo测试版:
create function yangstr(str varchar(255), tar varchar(50)) returns varchar(255) no sql begin declare pos int; declare secstr varchar(255); declare finstr varchar(255); set pos = locate(tar,str); if pos = 0 then return null; else set secstr = substr(str,pos + LENGTH(tar) + 2); set finstr = substr(secstr,locate(':',secstr)+1,locate(';',secstr) - 1 - locate(':',secstr)); set finstr = trim(BOTH '"' FROM finstr); return finstr; end if; end$$
然后写了个递归方法处理存在数组的情况,但是mysql function不支持递归,所以改成了下面的循环处理:
create function yangstr(str varchar(255), tar varchar(50)) returns varchar(255) no sql begin
declare pos int; declare pointpos int; declare secstr varchar(255); declare tartype char(2); declare fircolon int; declare seccolon int; declare finstr varchar(255); declare currtar varchar(50);declare nest boolean default true;
while nest do
set pointpos = locate('.',tar);
if pointpos = 0 then
#基本类型
set pos = locate(tar,str);
if pos = 0 then
return null;
else
set secstr = substr(str,pos + LENGTH(tar) + 2);
set tartype=left(secstr,2);
if tartype = 'i:' then
set finstr = substr(secstr,locate(':',secstr)+1,locate(';',secstr) - 1 - locate(':',secstr)); set finstr = trim(BOTH '"' FROM finstr);
return finstr;
elseif tartype = 's:' then
set fircolon = locate(':',secstr);
set finstr = substr(secstr,fircolon+1,locate(';',secstr) - fircolon);
set seccolon = locate(':',finstr);
set finstr = substr(finstr,seccolon+1,locate(';',finstr) - 1 - seccolon);
set finstr = trim(BOTH '"' FROM finstr);
return finstr;
else
set str = secstr;
end if;
end if;
else
#array
set currtar = left(tar, pointpos - 1);
set pos = locate(currtar,str);
if pos = 0 then
return null;
else
set str = substr(str,pos + LENGTH(currtar) + 2);
set tartype=left(str,2);
if tartype = 'a:' then
set tar = substr(tar, pointpos + 1);
end if;
end if;
end if;
end while;
end$$
当然这不是真正的解析,只是为了达到取值的目的,存在字段中的key对应的值存在与所查字段类型(tartype 的值)存在一定冲突问题,所以在使用前要确保字段值中没有相关字符串(当然存在冲突的可能性还是比较小的)。
整个调试过程还是挺麻烦的,不过付出是有回报的,下面看下实现效果:
SELECT `name`,age,yangstr(ext,'credit') FROM my_user :
SELECT `name`,age,yangstr(ext,'title') FROM my_user :
SELECT `name`,age,yangstr(ext,'credit') FROM my_user WHERE yangstr(ext,'credit') > 100
基本满足使用需求,如果对你无论是学习还是工作有用点帮助的话,点个免费的赞吧,谢谢!