mysql 之 Json字段操作

迈不过友情╰ 2023-07-20 05:35 83阅读 0赞

**Mysql 自5.7之后增加了很多函数用于处理Json字串。方便实用。**

# **基本改变** #

### Json数组： ###

["abc", 10, null, true, false]

### Json对象： ###

{"k1": "value", "k2": 10}

**JSON数组元素和JSON对象键值内允许嵌套**

# Json函数 #

### JSON\_TYPE()：返回json字串类型 ###

mysql> SELECT JSON_TYPE('["a", "b", 1]');
    +----------------------------+
    | JSON_TYPE('["a", "b", 1]') |
    +----------------------------+
    | ARRAY                      |
    +----------------------------+
    
    mysql> SELECT JSON_TYPE('"hello"');
    +----------------------+
    | JSON_TYPE('"hello"') |
    +----------------------+
    | STRING               |
    +----------------------+
    
    mysql> SELECT JSON_TYPE('hello');
    ERROR 3146 (22032): Invalid data type for JSON data in argument 1
    to function json_type; a JSON string or JSON type is required.

MySQL使用`utf8mb4`字符集和`utf8mb4_bin`排序规则处理JSON上下文中使用的字符串 。其他字符集中的字符串将`utf8mb4`根据需要转换。（对于`ascii`或 `utf8`字符集中的字符串，无需进行转换，因为`ascii`和`utf8`是的子集`utf8mb4。一般将数据库字符集设置为utf8mb4）`

### JSON\_ARRAY()：将数值包装为json数组 ###

mysql> SELECT JSON_ARRAY('a', 1, NOW());
    +----------------------------------------+
    | JSON_ARRAY('a', 1, NOW())              |
    +----------------------------------------+
    | ["a", 1, "2015-07-27 09:43:47.000000"] |
    +----------------------------------------+

### JSON\_OBJECT()：将数值包装为json对象 ###

mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
    +---------------------------------------+
    | JSON_OBJECT('key1', 1, 'key2', 'abc') |
    +---------------------------------------+
    | {"key1": 1, "key2": "abc"}            |
    +---------------------------------------+

### JSON\_MERGE()：将多个json合并为一个 ###

mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
    +--------------------------------------------+
    | JSON_MERGE('["a", 1]', '{"key": "value"}') |
    +--------------------------------------------+
    | ["a", 1, {"key": "value"}]                 |
    +--------------------------------------------+

### Json字符串中的key-value访问 ###

使用column-path运算符 `->得到字段中json某个key的值，但是值中的"""和"\"都会显示，如果不想显示，使用``->>。`

#创建表
    CREATE TABLE facts (ids JSON);
    
    #插入数据  这里要注意是否启用了服务器SQL模式，插入数据时要注意特殊符号
    INSERT INTO facts VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
    
    #查看数据
    mysql> select ids from facts;
    +---------------------------------------------------------+
    | ids                                                     |
    +---------------------------------------------------------+
    | {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
    +---------------------------------------------------------+
    1 row in set (0.00 sec)

mysql> SELECT ids->'$.mascot' FROM facts;
    +---------------------------------------------+
    | ids->'$.mascot'                             |
    +---------------------------------------------+
    | "Our mascot is a dolphin named \"Sakila\"." |
    +---------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT ids->>'$.mascot' FROM facts;
    +-----------------------------------------+
    | ids->>'$.mascot'                   |
    +-----------------------------------------+
    | Our mascot is a dolphin named "Sakila". |
    +-----------------------------------------+
    
    #注意，这里访问json字段key使用单引号还是双引号取决于sql模式

### 读取json字段中某个key的值 ###

SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
    +---------------------------------------------------------+
    | JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
    +---------------------------------------------------------+
    | "Aztalan"                                               |
    +---------------------------------------------------------+
    
    #去除值双引号
    mysql> SELECT JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name'));
    +-----------------------------------------------------------------------+
    | JSON_UNQUOTE(JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name')) |
    +-----------------------------------------------------------------------+
    | Aztalan                                                               |
    +-----------------------------------------------------------------------+

小结：

<table> 
 <tbody> 
  <tr> 
   <td>‘$.*’</td> 
   <td>返回全部json</td> 
  </tr> 
  <tr> 
   <td>‘$.title’</td> 
   <td>返回key=”title”的数据</td> 
  </tr> 
  <tr> 
   <td>‘$**.text’</td> 
   <td>返回所有最底层key=”text”的数据</td> 
  </tr> 
  <tr> 
   <td>‘$.content[*].item1[*]’</td> 
   <td>返回key=content的list的key=item1的list的所有内容</td> 
  </tr> 
 </tbody> 
</table>

**[官网参考地址][Link 1]**

[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/json.html