MySQL中实现含JSON字段的Upsert操作问题咨询

现有数据表结构如下：

CREATE TABLE `foo` (
  `user_id` varchar(255) NOT NULL,
  `settings` json NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `IDX_5471ee230b9746dfb775d6c354` (`user_id`)
)

需要对user_id和JSON类型的settings字段执行Upsert操作。当前使用的REPLACE INTO语句虽然能处理user_id的Upsert，但会直接覆盖整个settings字段：

REPLACE INTO foo(user_id, settings)   
VALUES (
    '3',JSON_SET(
            settings,
            "$.topLevelField", 
                JSON_OBJECT('nestedField1', true, 
                            'nestedField2', "01-01-2020")
        )
);

求助如何实现既能完成Upsert，又不覆盖settings原有JSON内容的SQL语句。

要实现保留原有JSON内容的Upsert，应该使用**INSERT ... ON DUPLICATE KEY UPDATE**语句，而非REPLACE INTO。REPLACE INTO的逻辑是当主键/唯一键冲突时先删除旧记录再插入新记录，必然会覆盖所有字段；而ON DUPLICATE KEY UPDATE可以仅更新需要修改的部分，同时保留原有字段的内容。

具体SQL语句 #

INSERT INTO foo(user_id, settings)
VALUES (
    '3',
    JSON_SET(
        '{}', -- 插入新记录时的初始空JSON
        "$.topLevelField", 
        JSON_OBJECT('nestedField1', true, 'nestedField2', "01-01-2020")
    )
)
ON DUPLICATE KEY UPDATE
    settings = JSON_SET(
        foo.settings, -- 冲突时使用已有记录的settings值
        "$.topLevelField", 
        JSON_OBJECT('nestedField1', true, 'nestedField2', "01-01-2020")
    );

关键说明 #

• 插入新记录时，用'{}'作为初始JSON对象，确保settings字段始终是合法的JSON格式（符合表结构NOT NULL要求）。
• 当user_id冲突触发更新时，JSON_SET会基于原有foo.settings的内容修改指定路径的字段，不会覆盖其他未涉及的JSON键值对。
• 如果需要同时修改多个JSON路径，可在JSON_SET中追加参数，例如：JSON_SET(foo.settings, "$.field1", value1, "$.field2", value2)。

简化写法 #

如果追求语句简洁，也可以用以下方式，插入时先传入空JSON，冲突时直接基于现有值更新：

INSERT INTO foo(user_id, settings)
VALUES ('3', '{}')
ON DUPLICATE KEY UPDATE
    settings = JSON_SET(
        settings,
        "$.topLevelField", 
        JSON_OBJECT('nestedField1', true, 'nestedField2', "01-01-2020")
    );

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