MySQL JSON 类型用法

MySQL JSON 类型用法

存取示例直接使用 通用 Mapper
MySQL 相关文档:

1. 数据库存取

使用 MyBatis 操作数据库,针对 JSON 类型字段,提供一个类型处理器用于数据库存取时的数据转换。

1.1 类型处理器

@MappedTypes({Map.class, JsonObject.class, JsonArray.class, JsonPojo.class})
public class JsonTypeHandler extends BaseTypeHandler<Object> {
    private static final Gson G = new Gson();
    private final Class javaType;

    public JsonTypeHandler(Class javaType) {
        this.javaType = javaType;
    }

    public Class getJavaType() {
        return javaType;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, 
                                    Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, G.toJson(parameter));
    }

    public Object toJsonObject(String jsonStr) {
        if (StrUtil.isNotEmpty(jsonStr)) {
            //可以考虑针对 Map 等特殊类型进行特殊处理
            return G.fromJson(jsonStr, getJavaType());
        }
        return null;
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return toJsonObject(rs.getString(columnName));
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return toJsonObject(rs.getString(columnIndex));
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex)
      throws SQLException {
        return toJsonObject(cs.getString(columnIndex));
    }

}

1.2 示例对象

结合通用 Mapper 使用,因此对象添加特殊注解指定类型处理器即可。

@Table(name = "json_model")
public class JsonModel implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    private Long id;

    @Column(name = "json_str")
    private String jsonStr;

    @ColumnType(column = "json_map", typeHandler = JsonTypeHandler.class)
    private Map jsonMap;

    @ColumnType(column = "json_object", typeHandler = JsonTypeHandler.class)
    private JsonObject jsonObject;

    @ColumnType(column = "json_array", typeHandler = JsonTypeHandler.class)
    private JsonArray jsonArray;

    @ColumnType(column = "json_field_model", typeHandler = JsonTypeHandler.class)
    private JsonFieldModel jsonFieldModel;

	//省略 getter,setter
}

id 字段外,其他都是 JSON 字段,分别测试用 java 中的不同类型来存储 JSON 字段的值。

该对象对应的表结构如下:

CREATE TABLE `json_model` (
  `id` bigint(20) NOT NULL,
  `json_str` json DEFAULT NULL,
  `json_map` json DEFAULT NULL,
  `json_object` json DEFAULT NULL,
  `json_array` json DEFAULT NULL,
  `json_field_model` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1.3 保存和查询测试

public class JsonDaoTest extends BaseTest {

    /**
     * Dao 接口
     */
    @Autowired
    private JsonDao jsonDao;

    public static final Gson G = new Gson();

    @Test
    public void testInsert() {
        JsonModel model = new JsonModel();
        model.setId(1L);

        JsonFieldModel fieldModel = new JsonFieldModel();
        fieldModel.setName("liu");
        fieldModel.setAge(30);
        fieldModel.setBirthday(new Date());
        fieldModel.setPhones(Arrays.asList("110", "120", "119"));
        fieldModel.setAddress(new String[]{"河北", "石家庄市", "藁城区"});

        String json = GsonUtil.toJson(fieldModel);

        model.setJsonStr(json);
        model.setJsonMap(GsonUtil.fromJson(json));
        model.setJsonObject(JsonParser.parseString(json).getAsJsonObject());
        model.setJsonArray(
          JsonParser.parseString("[" + json + "," + json + "]").getAsJsonArray());
        model.setJsonFieldModel(fieldModel);

        Assert.assertEquals(1, jsonDao.insert(model));
    }

    @Test
    public void testSelectByPrimaryKey() {
        JsonModel model = jsonDao.selectByPrimaryKey(1L);
        System.out.println(model.getId());
    }

}

其中 JsonDao 是一个 通用 Mapper 形式的 Dao:

public interface JsonDao extends Mapper<JsonModel> {

}

保存到数据库的数据如下(insert 形式):

INSERT INTO `json_test`.`json_model` (`id`, `json_str`, `json_map`, `json_object`, `json_array`, `json_field_model`) VALUES ('1', '{\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": 1581492216301}', '{\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": 1581492216301}', '{\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": 1581492216301}', '[{\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": 1581492216301}, {\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": 1581492216301}]', '{\"age\": 30, \"name\": \"liu\", \"phones\": [\"110\", \"120\", \"119\"], \"address\": [\"河北\", \"石家庄市\", \"藁城区\"], \"birthday\": \"Feb 12, 2020, 3:23:36 PM\"}');

2. MySQL JSON 字段函数

利用 MySQL JSON 字段函数可以对字段进行增删改和查询等多种操作。

重要:下面查询列都可以用于 where 条件判断

针对下面的数据进行演示

{
  "age": 30,
  "name": "liu",
  "phones": [
    "110",
    "120",
    "119"
  ],
  "address": [
    "河北",
    "石家庄市",
    "藁城区"
  ],
  "birthday": 1581492216301
}

2.1 查询某个字段是否包含某个值

注意数据类型,只有类型一致,数据一致才相等

SELECT json_contains(json_str, '30', '$.age')
FROM json_model;

-- 结果
-- 1

SELECT json_contains(json_str, '"河"', '$.address'), 
       json_contains(json_str, '"河北"', '$.address[0]'), 
       json_contains(json_str, '"河北省"', '$.address')
FROM json_model;
-- 结果
-- 0	1	0

2.2 判断是否存在某个路径

  • one 有一个就行
  • all 必须都有
SELECT json_contains_path(json_str, 'one', '$.xingming', '$.nianling'), 
       json_contains_path(json_str, 'one', '$.name', '$.xingming', '$.nianling'), 
       json_contains_path(json_str, 'all', '$.address', '$.xingming', '$.nianling'), 
       json_contains_path(json_str, 'all', '$.name', '$.age', '$.address')
FROM `json_model`;
-- 结果
-- 0	1	0	1

2.3 提取指定字段值

SELECT json_extract(json_str, '$.name') as name, 
       json_extract(json_str, '$.age') as age, 
       json_extract(json_str, '$.address') as addresses, 
       json_extract(json_str, '$.address[0]') as address
FROM `json_model`;
-- 结果
-- "liu"	30	["河北", "石家庄市", "藁城区"]	"河北"

-- 上面函数的特殊写法
SELECT json_str->'$.name' as name, 
       json_str->'$.age' as age, 
       json_str->'$.address' as addresses, 
       json_str->'$.address[0]' as address
FROM `json_model`
where json_str->'$.name' = 'liu'
order by json_str->'$.name';
-- 结果
-- "liu"	30	["河北", "石家庄市", "藁城区"]	"河北"

-- 在上面基础上的无引号操作符
SELECT json_str->>'$.name' as name, 
			 json_unquote(json_str->'$.name') as name2, 
			 json_unquote(json_extract(json_str, '$.name')) as name3, 
	   json_str->>'$.age' as age, 
	   json_str->>'$.address' as addresses, 
	   json_str->>'$.address[0]' as address
FROM `json_model`
where json_str->>'$.name' = 'liu'
order by json_str->'$.name';
-- 结果
-- liu	liu	liu	30	["河北", "石家庄市", "藁城区"]	河北

2.4 获取 JSON 中的 keys

select json_keys(json_str) 
from `json_model`;
-- 结果
-- ["age", "name", "phones", "address", "birthday"]

2.5 搜索路径

  • one 返回一个路径
  • all 返回全部
select json_search(json_str, 'one', 'liu') 
from json_model;
-- 结果
-- "$.name"

select json_search(json_str, 'all', '河%'), json_search(json_str, 'all', '河')
from json_model;
-- 结果
-- "$.address[0]"	NULL

select json_search(json_str, 'all', '河%', NULL, '$.address'), 
       json_search(json_str, 'all', '河%', NULL, '$.name') 
from json_model;
-- 结果
-- "$.address[0]"	NULL

2.6 JSON 属性相关函数

select json_depth(json_str) 
from json_model;
-- 结果
-- 3

-- length 针对不同类型计算方式不同
select json_length(json_str) 
from json_model;
-- 结果
-- 5

-- 类型
select json_type(json_str) 
from json_model;
-- 结果
-- OBJECT

select json_type(json_str->'$.address') 
from json_model;
-- 结果
-- ARRAY

-- 是否有效 json
select json_valid(id),json_valid(json_str)
from json_model;
-- 结果
-- 0	1

2.7 工具类

-- 格式化 JSON
select json_pretty(json_str) from json_model;

2.8 修改删除更新值

官方文档: 12.17.4 Functions That Modify JSON Values

MySQL 可以通过函数直接对 JSON 字段的内容进行操作,这部分操作太细致,实际使用时,取出整个 JSON 修改再保存的情况比较多,这里不在提供示例。

isea533 CSDN认证博客专家 运维开发 系统架构
《MyBatis从入门到精通》作者,MyBatis分页插件PageHelper作者,通用Mapper作者,个人网站:https://mybatis.io
©️2020 CSDN 皮肤主题: 酷酷鲨 设计师:CSDN官方博客 返回首页