在Mysql中使用SQL拼接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函数校验，返回均为有效，因此拼接无误。

 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的值

但是有几个注意点：

1. 如果json_object参数为奇数个，Mysql将会返回错误：ERROR 1582 (42000): Incorrect parameter count in the call to native function 'json_object'

1. 如果key是null，Mysql将会返回错误：ERROR 3158 (22032): JSON documents may not contain NULL member names

1. 特殊字符"、\等对于拼接结果没有影响，对于姐妹篇的函数，保留原始数据的大小写格式
2. 对于值为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拼接效益要高的事情。