mysql - JSON_ARRAY_APPEND() 如果目标值为空则拒绝 Mysql

Question

这里是场景，将用户 ID 添加到 user_details 中。如果 external_id 不存在于配置文件表中并且返回 null(select 语句返回 null)怎么办？

JSON_ARRAY_APPEND 如何拒绝查询并且不添加到 user_details JSON 列。

UPDATE column1 SET user_details= JSON_SET(user_details, "$.ids", IFNULL(user_details->'$.ids',JSON_ARRAY())),
user_details= JSON_ARRAY_APPEND(user_details, "$.ids", (Select id from column2 where external_id='999999999999999'))
where id = 880; 

谢谢

Answer 1

在 UPDATE 语句的 WHERE 子句中检查这一点。

UPDATE column1 SET user_details= JSON_SET(user_details, "$.ids", IFNULL(user_details->'$.ids',JSON_ARRAY())),
user_details= JSON_ARRAY_APPEND(user_details, "$.ids", (Select id from column2 where external_id='999999999999999'))
where id = 880
AND EXISTS (Select id from column2 where external_id='999999999999999')

或者使用JOIN而不是嵌套子查询。

UPDATE column1 AS c1
CROSS JOIN column2 AS c2
SET user_details= JSON_SET(c1.user_details, "$.ids", IFNULL(c1.user_details->'$.ids',JSON_ARRAY())),
    c1.user_details= JSON_ARRAY_APPEND(c1.user_details, "$.ids",c2.id)
WHERE c1..id = 880
AND c2.external_id = '999999999999999'