如何在MySQL數據庫中使用JSON數據字段

本文探討了MySQL 9.1中JSON實現的細節，延續了之前文章《SQL vs NoSQL: The Differences》中關於SQL和NoSQL數據庫界限模糊的論點，兩者都在互相借鑒特性。 MySQL 5.7 InnoDB數據庫和PostgreSQL 9.2 都直接支持在單個字段中存儲JSON文檔類型。

需要注意的是，任何數據庫都能將JSON文檔作為單個字符串blob存儲。然而，MySQL和PostgreSQL支持將驗證後的JSON數據存儲為真正的鍵值對，而不是簡單的字符串。

關鍵要點

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

在MySQL JSON列中存儲JSON文檔並不意味著你應該這樣做

規範化是一種用於優化數據庫結構的技術。第一範式(1NF)規則規定每一列都應該保存單個值——而存儲多值JSON文檔顯然違反了這一規則。

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

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

也就是說，對於稀疏數據或自定義屬性，JSON有一些很好的用例。

創建帶有JSON數據類型列的表

考慮一家銷售書籍的商店。所有書籍都有ID、ISBN、標題、出版商、頁數和其他明確的關係型數據。

現在，如果你想為每本書添加任意數量的類別標籤。你可以在SQL中使用以下方法實現：

1. 一個標籤表，存儲每個標籤名稱及其唯一的ID；
2. 一個標籤映射表，包含許多到許多的記錄，將書籍ID映射到標籤ID。

這種方法有效，但對於一個次要功能來說，它過於繁瑣，需要付出相當大的努力。因此，你可以在MySQL數據庫的book表中為標籤定義一個MySQL JSON字段：

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

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

但是，使用MySQL 9.1，你可以對從JSON數據派生的生成列創建函數索引，這使得能夠索引JSON文檔中的特定元素。這些生成的列可以是虛擬的或存儲的，並作為輔助索引進行索引。

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

添加JSON數據

可以在INSERT或UPDATE語句中傳入整個JSON文檔，從而可以輕鬆地將JSON移動到MySQL進行存儲和操作。

例如，我們的書籍標籤可以作為數組（在字符串內）傳遞：

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

也可以使用以下函數創建JSON：

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

JSON_TYPE()函數允許你檢查JSON值的類型。它應該返回OBJECT、ARRAY、標量類型（INTEGER、BOOLEAN等）、NULL或錯誤。例如：

<code class="language-sql">-- 返回ARRAY：
SELECT JSON_TYPE('[1, 2, "abc"]');

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

-- 返回错误：
SELECT JSON_TYPE('{"a": 1, "b": 2');</code>

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

<code class="language-sql">-- 返回1：
SELECT JSON_TYPE('[1, 2, "abc"]');

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

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

嘗試插入無效的JSON文檔將引發錯誤，並且不會插入/更新整個記錄。

在MySQL JSON列中搜索JSON文檔

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

<code class="language-sql">-- 所有带有“JavaScript”标签的书籍：
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');</code>

JSON_SEARCH()函數返回JSON文檔中值的路徑。沒有匹配項時返回NULL。

你還可以通過傳遞“one”和“all”標誌以及搜索字符串來指定是否需要查找所有匹配項或單個匹配項（其中%與任意數量的字符匹配，_與LIKE一樣匹配一個字符） 。例如：

<code class="language-sql">-- 所有标签以“Java”开头的书籍：
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;</code>

JSON_TABLE()函數將JSON數據轉換為關係格式，以便更容易查詢：

<code class="language-sql">SELECT * 
FROM JSON_TABLE(
    '[{"tag": "SQL"}, {"tag": "JSON"}]', 
    '$[*]' COLUMNS (tag VARCHAR(50) PATH '$.tag')
) AS tags_table;</code>

JSON路徑

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

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

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

• 一個句點後跟一個名稱，例如$.website
• [N]，其中N是零索引數組中的位置
• .[*]通配符評估對象的全部成員
• [*]通配符評估數組的全部成員
• prefix**suffix通配符評估所有以命名前綴開頭並以命名後綴結尾的路徑

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

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

路徑示例：

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

你可以使用JSON提取MySQL函數有效地從你的book表中提取名稱和第一個標籤：

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

對於更複雜的示例，假設你有一個包含JSON配置文件數據的用戶表。例如：

id	name	profile
1	Craig	{ "email": ["craig@email1.com", "craig@email2.com"], "twitter": "@craigbuckler" }
2	SitePoint	{ "email": [], "twitter": "@sitepointdotcom" }

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

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

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

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

修改JSON文檔的一部分

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

• JSON_SET(doc, path, val[, path, val]…): 在文檔中插入或更新數據。 UPDATE book SET tags = JSON_SET(tags, '$[0]', 'Updated Tag');
• JSON_INSERT(doc, path, val[, path, val]…): 在文檔中插入數據，而不覆蓋現有值。 UPDATE book SET tags = JSON_INSERT(tags, '$[0]', 'New Tag');
• JSON_REPLACE(doc, path, val[, path, val]…): 替換文檔中的數據。 UPDATE book SET tags = JSON_REPLACE(tags, '$[0]', 'Replaced Tag');
• JSON_MERGE_PATCH(doc, doc[, doc]…): 合併兩個或多個JSON文檔，用後續文檔的值替換現有鍵。 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]…): 將值追加到數組的末尾。 UPDATE book SET tags = JSON_ARRAY_APPEND(tags, '$', 'New Tag');
• JSON_ARRAY_INSERT(doc, path, val[, path, val]…): 在JSON數組中的特定位置插入值。 UPDATE book SET tags = JSON_ARRAY_INSERT(tags, '$[0]', 'Inserted Tag');
• JSON_REMOVE(doc, path[, path]…): 從文檔中刪除數據。 UPDATE book SET tags = JSON_REMOVE(tags, '$[1]');
• JSON_PRETTY(val): 美化JSON文檔，使其更易於閱讀。 SELECT JSON_PRETTY('{"name": "SitePoint", "tags": ["MySQL", "JSON"]}');

例如，如果你想為任何已經具有“JavaScript”標籤的書籍添加“technical”標籤，可以使用JSON_MERGE_PATCH()函數：

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

更多信息

MySQL文檔提供了關於MySQL JSON數據類型和相關JSON函數的詳細信息。

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

也就是說，對於SQL應用程序中更模糊的數據需求，JSON數據類型可能會節省一些工作量。

關於在MySQL中使用JSON數據的常見問題

你可以在MySQL中使用JSON嗎？

MySQL通過提供JSON數據類型來支持JSON，該類型用於在列中存儲JSON格式的數據。從MySQL 5.7.8開始，你可以創建帶有JSON列的表，允許你使用SQL插入、更新和查詢JSON數據。 MySQL提供了一系列JSON函數來處理這些列中的JSON數據，從而可以提取、修改和操作JSON數據。

此外，你可以在SQL查詢中使用JSON數據，在需要時使用JSON_TABLE等函數將其轉換為關係數據。但是，重要的是要理解，MySQL從根本上來說是一個關係型數據庫，其JSON數據類型支持旨在促進在關係上下文中處理JSON數據，而不是成為一個成熟的NoSQL JSON數據庫。

如上文所述，僅僅因為你可以存儲JSON，並不意味著你應該這樣做：規範化是一種用於優化數據庫結構的技術。第一範式(1NF)規則規定每一列都應該保存單個值——而存儲多值JSON文檔違反了這一規則。

在MySQL中存儲JSON是否可以？

在以下情況下，在MySQL中存儲JSON是可以的：

• 半結構化或動態數據，不適合嚴格的模式。
• 自定義屬性，其中關係設計效率低下。
• 與JSON API集成，用於存儲有效載荷或日誌。

但是，JSON不應替代結構化和經常查詢數據的規範化關係存儲。雖然MySQL 9.1通過函數索引和JSON_TABLE等功能改進了JSON功能，但對於大型數據集或複雜查詢，JSON操作仍然可能會帶來開銷。

如何在MySQL查詢中使用JSON？

你可以通過使用MySQL的JSON函數在MySQL查詢中使用JSON。這些函數使你能夠提取、操作和查詢存儲在JSON列或數據庫中JSON格式字符串中的JSON數據。要訪問JSON列中的JSON數據，請使用->運算符，後跟所需JSON元素的路徑。

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

何時在MySQL中使用JSON？

你應該在以下情況下在MySQL中使用JSON：

1. 半結構化數據：處理不可預測或稀疏字段（例如自定義屬性）時使用JSON。
2. 動態模式：當數據需求頻繁變化時，JSON提供靈活性。
3. 層次結構或嵌套數據：JSON支持具有父子關係或數組的數據。
4. API集成：將有效載荷、響應或日誌存儲為JSON文檔。

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

• 需要索引的經常查詢的字段（函數索引可以有所幫助，但關係設計通常更快）。
• 需要規範化的嚴格關係數據。
• 在JSON路徑上的複雜查詢會降低性能的情況。

如何在MySQL中存儲JSON數據？

要在MySQL中存儲JSON數據，你有兩個主要選項。首先，你可以使用MySQL中引入的JSON數據類型來創建一個帶有JSON列的表。此方法為JSON數據提供結構化存儲和更好的查詢性能。

或者，你可以在常規VARCHAR或TEXT列中將JSON數據存儲為文本。當主要需要存儲和檢索JSON數據而無需複雜的數據庫操作時，此方法適用。

如何在MySQL中索引JSON數據？

雖然你不能直接索引JSON列，但MySQL允許你對從JSON值派生的生成列創建函數索引。

例如，要索引JSON數組的第一個元素：

<code class="language-sql">CREATE TABLE `book` (
  `id` MEDIUMINT() UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(200) NOT NULL,
  `tags` JSON DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;</code>

此方法提高了對經常訪問的JSON路徑的查詢性能。

對於JSON數據，你應該使用MySQL還是NoSQL數據庫？

這取決於你的項目需求：

• 如果你需要關係存儲，偶爾處理半結構化數據、自定義屬性或關係模型中的層次結構數據，請選擇MySQL。
• 如果你的項目涉及大量的JSON存儲、靈活的模式和以文檔為基礎的操作作為主要用例，請選擇NoSQL數據庫（如MongoDB）。

MySQL的JSON支持非常適合混合工作負載，但不能完全替代專門用於文檔存儲的NoSQL數據庫。

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

要從MySQL JSON字段中提取特定值，請使用JSON_EXTRACT()函數或簡寫->運算符。

<code class="language-sql">ALTER TABLE book
ADD COLUMN first_tag VARCHAR(50) AS (JSON_UNQUOTE(tags->'$[0]')),
ADD INDEX idx_first_tag (first_tag);</code>

如何查詢和過濾MySQL JSON字段中的數據？

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

<code class="language-sql">INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);</code>

以上是如何在MySQL數據庫中使用JSON數據字段的詳細內容。更多資訊請關注PHP中文網其他相關文章！