mysql數(shù)據(jù)庫中查詢json的技巧大全
前置工作
CREATE TABLE `mk_task_record` ( `task_id` int NOT NULL AUTO_INCREMENT, `task_name` varchar(50) DEFAULT NULL, `result_json` json DEFAULT NULL, `result_str` longtext, `create_time` datetime DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`task_id`), KEY `task_name` (`task_name`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
插入數(shù)據(jù)：
插入帶轉(zhuǎn)義符的json：
INSERT INTO `test`.`mk_task_record`(`task_id`, `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES (1, '哈哈1', '{\"age\": 22, \"name\": \"李四\", \"major\": \"計算機科學\", \"university\": \"清華大學\", \"is_graduated\": false}', '{\r\n \"name\": \"李四\",\r\n \"age\": 22,\r\n \"major\": \"計算機科學\",\r\n \"university\": \"清華大學\",\r\n \"is_graduated\": false\r\n}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');
INSERT INTO `test`.`mk_task_record`(`task_id`, `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES (2, '哈哈2', '{\"age\": 22, \"name\": \"張三\", \"major\": \"計算機科學\", \"university\": \"清華大學\", \"is_graduated\": false}', '{\r\n \"name\": \"張三\",\r\n \"age\": 22,\r\n \"major\": \"計算機科學\",\r\n \"university\": \"清華大學\",\r\n \"is_graduated\": false\r\n}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');插入不帶轉(zhuǎn)義符的json：
INSERT INTO `test`.`mk_task_record`( `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES ( '哈哈3', '{"name": "王五", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}', '{"name": "王五", "age": 35, "address": {"street": "123 Main St", "city": "Chicago"}}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');
INSERT INTO `test`.`mk_task_record`( `task_name`, `result_json`, `result_str`, `create_time`, `update_time`) VALUES ( '哈哈4', '{"name": "趙六", "age": 30, "address": {"street": "123 Main St", "city": "Chicago"}}','{"name": "趙六", "age": 30, "address": {"street": "123 Main St", "city": "Chicago"}}', '2025-01-01 00:13:49', '2025-01-01 00:13:52');- result_json是json格式；
- result_result是longtext格式；
經(jīng)過實際測試，json、varchar、text類型的json格式都可以使用以下的函數(shù)，且效果相同。
一、JSON_EXTRACT 提取指定數(shù)據(jù)
1.1 提取簡單值
SELECT JSON_EXTRACT(result_str,'$.name') from mk_task_record; SELECT JSON_EXTRACT(result_json,'$.name') from mk_task_record;
- json類型：
- text類型
結果也帶引號；
如何去掉引號呢？
——查看【四、JSON_UNQUOTE 取消雙引號】
- 作條件查詢
select *from mk_task_record where json_extract(result_str,'$.name') ='張三'; select *from mk_task_record where json_extract(result_json,'$.name') ='張三';
兩條sql返回的都一樣：
http://www.dbjr.com.cn/database/3356367c4.htm
二、字段->'$.json屬性’進行查詢條件，以及數(shù)據(jù)反顯
select result_json->'$.name' from mk_task_record;
作為查詢條件，不用轉(zhuǎn)移雙引號。
三、JSON_UNQUOTE 取消雙引號
JSON_UNQUOTE() 函數(shù)取消雙引號引用 JSON 值，并將結果作為字符串返回。
3.1 語法：
JSON_UNQUOTE(json_val)
參數(shù)說明：
json_val：必需的。一個字符串。
返回值：
取消雙引號引用 JSON 值
返回NULL情況：參數(shù)為 NULL。
報錯情況：不是有效的 JSON 字符串文本。能夠識別下表中的轉(zhuǎn)義字符：
- "：雙引號 "
- \b：退格字符
- \f：換頁符
- \n：換行符
- \r：回車符
- \t：制表符
- \：反斜杠
- \uXXXX：Unicode 值 XXXX 的 UTF-8 字節(jié)
3.2 示例：
SELECT JSON_UNQUOTE(JSON_EXTRACT(result_json,'$.name')) from mk_task_record;
五、JSON_SEARCH 查找
JSON_SEARCH() 函數(shù)返回一個給定字符串在一個 JSON 文檔中的路徑。
5.1 語法：
JSON_SEARCH(json, one_or_all, search_str) JSON_SEARCH(json, one_or_all, search_str, escape_char) JSON_SEARCH(json, one_or_all, search_str, escape_char, path)
參數(shù)說明：
- on：必需的。一個 JSON 文檔。
- one_or_all：必需的?？捎弥担?lsquo;one’, ‘all’。
- 規(guī)則如下：‘one’：返回第一個匹配的路徑。‘all’：返回所有匹配的路徑。所有的路徑會包裝在一個數(shù)組內(nèi)返回。
- search_str：必需的。被搜索的字符串。 您可以在 search_str 參數(shù)中使用 % 和 _ 通配符，就像 LIKE 一樣：% 匹配任意數(shù)量的任意字符。_ 匹配一個任意字符。
- escape_char：可選的。 如果 search_str 中包含 % 和 _，需要在他們之前添加轉(zhuǎn)移字符。默認是 \。
- path：可選的。只能在此路徑下進行搜索。
返回值：一個路徑字符串或者由多個路徑組成的數(shù)組。返回 NULL情況：未搜索到指定的字符串。JSON 文檔中不存在指定的 path。任意一個參數(shù)為 NULL。
報錯情況：json 不是有效的 JSON 文檔。path 不是有效的路徑表達式。
5.2 示例：
select *from mk_task_record where JSON_SEARCH(result_json,'one','張三');
六、JSON_SET 插入或更新數(shù)據(jù)
JSON_SET() 函數(shù)在一個 JSON 文檔中插入或更新數(shù)據(jù)并返回新的 JSON 文檔。它相當于是 JSON_INSERT() 和 JSON_REPLACE() 的組合。
6.1 語法：
JSON_SET(json, path, value[, path2, value2] ...)
參數(shù)說明：
- json：必需的。被修改的 JSON 文檔。
- path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。
- value：必需的。要設置的數(shù)據(jù)。
插入或更新數(shù)據(jù)并返回新的 JSON 文檔。規(guī)則如下：
- 存在路徑：更新。
- 不存在路徑：添加。
- 若value 為字符串：直接插入。
- 返回 NULL情況：JSON 文檔或者路徑為 NULL。
報錯情況：json 不是有效的 JSON 文檔。
path 不是有效的路徑表達式或者其中包含 * 或 **。
6.2 示例：
UPDATE mk_task_record SET result_str = JSON_SET(result_str, '$.name', '王麻子', '$.age', '36') where task_id =1;
七、JSON_CONTAINS 判斷是否包含
JSON_CONTAINS() 函數(shù)檢查一個 JSON 文檔中是否包含另一個 JSON 文檔。
7.1 語法：
JSON_CONTAINS(target_json, candidate_json) JSON_CONTAINS(target_json, candidate_json, path)
參數(shù)說明：
- target_json必需的。一個 JSON 文檔。
- candidate_json：必需的。被包含的 JSON 文檔。
- path：可選的。一個路徑表達式。
- 返回值：
包含：返回1。
不包含：返回0。
返回 NULL情況：JSON 文檔中不存在指定的路徑。
任意一個參數(shù)為 NULL。
報錯情況：
json 不是有效的 JSON 文檔。path 不是有效的路徑表達式。
示例：
SELECT * FROM mk_task_record WHERE JSON_CONTAINS(result_json, '"張三"','$.name');
注意："張三"有雙引號一定加，否則報錯：
八、JSON_REMOVE 刪除指定數(shù)據(jù)
JSON_REMOVE() 函數(shù)從一個 JSON 文檔中刪除由路徑指定的數(shù)據(jù)并返回修改后的 JSON 文檔。
8.1 語法：
JSON_REMOVE(json, path[, path] ...)
參數(shù)說明：
- json：必需的。一個 JSON 文檔。
- path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。
返回值：刪除后的JSON文檔。
8.2 示例：
select JSON_REMOVE(result_str,'$.major') from mk_task_record where task_id =1;
九、JSON_REPLACE 替換數(shù)據(jù)
JSON_REPLACE() 函數(shù)在一個 JSON 文檔中替換已存在的數(shù)據(jù)并返回新的 JSON 文檔
9.1 語法：
JSON_REPLACE(json, path, value[, path2, value2] ...)
參數(shù)說明：
- json：必需的。被修改的 JSON 文檔。
- path：必需的。一個有效的路徑表達式，它不能包含 * 或 **。
- value：必需的。新的數(shù)據(jù)。
- 返回值：替換后的JSON文檔?；?NULL情況：JSON 文檔或者路徑為 NULL。
報錯情況：
json 不是有效的 JSON 文檔。path 不是有效的路徑表達式或者其中包含 * 或 **。
9.2 示例：
select JSON_REPLACE(result_str,'$.university','復旦大學') from mk_task_record where task_id =1;
拓展：
http://www.dbjr.com.cn/database/3356414s9.htm
總結
到此這篇關于mysql數(shù)據(jù)庫中查詢json技巧大全的文章就介紹到這了,更多相關mysql查詢json技巧內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
MySQL錯誤TIMESTAMP column with CURRENT_TIMESTAMP的解決方法
這篇文章主要介紹了MySQL錯誤TIMESTAMP column with CURRENT_TIMESTAMP的解決方法,需要的朋友可以參考下2014-06-06
這篇文章詳細介紹了MySQL中的各種約束類型,包括非空約束、默認約束、檢查約束、唯一約束和主鍵約束,以及外鍵約束,每種約束類型都有其特定的用途和語法,并且可以是列級約束或表級約束,文章還探討了如何給約束命名、刪除約束2025-01-01
這篇文章主要介紹了mysql斷電后無法啟動的問題小結,本文給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友參考下吧2024-04-04
在開始安裝前，先說明一下mysql-5.6.4與較低的版本在安裝上的區(qū)別，從mysql-5.5起，mysql源碼安裝開始使用cmake了，因此當我們配置安裝目錄./configure --perfix=/.....的時候和以前的會有些區(qū)別，這點我們稍后會提到2013-06-06
本文主要介紹了MySQL8 批量修改字符集腳本，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-03-03
行級鎖加鎖規(guī)則比較復雜,不同的場景,加鎖的形式是不同的,本文主要介紹了深入理解MySQL中的行級鎖,具有一定的參考價值,感興趣的可以了解一下2024-01-01
MySQL中count(distinct?col...)組合使用的注意要點詳解
@count()是一個聚合函數(shù),返回指定匹配條件的行數(shù),開發(fā)中常用來統(tǒng)計表中數(shù)據(jù)、全部數(shù)據(jù)、不為null數(shù)據(jù)或者去重數(shù)據(jù),這篇文章主要給大家介紹了關于MySQL中count(distinct?col...)組合使用的注意要點,需要的朋友可以參考下2024-08-08
linux系統(tǒng)ubuntu18.04安裝mysql 5.7
這篇文章主要為大家詳細介紹了linux系統(tǒng)ubuntu18.04安裝mysql 5.7，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-09-09
下面小編就為大家?guī)硪黄鉀QMYSQL連接端口被占引入文件路徑錯誤的問題。小編覺得挺不錯的，現(xiàn)在就分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-08-08
MySQL學習第二天，主要為大家詳細介紹了在Windows 64位操作系統(tǒng)下安裝和配置MySQL的具體步驟，整理一份mysql winx64安裝配置方法教程，感興趣的小伙伴們可以參考一下2016-05-05