继上一篇文章《关于Maxcompute中SQL处理拼接json字符串的问题》后,个人觉得还是应该把知识点介绍全面,既然介绍了在maxcompute中SQL拼接Json字符串的问题,那么在Mysql中也应该跟大家介绍一下。毕竟两者的语法是不同的,实现的方式也有差异。作为读者可以了解在不同数据库的规范下如何进行准确的拼接Json字符串。
首先确认一下以下脚本在mysql版本5.6.16-log
下验证,通过脚本
select version();
Json格式形如:它由大括号包裹,包含一组键值对,每个键值对之间通过逗号分隔。每个键值对由键(key)和值(value)组成,用冒号分隔。键是一个字符串,用双引号括起来,然后是冒号,而值可以是字符串、数值,布尔值、数组、甚至是另一个JSON对象。示例如下,以阿里巴巴图片库的返回json数据作为演示:
1 常规做法,concat函数,或者concat_ws函数来硬拼接
造的假数据如下:
select
json_str,
JSON_VALID(json_str) as is_valid
from(
select
concat('{"',typ,'":',cnt,',"',scd_typ,'":',amt,',"',yer,'":"',yer_wek,'"}') as json_str
from t_test_data
) t
结果如下:
经过json_valid函数校验,返回均为有效,因此拼接无误。
但是同在姐妹篇《关于Maxcompute中SQL处理拼接json字符串的问题》的问题一样,如果在字符串里含有特殊字符,这种拼接依然是无效的,如下:
select
typ,cnt,scd_typ,amt,
json_str,
JSON_VALID(json_str) as is_valid
from(
select
typ,cnt,scd_typ,amt,
concat('{"',typ,'":',cnt,',"',scd_typ,'":',amt,'}') as json_str
from(
select '海运"空运' as typ,100 as cnt,'hello\nworld' as scd_typ,50 as amt
) t ) tt
结果如下:
发现字符串中存在 "
字符,导致使用"
拼接json出错,这样也就促成了第二种字符串拼接Json的方式了:mysql自带函数
2 Mysql自带函数:json_object
我们通过mysql函数手册查看json_object命令格式如下:
JSON_OBJECT(key, value[, key2, value2, ...])
key:必填字段,对象中的键
value:必填字段,对应中key的值
但是有几个注意点:
- 如果json_object参数为奇数个,Mysql将会返回错误:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'
- 如果key是null,Mysql将会返回错误:
ERROR 3158 (22032): JSON documents may not contain NULL member names
- 特殊字符
"
、\
等对于拼接结果没有影响,对于姐妹篇的函数,保留原始数据的大小写格式 - 对于值为null,拼接结果依然有效
还是以1中的数据为例:
select
json_str,
JSON_VALID(json_str) as is_valid
from(
select
JSON_OBJECT(typ,cnt,scd_typ,amt,yer,yer_wek) as json_str
from t_test_data) t
结果如下:
如果是对于特殊情况呢,是否也好用呢?
select
typ,cnt,scd_typ,amt,
json_str,
JSON_VALID(json_str) as is_valid
from(
select
typ,cnt,scd_typ,amt,
JSON_OBJECT(typ,cnt,scd_typ,amt) as json_str
from(
select '海运"空运' as typ,100 as cnt,'helloworld' as scd_typ,50 as amt
union all
select '空运' as typ,null as cnt,'hello\nworld' as scd_typ,50 as amt
union all
select 'ABC' as typ,null as cnt,'DFB' as scd_typ,50 as amt
) t ) tt
具体结果如下:
3 结论
通过2中的特殊情况,我们可以发现对于函数,字符串中的数据是不会对最终结果有影响的。因此就代码的健壮度而言,使用系统自带函数json_object依然是比concat拼接效益要高的事情。