MySQL JSON字段数据类型

时间:2022-06-19 jacklin 人气:6

前言

JSON 类型是从 MySQL 5.7 版本开始支持的功能，MySQL 支持由RFC 7159定义的本机JSON数据类型，该类型可有效访问 JSON(JavaScript 对象 table 示法)文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON数据类型具有以下优点：

自动验证存储在JSON列中的 JSON 文档。无效的文档会产生错误。
优化的存储格式。存储在JSON列中的 JSON 文档将转换为内部格式，从而可以快速读取文档元素。
JSON列不能具有非NULL的默认值。

创建JSON值

JSON类型字段可以是JSONObject，也可以是JSONArray类型，在 MySQL 中，JSON 值被编写为字符串。 MySQL 会解析在需要 JSON 值的上下文中使用的任何字符串，如果该字符串作为 JSON 无效，则会产生错误。这些上下文包括将值插入具有JSON数据类型的列中，以及将参数传递给需要 JSON 值的函数(在 MySQL JSON 函数的文档中通常显示为* json_doc 或 json_val *)。

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

搜索JSON类型数据

SELECT * FROM sd_school_faq WHERE faq_similar_question -> '$[*].*' LIKE '%课表在哪里？%';

路径可以包含*或**通配符：
.[*]求值为 JSON 对象中所有成员的值。
- [*]计算 JSON 数组中所有元素的值。
- prefix**suffix计算所有以命名前缀开头并以命名后缀结尾的路径。
文档中不存在的路径(评估为不存在的数据)评估为NULL。

在 JSON 和非 JSON 值之间转换

下 table 概述了在 JSON 值和其他类型的值之间进行转换时 MySQL 遵循的规则：

table11.3 JSON 转换规则

other type	CAST(其他类型的 AS JSON)	CAST(JSON AS 其他类型)
JSON	No change	No change
utf8 字符类型(`utf8mb4`，`utf8`，`ascii`)	该字符串将解析为 JSON 值。	JSON 值被序列化为`utf8mb4`字符串。
其他字符类型	其他字符编码将隐式转换为`utf8mb4`，并按 utf8 字符类型所述进行处理。	JSON 值被序列化为`utf8mb4`字符串，然后转换为其他字符编码。结果可能没有意义。
`NULL`	结果为 JSON 类型的`NULL`值。	Not applicable.
Geometry types	通过调用`ST_AsGeoJSON()`将几何值转换为 JSON 文档。	非法操作。解决方法：将`CAST(json_val AS CHAR)`的结果传递给`ST_GeomFromGeoJSON()`
所有其他类型	结果是由单个标量值组成的 JSON 文档。	如果 JSON 文档由目标类型的单个标量值组成，并且该标量值可以转换为目标类型，则成功。否则，返回`NULL`并产生警告。

JSON 值的ORDER BY和GROUP BY按照以下原则工作：

标量 JSON 值的排序使用与前面的讨论相同的规则。
对于升序排序，SQL NULL在所有 JSON 值(包括 JSON 空 Literals)之前进行排序；对于降序排序，SQL NULL对所有 JSON 值(包括 JSON 空 Literals)进行排序。
JSON 值的排序键受max_sort_length系统变量的值限制，因此仅在前max_sort_length个字节之后才不同的键比较相等。
当前不支持对非标量值进行排序，并且会出现警告。

对于排序，将 JSON 标量转换为其他一些本机 MySQL 类型可能是有益的。例如，如果名为jdoc的列包含 JSON 对象，其成员由id键和一个非负值组成，则使用此 table 达式按id值进行排序：

ORDER BY CAST(JSON_EXTRACT(jdoc, '$.id') AS UNSIGNED)

如果碰巧有一个生成的列被定义为使用与ORDER BY中相同的 table 达式，则 MySQL 优化器将识别出该列，并考虑将索引用于查询执行计划。

JSON 值的汇总

对于 JSON 值的汇总，与其他数据类型一样，将忽略 SQL NULL值。非NULL值将转换为数字类型并进行汇总，但MIN()，MAX()和GROUP_CONCAT()除外。尽管可能会发生截断和精度损失，但转换为数字应该为数字标量的 JSON 值产生有意义的结果。转换为其他 JSON 值的数量可能不会产生有意义的结果。

总结

加载全部内容