mysql查询json数据web，mysql处理json数据查询条件

本文内容列表：

• 1.Mysql请求json数据中特定key的值
• 2、Mysql5.7从json字符串请求特定属性值
• 3.mysql json字符串查询
• 4.如何用mysql搜索json格式的数据
• 5. mysql中的json数据类型

Mysql请求json数据中特定key的值

mysql中有一个表中有一个json格式的字段。假设字段 Properties

{

"ocsp.event.append-timestamp.enable": "true" 表示，

"ocsp.streaming.data .filter.expression": "id=e4_json" ,

"ocsp.event.enable": "true",

"ocsp.schema.field.names": "id",

"ocsp.event.output.numPartitions": "0",

"ocsp.event.json-format.enable": "true",

"ocsp.streaming.field.translation.enable": "false",

"ocsp.event.append-id.启用" : "false",

"ocsp.streaming.data.keys": "id",

"ocsp.stream.sql": "从 uuu_5dea34758400_oi5xs0rdaf 中选择 ID，其中 id=e4_ocsp"," .kafka .topic ": "hn_20210216b",

"ocsp.event.period": "{\"周期\":\"日\",\"时间\":[{\"开始\":{\"d\":\"0\",\ "h\":\"03:30:23\"},\"end\":{\"d\":\"0\",\"h\":\"23:20:23\" }}],\"startDate\":\"2021-02-16\",\"endDate\":\"2021-03-28\"}",

"ocsp.event.periodSwitch": " true",

"ocsp.event.output": "5d74080d1ac0"

}

现在如果需要查询 ocsp.stream.sql 的值，可以使用 json_extract 函数。请注意，如果键由点分隔符组成，则必须将整个键用双引号括起来

select json_extract(Properties,'$. "ocsp.schema.field.names" ') from COMPONENT where type=' EVENT ' and id ='5dea403ed7c0';

json_keys函数可用于json中的所有关键字段

同时从COMPONENT中选择json_keys（属性），其中type='EVENT'和id='5dea403ed7c';

mysql5.7从json字符串中请求某个属性值

可以使用JSON_EXTRACT函数在json字段或json字符串类型的文本字段中查询json对象的特定属性值。注意级别之间必须用点连接，例如：

SELECT id, JSON_EXTRACT(content, '$.all_result.quality_result.total_score') AS total_score FROM tableName

其中，content 是一个字段存储json格式字符串的表，

$.all_result.quality_result.total_score 表示：content字段的json字符串中all_result属性下的quality_result属性的总分total_score。

内容 已保存内容：

{

"return_code": "200",

"return_info": "处理成功",

"all_result": {: "_quality" 1

}

}

mysql json字符串查询

注1：字段类型为varchar、char、text等字符类型

注2：内容为json

注3：mysql服务器版本为5.7及以上

语法：select from表名 where Field-'$.property'='查询内容';

select * from Table1 where col1-'$.name'='xiaoming';

mysql如何查找json格式的数据

在比较MySQL和PostgreSQL的时候，总是会比较PG的JSON格式支持的优势。其实MariaDB早期也有存储非结构化数据的解决方案，称为动态列，但该解决方案是以BLOB类型存储的。由此带来的问题是查询性能不高，无法有效建立索引。相对于一些文档数据库来说，好处并不是很大，所以社区中的反响其实也比较一般。当然，MariaDB动态分区功能并不仅限于存储非结构化数据，但本文不会对其进行扩展。

从MySQL 5.7.7实验室版本开始，InnoDB存储引擎天然支持JSON格式，这并不是简单的类似BLOB的替代。原生 JSON 格式支持具有以下优点：

JSON 数据有效性检查：BLOB 类型无法在数据库层执行此类约束检查

查询性能提升：查询不需要遍历所有字符串来查找数据

支持索引：JSON中的部分数据可以通过虚拟列的功能建立索引

首先我们来看看如何在MySQL中使用原生JSON格式：

mysql create table user (uid int auto_increment,

- data json,primary key(uid))engine=innodb;

查询正常，0行受影响（0.01秒）mysq in set user value ​​(NULL,

- '{"姓名":"David","邮件":"jianchangyao@gmail.com","地址":"上海"}');

查询OK,1行受影响（0.00秒）

mysql插入用户值​​(NULL,'{"name":"Amy","mail":"amy@gmail.com"}');

查询OK, 1 row受影响（0.00秒）

可以看到我们创建了一个新的表user并将列数据定义为JSON类型。这意味着我们检查嵌入数据的 JSON 格式，以确保其符合 JSON 格式的约束。如果插入非法的JSON数据，会报如下错误：

mysql insert into user values​​​​(NULL, "Test") ;

ERROR 3130 (22032): Invalid JSON text: "Invalid value " 在值（或列）“Test”的位置 2 上。MariaDB 动态列的改进：

mysql select jsn_extract(data, '$.name'),jsn_extract(data,'$ .Address') from user ;

+--------------------------------+ ---------------- --+

| jsn_extract(数据, '$.name') | jsn_extract(data,'$.address') |

+--------------------------------+-- --- --------------------- ----+

| “大卫”| 《上海》|

| “艾米”| NULL |

+---------------- ----------+---------------- - -----------------+

2 rows in set (0.00 sec)

当然，最令人兴奋的功能应该是MySQL 5.7的虚拟列功能，它通过传统的B+树索引快速添加JSON格式的一些属性。使用方法是先创建虚拟列，然后在虚拟列上创建索引：

mysql ALTER TABLE user ADD user_name varchar(128)

- ALWAYS GENERATED AS (jsn_extract(data,' $.name) ')) VIRTUAL;

查询正常，0 行受影响（0.01 秒）

记录：0 重复：0 警告：0

mysql 从用户中选择用户名；

---- ---- -- --- -+

|用户名 |

+-----------+

| 《艾米》|

| "David" |

+------+

集合中的 2 行（0.00 秒）

mysql 更改表用户添加索引 idx_username (user_name);

查询正常，2 行受影响 ( 0.01 秒）

记录：2 重复：0 警告：0

然后就可以通过添加的索引快速查询用户名了，就像普通的列查询一样。通过该语句您可以验证优化器是否选择了在虚拟列上创建的新索引：

mysql statements select * from user where user_name='"Amy"'\G

**** **** * ********************** 第一排 ********************** *** ** **

ID：1

select_type：SIMPLE

表：user

分区：NULL

类型：ref

possible_keys：idx♹eruser_username♹ key_len：131

ref ：const

行：1

过滤：100.00

额外：NULL

集合中的 1 行，1 个警告（0.00 秒）JSON 格式。我相信 PostgreSQL 阵营需要找到新的策略来“攻击”MySQL。如果没有意外发生，我们仍然会继续优化。毕竟这是MySQL目前要克服的最大问题。幸运的是，MySQL 团队已经在重构优化器代码。我相信下一个版本中将会包含更好的优化器。彻底突破。而很多文档数据库已经在厕所里哭了。

mysql中的json数据类型

JSON 数组包含以逗号分隔的值列表，并括在 [and] 字符中：

JSON 对象包含以逗号分隔的键值对数组，并括在 [and] 字符中。 { 和 } 字符、键和 JSON 对象必须是字符串：

JSON 数组元素和 JSON 对象键值中允许嵌套：

在 MySQL 中，JSON 值被写为字符串。 MySQL 将解析上下文中使用的任何需要 JSON 值的字符串，如果该字符串不是有效的 JSON，则会产生错误。

1.JSON_ARRAY 生成json数组

JSON_ARRAY(val1,val2,val3...) 生成具有指定元素的 json 数组。

2.JSON_OBJECT 生成 json 对象

JSON_OBJECT(key1,val1,key2,val2...) 生成指定 K-V 对的 json 对象。如果任意键为 NULL 或者参数个数为奇数，则会抛出错误。

3. JSON_QUOTE 加 " 符号

JSON_QUOTE(json_val) -- 将 json_val 用 " 符号括起来。

2. 插入记录

创建表

插入包含 json 数组的记录

插入包含 json 对象的记录

路径表达式对于 JSON 文档或对 JSON 文档的更改非常有用。文档 用于指定文档中操作的位置。例如，以下查询从 JSON 文档中提取具有 name 键的成员的值：

路径语法对所考虑的 JSON 文档使用前导 $ 字符，后面可以选择连续指定更具体部分的选择器。 of document :

如果路径表达式中未加引号的键名称是非法的，则必须将命名键的路径部分加引号。让 $ 引用该值，并且包含所有空格的键必须用引号引起来：

您可以使用带有 to 关键字的范围来指定 JSON 数组的子集。

支持最后一个关键字作为数组中最后一个元素的索引的同义词。该形式的表达式可用于相对寻址或范围定义，如下所示：last - * N *

4.JSON_REMOVE() 获取 JSON 文档和一个或多个值，指定来自文档路径被删除。返回值是原始文档减去文档中存在的路径选择的值

JSON 值可以与 =,, =,,=,,!=,=

JSON 值进行比较 不可以但支持以下比较运算符和函数：BETWEEN、IN()、GREATEST()、LEAST()

上面列出的比较运算符和函数通过将 JSON 值转换为原生 MySQL 数字或字符串数​​据类型来工作，因此它们具有一致的非 JSON 标量类型。

JSON值的比较发生在两个级别。第一级比较基于比较值的 JSON 类型。如果类型不同，则比较的结果仅由哪个类型具有更高的优先级来确定。如果两个值具有相同的 JSON 类型，则使用特定于类型的规则执行二级比较。

NULL-INTEGER、DOUBLE-STRING-OBJECT-ARRAY-BOOLEAN-DATE-TIME-DATETIME-OPAQUE-BIT-BLOB 的优先级按此顺序变大。

对于具有相同优先级的 JSON 值，比较规则是特定于类型的：

1) BLOB、BIT、OPAQUE

比较两个值的第一个字节，其中 N 是较短的值。字节和。如果 N 个两个值的前字节相同，则较短的值排在较长的值之前。

2) DATETIME

表示较早时间点的值排在表示较晚时间点的值之前。如果两个值最初分别来自 MySQL DATETIME 和 TIMESTAMP 类型，并且表示相同的时间点，则它们相等。

3）TIME

两个时间值中较小的值排在较大值之前。

4) 日期`

较早的日期排在较晚的日期之前。

5）ARRAY

如果两个JSON数组的长度相同并且数组中对应位置的值相等，则它们相等。如果数组不相等，则它们的顺序由第一个位置的不同元素决定。该位置值较小的数组首先排序。如果较短数组中的所有值与较长数组中的对应值相同，则较短数组先排序。

8) STRING

字符串按字典顺序在要比较的两个字符串之间排序，其中 N 是较短字符串的长度。如果两个字符串的前 N ​​个字节相同，则较短的字符串被认为小于较长的字符串。