添加链接 注册    登录
link之家
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
鼻子大的松鼠  ·  Apache ECharts 5 ...·  1 年前    · 
灰常酷的猴子  ·  本地调试 服务长时间无反应 ...·  1 年前    · 
独立的饺子  ·  reactjs - Type ...·  1 年前    · 
link之家  ›  MySQL 中 JSON 类型应用 - 链滴
mysql mysql执行计划 mysql时间函数 select函数
https://ld246.com/article/1563329010115
微醺的绿茶
1 年前

MySQL 8.0

The JSON Data Type

MySQL 从 5.7.8 开始，支持原生的 JSON 数据类型。可以高效的访问 JSON 文档中的数据。与在字符串列中存储 JSON 格式字符串相比，JSON 数据类型有以下优势：

  • 可自动验证存储的 JSON 数据格式是否正确。
  • 优化的存储格式。存储在 JSON 列中的 JSON 文档将转化为内部格式，以允许对文档元素进行快速访问。当服务器读取以二进制存储的 JSON 值时，不需要再对文本解析后取值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需在文档之前或之后读取所有值。

    • JSON 列占用的空间与 LONGBLOB 或 LONGTEXT 基本相同。

    在 MySQL 8.0.13 之前，JSON 列不能定义非 null 的默认值。

    以下测试 mysql 版本：8.0.16 , 个别测试会与较老的版本结果不一致。

    创建 JSON 值

    JSON 数组包含由逗号分隔并由中括号包裹的值列表 

    ["abc", 10, null, true, false]

    JSON 对象包含一组由逗号分隔并且由大括号包裹的键值对
    

    {"k1": "value", "k2": 10}

    JSON 对象中的键必须是字符串。JSON 数组中的元素和 JSON 对象键值允许嵌套。
    

    [99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

    JSON 值在插入前会校验正确性。
    

    create table t1 (jdoc JSON);
insert into t1 values
('{"key1":"value1","key2":"value2"}');
insert into t1 values
('"stringvalue"');
insert into t1 values
('[1, 2,');
/**插入的value值中下标6的位置有错
> 3140 - Invalid JSON text: "Invalid value." at position 6 in value for column 't1.jdoc'.*/

    JSON_TYPE()函数需要 JSON 参数，并尝试将其解析为 JSON 值。如果值有效，则返回值的 JSON 类型，否则报错。
    

    mysql> SELECT JSON_TYPE('["a", "b", 1]');
> ARRAY 
mysql> SELECT JSON_TYPE('"hello"');
> STRING
mysql> select json_type('{"key":"value"}') type;
> OBJECT
mysql> SELECT JSON_TYPE('hello');
> ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.

    JSON_ARRAY()函数可以将传入的参数（允许 null）转化为一个 JSON 数组
    

    mysql> SELECT JSON_ARRAY('a', 1, NOW());
> ["a", 1, "2019-07-13 15:56:47.000000"]

    JSON_OBJECT()函数可将传入参数（允许 null）转化为一个 JSON 对象
    

    mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
> {"key1": 1, "key2": "abc"}
* JSON对象中key值不能为null，value可以为null*/
mysql> SELECT JSON_OBJECT('A','a',null,null,'C','c');
> JSON documents may not contain NULL member names.

    JSON_MERGE_PRESERVE()接受两个或多个 JSON 文档并返回组合结果(关于 JSON 值的合并，下面会有更加详细的测试)
    

    mysql> SELECT JSON_MERGE_PRESERVE('["a", 1]', '{"key": "value"}');
>	["a", 1, {"key": "value"}]

    JSON 值可以赋给用户自定义变量
    

    mysql> set @j = JSON_OBJECT('key','value');
mysql> select @j;
> {"key": "value"}

    然而，用户定义的变量不能是 JSON 数据类型，因此尽管前面示例中的 @j 看起来像 JSON 值并且具有与 JSON 值相同的字符集和排序规则，但它没有 JSON 数据类型。相反，JSON_OBJECT() 的结果在分配给变量时会转换成字符串。
    

    ps：官方文档上的这段话可能只是提示一下用户变量的数据类型不能是 JSON，但是使用起来好像也没什么问题。
    

    下面的例子可以看到，变量是正确的 json 格式数据时，使用 json 函数对其操作都没问题。




    

    

    mysql> SET @x = '{ "a": 1, "b": 2 }',
     >     @y = '{ "a": 3, "c": 4 }',
     >     @z = '{ "a": 5, "d": 6 }';
mysql> SELECT  JSON_MERGE_PATCH(@x, @y, @z)    AS Patch,
    ->         JSON_MERGE_PRESERVE(@x, @y, @z) AS Preserve\G
>   Patch: {"a": 5, "b": 2, "c": 4, "d": 6}
>Preserve: {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6}
mysql> SET @j = '["a", ["b", "c"], "d"]';
mysql> SELECT JSON_REMOVE(@j, '$[1]');
> ["a", "d"]
mysql> set @a = Json_object('key','value'), @b = json_object('key','value2');
mysql> select json_merge_preserve(@a,@b);
> {"key": ["value", "value2"]}

    通过转化 json 值生成的字符串，字符集是 utf8mb4，排序规则是 utf8mb4_bin
    

    mysql> SELECT CHARSET(@j), COLLATION(@j);
>  utf8mb4  |  utf8mb4_bin 

    由于 utf8mb4_bin 是二进制排序规则，因此 json 值的比较区分大小写
    

    mysql> SELECT JSON_ARRAY('x') = JSON_ARRAY('x'),JSON_ARRAY('x') = JSON_ARRAY('X');
> 1  |  0

    区分大小写也适用于 JSON null，true 和 false 文字，他们必须始终以小写形式写入。只有小写字母时 json 才是 json 有效值。
    

    mysql>  SELECT JSON_VALID('null'), JSON_VALID('Null'), JSON_VALID('NULL');
>  1	|	0	|	0
mysql>  SELECT CAST('null' AS JSON);
mysql>  SELECT CAST('Null' AS JSON);
> Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.

    JSON 是区分大小写的，而 SQL 是不区分的。下列值都可以成功被识别为 null。
    

    mysql> SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
> 1	|	1	|	1

    当你想要讲引号字符( " 或 " )插入到 JSON 文档中，你需要使用 \ 转义字符。
    

    mysql> CREATE TABLE facts (sentence JSON);
mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

    如果将值作为 JSON 对象文字传入，则此方法不起作用。如下示例，第一个 \" 的位置会被当做 Our ma...这个属性的结束位置，所以报了缺少 逗号 或 大括号 的错误。




    

    

    mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
>	> 3140 - Invalid JSON text: "Missing a comma or '}' after an object member." at position 43 in value for column 'facts.sentence'.

    这里需要使用双反斜杠来使之生效
    

    mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');

    查询查看效果,可以看到两种方式的结果是一样的。
    

    mysql>	SELECT * FROM facts;
>	{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
	{"mascot": "Our mascot is a dolphin named \"Sakila\"."}

    要使用键查找特定的值，可以使用 column-path 操作符 ->,可以看到查询结果是完整的 value 值，包括引号和转义符
    

    mysql> select sentence->"$.mascot" from facts;
> "Our mascot is a dolphin named \"Sakila\"."

    有时候我们只需要里面的字符串，可以使用 ->> 运算符来查询;这样查询到的结果就只是需要显示的值了。
    

    mysql> select sentence->>"$.mascot" from facts;
>	Our mascot is a dolphin named "Sakila".

    如果启动了 NO_BACKSLASH_ESCAPES 服务器 SQL 模式，则前面的插入对象的实例将无法正常工作。如果设置了此模式，则可以使用单个反斜杠而不是双反斜杠来插入 JSON 对象文字，并保留反斜杠。如果在执行插入时使用 JSON_OBJECT() 函数并设置了此模式，则必须替换单引号和双引号
    

    mysql> INSERT INTO facts VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
mysql>	SELECT * FROM facts;
>	{"mascot": "Our mascot is a dolphin named \"Sakila\"."}

    JSON 值的规范化，合并和自动包装
    

    解析字符串并发现它是有效的 JSON 文档时，它也会进行规范化。比如 JSON 对象中不能有重复的 key，那么有重复 key 时，后面的 value 会覆盖前面的
    

    mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
>	{"key1": "def", "key2": "abc"}

    将值插入 json 列时也会执行规范化
    

    mysql>	CREATE TABLE t1 (c1 JSON);
mysql>	INSERT INTO t1 VALUES
('{"x": 17, "x": "red"}'),
('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql>	SELECT c1 FROM t1;
>	{"x": "red"}
	{"x": [3, 5, 7]}

    这种 “last duplicate key wins（最后重复秘钥获胜）”的规则由 RFC 7159 建议，并且大多数 JavaScript 解析器都支持这个规则。（Bug #86866,Bug #26369555）
    

    在 8.0.3 之前的 MySQL 版本中，重复的 key value 会被丢弃。如在 5.7.26 版本的 MySQL 做上面同样的测试，结果就不一样
    

    mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc', 'key1', 'def');
>	{"key1": 1, "key2": "abc"}
mysql>	CREATE TABLE t1 (c1 JSON);
mysql>	INSERT INTO t1 VALUES
('{"x": 17, "x": "red"}'),
('{"x": 17, "x": "red", "x": [3, 5, 7]}');
mysql>	SELECT c1 FROM t1;
>	{"x": 17}
	{"x": 17}

    MySQL 还会丢弃原始 JSON 文档中键、值或元素之间的额外空格。为了使查找更有效，它还对 JSON 对象的键进行排序。目前在这两个版本中做的简单测试，排序结果都是一样的。当然官方文档上也有提示：此排序结果可能会发生变化，并且不保证在各个版本中保持一致。




    

    

    mysql>	select JSON_OBJECT('key1','value1','key3','value3','key2','value2');
>	{"key1": "value1", "key2": "value2", "key3": "value3"}

    合并 JSON 值
    

    MySQL 中有三个合并方法：
    

  • JSON_MERGE:  在 MySQL 8.0.3 中已弃用，并且在将来的版本中将被删除。
    • 

  • JSON_MERGE_PRESERVE：保留重复键的值。就是重命名后的 JSON_MERGE()
    • 

  • JSON_MERGE_PATCH：丢弃除最后一个值之外的所有键
    • 

    **合并数组：**将多个数组合并成单个数组。
    

    JSON_MERGE_PRESERVE() 通过将后一数组追加到前一数组末尾实现。
    

    JSON_MERGE_PATCH() 将每个参数视为由单个元素组成的数组（因此它们的下标都是 0），然后应用 “last duplicate key wins” 规则取最后一个参数。
    

    mysql> SELECT
    ->   JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Preserve,
    ->   JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2, "a", "b", "c", true, false]
   Patch: [true, false]

    注意：方法中都允许有 null ，但是结果需要注意。JSON_MERGE_PRESERVE() 方法中，若作为参数的数组中有个 null 值，那最终结果中也会有个 null，方法不会去除 null 值和重复值。
    

    mysql> SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]','[true, false]','["a", null, "c"]') AS Preserve;
>	[1, 2, "a", "b", "c", true, false, "a", null, "c"]

    但如果作为参数的数组本身就是 null，那合并结果也是 null
    

    mysql>	SELECT JSON_MERGE_PRESERVE('[1, 2]', '["a", "b", "c"]',null,'[true, false]') AS Preserve;

    JSON_MERGE_PATCH() 方法由于只保留最后一个数组，所以 null 对它并没有什么影响，只有当最后一个参数为 null 时结果才为 null
    

    mysql>	SELECT JSON_MERGE_PATCH('[1, 2]', '["a", "b", "c"]', '[true, false]',null,'["a", "b", "c"]',null) AS Patch;

    **合并对象：**将多个对象合并成单个对象
    

    JSON_MERGE_PRESERVE():将多个对象的键值对组合成一个新对象，会将重复键的值组成一个数组（不会去除重复值和 null）赋给这个键。
    

    JSON_MERGE_PATCH():将多个对象的键值对组合成一个新对象，重复键的值会用 last duplicate key wins（最后重复秘钥获胜）规则取最后一个。
    

    mysql> SELECT JSON_MERGE_PRESERVE('{"a": 1, "b": 2}', '{"c": 3, "a": 1}', '{"c": 5, "d": 3}','{"d":null}') AS Preserve,
JSON_MERGE_PATCH('{"a": 3, "b": 2}', '{"c": 3, "a": 4}', '{"c": 5, "d": 3}') AS Patch\G
*************************** 1. row ***************************
Preserve: {"a": [1, 1], "b": 2, "c": [3, 5], "d": [3, null]}
   Patch: {"a": 4, "b": 2, "c": 5, "d": 3}

    合并非阵列值：
    

    在需要数组值的上下文中使用的非阵列值被自动包装：该值被 [ 和 ] 字符包围以将其转换为数组。在下列语句中，每个参数都自动包装为数组（[1],[2]）。然后按照 合并数组 的规则进行合并。
    

    mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('1', '2') AS Preserve,
	  ->   JSON_MERGE_PATCH('1', '2') AS Patch\G
*************************** 1. row ***************************
Preserve: [1, 2]
   Patch: 2

    需要注意的是，参数必须是正确的 json 类型值。可以使用 JSON_TYPE()方法检查值是否正确。
    

    mysql>	SELECT JSON_MERGE_PRESERVE('"a"', '"b"') AS Preserve, JSON_MERGE_PATCH('"a"', '"b"') AS Patch;
*************************** 1. row ***************************
Preserve: ["a", "b"]
   Patch: "b"
mysql>	SELECT JSON_MERGE_PRESERVE('a', 'b') AS Preserve, JSON_MERGE_PATCH('a', 'b') AS Patch;
>	Invalid JSON text in argument 1 to function json_merge_preserve: "Invalid value." at position 0.

    **合并数组和对象的组合：**合并的参数中既有数组，又有对象
    

    合并时会将对象自动包装为数组，然后按照 合并数组 的规则合并。
    

    mysql> SELECT
	  ->   JSON_MERGE_PRESERVE('[10, 20]', '{"a": "x", "b": "y"}') AS Preserve,
	  ->   JSON_MERGE_PATCH('[10, 20]', '{"a": "x", "b": "y"}') AS Patch\G
*************************** 1. row ***************************
Preserve: [10, 20, {"a": "x", "b": "y"}]
   Patch: {"a": "x", "b": "y"}

    查询和修改 JSON 值
    

    JSON 路径表达式选择 JSON 文档中的值。
    

    路径表达式对于提取 JSON 文档的一部分或修改 JSON 文档的函数很有用，以指定该文档中的操作位置。
    

    以下查询从 JSON 文档中提取名称为 key 的成员的值：
    

    mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
>	 "Aztalan"   

    路径语法使用前导 $ 字符来表示正在计算的 JSON 文档，后面可以跟选择器，来连续指示文档更具体的部分：
    

  • 后跟秘钥名称的句点用具有给定键的对象命名成员。如果没有引号的名称在路径表达式中不合法（例如，如果它包含空格），则必须在双引号内指定键名。
    • 

  • 附加到选择数组的路径的 [N] 命名数组中位置 N 处的值。数组位置是从零开始的整数。如果 path 没有选择数组值，则 path[0]计算为与 path 相同的值
    • 

    mysql>	SELECT JSON_SET('"x"', '$[0]', 'a');

    [M] 到 [N] 指定以位置 M 处的值开始并以位置 N 处的值结束的数组值的子集或范围。
    

    last 支持作为最右边数组元素的索引的同义词。还支持数组元素的相对寻址。如果 path 未选择数组值，则 path[last] 将计算为与 path 相同的值。
    

    路径可以包含 * 或 ** 通配符
    

    .[*] 计算 JSON 对象中所有成员的值
    

    mysql>	SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.*');
>	[14, "Aztalan"]

    [*] 计算 JSON 数组中所有元素的值
     
    推荐文章
    鼻子大的松鼠  ·  Apache ECharts 5 系列教程（2）视觉设计 - ECharts的空间 - OSCHINA - 中文开源技术交流社区
    1 年前
    灰常酷的猴子  ·  本地调试 服务长时间无反应 niws.loadbalancer.availabilityFilteringRule.activeConnectionsLimit = 2147483647_wangooo的博客-CSDN博客
    1 年前
    独立的饺子  ·  reactjs - Type 'MouseEvent<HTMLButtonElement, MouseEvent>' is not assignable to type 'MouseEventHandler<HTMLButtonElement>' - Stac
    1 年前
    今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    link之家 - 链接快照平台
    © 2024 ~ 沪ICP备11025650号