MySQL中的JSON字段List成員檢查

更新時間：2022年08月25日 11:49:33 作者：彭世瑜

這篇文章主要介紹了MySQL中的JSON字段List成員檢查，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

JSON字段List成員檢查

文檔

https://dev.mysql.com/doc/refman/8.0/en/json.html
https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html

方法

JSON_CONTAINS(target, candidate[, path])

value MEMBER OF(json_array)

查詢示例

mysql> set @list = JSON_ARRAY(1, 2);
Query OK, 0 rows affected (0.01 sec)

mysql> select 1 MEMBER OF(@list);
+--------------------+
| 1 MEMBER OF(@list) |
+--------------------+
| ? ? ? ? ? ? ? ? ?1 |
+--------------------+
1 row in set (0.00 sec)


mysql> SELECT JSON_CONTAINS(@list, '1');
+---------------------------+
| JSON_CONTAINS(@list, '1') |
+---------------------------+
| ? ? ? ? ? ? ? ? ? ? ? ? 1 |
+---------------------------+
1 row in set (0.01 sec)

MySQL中JSON字段操作

Mysql 自5.7之后增加了很多函數(shù)用于處理Json字串。方便實用。

基本改變

Json數(shù)組：

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

Json對象：

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

JSON數(shù)組元素和JSON對象鍵值內(nèi)允許嵌套

Json函數(shù)

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排序規(guī)則處理JSON上下文中使用的字符串。其他字符集中的字符串將utf8mb4根據(jù)需要轉(zhuǎn)換。（對于ascii或 utf8字符集中的字符串，無需進(jìn)行轉(zhuǎn)換，因為ascii和utf8是的子集utf8mb4。一般將數(shù)據(jù)庫字符集設(shè)置為utf8mb4）

JSON_ARRAY()：將數(shù)值包裝為json數(shù)組

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

JSON_OBJECT()：將數(shù)值包裝為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的值，但是值中的"""和"\"都會顯示，如果不想顯示，使用->>。

#創(chuàng)建表
CREATE TABLE facts (ids JSON);
?
#插入數(shù)據(jù) ?這里要注意是否啟用了服務(wù)器SQL模式，插入數(shù)據(jù)時要注意特殊符號
INSERT INTO facts VALUES (JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
?
#查看數(shù)據(jù)
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 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-----------------------------------------------------------------------+

小結(jié)：

‘$.*’	返回全部json
‘$.title’	返回key=”title”的數(shù)據(jù)
‘$**.text’	返回所有最底層key=”text”的數(shù)據(jù)
‘$.content[].item1[]’	返回key=content的list的key=item1的list的所有內(nèi)容

官網(wǎng)參考地址

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: