深入探索 MySQL 8 中的 JSON 类型：功能与应用

码到三十五 ： 个人主页

心中有诗画，指尖舞代码，目光览世界，步履越千山，人间尽值得 !

---

---

随着 NoSQL 数据库的兴起，JSON 作为一种轻量级的数据交换格式受到了广泛的关注。为了满足现代应用程序的需求，MySQL 8引入了原生的 JSON 数据类型，提供了一系列强大的 JSON 函数来处理和查询 JSON 数据。本文将深入探讨 MySQL 8 中JSON 类型的特性、函数、索引以及实际应用场景。

目录

• • 1. 引言
  • 2. JSON 数据类型特性
  • 3. JSON 函数
  • • 3.1 JSON_EXTRACT()
    • 3.2 JSON_INSERT()
    • 3.3 JSON_REPLACE()
    • 3.4 JSON_REMOVE()
    • 3.5 JSON_ARRAY() 和 JSON_OBJECT()
    • 3.6 JSON_KEYS()
    • 3.7 JSON_VALID()
    • 3.8 JSON_QUOTE() 和 JSON_UNQUOTE()
    • 3.9 JSON_CONTAINS()
    • 3.9 JSON_CONTAINS_PATH()
    • 3.10 JSON_ARRAY_APPEND()
    • 3.11 JSON_ARRAY_INSERT()
    • 4. JSON 索引
    • • 4.1 添加虚拟列
      • 4.2 为虚拟列创建索引
      • 4.3 查询优化
      • 5. 实际应用场景
      • 6. 注意事项
      • 7. 结语

        1. 引言

        在早期的 MySQL 版本中，开发者通常将 JSON 数据以字符串的形式存储在数据库中，这导致了查询效率低下和数据处理复杂。为了解决这个问题，MySQL 8 引入了原生的 JSON 数据类型，允许我们以结构化的方式存储和查询 JSON 数据。

        2. JSON 数据类型特性

        • 验证：当插入或更新 JSON 列时，MySQL 会自动验证数据的 JSON 格式，确保数据的完整性。
        • 优化存储：JSON 数据类型以二进制格式存储，相比纯文本存储更加高效。
        • 灵活性：JSON 列可以存储数组、对象、嵌套结构等，为数据的表示提供了极大的灵活性。

          3. JSON 函数

          MySQL 8 提供了一系列内置函数来操作和查询 JSON 数据：

          • 提取数据：使用 JSON_EXTRACT() 函数可以从 JSON 文档中提取指定的数据片段。
          • 修改数据：JSON_INSERT()、JSON_REPLACE() 和 JSON_REMOVE() 函数允许你向 JSON 文档中添加、替换或删除数据。
          • 创建 JSON：JSON_ARRAY() 和 JSON_OBJECT() 函数用于创建 JSON 数组和对象。
          • 查询功能：JSON_LENGTH()、JSON_KEYS()、JSON_VALID() 等函数，用于获取 JSON 数据的长度、键或验证 JSON 数据的有效性。
          • 其他函数，如 JSON_QUOTE(), JSON_UNQUOTE(), JSON_CONTAINS(), JSON_CONTAINS_PATH(), JSON_ARRAY_APPEND(), JSON_ARRAY_INSERT() 等等。

            下面的例子看看每个函数的具体使用方法：

            首先，我们创建一个名为 json_example 的表，并插入一条 JSON 数据：

            CREATE TABLE json_example (  
              id INT AUTO_INCREMENT PRIMARY KEY,  
              data JSON  
            );  
              
            INSERT INTO json_example (data) VALUES (  
              '{  
                "name": "John Doe",  
                "age": 30,  
                "address": {  
                  "street": "123 Main St",  
                  "city": "Anytown",  
                  "zip": "12345"  
                },  
                "phoneNumbers": ["123-456-7890", "987-654-3210"],  
                "isActive": true  
              }'  
            );
            

            表 json_example 中有一条包含 JSON 数据的记录。接下来，我们将使用不同的 JSON 函数来查询和修改这个数据.

            3.1 JSON_EXTRACT()

            • 提取 JSON 数据中的特定部分

              -- 提取 name 字段的值  
              SELECT JSON_EXTRACT(data, '$.name') AS extracted_name FROM json_example;  
              -- 结果: "John Doe"  
                
              -- 提取 address 对象的 city 字段的值  
              SELECT JSON_EXTRACT(data, '$.address.city') AS city FROM json_example;  
              -- 结果: "Anytown"
              

              3.2 JSON_INSERT()

              • 向 JSON 数据中插入新的部分，如果路径已存在则不会替换。

                -- 在 phoneNumbers 数组后面插入一个新的电话号码  
                UPDATE json_example  
                SET data = JSON_INSERT(data, '$.phoneNumbers[2]', '555-123-4567');  
                -- 此时 phoneNumbers 变为 ["123-456-7890", "987-654-3210", "555-123-4567"]
                

                3.3 JSON_REPLACE()

                • 替换 JSON 数据中的部分，如果路径不存在则不会添加。

                  -- 替换 name 字段的值  
                  UPDATE json_example  
                  SET data = JSON_REPLACE(data, '$.name', 'Jane Smith');  
                  -- 此时 name 变为 "Jane Smith"
                  

                  3.4 JSON_REMOVE()

                  • 从 JSON 数据中移除指定的部分。

                    -- 移除 phoneNumbers 数组中的第一个电话号码  
                    UPDATE json_example  
                    SET data = JSON_REMOVE(data, '$.phoneNumbers[0]');  
                    -- 此时 phoneNumbers 变为 ["987-654-3210", "555-123-4567"]
                    

                    3.5 JSON_ARRAY() 和 JSON_OBJECT()

                    • 创建 JSON 数组和对象

                      -- 创建一个新的 JSON 数组  
                      SELECT JSON_ARRAY('a', 1, TRUE);  
                      -- 结果: ["a", 1, true]  
                        
                      -- 创建一个新的 JSON 对象  
                      SELECT JSON_OBJECT('key1', 'value1', 'key2', 2);  
                      -- 结果: {"key1": "value1", "key2": 2}
                      JSON_LENGTH() - 获取 JSON 文档或数组的长度。
                      sql
                      -- 获取 phoneNumbers 数组的长度  
                      SELECT JSON_LENGTH(data->'$.phoneNumbers') AS phone_numbers_length FROM json_example;  
                      -- 结果: 2 (因为 phoneNumbers 数组现在有两个元素)
                      

                      3.6 JSON_KEYS()

                      • 获取 JSON 对象的所有键

                        -- 获取 JSON 对象的所有键  
                        SELECT JSON_KEYS(data) AS object_keys FROM json_example;  
                        -- 结果: ["name", "age", "address", "phoneNumbers", "isActive"]
                        

                        3.7 JSON_VALID()

                        • 验证 JSON 数据的有效性。

                          -- 验证 data 列是否包含有效的 JSON  
                          SELECT JSON_VALID(data) AS is_valid_json FROM json_example;  
                          -- 结果: 1 (表示 true，因为 data 列包含有效的 JSON)
                          

                          3.8 JSON_QUOTE() 和 JSON_UNQUOTE()

                          • 将字符串转换为 JSON 格式的字符串，以及反向操作。

                            假设json_example 表中存在这样一条数据

                            INSERT INTO json_example (data) VALUES (  
                              '{  
                                "name": "John",  
                                "interests": ["reading", "music"],  
                                "friends": [  
                                  {"name": "Alice", "age": 28},  
                                  {"name": "Bob", "age": 32}  
                                ]  
                              }'  
                            );
                            

                            现在我们将使用上述函数对这条数据进行操作：

                            -- 使用 JSON_QUOTE 将普通字符串转换为 JSON 字符串  
                            SELECT JSON_QUOTE('Hello, World!') AS quoted_string;  
                            -- 结果: ""Hello, World!""  
                              
                            -- 使用 JSON_UNQUOTE 将 JSON 字符串转换回普通字符串  
                            SELECT JSON_UNQUOTE('"Hello, World!"') AS unquoted_string;  
                            -- 结果: Hello, World!
                            

                            请注意，在实际的数据列上使用这些函数时，你通常会对已存储的 JSON 值或要插入的值进行操作。

                            3.9 JSON_CONTAINS()

                            • 检查 JSON 文档是否包含指定的值。

                              -- 检查 interests 数组是否包含 "reading"  
                              SELECT JSON_CONTAINS(data->'$.interests', '"reading"') AS contains_reading FROM json_example;  
                              -- 结果: 1 (表示 true，因为 interests 数组包含 "reading")
                              

                              注意，因为 JSON 中的字符串是被双引号包围的，所以我们在查询时也需要对搜索的字符串值加上双引号。

                              3.9 JSON_CONTAINS_PATH()

                              • 检查 JSON 文档是否包含指定的路径。

                                -- 检查是否存在 friends 数组中的对象的 name 路径  
                                SELECT JSON_CONTAINS_PATH(data, 'one', '$.friends[*].name') AS contains_path FROM json_example;  
                                -- 结果: 1 (表示 true，因为存在该路径)
                                

                                3.10 JSON_ARRAY_APPEND()

                                • 向 JSON 数组追加元素。

                                  -- 向 interests 数组追加 "traveling"  
                                  UPDATE json_example  
                                  SET data = JSON_SET(data, '$.interests[2]', 'traveling');  
                                  -- 注意：这里使用了 JSON_SET，因为 JSON_ARRAY_APPEND 需要指定路径到具体数组  
                                  -- 在 MySQL 8.0.17 及更高版本中，可以使用 JSON_ARRAY_APPEND 正确地追加元素  
                                  -- 例如: JSON_ARRAY_APPEND(data, '$.interests', 'traveling')
                                  

                                  注意：上面的例子中使用了 JSON_SET 而不是 JSON_ARRAY_APPEND，因为在 MySQL 8.0.17 之前，JSON_ARRAY_APPEND 的语法有些不同，它要求指定路径到一个具体的数组元素。从 8.0.17 开始，JSON_ARRAY_APPEND 可以正确地追加到数组末尾。

                                  正确的 JSON_ARRAY_APPEND 用法如下：

                                  -- 向 interests 数组追加 "traveling"（适用于 MySQL 8.0.17 及更高版本）  
                                  UPDATE json_example  
                                  SET data = JSON_ARRAY_APPEND(data, '$.interests', 'traveling');
                                  

                                  3.11 JSON_ARRAY_INSERT()

                                  • 在 JSON 数组的指定位置插入元素。

                                    -- 在 interests 数组的第一个位置插入 "gaming"  
                                    UPDATE json_example  
                                    SET data = JSON_ARRAY_INSERT(data, '$.interests[0]', 'gaming');  
                                    -- 结果: interests 数组现在是 ["gaming", "reading", "music", "traveling"]
                                    

                                    4. JSON 索引

                                    为了提高查询性能，MySQL 8 支持为 JSON 列创建索引。但由于 JSON 数据的灵活性，直接对整个 JSON 文档创建索引并不高效。因此，MySQL 引入了虚拟列（Virtual Columns）的概念。

                                    • 虚拟列：虚拟列允许你根据 JSON 列中的值生成一个新的列，并为这个新列创建索引。这样，当你根据 JSON 数据中的某个字段进行查询时，MySQL 可以使用索引来加速查询。（关于虚拟列我将在之后的文章详解）
                                    • 创建索引：通过结合使用 JSON_EXTRACT() 函数和虚拟列，你可以轻松地为 JSON 数据中的特定字段创建索引。

                                      基于上面的json_example 表，我们来看下为json字段创建索引

                                      4.1 添加虚拟列

                                      我们将添加一个名为 first_interest 的虚拟列，该列将存储 interests 数组的第一个元素。

                                      ALTER TABLE json_example  
                                      ADD first_interest VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.interests[0]'))) VIRTUAL;
                                      

                                      在这里，我们使用了 JSON_EXTRACT() 来获取 interests 数组的第一个元素，并用 JSON_UNQUOTE() 去除引号，因为 JSON_EXTRACT() 返回的是 JSON 格式的字符串。

                                      4.2 为虚拟列创建索引

                                      CREATE INDEX idx_first_interest ON json_example(first_interest);
                                      

                                      现在，我们为 first_interest 列创建了一个索引，这将加速基于该列的查询。

                                      4.3 查询优化

                                      现在，我们可以基于 first_interest 列进行查询，并利用索引来加速查询过程。

                                      SELECT * FROM json_example WHERE first_interest = 'reading';
                                      

                                      由于我们为 first_interest 创建了索引，这个查询将会更加高效。但是，请注意，这种方法仅适用于查询 interests 数组的第一个元素。如果你需要查询数组中的其他元素，你可能需要采用其他策略，比如使用全文搜索、倒排索引或者将 JSON 数据规范化到关系型结构中。

                                      5. 实际应用场景

                                      • 配置文件存储：应用程序的配置信息通常以 JSON 格式表示。使用 MySQL 8 的 JSON 数据类型，你可以轻松地将这些配置信息存储在数据库中，并使用 JSON 函数进行查询和修改。
                                      • 日志记录：日志条目通常以结构化的格式存储，JSON 是一个理想的选择。通过将日志数据存储在 JSON 列中，你可以轻松地分析和查询日志数据。
                                      • 与前端集成：使用 JSON 与后端进行数据交换。使用 MySQL 8 的 JSON 支持，你可以简化数据库与前端之间的数据交互。

                                        6. 注意事项

                                        • 性能：虽然 MySQL 8 提供了对 JSON 的支持，但与传统的关系型数据相比，JSON 查询可能仍然不够高效。
                                        • 数据验证：虽然 MySQL 会验证 JSON 数据的格式，但它不会验证数据的业务规则或完整性。
                                        • 复杂性：JSON 数据的结构可能比传统的关系型数据更复杂，这可能会增加查询和维护的难度。

                                          7. 结语

                                          MySQL 8 的 JSON 数据类型为存储和查询 JSON 数据提供了强大的支持。通过内置的 JSON 函数和虚拟列索引，开发者可以高效地处理 JSON 数据，满足现代应用程序的需求。如果你正在开发需要存储和查询 JSON 数据的应用程序，不妨考虑使用 MySQL 8 的 JSON 功能来简化你的工作。

                                          ---

                                          ---

                                          听说...关注下面公众号的人都变牛了，纯技术，纯干货 !