使用MySQL從JSON字符串提取數(shù)據(jù)的方法詳解

更新時間：2024年10月22日 11:39:50 作者：微笑聽雨。

在現(xiàn)代數(shù)據(jù)庫管理中,JSON 格式因其靈活性而廣泛使用,然而,當(dāng)數(shù)據(jù)存儲在 JSON 中時,我們經(jīng)常需要將其轉(zhuǎn)換為更易于處理的格式,本篇文章將通過一個具體的 SQL 查詢示例,展示如何從存儲在 MySQL 中的 JSON 字符串提取數(shù)據(jù)并重新格式化,需要的朋友可以參考下

1. 背景知識

JSON（JavaScript Object Notation）是一種輕量級的數(shù)據(jù)交換格式，易于閱讀和編寫，同時也易于機(jī)器解析和生成。MySQL 從版本 5.7 開始支持 JSON 數(shù)據(jù)類型，使得在數(shù)據(jù)庫中存儲和操作 JSON 數(shù)據(jù)成為可能。

在許多應(yīng)用中，JSON 字符串可能存儲在表的某個字段中，我們需要提取和轉(zhuǎn)換這些數(shù)據(jù)以便進(jìn)行進(jìn)一步分析或展示。

2. 示例數(shù)據(jù)

假設(shè)我們在 wf_lcdy 表中有一個字段 lct，其中存儲了如下 JSON 字符串：

{"15775d64e52c4ba3a8eef4bafc5f40e5":"875 162","75b67fab657748a9ab4bba141bfa0d36":"375 98","428299fd90814b3eaf129e8246f82b2a":"155 126"}

我們希望將其轉(zhuǎn)換為以下格式的數(shù)組：

[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]

3. SQL 查詢分析

以下是實現(xiàn)這一轉(zhuǎn)換的 SQL 查詢：

SELECT
    CONCAT('[', GROUP_CONCAT(
            CONCAT(
                    '{"id":"',
                    SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
                    '", "x":',
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
                    ', "y":',
                    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
                    '}'
            )
                ), ']') AS result
FROM (
         SELECT
             TRIM(BOTH '"' FROM kv) AS kv
         FROM (
                  SELECT
                      SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', numbers.n), ',', -1) AS kv
                  FROM wf_lcdy
                           JOIN (
                      SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                      SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
                      SELECT 9 UNION ALL SELECT 10
                  ) numbers
                  WHERE CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', '')) >= numbers.n - 1 AND ID = '0c86346993d64d98ad17892974bf8963'
              ) AS temp
     ) AS kv_pairs;

3.1 查詢結(jié)構(gòu)解析

內(nèi)層查詢：
- 去除多余字符：首先，使用 REPLACE 函數(shù)將 lct 字段中的 {、} 和 " 去掉。這樣可以簡化后續(xù)處理。
- 分割字符串：使用 SUBSTRING_INDEX 將每個鍵值對分割開。我們通過一個數(shù)字表（1到10）來實現(xiàn)。數(shù)字表的作用是幫助我們迭代處理每個鍵值對，因為我們無法預(yù)先知道 JSON 中鍵值對的數(shù)量。

SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(lct, '{', ''), '}', ''), '"', ''), ',', numbers.n), ',', -1) AS kv

這段代碼將 JSON 字符串拆分為多個鍵值對，kv 列中將包含這樣的值，例如：
- 15775d64e52c4ba3a8eef4bafc5f40e5:875 162
- 75b67fab657748a9ab4bba141bfa0d36:375 98
- 428299fd90814b3eaf129e8246f82b2a:155 126
中層查詢：
- 在此查詢中，我們會對 kv 列進(jìn)行進(jìn)一步處理。使用 TRIM(BOTH '"' FROM kv) 去掉多余的引號，以確保后續(xù)操作不會受到影響。

SELECT
    TRIM(BOTH '"' FROM kv) AS kv

外層查詢：
- 聚合和格式化：在外層查詢中，我們使用 GROUP_CONCAT 聚合所有的 kv 對，并使用 CONCAT 生成目標(biāo)格式的 JSON 字符串。
- 提取數(shù)據(jù)：使用 SUBSTRING_INDEX 提取 id、x 和 y 的值，并將它們轉(zhuǎn)換為相應(yīng)的格式。這里的關(guān)鍵在于分割字符串并提取數(shù)字。

GROUP_CONCAT(
    CONCAT(
        '{"id":"',
        SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', 1), '"', -1),
        '", "x":',
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', 1) AS UNSIGNED),
        ', "y":',
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(kv, ':', -1), ' ', -1) AS UNSIGNED),
        '}'
    )
)

- 最終結(jié)果：最終的結(jié)果將是一個字符串，格式為 JSON 數(shù)組。

4. 查詢結(jié)果

運(yùn)行上述查詢后，您將得到所需的結(jié)果格式：

[{"id":"15775d64e52c4ba3a8eef4bafc5f40e5","x":875,"y":162},{"id":"75b67fab657748a9ab4bba141bfa0d36","x":375,"y":98},{"id":"428299fd90814b3eaf129e8246f82b2a","x":155,"y":126}]

5. 性能考慮

字符長度計算：CHAR_LENGTH(lct) - CHAR_LENGTH(REPLACE(lct, ',', '')) 的計算用于確保我們只處理存在的鍵值對。此方法對性能有一定影響，特別是對于大文本。
數(shù)字表的使用：由于 JSON 的結(jié)構(gòu)可能變化，數(shù)字表的使用可以擴(kuò)展以支持更多的鍵值對。在實際應(yīng)用中，您可以根據(jù)需要增加數(shù)字的范圍。

6. 總結(jié)

通過上述 SQL 查詢，我們成功地從一個包含 JSON 字符串的字段中提取了數(shù)據(jù)并轉(zhuǎn)換成了另一種結(jié)構(gòu)化格式。這種方法展示了 MySQL 在處理 JSON 數(shù)據(jù)方面的靈活性和強(qiáng)大能力。

在實際應(yīng)用中，您可以根據(jù)具體的需求對查詢進(jìn)行適當(dāng)?shù)男薷?，以適應(yīng)不同結(jié)構(gòu)的 JSON 數(shù)據(jù)。此外，了解 SQL 中字符串處理和聚合函數(shù)的使用，對于提升數(shù)據(jù)處理的能力和效率至關(guān)重要。希望本篇文章對您在處理 JSON 數(shù)據(jù)時有所幫助！

以上就是使用MySQL從JSON字符串提取數(shù)據(jù)的方法詳解的詳細(xì)內(nèi)容，更多關(guān)于MySQL JSON提取數(shù)據(jù)的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: