MySQL JSON数据类型如何操作?这里告诉你~

数据库 MySQL
mysql 自 5.7.8 版本开始,就支持了 json 结构的数据存储和查询,这表明了 mysql 也在不断的学习和增加 nosql 数据库的优点。但 mysql 毕竟是关系型数据库,在处理 json 这种非结构化的数据时,还是比较别扭的。

 

概述

mysql 自 5.7.8 版本开始,就支持了 json 结构的数据存储和查询,这表明了 mysql 也在不断的学习和增加 nosql 数据库的优点。但 mysql 毕竟是关系型数据库,在处理 json 这种非结构化的数据时,还是比较别扭的。

创建一个 JSON 字段的表

首先先创建一个表,这个表包含一个 json 格式的字段:

  1. CREATE TABLE table_name ( 
  2.     id INT NOT NULL AUTO_INCREMENT,  
  3.     json_col JSON, 
  4.     PRIMARY KEY(id) 
  5. ); 

上面的语句,主要注意 json_col 这个字段,指定的数据类型是 JSON。

插入一条简单的 JSON 数据

  1. INSERT INTO 
  2.     table_name (json_col)  
  3. VALUES 
  4.     ( 
  5. '{"City": "Galle", "Description": "Best damn city in the world"}' 
  6. ); 

上面这个 SQL 语句,主要注意 VALUES 后面的部分,由于 json 格式的数据里,需要有双引号来标识字符串,所以,VALUES 后面的内容需要用单引号包裹。

插入一条复杂的 JSON 数据 

  1. INSERT INTO table(col)  
  2. VALUES
  3. '{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' 
  4. ); 

这地方,我们插入了一个 json 数组。主要还是注意单引号和双引号的问题。

修改 JSON 数据

之前的例子中,我们插入了几条 JSON 数据,但是如果我们想修改 JSON 数据里的某个内容,怎么实现了?比如我们向 variations 数组里增加一个元素,可以这样:

  1. UPDATE myjson SET dict=JSON_ARRAY_APPEND(dict,'$.variations','scheveningen'WHERE id = 2; 

这个 SQL 语句中,$ 符合代表 JSON 字段,通过. 号索引到 variations 字段,然后通过 JSON_ARRAY_APPEND 函数增加一个元素。现在我们执行查询语句:

  1. SELECT * FROM myjson 

得到的结果是: 

  1. +----+-----------------------------------------------------------------------------------------+ 
  2. | id | dict                                                                                    | 
  3. +---+-----------------------------------------------------------------------------------------+ 
  4. |    | { "opening" :  "Sicilian" ,  "variations" : [ "pelikan" ,  "dragon" ,  "najdorf" ,  "scheveningen" ]} | 
  5. +----+-----------------------------------------------------------------------------------------+ 
  6.  row  in   set  ( 0.00  sec) 

关于 MySQL 中,JSON 数据的获取方法,参照官方链接 JSON Path Syntax

创建索引

MySQL 的 JSON 格式数据不能直接创建索引,但是可以变通一下,把要搜索的数据单独拎出来,单独一个数据列,然后在这个字段上键一个索引。下面是官方的例子: 

  1. mysql> CREATE TABLE jemp ( 
  2.     ->     c JSON, 
  3.     ->     g INT GENERATED ALWAYS AS (c-> "$.id" ), 
  4.     ->     INDEX i (g) 
  5.     -> ); 
  6. Query  OK,   rows affected ( 0.28  sec) 
  7. mysql> INSERT INTO jemp (c) VALUES 
  8.      >   ( '{"id": "1", "name": "Fred"}' ), ( '{"id": "2", "name": "Wilma"}' ), 
  9.      >   ( '{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}' ); 
  10. Query  OK,   rows affected ( 0.04  sec) 
  11. Records :  Duplicates:  Warnings :  
  12. mysql> SELECT c->> "$.name"  AS name 
  13.      >     FROM jemp WHERE g >  
  14. +--------+ 
  15. name   | 
  16. +--------+ 
  17. |  Barney  | 
  18. |  Betty   | 
  19. +--------+ 
  20.  rows  in set 0.00  sec) 
  21. mysql> EXPLAIN SELECT c->> "$.name"  AS name 
  22.      >    FROM jemp WHERE g >  \G 
  23. ***************************  1.  row *************************** 
  24.            id:  
  25.   select_type: SIMPLE 
  26.         table: jemp 
  27.    partitions: NULL 
  28.          type: range 
  29. possible_keys: i 
  30.           key: i 
  31.       key_len:  
  32.            
  33. ref NULL 
  34.          rows:  
  35.      filtered: 100.00          
  36. Extra:  Using where
  37.   row  in set ,   warning ( 0.00  sec) 
  38. mysql> SHOW WARNINGS\G 
  39. ***************************  1 row *************************** 
  40.    
  41. Level :  Note 
  42.     
  43. Code :  1003 
  44. Message :  /* select#1 */ select  json_unquote(json_extract( `test` `jemp` `c` '$.name')) 
  45. AS  `namefrom `test` `jemp`where  ( `test` `jemp` `g`  >  
  46. 1 row  in set ( 0.00  sec) 

这个例子很简单,就是把 JSON 字段里的 id 字段,单独拎出来成字段 g,然后在字段 g 上做索引,查询条件也是在字段 g 上。

字符串转 JSON 格式

把 json 格式的字符串转换成 MySQL 的 JSON 类型:

  1. SELECT CAST('[1,2,3]' as JSON) ; 
  2.  
  3. SELECT CAST('{"opening":"Sicilian","variations":["pelikan","dragon","najdorf"]}' as JSON);  

所有 MYSQL JSON 函数

  1. Name  Description 
  2. JSON_APPEND()  Append data to JSON document 
  3. JSON_ARRAY()  Create JSON array 
  4. JSON_ARRAY_APPEND()  Append data to JSON document 
  5. JSON_ARRAY_INSERT()  Insert into JSON array-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT(). 
  6. JSON_CONTAINS()  Whether JSON document contains specific object at path 
  7. JSON_CONTAINS_PATH()  Whether JSON document contains any data at path 
  8. JSON_DEPTH()  Maximum depth of JSON document 
  9. JSON_EXTRACT()  Return data from JSON document->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()). 
  10. JSON_INSERT()  Insert data into JSON document 
  11. JSON_KEYS()  Array of keys from JSON document 
  12. JSON_LENGTH()  Number of elements in JSON document 
  13. JSON_MERGE()  Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE() 
  14. JSON_MERGE_PRESERVE()  Merge JSON documents, preserving duplicate keys 
  15. JSON_OBJECT()  Create JSON object 
  16. JSON_QUOTE()  Quote JSON document 
  17. JSON_REMOVE()  Remove data from JSON document 
  18. JSON_REPLACE()  Replace values in JSON document 
  19. JSON_SEARCH()  Path to value within JSON document 
  20. JSON_SET()  Insert data into JSON document 
  21. JSON_TYPE()  Type of JSON value 
  22. JSON_UNQUOTE()  Unquote JSON value 
  23. JSON_VALID()  Whether JSON value is valid 
责任编辑:庞桂玉 来源: ITPUB
相关推荐

2011-05-26 13:54:04

Json

2017-07-10 13:38:07

MySQL数据类型整数类型

2010-10-15 13:28:34

MySql数据类型

2019-11-12 08:53:32

PG数据数据库

2020-09-18 10:18:08

MySQL数据插入数据库

2010-10-08 14:04:44

MySQL数值数据类型

2017-08-25 09:18:04

2010-06-13 18:00:56

MySQL数据类型

2010-06-10 10:06:01

MySQL数据类型

2010-01-07 16:55:06

JSON字符串

2024-03-25 08:18:31

2009-09-01 16:35:55

C#操作String数

2010-09-15 14:02:07

关键数据备份

2016-11-01 14:37:15

老龄化智慧养老大数据

2018-04-04 12:54:51

航空大数据航班延误

2010-05-31 10:35:12

MySQL数据类型

2016-08-18 14:13:55

JavaScript基本数据引用数据

2010-01-07 16:45:51

JSON数据类型

2014-01-05 17:08:09

PostgreSQL数据类型

2019-08-12 11:40:48

数据库SQLite3数据类型
点赞
收藏

51CTO技术栈公众号