表构造

CREATE TABLE `users` (  `id` int NOT NULL AUTO_INCREMENT,  `profile` json NOT NULL COMMENT "材料 object",  `favor` json NOT NULL "珍藏 array",  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

插入数据

能够应用 JSON_OBJECT / JSON_ARRAY 来组装 JSON 数据

INSERT INTO `users`(`profile`, `favor`) VALUES(JSON_OBJECT("name", "big", "age", 18, "hobby", JSON_ARRAY("football", "game")), JSON_ARRAY(1, 2)),(JSON_OBJECT("name", "cat", "age", 20, "hobby", JSON_ARRAY("basketball", "game")), JSON_ARRAY(2, 3)),(JSON_OBJECT("name", "sqrt", "age", 24, "hobby", JSON_ARRAY("game")), JSON_ARRAY(2));

查问数据

  1. key 应用 $.key1.key2 的形式读取
  2. arr 应用 $[index] 的形式读取
  3. {"key1": {"key2": [1, 2, 3]}} 读取 $.key1.key2[0]

JSON_CONTAINS 即能够做等值查找，也能够对数组做元素蕴含查找

SELECT    `profile` -> "$.name" AS `name`,    `profile` -> "$.hobby[0]" AS `hobby_first`,    `profile` -> "$.ext.avatar" AS `avatar`,    `favor` -> "$[0]" AS `favor_first` FROM    `users` WHERE    `profile` -> "$.age" >= 20    AND JSON_CONTAINS(`profile`, '"big"', "$.name")    AND JSON_CONTAINS(`profile`, '"game"', "$.hobby")    AND JSON_CONTAINS(`favor`, 1, "$");-- profile.age >= 20-- profile.name = "big"-- profile.hobby 中有 "game"-- favor 中有 1
-- 没有 "basketball" 喜好的用户SELECT    `profile` FROM    users WHERE    !JSON_CONTAINS( `profile`, '"basketball"', "$.hobby" )

数组操作

JSON_ARRAY: 创立 json array
JSON_ARRAY_INSERT: 向 json array 中插入数据 操作的对象 必须为数组
JSON_ARRAY_APPEND: 如果操作对象不是数组，则会转为数组并追加 / 是数组则会间接追加

-- [1, 2, 3]SELECT JSON_ARRAY(1, 2, 3); -- 指定插入的位序SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[0]", "hello1");SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[3]", "hello2");-- 越界了会被追加在最初 [1, 2, 3, "hello100"]SELECT JSON_ARRAY_INSERT("[1,2,3]", "$[100]", "hello100");-- [1, [2, "hello3"], 3]SELECT JSON_ARRAY_INSERT("[1,[2],3]", "$[1][1]", "hello3");-- [1, 2, 3, "hello4"]SELECT JSON_ARRAY_APPEND("[1,2,3]", "$", "hello4");-- [[1, "hello5"], 2, 3]SELECT JSON_ARRAY_APPEND("[1,2,3]", "$[0]", "hello5");

属性操作

  1. JSON_INSERT 插入某属性值（属性不存在时才会插入）
  2. JSON_SET/JSON_REPLACE 设置某属性值
  3. JSON_REMOVE 删除某属性值
-- profile.name 移除UPDATE `users` SET `profile` = JSON_REMOVE(`profile`, "$.name");-- profile.name 插入 （!!path 不存在时才会执行插入!!）UPDATE `users` SET `profile` = JSON_INSERT(`profile`, "$.name", "sqrt");-- favor 追加 a、bUPDATE `users` SET `favor` = JSON_ARRAY_APPEND(`favor`, "$", "a", "$", "b");UPDATE `users` SET `favor` = JSON_ARRAY_INSERT(`favor`, "$", "a", "$", "b");-- favor 设为 xxx 值UPDATE `users` SET `favor` = JSON_SET(`favor`, "$", JSON_ARRAY());-- profile.name 设为 json_set / json_replaceUPDATE `users` SET `profile` = JSON_SET(`profile`, "$.name", "json_set");UPDATE `users` SET `profile` = JSON_REPLACE(`profile`, "$.name", "json_replace");