mysql JSON_SET 遇到的坑

使用JSON_SET（）更新jons字段

事故sql

UPDATE tableName
SET extend_json = JSON_SET( IFNULL( extend_json, '{}' ), '$.mallServer.url', '222' ),
extend_json = JSON_SET( IFNULL( extend_json, '{}' ), '$.belongHongKong', 0 ) 
WHERE id = 4;

问题

extend_json 中包含有mallServer.url这个key的时候更新正常，但是当extend_json 不包含mallServer.url这个key的时候只有belongHongKong这个key能正常更新，而mallServer.url不会被更新上去，

最后经过排查发现使用JSON_SET（）进行更新的时候，如果key只有一层（belongHongKong）无论json字段是否包含key都会执行没有插入有则更新，但是如果key存在两层或以上（mallServer.url）且josn中不包含此key在使用JSON_SET（）则不会进行插入操作

解决

使用JSON_OBJECT(), 将多层key转化包装成json对象
优化sql如下

UPDATE tableName
SET extend_json = JSON_SET( IFNULL( extend_json, '{}' ), '$.mallServer', JSON_OBJECT( "url", '22222' ) ),
extend_json = JSON_SET( IFNULL( extend_json, '{}' ), '$.belongHongKong', 0 ) 
WHERE
 id = 3;

结果：无论是否有有key，多层的key也可以插入或者更新