NULL 与唯一约束

概念定义

唯一的含义是“两个非空值不得相等”。NULL 是未知而非值，按标准规则永不违反唯一性。本想“至多一个有效值加任意多个缺失”的列，因此可以接受无限个缺失——多数时候并不是模式设计者的本意。

工作机制

标准引擎允许唯一索引中重复 NULL。MySQL 对多列唯一键中含 NULL 的行即使其余列重复也视为不同；PostgreSQL 提供“判别列非空时唯一”的部分唯一索引这个精确工具；Oracle 历史上把单列重复 NULL 视为冲突，偏离标准。

使用场景

每个用户一个主邮箱（其余为 NULL）、每账户一个活跃订阅、按可选类型键控的单例行。应改用部分唯一索引，或重构让约束作用于真实值。

常见误区

用空字符串等哨兵值绕过怪癖，会让假数据渗进查询与聚合。ORM 校验与约束语义只是近似，并发下还有竞态——数据库约束才是仲裁者。各引擎行为差异大，依赖之前要显式测试。

代码示例

-- 每个邮箱保留 created_at 最新的一行，标记其余为重复
WITH ranked AS (
  SELECT
    id,
    email,
    created_at,
    ROW_NUMBER() OVER (
      PARTITION BY email
      ORDER BY created_at DESC, id DESC
    ) AS rn
  FROM subscribers
)
SELECT id, email, created_at, (rn = 1) AS is_primary
FROM ranked
ORDER BY email, rn;

-- 一次性清扫：删除全部非首行
WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY created_at DESC, id DESC
         ) AS rn
  FROM subscribers
)
DELETE FROM subscribers s
USING ranked r
WHERE s.id = r.id
  AND r.rn > 1;

-- 上游堵漏：唯一索引阻止重复再次写入
CREATE UNIQUE INDEX CONCURRENTLY idx_subscribers_email
  ON subscribers (email);

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
)

type Profile struct {
	ID       int64          `json:"id"`
	Nickname sql.NullString `json:"-"`
	Bio      *string        `json:"bio"`
}

type profileJSON struct {
	ID       int64   `json:"id"`
	Nickname *string `json:"nickname"`
	Bio      *string `json:"bio"`
}

func nullString(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	return &value.String
}

func (p Profile) MarshalJSON() ([]byte, error) {
	return json.Marshal(profileJSON{
		ID:       p.ID,
		Nickname: nullString(p.Nickname),
		Bio:      p.Bio,
	})
}

func scanProfile(row *sql.Row) (Profile, error) {
	var profile Profile
	err := row.Scan(&profile.ID, &profile.Nickname, &profile.Bio)
	return profile, err
}

func main() {
	encoded, _ := json.Marshal(Profile{ID: 7})
	fmt.Println(string(encoded))
	encoded, _ = json.Marshal(Profile{
		ID:       8,
		Nickname: sql.NullString{String: "ada", Valid: true},
	})
	fmt.Println(string(encoded))
}