mysql解析json数组mysql在5.7开始支持json解析了 也可以解析数组哦！
直接上demo　select substr(col, 2, length(col) - 2), length(col) from (select json_extract(json_extract(json_extract(state, "$.tpl"),"$.items" ), "$[0].url") as col from page order by id desc limit 100) t;
json_extract可以解析sql , tpl就是你json的key值
如果是数组，用$[*].url 或者 $[0].url 获取全部的value 或者某个下标的url
下面这个demo可以直接复制到sql运行　select json_extract(json_extract(json_extract('{"tpl":{"items":[{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/fiz0otkhtzod7fotkp55snulgiku.png?imageview2/2/w/750","id":1542348252537},{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/flr1vdqwezd406noslfrjuez4g_x.png?imageview2/2/w/750","id":1542348263477},{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/fhmuykwvnombv8i1dlqbm1kax5kn.png?imageview2/2/w/750","id":1542348269599},{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/flgr4iunelpbcgjn2re_9a8jx30v.png?imageview2/2/w/750","id":1542348276124},{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/fpxf8ethxu8aqriikbsydjnu2xd5.png?imageview2/2/w/750","id":1542348282561},{"type":"image","config":{"expandable":true,"linkable":true},"url":"https://fs.esf.fangdd.net/test/fkuz5m7jd6ke2slsyreducozc3xh.png?imageview2/2/w/750","id":1542348288150,"link":"http://www.baidu.com"}],"bottomitems":[],"title":"demo2","description":"","wxlogo":"","bodystyleinline":{},"bg":"","bgtype":"","bottomstyleinline":{},"bottombg":"","bottombgtype":"","uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830","activeitemsname":"items","activeimgtype":"","authinfo":{"role_list":[{"name":"test","access_key_list":[]},{"name":"审核人员","access_key_list":[]}],"city_list":[],"userid":3108779,"username":"zhangyusheng","email":"zhangyusheng@xxx.com","mobile":"123123","truename":"张昱升","isemployee":true}}}', "$.tpl"), "$.items"), "$[0].url");
我们来分析一下原始json为
{ "tpl":{ "items":[ { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/fiz0otkhtzod7fotkp55snulgiku.png?imageview2/2/w/750", "id":1542348252537 }, { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/flr1vdqwezd406noslfrjuez4g_x.png?imageview2/2/w/750", "id":1542348263477 }, { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/fhmuykwvnombv8i1dlqbm1kax5kn.png?imageview2/2/w/750", "id":1542348269599 }, { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/flgr4iunelpbcgjn2re_9a8jx30v.png?imageview2/2/w/750", "id":1542348276124 }, { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/fpxf8ethxu8aqriikbsydjnu2xd5.png?imageview2/2/w/750", "id":1542348282561 }, { "type":"image", "config":{ "expandable":true, "linkable":true }, "url":"https://fs.esf.fangdd.net/test/fkuz5m7jd6ke2slsyreducozc3xh.png?imageview2/2/w/750", "id":1542348288150, "link":"http://www.baidu.com" } ], "bottomitems":[ ], "title":"demo2", "description":"", "wxlogo":"", "bodystyleinline":{ }, "bg":"", "bgtype":"", "bottomstyleinline":{ }, "bottombg":"", "bottombgtype":"", "uuid":"aaef8dfe-256a-4559-aec9-95d1fcdcf830", "activeitemsname":"items", "activeimgtype":"", "authinfo":{ "role_list":[ { "name":"test", "access_key_list":[ ] }, { "name":"审核人员", "access_key_list":[ ] } ], "city_list":[ ], "userid":3108779, "username":"zhangyusheng", "email":"zhangyusheng@xxx.com", "mobile":"23123", "truename":"张昱升", "isemployee":true } }}
$.tpl就是获取tpl这个键key
$[0].url就是获取[{url:1},{url:2}] 这个数组第一个对象的url值 也就是1
mysql json字符串解析成对应字段字段名 ：mobile ,内容：{"contactname":"段xx","contactjobtitle":"待确认","contactmobile":"131xxxxxxx"}。
解决方法：json_extract执行sql：
查询结果：
结果带引号，并不能真正使用。
解决方法：replace执行sql：
查询结果：
问题解决。
sql语句：
selectreplace (json_extract (mobile, '$.contactname'),'"','') as 'contactname',replace (json_extract (mobile, '$.contactmobile'),'"','') as 'contactmobile',replace (json_extract (mobile, '$.contactjobtitle'),'"','') as 'contactjobtitle'fromcscw_clientwhereid = 'xxxxxxxxxxxxxxx'
以上就是mysql怎么解析json数组的详细内容。