MySQL编程进阶之：JSON数据类型的编程：如何使用`JSON_EXTRACT`、`JSON_SET`和`JSON_ARRAY`等函数。

各位靓仔靓女，欢迎来到“MySQL JSON数据类型进阶编程”特别讲座！ 今天咱们不搞虚的，直接上干货，一起把MySQL的JSON玩得飞起。

开场白：JSON，MySQL的“新欢”

想当年，数据库里存点啥都得规规矩矩的，一个字段对应一个值。但是时代变了，需求也越来越骚气。 比如，你想存个用户配置，里面有各种各样的设置，每个用户的设置还不一样，怎么办？难道要为每个设置都加一列？那数据库还不得爆炸？

这时候，JSON就成了MySQL的“新欢”。 它可以让你在一个字段里存任意格式的JSON数据，灵活性简直不要太好！ 接下来，咱们就来好好宠幸一下它。

第一部分：JSON_EXTRACT：从JSON里“挖宝”

JSON_EXTRACT函数，顾名思义，就是从JSON数据里提取你想要的部分。 就像挖宝一样，你要告诉它你想挖哪个宝藏。

• 基本语法：

  JSON_EXTRACT(json_doc, path)

  • json_doc: 包含JSON数据的字段或者JSON字符串。
  • path: 指定要提取的JSON元素的路径。 这是关键！

• Path表达式：

  Path表达式是JSON_EXTRACT的灵魂。 它告诉MySQL你要挖哪个宝藏。 咱们用几个例子来说明：

  • '$'：代表整个JSON文档。
  • '$.name'：提取JSON文档中键为"name"的值。
  • '$.address.city'：提取JSON文档中"address"对象中键为"city"的值。
  • '$.hobbies[0]'：提取JSON文档中"hobbies"数组的第一个元素（下标从0开始）。
  • '$.scores[*]'：提取JSON文档中"scores"数组的所有元素。
  • '$.scores[0 to 2]'：提取JSON文档中"scores"数组的第1到第3个元素。 MySQL 8.0.17及更高版本支持。

• 代码示例：

  假设我们有个users表，其中profile字段是JSON类型，存储了用户的个人信息。

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(255) NOT NULL,
      profile JSON
  );
  
  INSERT INTO users (username, profile) VALUES
  ('张三', '{"name": "张三", "age": 30, "address": {"city": "北京", "country": "中国"}, "hobbies": ["篮球", "唱歌"]}'),
  ('李四', '{"name": "李四", "age": 25, "address": {"city": "上海", "country": "中国"}, "hobbies": ["游泳", "跳舞"], "scores": [90, 85, 95]}');

  • 提取张三的名字：

    SELECT JSON_EXTRACT(profile, '$.name') AS name FROM users WHERE username = '张三';
    -- 输出： "张三"

  • 提取李四的年龄：

    SELECT JSON_EXTRACT(profile, '$.age') AS age FROM users WHERE username = '李四';
    -- 输出： 25

  • 提取张三的城市：

    SELECT JSON_EXTRACT(profile, '$.address.city') AS city FROM users WHERE username = '张三';
    -- 输出： "北京"

  • 提取李四的第一个爱好：

    SELECT JSON_EXTRACT(profile, '$.hobbies[0]') AS first_hobby FROM users WHERE username = '李四';
    -- 输出： "游泳"

  • 提取李四的所有成绩：

    SELECT JSON_EXTRACT(profile, '$.scores[*]') AS all_scores FROM users WHERE username = '李四';
    -- 输出： "[90, 85, 95]"

  • 提取李四的前两个成绩（MySQL 8.0.17+）：

    SELECT JSON_EXTRACT(profile, '$.scores[0 to 1]') AS top_scores FROM users WHERE username = '李四';
    -- 输出： "[90, 85]"

• 进阶用法：

  • 使用->>操作符： ->>操作符是JSON_EXTRACT的简化写法，并且它会自动将提取出来的值转换为字符串类型。

    SELECT profile->>'$.name' AS name FROM users WHERE username = '张三';
    -- 输出： 张三 (注意：没有双引号了)

  • 使用->操作符： ->操作符和JSON_EXTRACT功能类似，但不会将结果转换为字符串类型。

    SELECT profile->'$.name' AS name FROM users WHERE username = '张三';
    -- 输出： "张三" (注意：有双引号)

  • 多重提取： JSON_EXTRACT可以一次提取多个值。

    SELECT JSON_EXTRACT(profile, '$.name', '$.age') AS name_age FROM users WHERE username = '张三';
    -- 输出： ["张三", 30]

  • 处理不存在的路径： 如果Path表达式指定的路径不存在，JSON_EXTRACT会返回NULL。

    SELECT JSON_EXTRACT(profile, '$.nonexistent') AS nonexistent FROM users WHERE username = '张三';
    -- 输出： NULL

第二部分：JSON_SET：给JSON数据“添砖加瓦”

JSON_SET函数用于修改JSON文档中的值，或者添加新的键值对。 你可以把它想象成一个JSON数据的装修工，哪里不满意就改哪里。

• 基本语法：

  JSON_SET(json_doc, path, val[, path, val] ...)

  • json_doc: 要修改的JSON文档。
  • path: 指定要修改的JSON元素的路径。
  • val: 要设置的新值。

• 代码示例：

  还是用之前的users表。

  • 修改张三的年龄：

    UPDATE users
    SET profile = JSON_SET(profile, '$.age', 31)
    WHERE username = '张三';
    
    SELECT profile FROM users WHERE username = '张三';
    -- 输出： {"name": "张三", "age": 31, "address": {"city": "北京", "country": "中国"}, "hobbies": ["篮球", "唱歌"]}

  • 给李四添加一个新的爱好：

    UPDATE users
    SET profile = JSON_SET(profile, '$.hobbies[2]', '写作')
    WHERE username = '李四';
    
    SELECT profile FROM users WHERE username = '李四';
    -- 输出： {"name": "李四", "age": 25, "address": {"city": "上海", "country": "中国"}, "hobbies": ["游泳", "跳舞", "写作"], "scores": [90, 85, 95]}

  • 修改李四的城市：

    UPDATE users
    SET profile = JSON_SET(profile, '$.address.city', '广州')
    WHERE username = '李四';
    
    SELECT profile FROM users WHERE username = '李四';
    -- 输出： {"name": "李四", "age": 25, "address": {"city": "广州", "country": "中国"}, "hobbies": ["游泳", "跳舞", "写作"], "scores": [90, 85, 95]}

  • 添加一个新的顶级键值对：

    UPDATE users
    SET profile = JSON_SET(profile, '$.gender', 'male')
    WHERE username = '张三';
    
    SELECT profile FROM users WHERE username = '张三';
    -- 输出： {"name": "张三", "age": 31, "address": {"city": "北京", "country": "中国"}, "hobbies": ["篮球", "唱歌"], "gender": "male"}

• 注意事项：

  • JSON_SET会插入新的元素，如果路径已经存在，则会更新对应的值。
  • JSON_SET可以一次设置多个键值对。
  • 如果json_doc本身不是一个有效的JSON文档，JSON_SET会返回NULL。

第三部分：JSON_ARRAY：打造JSON“军团”

JSON_ARRAY函数用于创建一个JSON数组。 你可以用它来组建一个JSON数据的“军团”。

• 基本语法：

  JSON_ARRAY([val[, val] ...])

  • val: 数组中的元素。可以是任何数据类型。

• 代码示例：

  • 创建一个包含数字的JSON数组：

    SELECT JSON_ARRAY(1, 2, 3, 4, 5);
    -- 输出： [1, 2, 3, 4, 5]

  • 创建一个包含字符串的JSON数组：

    SELECT JSON_ARRAY('apple', 'banana', 'orange');
    -- 输出： ["apple", "banana", "orange"]

  • 创建一个包含混合数据类型的JSON数组：

    SELECT JSON_ARRAY(1, 'hello', true, NULL);
    -- 输出： [1, "hello", true, null]

  • 创建一个嵌套的JSON数组：

    SELECT JSON_ARRAY(1, JSON_ARRAY(2, 3), 4);
    -- 输出： [1, [2, 3], 4]

  • 将JSON_ARRAY与其他函数结合使用：

    假设我们需要创建一个JSON数组，包含所有用户的用户名。

    SELECT JSON_ARRAYAGG(username) FROM users;
    -- 输出： ["张三", "李四"]  (假设users表里只有张三和李四)

    这里用到了JSON_ARRAYAGG函数，它可以将多行数据聚合成一个JSON数组。

• 应用场景：

  • 动态生成JSON数据。
  • 将多个值组合成一个数组，方便存储和查询。
  • 作为其他JSON函数的参数。

第四部分： 实战演练：构建一个复杂JSON结构

现在，让我们来一个稍微复杂一点的例子，模拟一个电商网站的商品数据。 假设我们需要存储商品的各种信息，包括基本信息、价格、库存、属性、评价等等。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(255) NOT NULL,
    product_data JSON
);

INSERT INTO products (product_name, product_data) VALUES (
    'Example Product',
    JSON_OBJECT(
        'basic_info', JSON_OBJECT(
            'name', 'Example Product',
            'description', 'This is a sample product.',
            'category', 'Electronics'
        ),
        'price_info', JSON_OBJECT(
            'price', 99.99,
            'currency', 'USD',
            'discount', 0.1
        ),
        'inventory', JSON_OBJECT(
            'quantity', 100,
            'location', 'Warehouse A'
        ),
        'attributes', JSON_ARRAY(
            JSON_OBJECT('name', 'Color', 'value', 'Black'),
            JSON_OBJECT('name', 'Size', 'value', 'Medium')
        ),
        'reviews', JSON_ARRAY(
            JSON_OBJECT('user', 'user1', 'rating', 5, 'comment', 'Great product!'),
            JSON_OBJECT('user', 'user2', 'rating', 4, 'comment', 'Good value for money.')
        )
    )
);

这个例子中，我们使用了JSON_OBJECT函数来创建JSON对象，并嵌套了多个JSON对象和JSON数组。 现在，我们可以使用JSON_EXTRACT来查询这些数据：

-- 获取商品名称
SELECT product_data->>'$.basic_info.name' AS product_name FROM products;

-- 获取商品价格
SELECT product_data->>'$.price_info.price' AS product_price FROM products;

-- 获取第一个属性的名称
SELECT product_data->>'$.attributes[0].name' AS first_attribute_name FROM products;

-- 获取所有评价的用户
SELECT JSON_EXTRACT(product_data, '$.reviews[*].user') AS review_users FROM products;

我们还可以使用JSON_SET来修改这些数据：

-- 修改商品价格
UPDATE products
SET product_data = JSON_SET(product_data, '$.price_info.price', 109.99)
WHERE product_name = 'Example Product';

-- 添加一个新的属性
UPDATE products
SET product_data = JSON_SET(product_data, '$.attributes[2]', JSON_OBJECT('name', 'Weight', 'value', '1kg'))
WHERE product_name = 'Example Product';

第五部分： JSON函数大集合

除了JSON_EXTRACT、JSON_SET和JSON_ARRAY之外，MySQL还提供了许多其他的JSON函数，可以让你更方便地操作JSON数据。 咱们列举一些常用的：

函数名	功能描述
JSON_OBJECT	创建一个JSON对象。
JSON_ARRAYAGG	将多行数据聚合成一个JSON数组。
JSON_CONTAINS	判断一个JSON文档是否包含另一个JSON文档。
JSON_CONTAINS_PATH	判断一个JSON文档是否包含指定的路径。
JSON_DEPTH	返回JSON文档的最大深度。
JSON_LENGTH	返回JSON文档的长度（数组元素的个数或对象键值对的个数）。
JSON_KEYS	返回JSON对象的键。
JSON_MERGE_PATCH	合并两个JSON文档，后一个文档覆盖前一个文档的同名键。
JSON_REMOVE	从JSON文档中删除指定的元素。
JSON_REPLACE	替换JSON文档中指定路径的值，如果路径不存在，则不进行任何操作。
JSON_SEARCH	在JSON文档中搜索指定的字符串。
JSON_VALID	判断一个字符串是否是有效的JSON文档。
JSON_TYPE	返回JSON值的类型。

总结：JSON，让MySQL更强大

JSON数据类型为MySQL带来了极大的灵活性，让你可以在一个字段里存储复杂的数据结构。 掌握JSON_EXTRACT、JSON_SET、JSON_ARRAY等函数，你就可以轻松地操作JSON数据，让MySQL在处理复杂数据时更加得心应手。

当然，JSON也不是万能的。 在选择是否使用JSON数据类型时，你需要权衡一下灵活性和性能之间的关系。 如果你的数据结构非常固定，并且需要频繁地进行查询和排序，那么传统的表结构可能更适合你。

好了，今天的讲座就到这里。 希望大家都能把MySQL的JSON玩得溜溜的！ 咱们下回再见！