mysql中的json數(shù)據(jù)類型

1. json

JSON數(shù)組包含一個(gè)由逗號(hào)分隔的值列表，并包含在 字符[和]字符中：

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

JSON對(duì)象包含一組由逗號(hào)分隔的鍵值對(duì)，并包含在字符{和 }字符中，JSON對(duì)象中的鍵必須是字符串：

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

在JSON數(shù)組元素和JSON對(duì)象鍵值中允許嵌套：

[99, {"id": "HK500", "cost": 75.99}, ["hot", "cold"]]
{"k1": "value", "k2": [10, 20]}

在MySQL中，JSON值被寫為字符串。MySQL解析在需要JSON值的上下文中使用的任何字符串，如果它作為JSON無效則會(huì)產(chǎn)生錯(cuò)誤。

2. 創(chuàng)建json值

1.JSON_ARRAY 生成json數(shù)組
JSON_ARRAY(val1,val2,val3...)生成一個(gè)包含指定元素的json數(shù)組。

SELECT JSON_ARRAY(1, "abc", NULL, TRUE); -- [1, "abc", null, true]

2.JSON_OBJECT 生成json對(duì)象
JSON_OBJECT(key1,val1,key2,val2...) 生成一個(gè)包含指定K-V對(duì)的json object。如果有key為NULL或參數(shù)個(gè)數(shù)為奇數(shù)，則拋錯(cuò)。

SELECT JSON_OBJECT('name', 'lily', 'age', 20); -- {"name": "lily", "age": 20}

3.JSON_QUOTE 加"號(hào)
JSON_QUOTE(json_val) -- 將json_val用"號(hào)括起來。

SELECT JSON_QUOTE('[1,2,3]'); -- "[1,2,3]"
  1. JSON_MERGE_PRESERVE()) 獲取兩個(gè)或多個(gè)JSON文檔并返回組合結(jié)果：
 SELECT JSON_MERGE_PRESERVE('[1, "abc"]', '{"name": "lily"}');-- [1, "abc", {"name": "lily"}]

2.插入記錄
創(chuàng)建一個(gè)表

CREATE TABLE t_json(id INT PRIMARY KEY, name VARCHAR(20) , info  JSON);

插入含有json數(shù)組的記錄

INSERT INTO t_json VALUES( 1, 'Lily', JSON_ARRAY(20, "student",now()));

插入含有json對(duì)象的記錄

INSERT INTO t_json(id,name,info) VALUES( 2, 'Amy', JSON_OBJECT("age", 19, "time", now()));
INSERT INTO t_json(id,sname,info) VALUES( 3, 'Tom', '{"age":20, "time":"2019-01-1 08:00:00"}');

3. 搜索json值。

路徑表達(dá)式對(duì)于提取JSON文檔的一部分或修改JSON文檔的函數(shù)很有用，以指定該文檔中的操作位置。例如，以下查詢從JSON文檔中提取具有name鍵的成員的值 ：

SELECT JSON_EXTRACT('{"id": 2, "name": "lucy"}', '$.name'); //lucy

路徑語法使用前導(dǎo)$字符來表示正在考慮的JSON文檔，可選地后跟選擇器，它們連續(xù)指示文檔的更多特定部分：

  • 后跟密鑰名稱的句點(diǎn)用具有給定鍵的對(duì)象命名成員。如果沒有引號(hào)的名稱在路徑表達(dá)式中不合法（例如，如果它包含空格），則必須在雙引號(hào)內(nèi)指定鍵名。
  • [*N*]附加到a path，選擇一個(gè)數(shù)組命名數(shù)組中位置的值N 。數(shù)組位置是從零開始的整數(shù)。
  • 路徑可以包含*或 **通配符：
    • .[*] 計(jì)算JSON對(duì)象中所有成員的值。
    • [*] 計(jì)算JSON數(shù)組中所有元素的值。
    • prefix****suffix計(jì)算所有以命名前綴開頭并以命名后綴結(jié)尾的路徑。
  • 文檔中不存在的路徑（評(píng)估為不存在的數(shù)據(jù)）的計(jì)算結(jié)果為NULL。
[3, {"a": [5, 6], "b": 10}, [99, 100]]
//$[1]為{"a": [5, 6], "b": 10}，$[1].a[1]為 6
//$[3]為NULL。

如果路徑表達(dá)式中的未加引號(hào)的鍵名稱不合法，則必須引用命名鍵的路徑組件。讓我們$參考這個(gè)值，且密鑰都包含空格，必須引用：

{"a fish": "shark", "a bird": "sparrow"}
//$."a fish"`為 `shark`。

mysql> SELECT JSON_EXTRACT('{"a": 1, "b": 2, "c": [3, 4, 5]}', '$.*');//[1, 2, [3, 4, 5]]

可以使用帶有to關(guān)鍵字的范圍來指定JSON數(shù)組的子集。

 SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');//[2, 3, 4]

last關(guān)鍵字被支撐為最后一個(gè)元素的陣列中的索引的同義詞。表單的表達(dá)式可用于相對(duì)尋址，也可用于范圍定義，如下所示：last - *N*

 SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[last-3 to last-1]');//[2, 3, 4]

4. 修改記錄

  1. json_array_insert()函數(shù)：添加新值但不替換現(xiàn)有值
  SET @j = '["a", {"b": [true, false]}, [10, 20]]';
 SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
 // ["a", {"b": [true, false]}, [10, 20, 2]]
  1. json_set()： 替換存在的路徑的值，并為不存在的路徑添加值：
 SET @j = '["a", {"b": [true, false]}, [10, 20]]';
 SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
//["a", {"b": [1, false]}, [10, 20, 2]]
  1. json_replace() 替換現(xiàn)有值并忽略新值：
SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
// ["a", {"b": [1, false]}, [10, 20]]

4.JSON_REMOVE()獲取JSON文檔和一個(gè)或多個(gè)指定要從文檔中刪除的值的路徑。返回值是原始文檔減去文檔中存在的路徑選擇的值

SET @j = '["a", {"b": [true, false]}, [10, 20]]';
SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
//["a", {"b": [true]}]

5. JSON值的比較和排序

JSON值可以使用進(jìn)行比較 =、<、<=、>、>=、<>、!=、<=>
JSON值尚不支持以下比較運(yùn)算符和函數(shù)：BETWEEN、IN()、GREATEST()、LEAST()
以上列出的比較運(yùn)算符和函數(shù)是通過將JSON值轉(zhuǎn)換為本機(jī)MySQL數(shù)值或字符串?dāng)?shù)??據(jù)類型，使它們具有一致的非JSON標(biāo)量類型。
JSON值的比較發(fā)生在兩個(gè)級(jí)別。第一級(jí)比較基于比較值的JSON類型。如果類型不同，則比較結(jié)果僅由哪種類型具有更高優(yōu)先級(jí)來確定。如果這兩個(gè)值具有相同的JSON類型，則使用特定于類型的規(guī)則進(jìn)行第二級(jí)比較。

NULL->INTEGER, DOUBLE->STRING->OBJECT->ARRAY->BOOLEAN->DATE->TIME->DATETIME->OPAQUE->BIT->BLOB按此順序優(yōu)先級(jí)依次變大。

對(duì)于具有相同優(yōu)先級(jí)的JSON值，比較規(guī)則是特定于類型的：
1） BLOB、BIT、OPAQUE
比較兩個(gè)值 的第一個(gè)字節(jié)，其中N是較短值中的字節(jié)數(shù)。如果N兩個(gè)值的第一個(gè)字節(jié)相同，則在較長值之前排序較短的值。
2） DATETIME
表示較早時(shí)間點(diǎn)的值在表示稍后時(shí)間點(diǎn)的值之前排序。如果兩個(gè)值最初 分別來自MySQL DATETIME和TIMESTAMP類型，則它們相等，如果它們代表相同的時(shí)間點(diǎn)。
3）TIME
兩個(gè)時(shí)間值中較小的一個(gè)在較大的值之前排序。
4） DATE`
較早的日期是在最近的日期之前訂購的。
5） ARRAY
如果兩個(gè)JSON數(shù)組具有相同的長度并且數(shù)組中相應(yīng)位置的值相等，則它們是相等的。如果數(shù)組不相等，則它們的順序由第一個(gè)位置中存在差異的元素確定。首先排序在該位置具有較小值的數(shù)組。如果較短數(shù)組的所有值都等于較長數(shù)組中的相應(yīng)值，則首先排序較短的數(shù)組。

   [] < ["a"] < ["ab"] < ["ab", "cd", "ef"] < ["ab", "ef"]
  1. BOOLEAN
    false小于 true 。
  2. OBJECT`
    如果兩個(gè)JSON對(duì)象具有相同的鍵集，則它們是相等的，并且每個(gè)鍵在兩個(gè)對(duì)象中具有相同的值。
   {"a": 1, "b": 2} = {"b": 2, "a": 1}//相等

8） STRING
字符串在被比較的兩個(gè)字符串以詞法字典序排序 ，其中N是較短字符串的長度。如果N兩個(gè)字符串的第一個(gè) 字節(jié)相同，則認(rèn)為較短的字符串小于較長的字符串。

   "a" < "ab" < "b" < "bc"
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 歌頌農(nóng)村換新容，窮溝千嶺慶興隆。院起層樓奔大有，巷鋪硬道步錦程。
    沙漠小胡楊閱讀 172評(píng)論 0贊 2
  • 十字路口 文/雄關(guān)漫道 十字路口 一個(gè)老婦在燒著冥紙 焰火凝重了她的雙眸 風(fēng)翻讀著她的記憶 我不知道，她要傾訴的 ...
    閆殿才閱讀 381評(píng)論 13贊 34

友情鏈接更多精彩內(nèi)容