首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >JSON插入到MySQL表中或如果存在更新

问JSON插入到MySQL表中或如果存在更新
EN

Stack Overflow用户
提问于 2018-05-10 11:04:20
回答 1查看 1.9K关注 0票数 0

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

下面是模式:

代码语言:javascript
复制
CREATE TABLE TAG_COUNTER (
    account       varchar(36) NOT NULL,
    time_id       INT NOT NULL,
    counters      JSON,
    PRIMARY KEY   (account, time_id)
)

示例数据如下所示。

代码语言:javascript
复制
'google', '20180510', '{"gmail_page_viewed" : 12000300, "search_page_viewed" : 9898884726423}'

实际上,我总是想更新这个表的(增加计数器)。但是每一天,start time_id(yyyyMMdd)都是新的,因此计数器json没有计数器密钥(例如gmail_page_viewed)。

因此,我想要的解决方案是创建新的json键列。

当value = 1不存在时。例如{"gmail_page_viewed" : 1}

如果存在计数器(例如gmail_page_viewed)键,则增加

value = value + 1

因此,如果存在json计数器,请在insert查询中帮助我进行更新。

UPDATE_1 (信息:在使用json时始终使用IFNULL )

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

第一次(在模式创建之后);尝试了以下sql

代码语言:javascript
复制
  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
  );

并得到了以下的反应,这是正确的,如预期。

代码语言:javascript
复制
'google', '20180510', '{"gmail_page_viewed": 1}'

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

代码语言:javascript
复制
  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
  );

得到了下面的信息。

代码语言:javascript
复制
'google', '20180510', '{"gmail_page_viewed": 1, "search_page_viewed": null}'  

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

UPDATE_2 (信息:当使用数字作为键时,在键周围使用双引号)

我也面临以下问题,并从这个答案中解决了这个问题。

MySQL5.7错误3143 (42000):无效的JSON路径表达式。错误在字符位置3附近。

UPDATE_3 (信息:组by,确保使用“NULL”或"> 0“在何处

按时间框架(例如按日、月、年)计算计数

代码语言:javascript
复制
  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)。

代码语言:javascript
复制
  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;

但是我想要的是如何知道每次页面计数的总体趋势?所以我需要像下面这样的东西,请帮帮忙。

代码语言:javascript
复制
group by SUBSTRING(time_id, 1, 6) AS month, KEY
order by SUM(counters->>'$.KEY') 
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-05-10 14:27:39

以下脚本可能对您的需求很有用:

代码语言:javascript
复制
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
  );

见[医]小提琴。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50271455

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档