时间戳

首页 > 解决方案 > JSON 插入 MySQL 表或更新（如果存在）

mysql - JSON 插入 MySQL 表或更新（如果存在）

问题描述

这是插入 MySQL 表或更新（如果存在）的延续，但这次我想更新 mysql 中的 json 条目

下面是架构：

CREATE TABLE TAG_COUNTER (
    account       varchar(36) NOT NULL,
    time_id       INT NOT NULL,
    counters      JSON,
    PRIMARY KEY   (account, time_id)
)

示例数据如下所示。

'google', '20180510', '{"gmail_page_viewed" : 12000300, "search_page_viewed" : 9898884726423}'

实际上我总是想更新（增加计数器）这个表。但是每天开始时间_id（yyyyMMdd）都是新的，因此计数器json没有计数器键（例如gmail_page_viewed）。


所以我想要的解决方案是
value = 1在它不存在时创建新的 json 键列。例如{"gmail_page_viewed" : 1}


如果计数器（例如gmail_page_viewed）键存在，则递增
value = value + 1

因此，如果 json 计数器存在，请帮助我进行更新插入查询。

UPDATE_1（信息：使用 json 时始终使用 IFNULL）

我使用了@wchiquito 建议的解决方案，但看到了以下问题。

第一次（在模式创建之后）；尝试了下面的sql

  INSERT INTO `TAG_COUNTER`
  (`partner_id`, `time_id`, `counters`)
VALUES
  ('google', '20180510', '{"gmail_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
  JSON_SET(`counters`,
           '$.gmail_page_viewed',
           JSON_EXTRACT(`counters`,
                        '$.gmail_page_viewed') + 1
  );

并得到以下回复；正如预期的那样是正确的。

'google', '20180510', '{"gmail_page_viewed": 1}'

然后在下一个查询中尝试使用不同的计数器，

  INSERT INTO `TAG_COUNTER`
  (`account`, `time_id`, `counters`)
VALUES
  ('google', '20180510', '{"search_page_viewed": 1}')
ON DUPLICATE KEY UPDATE `counters` =
  JSON_SET(`counters`,
           '$.search_page_viewed',
           JSON_EXTRACT(`counters`,
                        '$.search_page_viewed') + 1
  );

并得到了以下。

'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'

知道为什么这个新计数器 search_page_viewed 设置为 NULL ..？

UPDATE_2（信息：当数字用作键时，在键周围使用双引号）

我也遇到过以下问题，并从该答案中解决了

Mysql 5.7 错误 3143 (42000)：无效的 JSON 路径表达式。错误在字符位置 3 附近

UPDATE_3（信息：分组依据，确保 WHERE 使用“NOT NULL”或“>0”

获取每个时间范围的计数（例如按天、月、年）

  SELECT 
  SUBSTRING(time_id, 1, 6) AS month, 
  SUM(counters->>'$.gmail_page_viewed') 
  FROM TAG_COUNTER 
  WHERE counters->>'$.gmail_page_viewed' > 0
  GROUP BY month;

UPDATE_4（问题：）

我想查看每次的趋势页面（小时/天/月/年） 从下面的查询中我可以获得每次计数，但前提是我知道密钥（gmail_page_viewed）。

  SELECT 
  SUBSTRING(time_id, 1, 6) AS month, 
  SUM(counters->>'$.gmail_page_viewed') 
  FROM TAG_COUNTER 
  WHERE counters->>'$.gmail_page_viewed' > 0
  GROUP BY month;

但我想要的是我如何知道每次页数的总体趋势？所以我需要类似下面的东西，请帮忙。

group by SUBSTRING(time_id, 1, 6) AS month, KEY
order by SUM(counters->>'$.KEY')

标签： mysqljson

解决方案

以下脚本可以满足您的要求：

INSERT INTO `TAG_COUNTER`
  (`account`, `time_id`, `counters`)
VALUES
  ('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 50}'),
  ('google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": 50}'),
  ('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}'),
  ('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}'),
  ('google', '20180511', '{"gmail_page_viewed": 1, "search_page_viewed": 100}')
ON DUPLICATE KEY UPDATE `counters` =
  JSON_SET(`counters`,
           '$."gmail_page_viewed"',
           IFNULL(`counters` ->> '$."gmail_page_viewed"', 0) + 1,
           '$."search_page_viewed"',
           IFNULL(`counters` ->> '$."search_page_viewed"', 0) + 1
  );

请参阅dbfiddle。

推荐阅读