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 类型内容变动容易产生碎片,内容不是固定长度