如何编写MariaDB语句将JSON结构中所有items的qty更新为0？

嘿，这个需求其实用MariaDB的JSON处理能力就能轻松搞定，我分两种常见场景给你说明，看你使用的MariaDB版本：

场景1：MariaDB 10.6及以上版本（推荐） #

从10.6版本开始，MariaDB引入了JSON_TRANSFORM函数，它支持通过JSONPath批量修改JSON中的多个节点，写法非常简洁：

假设你的表名为your_table，存储这个JSON的字段名为json_column，更新语句如下：

UPDATE your_table
SET json_column = JSON_TRANSFORM(
    json_column,
    '$.items.*.qty' = 0
);

这里的$.items.*.qty是JSONPath表达式：

• $.items定位到JSON中的items对象
• *匹配items下面的所有子节点（也就是itemA、itemB、itemC这些动态命名的键）
• .qty指定要修改的字段，直接把所有匹配到的qty值设为0，一步到位。

场景2：MariaDB 10.6以下版本 #

如果你的版本不支持JSON_TRANSFORM，可以通过动态遍历JSON键+JSON_REPLACE的方式实现，推荐用存储过程来批量处理：

DELIMITER //
CREATE PROCEDURE ResetAllItemQty()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE row_id INT;
    DECLARE target_json JSON;
    DECLARE item_keys JSON;
    DECLARE key_index INT DEFAULT 0;
    DECLARE key_total INT;
    DECLARE current_item_key VARCHAR(255);

    -- 定义游标，遍历需要更新的记录（这里假设表有id主键，你可以根据实际调整）
    DECLARE data_cursor CURSOR FOR SELECT id, json_column FROM your_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN data_cursor;
    update_loop: LOOP
        FETCH data_cursor INTO row_id, target_json;
        IF done THEN
            LEAVE update_loop;
        END IF;

        -- 获取items下的所有键（比如itemA、itemB）
        SET item_keys = JSON_KEYS(target_json, '$.items');
        SET key_total = JSON_LENGTH(item_keys);
        SET key_index = 0;

        -- 逐个替换每个item的qty为0
        WHILE key_index < key_total DO
            SET current_item_key = JSON_UNQUOTE(JSON_EXTRACT(item_keys, CONCAT('$[', key_index, ']')));
            SET target_json = JSON_REPLACE(
                target_json,
                CONCAT('$.items.', current_item_key, '.qty'),
                0
            );
            SET key_index = key_index + 1;
        END WHILE;

        -- 将修改后的JSON更新回表
        UPDATE your_table SET json_column = target_json WHERE id = row_id;
    END LOOP;
    CLOSE data_cursor;
END //
DELIMITER ;

-- 调用存储过程执行更新
CALL ResetAllItemQty();

注意事项 #

• 执行更新操作前，建议先备份数据或者用SELECT语句测试修改后的JSON是否符合预期，比如：

  SELECT JSON_TRANSFORM(json_column, '$.items.*.qty' = 0) FROM your_table LIMIT 1;
  

• 如果只是更新单条记录，也可以直接手动指定每个item的路径，比如：

  UPDATE your_table
  SET json_column = JSON_REPLACE(
      json_column,
      '$.items.itemA.qty', 0,
      '$.items.itemB.qty', 0,
      '$.items.itemC.qty', 0
  )
  WHERE id = 1; -- 替换成你的目标记录ID
  

内容的提问来源于stack exchange，提问作者KrityAg