MySQL json类型使用

1,329 阅读4分钟

json概述

一般而言,我们在使用mysql这样的关系型数据库时,容易将思维僵化在关系型数据库这个概念里。但是其实关系模型并不适合全部的应用场景。因此才会有茫茫多优秀的nosql产品出现,用来存储多种多样不同的数据类型,比如json。

众所周知,json是一种非常灵活的数据格式,它拥有自己的存储格式,无需定义字段,可以任意无限扩展。

mysql使用json

但是在项目中引入越多的不同的技术,就越会增加开发人员的负担。如果单纯想使用json存储数据,其实mysql也足以胜任。mysql5.7开始支持json,8.0以后解决了更新 JSON 的日志性能瓶颈的问题。

也就是说,如果使用mysql8.0+的版本,它提供的丰富的高性能操作json的api,能满足非常多的json存储的需求了。

实际操作

ddl 创建表结构

 CREATE TABLE UserLogin (
     userId BIGINT NOT NULL,
     loginInfo JSON,
     PRIMARY KEY(userId)
 );

插入

 SET @a = '{"cellphone" : "13918888888","wxchat" : "破产码农","QQ" : "82946772"}';
 insert into UserLogin values(1,@a);
 SET @b = '{"cellphone" : "15026888888"
 ​

查找

json_extract:提取json字段的值

 select userId, json_extract(loginInfo, "$.cellphone") as cellphone
 from UserLogin;

json_unquote:去除引号

 select userId, json_unquote(json_extract(loginInfo, "$.cellphone")) as cellphone
 from UserLogin;

-> : 相当于json_extract()

 select userId, loginInfo -> "$.cellphone" as cellphone
 from UserLogin;

->> : 相当于json_unquote(json_extract())

 select userId, loginInfo ->> "$.cellphone" as cellphone
 from UserLogin;

json_contains函数

  • p1 : 表达式
  • p2 :搜索的值 : 如果找到了,则返回1
 select userId
 from UserLogin
 where json_contains(loginInfo ->> "$.cellphone", '15026888888') = 1;
 select json_contains(loginInfo ->> "$.cellphone", '15026888888') as contains
 from UserLogin
 where userId = 1;

JSON_CONTAINS_PATH:查询一个key是否存在 第二个参数 one表示至少应该存在一个键,检查wxchat或者cellphone是否存在(可以理解为 ||)

 select json_contains_path(loginInfo, 'one', "$.wxchat", "$.cellphone") as contains
 from UserLogin
 where userId = 2;

第二个参数 all 表示全部存在(可以理解为 &&)

select json_contains_path(loginInfo, 'all', "$.wxchat", "$.cellphone") as contains
from UserLogin
where userId = 2;

json_keys函数:获取JSON文档中的所有键

select json_keys(loginInfo) as ks from userlogin;

json_length函数:获取json文档的元素数量

select json_length(loginInfo) as len from userlogin;

修改

json_set函数 : 替换现有值,并添加不存在的值

update userlogin set loginInfo=json_set(loginInfo,"$.wxchat", "啊这","$.wb","wb") where userId=2;

json_insert函数:如果现有存在,就不修改。添加不存在的值。

update userlogin set loginInfo=json_insert(loginInfo,"$.wxchat", "啊这2","$.bilibili","sb") where userId=2;

json_array函数:生成一个json的数组

update userlogin set loginInfo=json_insert(loginInfo,"$.pixiv",json_array(1,2,3))

json_replace函数:如果存在,就被替换,如果不存在则不添加。

update userlogin set loginInfo=json_replace(loginInfo,"$.wxchat", "啊这2","$.pixiv","no") where userId=2;

删除

json_remove函数:如果存在,就被删除,如果不存在则啥也不会发生

update userlogin set loginInfo=json_remove(loginInfo,"$.bilibili","$.pixiv") where userId=2;

最佳实践

在数据库中,JSON 类型比较适合存储一些修改较少、相对静态的数据

比如登录方式这种数据,用户可能使用用户名、手机号、qq、微信、微博、github、等等方式登录,如果使用传统的关系模型,那可能会造成数据非常稀疏,很多列都null值。此外,如果想添加一种登录方式,那么修改表结构成本会比较高。而使用json则没有这样的顾忌。

数据量大时,可以对json的某个字段进行索引

可以利用 MySQL 的函数索引功能对 JSON 中的某个字段进行索引。

比如在上面的用户登录示例中,假设用户必须绑定唯一手机号,且希望未来能用手机号码进行用户检索时,可以创建下面的索引:

ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);

在这个例子中,column cellphone是mysql5.7推出的虚拟列,可以对虚拟列加索引,关于虚拟列本文不再赘述。

添加这个索引之后,再依据cellphone进行查询,就会走索引了。

EXPLAIN SELECT  *  FROM UserLogin 
WHERE cellphone = '17778888888'

标签可以使用json类型

一般情况下我们数据库中有存“标签”的需求,定义如下的标签表:

CREATE TABLE Tags (
    tagId bigint auto_increment,
    tagName varchar(255) NOT NULL,
    primary key(tagId)
);

tagId表示标签的id。传统的关系模型中存储用户-标签关系,需要定义这样的表:

CREATE TABLE Tags (
    tagId bigint,
    userId bigint,
    primary key(tagId,userId)
);

如果使用json,我们可以创建如下UserTag表:

CREATE TABLE UserTag (
    userId bigint NOT NULL,
    userTags JSON,
    PRIMARY KEY (userId)
);
INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');

userTags字段是一个json数组,用来存储标签的id。

MySQL 8.0.17 版本开始支持 Multi-Valued Indexes,用于在 JSON 数组上创建索引,并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes。

我们在userTags字段上创建索引:

ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));

建立好索引后,进行查找:

SELECT * FROM UserTag WHERE 10 MEMBER OF(userTags->"$")

也可以使用json_contains等任何json的函数进行操作。要注意使用前explain,看能不能走索引。

SELECT * FROM UserTag WHERE JSON_CONTAINS(userTags->"$", '[2,10]')

也可以使用json_overlaps:

JSON_OVERLAPS() (引入8.0.17) 比较两个JSON文档,如果它们具有相同的键值对或数组元素,则返回TRUE(1),否则返回FALSE(0)

SELECT * FROM UserTag WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');

总结

  • 使用 JSON 数据类型,推荐用 MySQL 8.0.17 以上的版本,性能更好,同时也支持 Multi-Valued Indexes;
  • JSON 数据类型的好处是无须预先定义列,数据本身就具有很好的描述性;
  • 不要将有明显关系型的数据用 JSON 存储,如用户余额、用户姓名、用户身份证等,这些都是每个用户必须包含的数据;
  • JSON 数据类型推荐使用在不经常更新的静态数据存储。
  • JSON 类型内容变动容易产生碎片,内容不是固定长度