MySQL之JSON字段使用

2024/7/15·1 views

自MySQL5.7.8版本以来，MySQL支持原生JSON数据类型。允许使用原生JSON数据类型比以前MySQL版本中所使用JSON文本格式更能有效地存储JSON文档。

MySQL以内部格式存储JSON文档，允许对文档元素的快速读取访问。JSON二进制格式的结构是允许服务器通过键或数组索引直接搜索JSON文档中的值，这非常快。

JSON文档的存储大约与存储LONGBLOB或LONGTEXT数据量相同。

要定义数据类型为JSON的列，请使用以下语法：

sql 复制代码

CREATE TABLE `json_test` (
  `id` int(11) unsigned NOT NULL,
  `j` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

json列不能有默认值，而且不能直接编入索引，可以在包含从json列中提取的生成列上创建索引，当从JSON列查询数据时，MySQL优化器将在匹配JSON表达式的虚拟列上查找兼容的索引。

如下json数据，存储为两条记录

sql 复制代码

insert into json_test values(2, '{"name":"test","age": 14}');
insert into json_test values(2, '{"name":"test1","age": 12}');

查询，使用列路径运算符(->)

sql 复制代码

select id, j->'$.name' as name from json_test;

查询结果如下

复制代码

+----+------------+
| id | name       |
+----+------------+
|  1 | "chengyao" |
|  2 | "test"     |
+----+------------+

name列值中有引号，怎么去掉，可以使用内联路径运算符( ->>)

sql 复制代码

select id, j->>'$.name' as name from json_test;

查询结果

复制代码

+----+----------+
| id | name     |
+----+----------+
|  1 | chengyao |
|  2 | test     |
+----+----------+

注意

如果json path中出现 _, - 等符号，需要将某一个路径引起来，例如

sql 复制代码

JSON_REMOVE(attributes, '$.placeholder."zh-CN"')