本文探討了MySQL 9.1中JSON實(shí)現(xiàn)的細(xì)節(jié)，延續(xù)了之前文章《SQL vs NoSQL: The Differences》中關(guān)於SQL和NoSQL數(shù)據(jù)庫(kù)界限模糊的論點(diǎn)，兩者都在互相借鑒特性。 MySQL 5.7 InnoDB數(shù)據(jù)庫(kù)和PostgreSQL 9.2 都直接支持在單個(gè)字段中存儲(chǔ)JSON文檔類型。

需要注意的是，任何數(shù)據(jù)庫(kù)都能將JSON文檔作為單個(gè)字符串blob存儲(chǔ)。然而，MySQL和PostgreSQL支持將驗(yàn)證後的JSON數(shù)據(jù)存儲(chǔ)為真正的鍵值對(duì)，而不是簡(jiǎn)單的字符串。

關(guān)鍵要點(diǎn)

• MySQL 5.7 InnoDB數(shù)據(jù)庫(kù)和PostgreSQL 9.2 直接支持JSON文檔類型，但由於直接索引的限制，應(yīng)謹(jǐn)慎使用。
• JSON最適合存儲(chǔ)稀疏數(shù)據(jù)、自定義屬性、層次結(jié)構(gòu)以及需要靈活性的情況。它不應(yīng)替代經(jīng)常查詢或索引數(shù)據(jù)的規(guī)範(fàn)化列。
• MySQL提供各種函數(shù)來(lái)創(chuàng)建、驗(yàn)證、搜索和修改JSON對(duì)象。這些函數(shù)包括JSON_ARRAY()、JSON_OBJECT()、JSON_QUOTE()、JSON_TYPE()、JSON_VALID()、JSON_CONTAINS()、JSON_SEARCH()，以及用於使用路徑表示法更新JSON文檔的JSON_SET()和JSON_MERGE_PATCH()等函數(shù)。
• MySQL 9.1支持對(duì)從JSON數(shù)據(jù)派生的生成列進(jìn)行函數(shù)索引，從而能夠高效地查詢特定的JSON元素。
• 雖然MySQL支持JSON，但它仍然是一個(gè)關(guān)係型數(shù)據(jù)庫(kù)。過(guò)度使用JSON可能會(huì)抵消SQL的優(yōu)勢(shì)。

在MySQL JSON列中存儲(chǔ)JSON文檔並不意味著你應(yīng)該這樣做

規(guī)範(fàn)化是一種用於優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)的技術(shù)。第一範(fàn)式(1NF)規(guī)則規(guī)定每一列都應(yīng)該保存單個(gè)值——而存儲(chǔ)多值JSON文檔顯然違反了這一規(guī)則。

如果你的數(shù)據(jù)有明確的關(guān)係型數(shù)據(jù)需求，請(qǐng)使用合適的單值字段。 JSON應(yīng)該作為最後手段謹(jǐn)慎使用。 JSON值字段不能直接索引，因此避免在經(jīng)常更新或搜索的列上使用它們。

對(duì)從JSON數(shù)據(jù)派生的生成列進(jìn)行函數(shù)索引，允許你索引JSON對(duì)象的某些部分，從而提高查詢性能。

也就是說(shuō)，對(duì)於稀疏數(shù)據(jù)或自定義屬性，JSON有一些很好的用例。

創(chuàng)建帶有JSON數(shù)據(jù)類型列的表

考慮一家銷售書(shū)籍的商店。所有書(shū)籍都有ID、ISBN、標(biāo)題、出版商、頁(yè)數(shù)和其他明確的關(guān)係型數(shù)據(jù)。

現(xiàn)在，如果你想為每本書(shū)添加任意數(shù)量的類別標(biāo)籤。你可以在SQL中使用以下方法實(shí)現(xiàn)：

1. 一個(gè)標(biāo)籤表，存儲(chǔ)每個(gè)標(biāo)籤名稱及其唯一的ID；
2. 一個(gè)標(biāo)籤映射表，包含許多到許多的記錄，將書(shū)籍ID映射到標(biāo)籤ID。

這種方法有效，但對(duì)於一個(gè)次要功能來(lái)說(shuō)，它過(guò)於繁瑣，需要付出相當(dāng)大的努力。因此，你可以在MySQL數(shù)據(jù)庫(kù)的book表中為標(biāo)籤定義一個(gè)MySQL JSON字段：

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

MySQL JSON列不能有默認(rèn)值，不能用作主鍵，不能用作外鍵，也不能有直接索引。

但是，使用MySQL 9.1，你可以對(duì)從JSON數(shù)據(jù)派生的生成列創(chuàng)建函數(shù)索引，這使得能夠索引JSON文檔中的特定元素。這些生成的列可以是虛擬的或存儲(chǔ)的，並作為輔助索引進(jìn)行索引。

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

添加JSON數(shù)據(jù)

可以在INSERT或UPDATE語(yǔ)句中傳入整個(gè)JSON文檔，從而可以輕鬆地將JSON移動(dòng)到MySQL進(jìn)行存儲(chǔ)和操作。

例如，我們的書(shū)籍標(biāo)籤可以作為數(shù)組（在字符串內(nèi)）傳遞：

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

也可以使用以下函數(shù)創(chuàng)建JSON：

• JSON_ARRAY() 函數(shù)，用於創(chuàng)建數(shù)組。例如：-- 返回[1, 2, "abc"]：SELECT JSON_ARRAY(1, 2, 'abc');
• JSON_OBJECT() 函數(shù)，用於創(chuàng)建對(duì)象。例如：-- 返回{"a": 1, "b": 2}：SELECT JSON_OBJECT('a', 1, 'b', 2);
• JSON_QUOTE() 函數(shù)，用於將字符串作為JSON值引用。例如：-- 返回"[1, 2, "abc"]"：SELECT JSON_QUOTE('[1, 2, "abc"]');
• CAST(anyValue AS JSON) 函數(shù)，用於將值轉(zhuǎn)換為JSON類型以確保有效性：SELECT CAST('{"a": 1, "b": 2}' AS JSON);

JSON_TYPE()函數(shù)允許你檢查JSON值的類型。它應(yīng)該返回OBJECT、ARRAY、標(biāo)量類型（INTEGER、BOOLEAN等）、NULL或錯(cuò)誤。例如：

-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回OBJECT：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回錯(cuò)誤：
SELECT JSON_TYPE('{"a": 1, "b": 2');

JSON_VALID()函數(shù)如果JSON有效則返回1，否則返回0：

-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

-- 返回1：
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- 返回0：
SELECT JSON_TYPE('{"a": 1, "b": 2');

嘗試插入無(wú)效的JSON文檔將引發(fā)錯(cuò)誤，並且不會(huì)插入/更新整個(gè)記錄。

在MySQL JSON列中搜索JSON文檔

使用JSON_CONTAINS()函數(shù)等MySQL JSON函數(shù)，可以檢查JSON文檔是否包含特定值。找到匹配項(xiàng)時(shí)返回1。例如：

-- 所有帶有“JavaScript”標(biāo)簽的書(shū)籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

JSON_SEARCH()函數(shù)返回JSON文檔中值的路徑。沒(méi)有匹配項(xiàng)時(shí)返回NULL。

你還可以通過(guò)傳遞“one”和“all”標(biāo)誌以及搜索字符串來(lái)指定是否需要查找所有匹配項(xiàng)或單個(gè)匹配項(xiàng)（其中%與任意數(shù)量的字符匹配，_與LIKE一樣匹配一個(gè)字符） 。例如：

-- 所有標(biāo)簽以“Java”開(kāi)頭的書(shū)籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

JSON_TABLE()函數(shù)將JSON數(shù)據(jù)轉(zhuǎn)換為關(guān)係格式，以便更容易查詢：

SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;

JSON路徑

使用JSON_EXTRACT()函數(shù)的MySQL JSON查詢可以根據(jù)指定的路徑從JSON文檔中檢索特定值。

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

所有路徑定義都以$開(kāi)頭，後跟其他選擇器：

• 一個(gè)句點(diǎn)後跟一個(gè)名稱，例如$.website
• [N]，其中N是零索引數(shù)組中的位置
• .[*]通配符評(píng)估對(duì)象的全部成員
• [*]通配符評(píng)估數(shù)組的全部成員
• prefix**suffix通配符評(píng)估所有以命名前綴開(kāi)頭並以命名後綴結(jié)尾的路徑

以下示例指的是以下JSON文檔：

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

路徑示例：

• $.a 返回 1
• $.c 返回 [3, 4]
• $.c[1] 返回 4
• $.d.e 返回 5
• $**.e 返回 [5]

你可以使用JSON提取MySQL函數(shù)有效地從你的book表中提取名稱和第一個(gè)標(biāo)籤：

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

對(duì)於更複雜的示例，假設(shè)你有一個(gè)包含JSON配置文件數(shù)據(jù)的用戶表。例如：

你可以使用JSON路徑提取Twitter名稱。例如：

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

你可以在WHERE子句中使用JSON路徑，只返回有Twitter帳戶的用戶：

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

修改JSON文檔的一部分

有幾個(gè)MySQL函數(shù)可以使用路徑表示法修改JSON文檔的一部分。這些函數(shù)包括：

• JSON_SET(doc, path, val[, path, val]…): 在文檔中插入或更新數(shù)據(jù)。 UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');
• JSON_INSERT(doc, path, val[, path, val]…): 在文檔中插入數(shù)據(jù)，而不覆蓋現(xiàn)有值。 UPDATE book SET tags = JSON_INSERT(tags, '$[0]', 'New Tag');
• JSON_REPLACE(doc, path, val[, path, val]…): 替換文檔中的數(shù)據(jù)。 UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');
• JSON_MERGE_PATCH(doc, doc[, doc]…): 合併兩個(gè)或多個(gè)JSON文檔，用後續(xù)文檔的值替換現(xiàn)有鍵。 UPDATE book SET tags = JSON_MERGE_PATCH(tags, '["technical"]') WHERE JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;
• JSON_ARRAY_APPEND(doc, path, val[, path, val]…): 將值追加到數(shù)組的末尾。 UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_INSERT(doc, path, val[, path, val]…): 在JSON數(shù)組中的特定位置插入值。 UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');
• JSON_REMOVE(doc, path[, path]…): 從文檔中刪除數(shù)據(jù)。 UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');
• JSON_PRETTY(val): 美化JSON文檔，使其更易於閱讀。 SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ["MySQL", "JSON"]}');

例如，如果你想為任何已經(jīng)具有“JavaScript”標(biāo)籤的書(shū)籍添加“technical”標(biāo)籤，可以使用JSON_MERGE_PATCH()函數(shù)：

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

更多信息

MySQL文檔提供了關(guān)於MySQL JSON數(shù)據(jù)類型和相關(guān)JSON函數(shù)的詳細(xì)信息。

再次提醒，除非絕對(duì)必要，否則不要使用JSON。你可以在MySQL中模擬整個(gè)面向文檔的NoSQL數(shù)據(jù)庫(kù)，但這會(huì)抵消SQL的許多好處，你不如直接切換到真正的NoSQL系統(tǒng)！

也就是說(shuō)，對(duì)於SQL應(yīng)用程序中更模糊的數(shù)據(jù)需求，JSON數(shù)據(jù)類型可能會(huì)節(jié)省一些工作量。

關(guān)於在MySQL中使用JSON數(shù)據(jù)的常見(jiàn)問(wèn)題

你可以在MySQL中使用JSON嗎？

MySQL通過(guò)提供JSON數(shù)據(jù)類型來(lái)支持JSON，該類型用於在列中存儲(chǔ)JSON格式的數(shù)據(jù)。從MySQL 5.7.8開(kāi)始，你可以創(chuàng)建帶有JSON列的表，允許你使用SQL插入、更新和查詢JSON數(shù)據(jù)。 MySQL提供了一系列JSON函數(shù)來(lái)處理這些列中的JSON數(shù)據(jù)，從而可以提取、修改和操作JSON數(shù)據(jù)。

此外，你可以在SQL查詢中使用JSON數(shù)據(jù)，在需要時(shí)使用JSON_TABLE等函數(shù)將其轉(zhuǎn)換為關(guān)係數(shù)據(jù)。但是，重要的是要理解，MySQL從根本上來(lái)說(shuō)是一個(gè)關(guān)係型數(shù)據(jù)庫(kù)，其JSON數(shù)據(jù)類型支持旨在促進(jìn)在關(guān)係上下文中處理JSON數(shù)據(jù)，而不是成為一個(gè)成熟的NoSQL JSON數(shù)據(jù)庫(kù)。

如上文所述，僅僅因?yàn)槟憧梢源鎯?chǔ)JSON，並不意味著你應(yīng)該這樣做：規(guī)範(fàn)化是一種用於優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)的技術(shù)。第一範(fàn)式(1NF)規(guī)則規(guī)定每一列都應(yīng)該保存單個(gè)值——而存儲(chǔ)多值JSON文檔違反了這一規(guī)則。

在MySQL中存儲(chǔ)JSON是否可以？

在以下情況下，在MySQL中存儲(chǔ)JSON是可以的：

• 半結(jié)構(gòu)化或動(dòng)態(tài)數(shù)據(jù)，不適合嚴(yán)格的模式。
• 自定義屬性，其中關(guān)係設(shè)計(jì)效率低下。
• 與JSON API集成，用於存儲(chǔ)有效載荷或日誌。

但是，JSON不應(yīng)替代結(jié)構(gòu)化和經(jīng)常查詢數(shù)據(jù)的規(guī)範(fàn)化關(guān)係存儲(chǔ)。雖然MySQL 9.1通過(guò)函數(shù)索引和JSON_TABLE等功能改進(jìn)了JSON功能，但對(duì)於大型數(shù)據(jù)集或複雜查詢，JSON操作仍然可能會(huì)帶來(lái)開(kāi)銷。

如何在MySQL查詢中使用JSON？

你可以通過(guò)使用MySQL的JSON函數(shù)在MySQL查詢中使用JSON。這些函數(shù)使你能夠提取、操作和查詢存儲(chǔ)在JSON列或數(shù)據(jù)庫(kù)中JSON格式字符串中的JSON數(shù)據(jù)。要訪問(wèn)JSON列中的JSON數(shù)據(jù)，請(qǐng)使用->運(yùn)算符，後跟所需JSON元素的路徑。

JSON_EXTRACT、JSON_SET和JSON_OBJECTAGG等JSON函數(shù)允許你過(guò)濾、修改、聚合和處理JSON數(shù)據(jù)。你還可以使用WHERE子句根據(jù)JSON值過(guò)濾行。 MySQL的JSON功能提供了一種靈活的方式來(lái)直接在數(shù)據(jù)庫(kù)查詢中交互和操作JSON對(duì)象。

何時(shí)在MySQL中使用JSON？

你應(yīng)該在以下情況下在MySQL中使用JSON：

1. 半結(jié)構(gòu)化數(shù)據(jù)：處理不可預(yù)測(cè)或稀疏字段（例如自定義屬性）時(shí)使用JSON。
2. 動(dòng)態(tài)模式：當(dāng)數(shù)據(jù)需求頻繁變化時(shí)，JSON提供靈活性。
3. 層次結(jié)構(gòu)或嵌套數(shù)據(jù)：JSON支持具有父子關(guān)係或數(shù)組的數(shù)據(jù)。
4. API集成：將有效載荷、響應(yīng)或日誌存儲(chǔ)為JSON文檔。

但是，避免在以下情況下使用JSON：

• 需要索引的經(jīng)常查詢的字段（函數(shù)索引可以有所幫助，但關(guān)係設(shè)計(jì)通常更快）。
• 需要規(guī)範(fàn)化的嚴(yán)格關(guān)係數(shù)據(jù)。
• 在JSON路徑上的複雜查詢會(huì)降低性能的情況。

如何在MySQL中存儲(chǔ)JSON數(shù)據(jù)？

要在MySQL中存儲(chǔ)JSON數(shù)據(jù)，你有兩個(gè)主要選項(xiàng)。首先，你可以使用MySQL中引入的JSON數(shù)據(jù)類型來(lái)創(chuàng)建一個(gè)帶有JSON列的表。此方法為JSON數(shù)據(jù)提供結(jié)構(gòu)化存儲(chǔ)和更好的查詢性能。

或者，你可以在常規(guī)VARCHAR或TEXT列中將JSON數(shù)據(jù)存儲(chǔ)為文本。當(dāng)主要需要存儲(chǔ)和檢索JSON數(shù)據(jù)而無(wú)需複雜的數(shù)據(jù)庫(kù)操作時(shí)，此方法適用。

如何在MySQL中索引JSON數(shù)據(jù)？

雖然你不能直接索引JSON列，但MySQL允許你對(duì)從JSON值派生的生成列創(chuàng)建函數(shù)索引。

例如，要索引JSON數(shù)組的第一個(gè)元素：

CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

此方法提高了對(duì)經(jīng)常訪問(wèn)的JSON路徑的查詢性能。

對(duì)於JSON數(shù)據(jù)，你應(yīng)該使用MySQL還是NoSQL數(shù)據(jù)庫(kù)？

這取決於你的項(xiàng)目需求：

• 如果你需要關(guān)係存儲(chǔ)，偶爾處理半結(jié)構(gòu)化數(shù)據(jù)、自定義屬性或關(guān)係模型中的層次結(jié)構(gòu)數(shù)據(jù)，請(qǐng)選擇MySQL。
• 如果你的項(xiàng)目涉及大量的JSON存儲(chǔ)、靈活的模式和以文檔為基礎(chǔ)的操作作為主要用例，請(qǐng)選擇NoSQL數(shù)據(jù)庫(kù)（如MongoDB）。

MySQL的JSON支持非常適合混合工作負(fù)載，但不能完全替代專門(mén)用於文檔存儲(chǔ)的NoSQL數(shù)據(jù)庫(kù)。

如何從MySQL JSON字段中提取特定值？

要從MySQL JSON字段中提取特定值，請(qǐng)使用JSON_EXTRACT()函數(shù)或簡(jiǎn)寫(xiě)->運(yùn)算符。

ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);

如何查詢和過(guò)濾MySQL JSON字段中的數(shù)據(jù)？

要查詢和過(guò)濾存儲(chǔ)在MySQL JSON字段中的數(shù)據(jù)，可以使用JSON_CONTAINS()和JSON_SEARCH()等函數(shù)。你還可以使用JSON_EXTRACT()檢索特定值以進(jìn)行進(jìn)一步過(guò)濾。

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

以上是如何在MySQL數(shù)據(jù)庫(kù)中使用JSON數(shù)據(jù)字段的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！