多态关联

概念定义

外键只能指向一张表。当一张子表——评论、附件、事件——要服务多类父表时，用判别列加 id 模拟引用。代价是数据库无法验证这对值是否真的指向存在的行。

工作机制

子行存 parent_type 与 parent_id，ORM 在查询时拼出正确的表名；连接退化为两步或应用侧完成。删除父表必须显式清扫子行或依赖 ORM 回调，因为不存在 ON DELETE CASCADE。

使用场景

活动流、标签这类横切关注点，可容忍完整性损失、替代方案又会让表数量爆炸时可以接受。完整性要紧时应改为公共超表让所有父表引用，或带 CHECK 约束的独占弧设计。

常见误区

多态不等于“灵活且安全”：它删掉的是数据库级引用保证，父表硬删会留下不可见的孤儿。判别值一旦被更多行依赖，迁移到更干净模型就越难——应尽早冻结枚举并当作契约对待；只按 id 连接的查询会静默命中无关父类型的行。

代码示例

-- 每个邮箱保留 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);

const TABLE = buildTable();

export function crc32(input: string | Uint8Array, seed = 0): number {
  const bytes = typeof input === "string" ? new TextEncoder().encode(input) : input;
  if (!(bytes instanceof Uint8Array)) throw new TypeError("input must be a string or Uint8Array");
  if (!Number.isInteger(seed)) throw new TypeError("seed must be an integer");
  let crc = (seed ^ 0xffffffff) >>> 0;
  for (const byte of bytes) {
    crc = (TABLE[(crc ^ byte) & 0xff] ^ (crc >>> 8)) >>> 0;
  }
  return (crc ^ 0xffffffff) >>> 0;
}

export function crc32Hex(input: string | Uint8Array): string {
  return crc32(input).toString(16).padStart(8, "0");
}

function buildTable(): Uint32Array {
  const table = new Uint32Array(256);
  for (let index = 0; index < 256; index += 1) {
    let value = index;
    for (let bit = 0; bit < 8; bit += 1) {
      value = (value & 1) === 1 ? (value >>> 1) ^ 0xedb88320 : value >>> 1;
    }
    table[index] = value >>> 0;
  }
  return table;
}

console.log(crc32Hex("hello"));
console.log(crc32Hex(new TextEncoder().encode("hello")));
console.log(crc32Hex("The quick brown fox jumps over the lazy dog"));