怎么在mysql中放入json数据

一、怎么在mysql中放入json数据

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

再来一个复杂点的例子，用的是EXPLAIN的JSON结果集。

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)

SELECT* FROM JSON_TABLE(@json_str1,

a1_1 varchar(100) PATH'$.key',

a1_2 varchar(100) PATH'$.ref[0]',

a1_3 varchar(100) PATH'$.filtered',

nested path'$.cost_info'

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'

b1_1 varchar(100) path'$.query_cost'

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

| 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|

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

当然，JSON_table函数还有其他的用法，我这里不一一列举了，详细的参考手册。

二、MySQL中使用JSON格式存储数据mysql中json

MySQL是一种流行的关系型数据库管理系统，被广泛应用于各种Web应用、企业应用和数据分析场景中。MySQL支持多种数据格式存储，其中一种最近越来越流行，那就是JSON格式。JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，经常用于Web应用中的数据交互。MySQL的支持JSON格式存储，使得其在处理日志、社交网络、文档管理等非结构化数据的场景中更具竞争力。

MySQL 5.7以及之后版本引入了本地支持JSON类型的能力，在表格结构中增加了“JSON类型”选项。JSON数据类型开辟了MySQL兼容非结构化和半结构化数据的通道。在此之前，如果想兼容这些类型的数据，MySQL只能使用BLOB或TEXT类型。

以下是在MySQL中创建JSON类型列的示例：

在数据库中存储JSON数据也很简单，可以使用INSERT INTO或UPDATE语句直接或间接地向JSON字段写入数据。

以可嵌套JSON属性的方式向user表格中插入数据，如下所示：

INSERT INTO user VALUES(1,‘{“name”:“John Doe”,“address”:{“street”:“123 Mn St”,“city”:“Anytown”},“phone_numbers”: [“800-555-1212”,“877-555-1212”]}’, 30);

要获取存储在JSON列中的数据，可以使用JSON_EXTRACT函数。JSON_EXTRACT函数需要两个参数，第一个参数是列名，第二个参数是JSON路径，指定要检索的数据。

例如，如果要检索存储在info列中的用户名称，可以使用以下代码：

SELECT JSON_EXTRACT(info,‘$.name’) AS user_name FROM user WHERE id= 1;

在上面的代码中，JSON_EXTRACT函数将检索用户名称数据，并将其作为用户名称输出。$后面的点(“.”)表示是从根节点开始。上述命令也可以使用另一种方式实现，如下所示：

SELECT info->’$.name’ AS user_name FROM user WHERE id= 1;

这两种方法都能达到内部数据结构导航的目的。选择哪种方法取决于个体更接管的哪一种格式。

如果要检索JSON列中的数组元素，则可以使用“[*]”通配符，例如：

SELECT JSON_EXTRACT(info,‘$.phone_numbers[*]’) AS phone_numbers FROM user WHERE id= 1;

在上述代码中，通配符将返回所有电话号码。

JSON路径还支持在其上执行一些基本运算。例如，您可以使用数字索引获取数组中的第一个元素，如下所示：

SELECT JSON_EXTRACT(info,‘$.phone_numbers[0]’) AS phone_number FROM user WHERE id= 1;

总体来说，MySQL 5.7之后的版本中，通过JSON格式存储数据，MySQL就可以有效的 supporting NoSQL数据模型并且随着功能的更新，MySQL将支持越来越多的JSON-related特性，从而运用在更多的非关系场景用例。

李玲莉.(2018). MYSQL 8.0的JSON数据类型及相关函数.现代数据库,(01), 44-45.

计算机网络及应用.(2019). MySQL 5.7 JSON类型使用–存储JSON类型及Json_EXTRACT的使用.计算机网络及应用,(04), 1-4.

三、MySQL中JSON查询的方法和实践mysql中json查询

在MySQL 5.7版本中，引入了JSON数据类型，这使得MySQL可以支持存储和操作JSON格式的数据。但是，如何在MySQL数据库中进行JSON查询并获得想要的结果呢？本文将探讨MySQL中JSON查询的方法和实践。

我们需要创建一个包含JSON字段的表。我们可以通过以下命令创建一个名为“employee”的表，其中包含一个JSON字段“attributes”：

在我们进行JSON查询之前，我们需要将JSON数据添加到表中。我们可以使用以下命令向表中添加JSON数据：

INSERT INTO employee(id, name, attributes)

VALUES(1,‘John’,‘{“age”: 25,“gender”:“male”,“department”:“IT”}’),

(2,‘Jane’,‘{“age”: 30,“gender”:“female”,“department”:“Finance”}’),

(3,‘Bob’,‘{“age”: 35,“gender”:“male”,“department”:“HR”}’);

3.使用JSON_EXTRACT函数进行JSON查询

接下来，我们将使用MySQL的JSON_EXTRACT函数进行JSON查询。JSON_EXTRACT函数的语法如下：

其中，json_data是要查询的JSON数据，而path则是要提取的JSON元素的路径。

例如，假设我们想从“employee”表中查询所有年龄大于30岁的员工的名字和部门。我们可以使用以下查询语句：

SELECT name, JSON_EXTRACT(attributes,‘$.department’) as department

WHERE JSON_EXTRACT(attributes,‘$.age’)> 30;

在上面的查询语句中，我们使用JSON_EXTRACT函数从“attributes”字段中提取“department”的值，并将其命名为“department”。然后，我们使用WHERE子句过滤年龄大于30岁的员工。我们选择“name”和“department”字段作为查询结果。

4.使用JSON_UNQUOTE函数解析JSON字符串

有时候，我们可能需要将JSON字符串转换为纯文本字符串。我们可以使用MySQL的JSON_UNQUOTE函数来实现这一点。JSON_UNQUOTE函数的语法如下：

例如，假设我们想从“employee”表中查询所有员工的年龄和部门，并将这些信息组合为一条字符串。我们可以使用以下查询语句：

SELECT CONCAT(JSON_UNQUOTE(JSON_EXTRACT(attributes,‘$.age’)),‘ years old, works in the‘, JSON_UNQUOTE(JSON_EXTRACT(attributes,‘$.department’))) as info

在上面的查询语句中，我们使用JSON_EXTRACT函数从“attributes”字段中提取“age”和“department”的值，并使用JSON_UNQUOTE函数将这些字符串转换为纯文本字符串。然后，我们使用CONCAT函数将这些信息组合为一条字符串作为查询结果。

在MySQL中进行JSON查询并不难。我们只需要了解如何使用JSON_EXTRACT和JSON_UNQUOTE函数，并熟悉JSON元素的路径表示方法。通过了解MySQL中的JSON查询，我们可以更轻松地处理JSON格式的数据。