JSON_SET未更新MySQL中的空JSON字段 [英] JSON_SET isn't updating null JSON field in MySQL

问题描述

我有一个可为空的JSON MySQL 5.7字段，发现几乎无法正常工作.

I have a nullable JSON MySQL 5.7 field which I am finding almost impossible to get working.

查询示例:

UPDATE `json_test` SET `data` = JSON_SET(`data`, '$.a', 1)

如果字段数据已经为NULL，则不会更新.

If the field data is NULL already, it won't update.

如果它是{ "a" : 2 }，那么它将正确更新为1.如果尚未设置，则需要对其进行设置，这是JSON_SET应该执行的操作.

If it's { "a" : 2 }, then it'll update correctly to 1. I need it to set if not set already, which is what JSON_SET is supposed to do.

有什么想法吗?

推荐答案

它是

否则，文档中不存在的路径的路径/值对为 忽略并且没有效果.

Otherwise, a path/value pair for a nonexisting path in the document is ignored and has no effect.

现在mysql不允许您在要更新的同一张表上使用子查询，但您可能可以使用CASE/WHEN通过UPDATE JOIN解决这个问题，但是我太懒了，所以我给您留了两个查询解决方案

Now mysql doesn't let you use a subquery on the same table that's being updated, but you could probably stil solve this with an UPDATE JOIN using CASE/WHEN but I am too lazy so I leave you with a two query solution.

UPDATE `json_test` SET `data` = JSON_SET(`data`, '$.a', 1) WHERE data IS NOT NULL;

UPDATE `json_test` SET `data` = JSON_OBJECT('$.a', 1) WHERE data IS NULL;

这篇关于JSON_SET未更新MySQL中的空JSON字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！