网站空间怎么选,wordpress腾讯后台账号,网站服务器崩溃影响,青岛网站搜索排名1 MySQL之JSON数据
总所周知#xff0c;mysql5.7以上提供了一种新的字段格式json#xff0c;大概是mysql想把非关系型和关系型数据库一口通吃#xff0c;所以推出了这种非常好用的格式#xff0c;这样#xff0c;我们的很多基于mongoDB的业务都可以用mysql去实现了。当然…1 MySQL之JSON数据
总所周知mysql5.7以上提供了一种新的字段格式json大概是mysql想把非关系型和关系型数据库一口通吃所以推出了这种非常好用的格式这样我们的很多基于mongoDB的业务都可以用mysql去实现了。当然了5.7的版本只是最基础的版本对于海量数据的效率是远远不够的不过这些都在mysql8.0解决了。今天我们就针对mysql的json数据格式操作做一个简单的介绍
点击了解Mybatis和MybatisPlus操作MySQL中json类型处理
1.1 建表添加数据
这里我们先创建一个简单的含json格式的数据库表其中json_value就为json格式的字段。
CREATE TABLE dept (id int(11) NOT NULL,dept varchar(255) DEFAULT NULL,json_value json DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;添加数据
insert into dept VALUES(1,部门1,{deptName: 部门1, deptId: 1, deptLeaderId: 3});
insert into dept VALUES(2,部门2,{deptName: 部门2, deptId: 2, deptLeaderId: 4});
insert into dept VALUES(3,部门3,{deptName: 部门3, deptId: 3, deptLeaderId: 5});
insert into dept VALUES(4,部门4,{deptName: 部门4, deptId: 4, deptLeaderId: 5});
insert into dept VALUES(5,部门5,{deptName: 部门5, deptId: 5, deptLeaderId: 5});1.2 基础查询操作
用法提示
如果json字符串不是数组则直接使用$.字段名如果json字符串是数组[Array]则直接使用$[对应元素的索引id]
1.2.1 一般json查询
使用 json字段名-$.json属性 进行查询条件 举个例子如果想查询deptLeader张五的数据那么sql语句如下
SELECT * from dept WHERE json_value-$.deptLeaderId5;查询出来的结果如下
1.2.2 多个条件查询
比如想查dept为“部门3”和deptLeaderId5的数据sql如下
SELECT * from dept WHERE json_value-$.deptLeaderId5 and dept部门3;查询和关系型数据库查询一致。
1.2.3 json中多个字段关系查询
比如想查询json格式中deptLeader张五和deptId5的数据
SELECT * from dept WHERE json_value-$.deptLeaderId5 and json_value-$.deptId5;1.2.4 关联表查询
这里我们再创建一张包含json格式的表
CREATE TABLE dept_leader (id int(11) NOT NULL,leaderName varchar(255) DEFAULT NULL,json_value json DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;插入一些测试数据
insert into dept_leader VALUES(1,leader1,{name: 王一, id: 1, leaderId: 1});
insert into dept_leader VALUES(2,leader2,{name: 王二, id: 2, leaderId: 3});
insert into dept_leader VALUES(3,leader3,{name: 王三, id: 3, leaderId: 4});
insert into dept_leader VALUES(4,leader4,{name: 王四, id: 4, leaderId: 5});
insert into dept_leader VALUES(5,leader5,{name: 王五, id: 5, leaderId: 5});这里我们要连表查询在dept 表中部门leader在dept_leader 中的详情
SELECT * from dept,dept_leader
WHERE dept.json_value-$.deptLeaderIddept_leader.json_value-$.id ;1.3 JSON函数操作
写到这里大家都发现了我们查询的json都是整条json数据这样看起来不是很方便那么如果我们只想看json中的某个字段怎么办
1.3.1 官方json函数
NameDescription解释-Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT()计算路径后返回JSON列的值;相当于JSON_EXTRACT ()-Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).从JSON列返回值后就算路径和取消引号的结果;相当于JSON_UNQUOTE (JSON_EXTRACT ())JSON_ARRAY()Create JSON array创建JSON数组JSON_ARRAY_APPEND()Append data to JSON document向JSON文档追加数据JSON_ARRAY_INSERT()Insert into JSON array插入JSON数组JSON_CONTAINS()Whether JSON document contains specific object at pathJSON文档是否包含路径上的特定对象JSON_CONTAINS_PATH()Whether JSON document contains any data at pathJSON文档是否在路径上包含任何数据JSON_DEPTH()Maximum depth of JSON documentJSON文档的最大深度JSON_EXTRACT()Return data from JSON document从JSON文档返回数据JSON_INSERT()Insert data into JSON document将数据插入JSON文档JSON_KEYS()Array of keys from JSON document来自JSON文档的键数组JSON_LENGTH()Number of elements in JSON documentJSON文档中的元素数量JSON_MERGE_PATCH()Merge JSON documents, replacing values of duplicate keys合并JSON文档替换重复键的值JSON_MERGE_PRESERVE()Merge JSON documents, preserving duplicate keys合并JSON文档保留重复的密钥JSON_OBJECT()Create JSON object创建JSON对象JSON_OVERLAPS()Compares two JSON documents, returns TRUE (1) if these have any key-value pairs or array elements in common, otherwise FALSE (0)比较两个JSON文档如果它们有共同的键值对或数组元素则返回TRUE(1)否则返回FALSE (0)JSON_PRETTY()Print a JSON document in human-readable format以人类可读的格式打印JSON文档JSON_QUOTE()Quote JSON document引用JSON文档JSON_REMOVE()Remove data from JSON document从JSON文档中删除数据JSON_REPLACE()Replace values in JSON document替换JSON文档中的值JSON_SCHEMA_VALID()Validate JSON document against JSON schema; returns TRUE/1 if document validates against schema, or FALSE/0 if it does not针对JSON模式验证JSON文档;如果文档针对模式进行验证则返回TRUE/1否则返回FALSE/0JSON_SCHEMA_VALIDATION_REPORT()Validate JSON document against JSON schema; returns report in JSON format on outcome on validation including success or failure and reasons for failure针对JSON模式验证JSON文档;以JSON格式返回关于验证结果的报告包括成功或失败以及失败原因JSON_SEARCH()Path to value within JSON documentJSON文档中值的路径JSON_SET()Insert data into JSON document将数据插入JSON文档JSON_STORAGE_FREE()Freed space within binary representation of JSON column value following partial update在部分更新后释放JSON列值的二进制表示形式中的空间JSON_STORAGE_SIZE()pace used for storage of binary representation of a JSON document用于存储JSON文档的二进制表示的空间JSON_TABLE()Return data from a JSON expression as a relational table以关系表的形式从JSON表达式返回数据JSON_TYPE()Type of JSON valueJSON值类型JSON_UNQUOTE()Unquote JSON value不引用JSON值JSON_VALID()Whether JSON value is validJSON值是否有效JSON_VALUE()Extract value from JSON document at location pointed to by path provided; return this value as VARCHAR(512) or specified type根据所提供的路径从JSON文档中所指向的位置提取值;返回该值为VARCHAR(512)或指定的类型MEMBER OF()Returns true (1) if first operand matches any element of JSON array passed as second operand, otherwise returns false (0)如果第一个操作数匹配作为第二个操作数的JSON数组中的任何元素则返回true(1)否则返回false (0)
1.3.2 -、-区别
-会保持json文档格式中原来格式但-会把所有引号去掉
1.3.2.1 在field中使用
-在field中使用的时候结果带引号-的结果不带引号
select json_value-$.deptId from deptselect json_value-$.deptId from dept1.3.2.2 在where条件中使用
特别注意-当做where查询是要注意类型的-是不用注意类型的
select * from dept where json_value-$.deptId1select * from dept where json_value-$.deptId1select * from dept where json_value-$.deptId1select * from dept where json_value-$.deptId11.3.2.3 在order中使用
没有发现有什么区别
select * from dept order by json_value-$.deptIdselect * from dept order by json_value-$.deptId1.3.3 json_extract():从json中返回想要的字段
用法json_extract(字段名,$.json字段名) 事例
select id,json_extract(json_value,$.deptName) as deptName from dept;1.3.4 JSON_CONTAINS():JSON格式数据是否在字段中包含特定对象
用法: JSON_CONTAINS(target, candidate[, path]) 事例:如果我们想查询包含deptName部门5的对象
select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT(deptName,部门5))1.3.5 JSON_OBJECT():将一个键值对列表转换成json对象
比如我们想查询某个对象里面的值等于多少 比如我们添加这么一组数据到dept表中
insert into dept VALUES(6,部门9,{deptName: {dept:de,depp:dd}, deptId: 5, deptLeaderId: 5});我们可以看到deptName中还有一个对象里面还有dept和depp两个属性字段那么我们应该怎么查询deppdd的员工呢。
用法JSON_OBJECT([key, val[, key, val] …]) 事例
SELECT * from (SELECT *,json_value-$.deptName as deptName FROM dept
) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT(depp,dd));1.3.6 JSON_ARRAY():创建JSON数组
比如我们添加这么一组数据到dept表中
insert into dept VALUES(7,部门9,{deptName: [1,2,3], deptId: 5, deptLeaderId: 5});
insert into dept VALUES(7,部门9,{deptName: [5,6,7], deptId: 5, deptLeaderId: 5});用法JSON_ARRAY([val[, val] …])
事例我们要查询deptName包含1的数据
SELECT * from dept WHERE JSON_CONTAINS(json_value-$.deptName,JSON_ARRAY(1))1.3.7 JSON_TYPE():查询某个json字段属性类型
用法JSON_TYPE(json_val) 事例比如我们想查询deptName的字段属性是什么
SELECT json_value-$.deptName ,JSON_TYPE(json_value-$.deptName) as type from dept 1.3.8 JSON_KEYS():JSON文档中的键数组
用法JSON_KEYS(json_value) 事例比如我们想查询json格式数据中的所有key
SELECT JSON_KEYS(json_value) FROM dept 接下来的3种函数都是新增数据类型的 JSON_SET(json_doc, path, val[, path, val] …) JSON_INSERT(json_doc, path, val[, path, val] …) JSON_REPLACE(json_doc, path, val[, path, val] …)
1.3.9 JSON_SET():将数据插入JSON格式中有key则替换无key则新增
这也是我们开发过程中经常会用到的一个函数 用法JSON_SET(json_doc, path, val[, path, val] …) 事例比如我们想针对id2的数据新增一组newData:新增的数据,修改deptName为新增的部门1 sql语句如下
update dept set json_valueJSON_SET({deptName: 部门2, deptId: 2, deptLeaderId: 4},$.deptName,新增的部门1,$.newData,新增的数据) WHERE id2;注意json_doc如果不带这个单元格之前的值之前的值是会新值被覆盖的比如我们如果更新的语句换成:
update dept set json_valueJSON_SET({a:1,b:2},$.deptName,新增的部门1,$.newData,新增的数据) WHERE id2我们可以看到这里json_doc是{“a”:“1”,“b”:“2”}这样的话会把之前的单元格值覆盖后再新增/覆盖这个单元格字段
1.3.10 JSON_INSERT():插入值往json中插入新值但不替换已经存在的旧值
用法JSON_INSERT(json_doc, path, val[, path, val] …) 事例
UPDATE dept set json_valueJSON_INSERT({a: 1, b: 2}, $.deptName, 新增的部门2,$.newData2,新增的数据2)
WHERE id2我们可以看到由于json_doc变化将之前的值覆盖了新增了deptName和newData2. 如果我们再执行以下刚才的那个sql只是换了value我们会看到里面的key值不会发生变化。 因为这个函数只负责往json中插入新值但不替换已经存在的旧值。
1.3.11 JSON_REPLACE()
用法JSON_REPLACE(json_doc, path, val[, path, val] …) 用例 如果我们要更新id2数据中newData2的值为更新的数据2 sql语句如下
UPDATE dept set json_valueJSON_REPLACE({a: 1, b: 2, deptName: 新增的部门2, newData2: 新增的数据2}, $.newData2, 更新的数据2) WHERE id 2;1.3.12 JSON_REMOVE():从JSON文档中删除数据
用法JSON_REMOVE(json_doc, path[, path] …) 举例删除key为a的字段。
UPDATE dept set json_valueJSON_REMOVE({a: 1, b: 2, deptName: 新增的部门2, newData2: 更新的数据2},$.a) WHERE id 2;
