Mysql 查询JSON结果的相关函数汇总_Mysql

来源:脚本之家  责任编辑:小易  

怎样在java中查询2113mysql得到如下的json格式5261的结果Java中查4102询出来一般不会1653是JSON,都是Object。你要版把Object返回给前端或者客户端的时候权,用Jackson或者FastJSON把Object转换成JSON格式。不知道你想在哪用JSON,所以只能这么回答了,我们知道,JSON是一32313133353236313431303231363533e78988e69d8331333433626534种轻量级的数据交互的格式,大部分NO SQL数据库的存储都用JSON。MySQL从5.7开始支持JSON格式的数据存储,并且新增了很多JSON相关函数。MySQL 8.0 又带来了一个新的把JSON转换为TABLE的函数JSON_TABLE,实现了JSON到表的转换。举例一我们看下简单的例子:简单定义一个两级JSON 对象mysql> set @ytt='{"name":[{"a":"ytt","b":"action"},  {"a":"dble","b":"shard"},{"a":"mysql","b":"oracle"}]}';Query OK, 0 rows affected (0.00 sec)第一级:mysql> select json_keys(@ytt);+-----------------+| json_keys(@ytt) |+-----------------+| ["name"]        晌消|+-----------------+1 row in set (0.00 sec)第二级:mysql> select json_keys(@ytt,'$.name[0]');+-----------------------------+| json_keys(@ytt,'$.name[0]') |+-----------------------------+| ["a", "b"]                  |+-----------------------------+1 row in set (0.00 sec)我们使用MySQL 8.0 的JSON_TABLE 来转换 @ytt。mysql> select * from json_table(@ytt,'$.name[*]' columns (f1 varchar(10) path '$.a', f2 varchar(10) path '$.b')) as tt;+-------+--------+| f1    | f2     |+-------+--------+| ytt   | action || dble  | shard  || mysql | oracle |+-------+--------+3 rows in set (0.00 sec)举例二再来一个洞搭复杂点的例子,用的是EXPLAIN 的JSON结果集。JSON 串 @json_str1。set @json_str1 = ' {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "1.00"    },    "table": {      "table_name": "bigtable",      "access_type": "const",      "possible_keys": [        "id"      ],      "key": "id",      "used_key_parts": [        "id"      ],      纳谨拿"key_length": "8",      "ref": [        "const"      ],      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "100.00",      "cost_info": {        "read_cost": "0.00",        "eval_cost": "0.20",        "prefix_cost": "0.00",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "log_time",        "str1",        "str2"      ]    }  }}';第一级:mysql> select json_keys(@json_str1) as 'first_object';+-----------------+| first_object    |+-----------------+| ["query_block"] |+-----------------+1 row in set (0.00 sec)第二级:mysql> select json_keys(@json_str1,'$.query_block') as 'second_object';+-------------------------------------+| second_object                       |+-------------------------------------+| ["table", "cost_info", "select_id"] |+-------------------------------------+1 row in set (0.00 sec)第三级:mysql>  select json_keys(@json_str1,'$.query_block.table') as 'third_object'\G*************************** 1. row ***************************third_object: ["key","ref","filtered","cost_info","key_length","table_name","access_type","used_columns","possible_keys","used_key_parts","rows_examined_per_scan","rows_produced_per_join"]1 row in set (0.01 sec)第四级:mysql> select json_extract(@json_str1,'$.query_block.table.cost_info') as 'forth_object'\G*************************** 1. row ***************************forth_object: {"eval_cost":"0.20","read_cost":"0.00","prefix_cost":"0.00","data_read_per_join":"176"}1 row in set (0.00 sec)那我们把这个JSON 串转换为表。SELECT * FROM JSON_TABLE(@json_str1,"$.query_block"COLUMNS(rowid FOR ORDINALITY,NESTED PATH '$.table'COLUMNS (a1_1 varchar(100) PATH '$.key',a1_2 varchar(100) PATH '$.ref[0]',a1_3 varchar(100) PATH '$.filtered',nested path '$.cost_info'columns (a2_1 varchar(100) PATH '$.eval_cost' ,a2_2 varchar(100) PATH '$.read_cost',a2_3 varchar(100) PATH '$.prefix_cost',a2_4 varchar(100) PATH '$.data_read_per_join'),a3 varchar(100) PATH '$.key_length',a4 varchar(100) PATH '$.table_name',a5 varchar(100) PATH '$.access_type',a6 varchar(100) PATH '$.used_key_parts[0]',a7 varchar(100) PATH '$.rows_examined_per_scan',a8 varchar(100) PATH '$.rows_produced_per_join',a9 varchar(100) PATH '$.key'),NESTED PATH '$.cost_info'columns (b1_1 varchar(100) path '$.query_cost'),c INT path "$.select_id")) AS tt;+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+| rowid | a1_1 | a1_2  | a1_3   | a2_1 | a2_2 | a2_3 | a2_4 | a3   | a4       | a5    | a6   | a7   | a8   | a9   | b1_1 | c    |+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+|     1 | id   | const | 100.00 | 0.20 | 0.00 | 0.00 | 176  | 8    | bigtable | const | id   | 1    | 1    | id   | NULL |    1 ||     1 | NULL | NULL  | NULL   | NULL | NULL | NULL | NULL | NULL | NULL     | NULL  | NULL | NULL | NULL | NULL | 1.00 |    1 |+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+2 rows in set (0.00 sec)当然,JSON_table 函数还有其他的用法,我这里不一一列举了,详细的参考手册。请点击输入图片描述请点击输入图片描述修改回答www.zgxue.com防采集请勿采集本网。

JSON 格式字段是 Mysql 5.7 新加的属性,不够它本质上以字符串性质保存在库中的,刚接触时我只了解 $.xx 查询字段的方法,因为大部分时间,有这个就够了,其他交给程序就行了,但是最近一些操作需要更复杂的查询操作,所以赶紧了解下更多的方法。

版本5.7.27 试过没问题

JSON_EXTRACT(json_doc [,path])

给你个思路吧,既然你能问出这种问题应该是有了思路就会自己做的人,就不帮你写代码了。 首先,你确定这个表的所有字段中没有二进制内容,如果没有的话,下面这个思路你可以试试,但是如果有的话估计就需要对二进制进行特别的处理了,如果你有的

查询字段

mysql数据库建立表,存储json字段用text类型 然后从接口中获取JSON数据,转成STRING格式,直接插入到这个字段就可以了。

mysql> set @j = '{"name":"wxnacy"}';mysql> select JSON_EXTRACT(@j, '$.name');+----------------------------+| JSON_EXTRACT(@j, '$.name') |+----------------------------+| "wxnacy" |+----------------------------+

在MySQL与PostgreSQL的对比中,PG的JSON格式支持优势总是不断被拿来比较。其实早先MariaDB也有对非结构化的数据进行存储的方案,称为dynamic column,但是方案是通过BLOB类型的方式来存储。

还有一种更简洁的方式,但是只能在查询表时使用

我们知道,JSON是一种轻量级的数据交互的格式,大部分NO SQL数据库的存储都用JSON。MySQL从5.7开始支持JSON格式的数据存储,并且新增了很多JSON相关函数。MySQL 8.0 又带来了一个新的把JSON转换为TABLE的函数JSON_TABLE,实现了JSON到表的转换。

mysql> select ext -> '$.name' from test;+-----------------+| ext -> '$.name' |+-----------------+| "wxnacy" |+-----------------+

在 $. 后可以正常的使用 JSON 格式获取数据方式,比如数组

mysql> set @j = '{"a": [1, 2]}';mysql> select JSON_EXTRACT(@j, '$.a[0]');+----------------------------+| JSON_EXTRACT(@j, '$.a[0]') |+----------------------------+| 1 |+----------------------------+

JSON_DEPTH(json_doc)

计算 JSON 深度,计算方式 {} [] 有一个符号即为一层,符号下有数据增加一层,复杂 JSON 算到最深的一次为止,官方文档说 null 值深度为 0,但是实际效果并非如此,列举几个例子

JSON_LENGTH(json_doc [, path])

计算 JSON 最外层或者指定 path 的长度,标量的长度为1。数组的长度是数组元素的数量,对象的长度是对象成员的数量。

mysql> SELECT JSON_LENGTH('[1, 2, {"a": 3}]');+---------------------------------+| JSON_LENGTH('[1, 2, {"a": 3}]') |+---------------------------------+| 3 |+---------------------------------+mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}');+-----------------------------------------+| JSON_LENGTH('{"a": 1, "b": {"c": 30}}') |+-----------------------------------------+| 2 |+-----------------------------------------+mysql> SELECT JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b');+------------------------------------------------+| JSON_LENGTH('{"a": 1, "b": {"c": 30}}', '$.b') |+------------------------------------------------+| 1 |+------------------------------------------------+

JSON_TYPE(json_doc)

返回一个utf8mb4字符串,指示JSON值的类型。 这可以是对象,数组或标量类型,如下所示:

mysql> SET @j = '{"a": [10, true]}';mysql> SELECT JSON_TYPE(@j);+---------------+| JSON_TYPE(@j) |+---------------+| OBJECT |+---------------+mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a'));+------------------------------------+| JSON_TYPE(JSON_EXTRACT(@j, '$.a')) |+------------------------------------+| ARRAY |+------------------------------------+mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]'));+---------------------------------------+| JSON_TYPE(JSON_EXTRACT(@j, '$.a[0]')) |+---------------------------------------+| INTEGER |+---------------------------------------+mysql> SELECT JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]'));+---------------------------------------+| JSON_TYPE(JSON_EXTRACT(@j, '$.a[1]')) |+---------------------------------------+| BOOLEAN |+---------------------------------------+

可能的返回类型

纯JSON类型: OBJECT:JSON对象 ARRAY:JSON数组 BOOLEAN:JSON真假文字 NULL:JSON null文字

数字类型: INTEGER:MySQL TINYINT,SMALLINT,MEDIUMINT以及INT和BIGINT标量 DOUBLE:MySQL DOUBLE FLOAT标量 DECIMAL:MySQL DECIMAL和NUMERIC标量

时间类型: DATETIME:MySQL DATETIME和TIMESTAMP标量 日期:MySQL DATE标量 TIME:MySQL TIME标量

字符串类型:

STRING:MySQL utf8字符类型标量:CHAR,VARCHAR,TEXT,ENUM和SET

二进制类型:

BLOB:MySQL二进制类型标量,包括BINARY,VARBINARY,BLOB和BIT

所有其他类型:

OPAQUE(原始位)

JSON_VALID

返回0或1以指示值是否为有效JSON。 如果参数为NULL,则返回NULL。

mysql> SELECT JSON_VALID('{"a": 1}');+------------------------+| JSON_VALID('{"a": 1}') |+------------------------+| 1 |+------------------------+mysql> SELECT JSON_VALID('hello'), JSON_VALID('"hello"');+---------------------+-----------------------+| JSON_VALID('hello') | JSON_VALID('"hello"') |+---------------------+-----------------------+| 0 | 1 |+---------------------+-----------------------+

以上就是Mysql 查询JSON结果的相关函数汇总的详细内容,更多关于MySQL json函数的资料请关注真格学网其它相关文章!

保存原有的数据类型?默认返回的都是字符串。追问http://zhidao.baidu.com/question/1239368523428137339.html嗯,能不能够帮助查看一下这个问题,你知道如何解答吗,你可以先把查出来的数据处理一下,然后自己重新组装json追问知道原因了,主要是php查询语句通过PHP mysql_fetch_object() 函数获取查询结果所得到的数据类型默认的时候全部都转换为字符串了,[见: http://zhidao.baidu.com/question/681282588811968852.html]但是你说的这个组装json之前的数据类型转换我可不会啊.追答PHP没有将字符串转整形的方法吗,这应该都和Java类似吧,你后台能从数据库获取数据了,处理数据还有什么问题吗,主要PHP我不懂,只能说说建议本回答被提问者和网友采纳,我们知道,JSON是一种轻量级的数据交互的格式,大部分NO SQL数据库的存储都用JSON。MySQL从5.7开始支持JSON格式的e68a84e8a2ad3231313335323631343130323136353331333433626534数据存储,并且新增了很多JSON相关函数。MySQL 8.0 又带来了一个新的把JSON转换为TABLE的函数JSON_TABLE,实现了JSON到表的转换。举例一我们看下简单的例子:简单定义一个两级JSON 对象mysql> set @ytt='{"name":[{"a":"ytt","b":"action"},  {"a":"dble","b":"shard"},{"a":"mysql","b":"oracle"}]}';Query OK, 0 rows affected (0.00 sec)第一级:mysql> select json_keys(@ytt);+-----------------+| json_keys(@ytt) |+-----------------+| ["name"]        |+-----------------+1 row in set (0.00 sec)第二级:mysql> select json_keys(@ytt,'$.name[0]');+-----------------------------+| json_keys(@ytt,'$.name[0]') |+-----------------------------+| ["a", "b"]                  |+-----------------------------+1 row in set (0.00 sec)我们使用MySQL 8.0 的JSON_TABLE 来转换 @ytt。mysql> select * from json_table(@ytt,'$.name[*]' columns (f1 varchar(10) path '$.a', f2 varchar(10) path '$.b')) as tt;+-------+--------+| f1    | f2     |+-------+--------+| ytt   | action || dble  | shard  || mysql | oracle |+-------+--------+3 rows in set (0.00 sec)举例二再来一个复杂点的例闹历此子,用的是EXPLAIN 的JSON结果集烂冲。JSON 串 @json_str1。set @json_str1 = ' {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "1.00"    },    "table": {      "table_name": "bigtable",      "access_type": "const",      "possible_keys": [        "id"      ],      "key": "id",      "used_key_parts": [        "id"      ],      "key_length": "8",      "ref": [        "const"      ],      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "100.00",      "cost_info": {        "read_cost": "0.00",        "eval_cost": "0.20",        "prefix_cost": "0.00",        "data_read_per_join": "176"      },      "used_columns": [      液迅  "id",        "log_time",        "str1",        "str2"      ]    }  }}';第一级:mysql> select json_keys(@json_str1) as 'first_object';+-----------------+| first_object    |+-----------------+| ["query_block"] |+-----------------+1 row in set (0.00 sec)第二级:mysql> select json_keys(@json_str1,'$.query_block') as 'second_object';+-------------------------------------+| second_object                       |+-------------------------------------+| ["table", "cost_info", "select_id"] |+-------------------------------------+1 row in set (0.00 sec)第三级:mysql>  select json_keys(@json_str1,'$.query_block.table') as 'third_object'\G*************************** 1. row ***************************third_object: ["key","ref","filtered","cost_info","key_length","table_name","access_type","used_columns","possible_keys","used_key_parts","rows_examined_per_scan","rows_produced_per_join"]1 row in set (0.01 sec)第四级:mysql> select json_extract(@json_str1,'$.query_block.table.cost_info') as 'forth_object'\G*************************** 1. row ***************************forth_object: {"eval_cost":"0.20","read_cost":"0.00","prefix_cost":"0.00","data_read_per_join":"176"}1 row in set (0.00 sec)那我们把这个JSON 串转换为表。SELECT * FROM JSON_TABLE(@json_str1,"$.query_block"COLUMNS(rowid FOR ORDINALITY,NESTED PATH '$.table'COLUMNS (a1_1 varchar(100) PATH '$.key',a1_2 varchar(100) PATH '$.ref[0]',a1_3 varchar(100) PATH '$.filtered',nested path '$.cost_info'columns (a2_1 varchar(100) PATH '$.eval_cost' ,a2_2 varchar(100) PATH '$.read_cost',a2_3 varchar(100) PATH '$.prefix_cost',a2_4 varchar(100) PATH '$.data_read_per_join'),a3 varchar(100) PATH '$.key_length',a4 varchar(100) PATH '$.table_name',a5 varchar(100) PATH '$.access_type',a6 varchar(100) PATH '$.used_key_parts[0]',a7 varchar(100) PATH '$.rows_examined_per_scan',a8 varchar(100) PATH '$.rows_produced_per_join',a9 varchar(100) PATH '$.key'),NESTED PATH '$.cost_info'columns (b1_1 varchar(100) path '$.query_cost'),c INT path "$.select_id")) AS tt;+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+| rowid | a1_1 | a1_2  | a1_3   | a2_1 | a2_2 | a2_3 | a2_4 | a3   | a4       | a5    | a6   | a7   | a8   | a9   | b1_1 | c    |+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+|     1 | id   | const | 100.00 | 0.20 | 0.00 | 0.00 | 176  | 8    | bigtable | const | id   | 1    | 1    | id   | NULL |    1 ||     1 | NULL | NULL  | NULL   | NULL | NULL | NULL | NULL | NULL | NULL     | NULL  | NULL | NULL | NULL | NULL | 1.00 |    1 |+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+2 rows in set (0.00 sec)当然,JSON_table 函数还有其他的用法,我这里不一一列举了,详细的参考手册。请点击输入图片描述请点击输入图片描述修改回答内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql installer community 5.7.16安装详细教程
  • win10下mysql 8.0.16 winx64安装图文最新教程
  • mysql 密码增强插件
  • mysql unique option prefix myisam_recover instead of myisam-
  • 详解mysql 8.0.18命令
  • mysql将表名称修改成大写的存储过程
  • windows下mysql 5.7版本中修改编码为utf-8的方法步骤
  • mysql 的存储引擎,myisam和innodb的区别
  • mysql 5.6.14主从复制(也称mysql ab复制)环境配置方法
  • mysql闪回工具binlog2sql安装配置教程详解
  • 查询Mysql表之后将结果转换为json时如何能够保持字...
  • 怎样在java中查询mysql得到如下的json格式的结果
  • 如果将json格式数据存储到类似mysql这样的关系型数...
  • mysql数据库中某个字段存的是json数据,如何对json...
  • mysql5.7.23使用JSON_EXTRACT函数后返回结果集为空
  • MySQL怎么写存储过程查询某个表的某一条记录,并把...
  • 如何将从接口取到的json数据存入mysql数据库
  • mysql 5.7 怎么查看 json_extract函数? mysql 5.6...
  • mysql 类似json字符串截取,获取某个值
  • 怎么把查询到的mysql结果变成json
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql installer community 5.7.16安装详细教程win10下mysql 8.0.16 winx64安装图文最新教程mysql 密码增强插件mysql unique option prefix myisam_recover instead of myisam-详解mysql 8.0.18命令mysql将表名称修改成大写的存储过程windows下mysql 5.7版本中修改编码为utf-8的方法步骤mysql 的存储引擎,myisam和innodb的区别mysql 5.6.14主从复制(也称mysql ab复制)环境配置方法mysql闪回工具binlog2sql安装配置教程详解mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解mysql8新特性:降序索引详解mysql优化表时提示 table is already up mysql 8.0.11 安装配置方法图文教程(win1检测mysql的表的故障的方法mysql distinct 的基本实现原理详解datagrip2020下载mysql驱动失败的问题mysql比较运算符正则式匹配regexp的详细使ubuntu18.0.4下mysql 8.0.20 安装配置方法mysql5.7中 performance和sys schema中的安装mysql出错”a windows service with
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved