mysql - JSON 等效于 INSERT...ON DUPLICATE KEY UPDATE，并增加现有的当前值

Question

我有这样的情况，简化了:

CREATE TABLE `user_points` (
  `date` date NOT NULL,
  `user_id` mediumint(8) unsigned NOT NULL,
  `points` int(11) NOT NULL,
  PRIMARY KEY (`date`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

因此，如果今天用户获得积分，我将:

INSERT INTO user_points (date, user_id, points) VALUES (CURDATE(),?,1) 
ON DUPLICATE KEY UPDATE points=points+1;

现在，假设我想将这些点存储到一个 JSON 字段中，该字段不仅包含点，还可能包含其他属性(这些属性是动态的，一些客户端永远不会使用任何，其他客户端会使用很多，这就是为什么我喜欢 JSON)

因此，我不想添加到 points，而是想在 data 列上设置 $.points。

是否可以进行单个查询来执行此操作？

• 如果没有包含这样的date 和user_id 的行，则插入一个包含 data={"points": 1} 的新行/li>
• 如果有一行并且data为空，SET data="{"points": 1}"
• 如果有一行包含一些 json，但没有 $.data，JSON_SET(data, '$.points', 1)
• 如果有一行和一些 $.points，将点数加一。

我试过:

SELECT JSON_SET('{"a": 1, "l": 2}', '$.points', 
    JSON_EXTRACT('{"a": 1, "l": 2}', '$.points')+1) AS 'Data';

但它不起作用(因为 NULL + 1 = NULL 而不是 1)

编辑:可以用 COALESCE 解决，但我不确定这是最好的解决方案:

SELECT JSON_SET('{"a": 1, "l": 2}', '$.points', 
    COALESCE(JSON_EXTRACT('{"a": 1, "l": 2}', '$.points'), 0)+1) AS 'Data';

完整的查询是:

INSERT INTO user_points (date, user_id, data) VALUES (CURDATE(),?,'{"points":1}') 
    ON DUPLICATE KEY UPDATE data=JSON_SET(data, '$.points', 
    COALESCE(JSON_EXTRACT(data, '$.points'), 0)+1);

Answer 1

这是我目前找到的有效解决方案，它需要两次 COALESCE(如果 data 为 null，如果 data.points 为 null:

INSERT INTO user_data (date, user_id, data) VALUES (CURDATE(), ?, '{"points":1}')
    ON DUPLICATE KEY UPDATE data=JSON_SET(COALESCE(data, '{}'), '$.points', 
    COALESCE(JSON_EXTRACT(data, '$.points'), 0)+1);

我想知道为什么它添加了一个 .0..