含可空列的复合主键设计及关联查询需求咨询

要满足你的需求，不需要用字符串'NULL'替代真实NULL，核心是利用唯一约束/索引处理可空列的唯一性，同时遵循SQL的NULL语义进行查询。以下是具体实现：

一、表结构与唯一性约束 #

通用方案（适配多数数据库：MySQL、SQL Server等） #

创建表时，通过COALESCE函数将NULL转换为一个业务中不会使用的特殊值，再创建复合唯一约束，这样既保留了NULL的语义，又能实现（parameter_id, visible_mask_substring）组合的唯一性：

CREATE TABLE myTable (
    parameter_id VARCHAR(255) NOT NULL,
    visible_mask_substring VARCHAR(255),
    other_column VARCHAR(255),
    -- 把NULL映射为特殊标记，确保同一parameter_id下只能有一条NULL记录
    UNIQUE KEY uq_parameter_mask (parameter_id, COALESCE(visible_mask_substring, '<<NULL_MARKER>>'))
);

这样：

• 首次插入('myParamName', NULL, 'alskdjflas')和('myParamName', 'A', 'asdf')会正常执行；
• 重复插入('myParamName', 'A', 'asdf2')或('myParamName', NULL, 'xxx')时，会触发唯一约束错误，满足你的重复插入拦截需求。

PostgreSQL专属优化方案 #

PostgreSQL支持部分索引，可以更精准地分别约束NULL和非NULL场景：

CREATE TABLE myTable (
    parameter_id VARCHAR(255) NOT NULL,
    visible_mask_substring VARCHAR(255),
    other_column VARCHAR(255)
);

-- 约束非NULL时，parameter_id + visible_mask_substring的组合唯一
CREATE UNIQUE INDEX uq_parameter_mask_not_null ON myTable (parameter_id, visible_mask_substring)
WHERE visible_mask_substring IS NOT NULL;

-- 约束NULL时，每个parameter_id只能有一条对应记录
CREATE UNIQUE INDEX uq_parameter_mask_null ON myTable (parameter_id)
WHERE visible_mask_substring IS NULL;

二、修正左连接查询语句 #

你原来的查询中使用mt.visible_mask_substring = NULL是错误的——SQL中NULL不能用=判断，必须用IS NULL，否则永远匹配不到记录。修正后的查询语句如下：

查询visible_mask_substring为NULL的关联记录：

SELECT * 
FROM parameters p 
LEFT JOIN myTable mt 
    ON mt.parameter_id = p.parameter_id 
    AND mt.visible_mask_substring IS NULL;

查询visible_mask_substring为'A'的关联记录：

SELECT * 
FROM parameters p 
LEFT JOIN myTable mt 
    ON mt.parameter_id = p.parameter_id 
    AND mt.visible_mask_substring = 'A';

为什么不要用'NULL'字符串替代真实NULL？ #

这种做法会破坏数据语义：

• NULL表示“未知/不存在”，而字符串'NULL'是一个具体值，两者语义完全不同；
• 查询时需要额外判断visible_mask_substring = 'NULL'，增加代码复杂度，也容易引发逻辑错误；
• 违背SQL标准的NULL处理规则，后续维护成本极高。

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