如何编写SQL语句将指定行中值为{}的列设为NULL？

静态列场景（列数较少时） #

如果表的列数量不多，可以直接写出每个列的更新逻辑，用CASE表达式判断值是否为'{}'，是的话替换为NULL，否则保留原值：

UPDATE XYZ
SET 
    col2 = CASE WHEN col2 = '{}' THEN NULL ELSE col2 END,
    col3 = CASE WHEN col3 = '{}' THEN NULL ELSE col3 END,
    col4 = CASE WHEN col4 = '{}' THEN NULL ELSE col4 END,
    col5 = CASE WHEN col5 = '{}' THEN NULL ELSE col5 END
WHERE col_id = 1234;

这条语句只会更新col_id = 1234的行，其他行不受影响。

动态列场景（列数较多时） #

如果表有大量列，手动写每个列的逻辑效率低，可以用动态SQL自动生成更新语句（以下以MySQL为例，其他数据库语法需调整）：

-- 定义表名和筛选条件
SET @table_name = 'XYZ';
SET @filter_condition = 'col_id = 1234';

-- 拼接每个列的更新逻辑
SELECT GROUP_CONCAT(
    CONCAT('`', column_name, '` = CASE WHEN `', column_name, '` = ''{}'' THEN NULL ELSE `', column_name, '` END')
) INTO @update_clause
FROM information_schema.columns
WHERE table_schema = DATABASE() 
  AND table_name = @table_name
  AND column_name != 'col_id'; -- 排除筛选列，避免误修改

-- 生成完整的UPDATE语句并执行
SET @full_sql = CONCAT('UPDATE ', @table_name, ' SET ', @update_clause, ' WHERE ', @filter_condition);

PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注意事项 #

1. 确保目标列允许存储NULL：如果列设置了NOT NULL约束，更新会失败，需要先调整列的约束。
2. 适配数据类型：如果{}是JSON类型的空对象而非字符串，需要修改判断条件，比如MySQL用JSON_TYPE(col2) = 'OBJECT' AND JSON_LENGTH(col2) = 0，PostgreSQL用col2 = '{}'::json。

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