MySQL含静态键JSON的INSERT ON DUPLICATE KEY UPDATE语句编写求助

我明白你现在的困境——把动态键的JSON改成嵌套静态结构后，原来的ON DUPLICATE KEY UPDATE逻辑完全不适用了，折腾6小时还没搞定各种异常，确实头疼。让我来帮你解决这个问题。

首先明确你的新JSON结构：每个动态页面键（比如gmail_page_viewed）对应一个包含page_name和page_visits的对象，目标是当account+time_id主键重复时，累加对应页面的page_visits数值。

场景1：每个INSERT值对应单个页面的JSON对象 #

如果你的插入语句里每个VALUES条目只包含一个页面的JSON（比如你原来例子里的单键结构），可以用下面的语句：

INSERT INTO `TAG_COUNTER` (`account`, `time_id`, `counters`) 
VALUES 
  ('google', '20180510', '{"gmail_page_viewed": {"page_name": "gmail_page_viewed", "page_visits": 1}}'),
  ('google', '20180510', '{"search_page_viewed": {"page_name": "search_page_viewed", "page_visits": 1}}'),
  ('google', '20180511', '{"gmail_page_viewed": {"page_name": "gmail_page_viewed", "page_visits": 1}}')
ON DUPLICATE KEY UPDATE 
  `counters` = JSON_MERGE_PRESERVE(
    `counters`,
    JSON_OBJECT(
      -- 获取当前插入行的JSON唯一键（比如gmail_page_viewed）
      JSON_KEYS(VALUES(`counters`))[0],
      JSON_OBJECT(
        "page_name", 
        JSON_EXTRACT(VALUES(`counters`), CONCAT('$."', JSON_KEYS(VALUES(`counters`))[0], '".page_name')),
        "page_visits", 
        -- 累加原有值（不存在则取0）和新值
        IFNULL(
          JSON_EXTRACT(`counters`, CONCAT('$."', JSON_KEYS(VALUES(`counters`))[0], '".page_visits')), 
          0
        ) + JSON_EXTRACT(VALUES(`counters`), CONCAT('$."', JSON_KEYS(VALUES(`counters`))[0], '".page_visits'))
      )
    )
  );

关键逻辑解释： #

• JSON_KEYS(VALUES(counters))[0]：提取当前插入行JSON中的唯一键（因为每个VALUES只传一个页面）
• JSON_MERGE_PRESERVE：合并原有JSON和新生成的页面对象，保留其他已存在的页面键，只更新目标键的page_visits
• IFNULL(..., 0)：处理原有JSON中没有该页面键的情况，避免累加出现NULL错误

场景2：每个INSERT值包含多个页面的JSON对象 #

如果单个VALUES条目里包含多个页面的JSON（比如同时传gmail_page_viewed和search_page_viewed），上面的方法就不适用了。这时可以用JSON_TABLE把JSON拆成行，再批量处理：

INSERT INTO `TAG_COUNTER` (`account`, `time_id`, `counters`)
SELECT 
  'google' AS account,
  '20180510' AS time_id,
  JSON_OBJECT(jt.page_key, JSON_OBJECT('page_name', jt.page_name, 'page_visits', jt.page_visits)) AS counters
FROM 
  JSON_TABLE(
    -- 替换成你要插入的多键JSON
    '{"gmail_page_viewed": {"page_name": "gmail_page_viewed", "page_visits": 1}, "search_page_viewed": {"page_name": "search_page_viewed", "page_visits": 1}}',
    '$.*' COLUMNS(
      page_key VARCHAR(255) PATH '$."$key"', -- 提取动态键名
      page_name VARCHAR(255) PATH '$.page_name',
      page_visits INT PATH '$.page_visits'
    )
  ) AS jt
ON DUPLICATE KEY UPDATE 
  `counters` = JSON_SET(
    `counters`,
    -- 构造目标页面的page_visits路径
    CONCAT('$."', jt.page_key, '".page_visits'),
    -- 累加原有值和新值
    IFNULL(JSON_EXTRACT(`counters`, CONCAT('$."', jt.page_key, '".page_visits')), 0) + jt.page_visits
  );

关键逻辑解释： #

• JSON_TABLE：把多键JSON拆成一行一个页面的结构化数据，方便逐个处理
• JSON_SET：直接定位到目标页面的page_visits字段更新，比JSON_MERGE_PRESERVE更精准，避免意外覆盖其他字段

常见问题规避 #

你之前遇到的引号异常、更新错误，大概率是这些原因：

• JSON路径格式错误：当键名包含特殊字符（如下划线）时，必须用$."key_name"格式包裹，不能直接写$.key_name
• 类型不匹配：确保page_visits是数值类型，不要传入字符串格式的数字
• NULL处理：一定要用IFNULL处理原有JSON中不存在目标键的情况，否则累加会得到NULL

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