MySQL5.7解析json数组

虽然我不太想把字段设计成json形式来保存数据，但是有时候遇到一些业务场景，这种json格式来存数据的方式确实有其可取之处。在java代码里处理过无数json，但是在数据库中直接处理还是头一次，所以记录一下。

1、模拟业务场景

我遇到的业务场景大致如下：
一个条数据，一个key，key我是直接用自增id来表示，但是具体内容需要国际化展示，就是说一个key除了对应中文的内容，还有英文、繁体、越南语、马来语等等一堆的国际化，所以我将这种国际化的name以国际化键值对象的json数组表示，大致内容如下：

[{"locale":"zh_CN","name":"清华大学"},{"locale":"zh_TW","name":"清華大學"},{"locale":"en_US","name":"Tsinghua University"},{"locale":"th_TH","name":"Qinghua มหาวิทยาลัย"},{"locale":"vi_VN","name":"Tiểu Sinh"}]

而我在一个业务场景中需要将简体中文的内容拿出来用，也就是上面的"清华大学"几个字，下面开始解析。

2、数据库

MySQL，版本5.7及以上会自带JSON_EXTRACT函数去来解析json，5.7之前的，就直接截取字符串了，这个没什么好说的，想怎么截就怎么截。

3、创建表及添加数据数据

-- 建表
CREATE TABLE `t_school` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `i18n_name` varchar(500) DEFAULT NULL COMMENT '国际化名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- 添加数据
INSERT INTO `lgs_tool_db`.`t_school`(`id`, `i18n_name`) VALUES (1, '[{\"locale\":\"zh_CN\",\"name\":\"清华大学\"},{\"locale\":\"zh_TW\",\"name\":\"清華大學\"},{\"locale\":\"en_US\",\"name\":\"Tsinghua University\"},{\"locale\":\"th_TH\",\"name\":\"Qinghua มหาวิทยาลัย\"},{\"locale\":\"vi_VN\",\"name\":\"Tiểu Sinh\"}]');
INSERT INTO `lgs_tool_db`.`t_school`(`id`, `i18n_name`) VALUES (2, '[{\"locale\":\"zh_CN\",\"name\":\"北京大学\"},{\"locale\":\"zh_TW\",\"name\":\"北京大學\"},{\"locale\":\"en_US\",\"name\":\"Peking University\"},{\"locale\":\"th_TH\",\"name\":\"มหาวิทยาลัยปักกิ่ง\"},{\"locale\":\"vi_VN\",\"name\":\"Đại học Bắc Kinh\"}]');

加好字后，数据如下显示：

4、解析jsonArray

MySQL5.7之后，自带JSON_EXTRACT函数解析json：
JSON_EXTRACT(json_doc, path[, path] …)
参数如下：
json_doc：待解析的json字符串，可以是自己写的字符串，也可以是表中json格式字段的字段名（注意：该字段不一定非要是json类型的，也可以是varchar,text这类的）。
path[…：需要获取的json字段名，想解析多个json对象字段就写多个。

1. 获取所有的简体中文的名称

SELECT JSON_EXTRACT(i18n_name,'$[0].name') FROM t_school

注意：$[0].name是一种格式，0是代表我想取jsonArray数组的第一个对象，name是json对象的字段名。
结果如下：

2. 获取所有语言的名称

SELECT JSON_EXTRACT(i18n_name,'$[*].name') FROM t_school

只是把上一句的0改成了*，就代表取jsonArray里的所有对象的name值
结果如下：

3. 取所有语言和其对应的名称

SELECT JSON_EXTRACT(i18n_name,'$[*].locale','$[*].name') FROM t_school

函数后面的字段参数写了两个，表示想查json对象两个字段的值，结果如下：

4. 去掉引号
上面数据查出来了，但是引号却是多余的，所以已第一个例子为例，去除引号：

SELECT REPLACE(JSON_EXTRACT(i18n_name,'$[0].name'),'\"','') AS locale  FROM t_school

以上只是把结果中的引号给替换掉了，结果如下：

5. jsonObject解析
上面几个是jsonArray的解析，如果保存的是json对象而不是json数组，那就需要用解析json对象的方式，json对象的解析也是用这个函数，就是json字段名那里不需要中括号，大致如下：

SELECT REPLACE(JSON_EXTRACT(i18n_name,'$.name'),'\"','') AS locale  FROM t_school

这个我没去验证了，但是肯定可以的。

以上就是JSON_EXTRACT对json解析。