MySQL 8.0.26如何基于双条件更新JSON数组内指定元素

问题场景 #

在MySQL表的JSON类型字段中存储了如下结构的数据：

{
  "components":[
      {
         "url":"www.google.com",
         "order":3,
         "accountId":"123",
         "autoPilot":true
      },
      {
         "url":"www.youtube.com",
         "order":7,
         "accountId":"987",
         "autoPilot":false
      }
  ],
  "addTabs":true,
  "activateHomeSection":true 
}

需要基于accountId和autoPilot两个属性做匹配，更新对应数组元素的url属性，匹配规则示例：

• accountId = 123
• autoPilot = true
• 目标更新url = 'facebook.com'
  即匹配成功后，原www.google.com需要被替换为www.facebook.com。
  现有方案为先查询匹配项再用正则替换，需要两次IO，性能差，且正则容易破坏JSON结构，尝试JSON_SEARCH、JSON_CONTAINS等内置函数未实现预期效果。

最优实现（MySQL 8.0+） #

直接使用JSON_TABLE打平数组+JSON_SET定点更新，单语句即可完成原子更新，性能远高于先查后改方案，也不会出现正则匹配失效问题：

UPDATE users u
JOIN JSON_TABLE(
    u.custom_page,
    '$.components[*]' COLUMNS (
        -- 获取数组元素序号（从1开始计数）
        elem_pos FOR ORDINALITY,
        autoPilot BOOLEAN PATH '$.autoPilot',
        accountId VARCHAR(36) PATH '$.accountId'
    )
) matched
SET u.custom_page = JSON_SET(
    u.custom_page,
    -- MySQL JSON数组下标从0开始，所以序号减1
    CONCAT('$.components[', matched.elem_pos - 1, '].url'),
    'facebook.com' -- 传入要更新的目标url参数即可
)
WHERE u.username = 'mihai2nd'
  AND matched.accountId = '123'
  AND matched.autoPilot = TRUE;

方案说明 #

• 整个更新是单语句原子操作，InnoDB引擎会自动加行锁，不存在并发修改不一致问题，相比先查后改的方案减少一次网络IO和表扫描，性能提升明显
• 完全使用MySQL原生JSON函数操作，不会出现正则替换导致的JSON格式损坏问题，对字段顺序、空格等格式变化不敏感
• 如果存在多个同时满足匹配条件的数组元素，语句会批量更新所有匹配项
• 若使用MySQL 5.7版本（不支持JSON_TABLE），可以通过递归CTE遍历数组下标实现相同逻辑，但MySQL 5.7的JSON操作性能本身较低，更建议升级到8.0+版本使用上述方案
• 禁止使用正则替换JSON字段内容：JSON属于结构化数据，正则无法感知语法结构，一旦字段顺序、空格、嵌套层级出现变化，正则匹配极易失效，甚至会破坏整个JSON字段的合法性。

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