波仔
🦏 第9章 列的属性
📄 一句话概括

UNIQUE 保证这一列（或这几列的组合）不许有两个一样的值。 但它不拦 NULL——多个 NULL 不算重复。 「不许重复」不等于「必须有值」。 要两个都保证，写 UNIQUE NOT NULL—— 这是建表时最常漏的组合。 复合唯一 UNIQUE (a, b) 管的是「两列的组合」， 单看每一列都能重复。 它背后自动建一个索引，所以查得也快。

📝 本章和前面几章的关系

上一篇管「值在不在范围内」，这一篇管「值重不重复」。 唯一约束是「业务规则」最常落成约束的一种： 用户名不许重、手机号不许重、订单号不许重。 但它有个反直觉的地方：它不管空。

一、单列唯一

email TEXT UNIQUE，插两个一样的邮箱，第二个报 UNIQUE constraint failed: demo_uq.email。 MySQL 的报错更友好：Duplicate entry '[email protected]' for key 'email'， 直接把那个重复的值打出来了。 排查冲突时这个细节很省事——不用自己再去查是哪条重复。

二、可以有空，而且可以有好几个空

三行都没有邮箱，全部进去了。 唯一约束不拦 NULL，因为 NULL 不等于 NULL，两个空不算重复。 这一点的实际后果： 「邮箱不许重复」不等于「邮箱必须有值」。 用户不填邮箱，你可以让一百行都是 NULL 邮箱。 要两个都保证，写 email TEXT UNIQUE NOT NULL。 这是建表时最常漏的一个组合—— 漏了以后补，就得先处理已有的多个 NULL，再重建表。

三、复合唯一：两列一起不许重复

UNIQUE (a, b) 写在表级。 Demo 里 (p, q) 重复被拦，(p, r) 放行。 它管的是「这两列的组合」，不是每一列各自唯一。 典型用途是「同一个用户对同一个商品只能有一条评价」—— 单看 user_id 会重复，单看 goods_id 也会重复，两个一起才唯一。 另一种常见用法是「防重复提交」：UNIQUE (user_id, action, date)， 同一个人同一天同一个动作只能记一次。 这比在程序里「先查再插」可靠得多—— 两个人同时提交时，程序那条路会双双通过，唯一约束不会。

四、唯一键背后是一个索引

PRAGMA index_list 能看到 sqlite_autoindex_demo_uq_1，一个唯一约束自动建了一个索引。 所以「按邮箱查人」这种查询会快很多——校验重复和加速查询，是同一个东西的两面。 代价是写入变慢一点：每插一行都要去索引里查一次有没有重复。 索引不是白给的，第 12 章专门讲这笔账。 但唯一约束通常是值得的：它同时解决了「正确性」和「查询速度」两个问题。

五、插入冲突的两个处理方式

INSERT OR IGNORE 撞了重复就跳过，不报错也不插入。 MySQL 那边写 INSERT IGNORE。 另一种是「撞了就改」：MySQL 的 ON DUPLICATE KEY UPDATE、SQLite 的 ON CONFLICT DO UPDATE。 两个都能做到「有就更新、没有就插入」，也就是常说的 upsert。 第三种是「撞了就报错」，什么都不做——这是默认行为，也是最安全的一种： 让调用方知道冲突了，由它决定怎么处理。 业务上「悄悄忽略」和「悄悄覆盖」都可能埋雷，别为了省事选它们。

六、动手 Demo：重复被拦、三个空都进去、复合唯一与自动索引

Demo 六段：先插一个邮箱、再插同一个撞一次；然后连插三个没填邮箱的行；再做一次复合唯一； 看唯一键背后的索引；最后用 OR IGNORE 化解冲突。 第二段是这一篇的重点——它证明「唯一」和「非空」是两件完全不同的事。

import _q46 as Q
c = Q.blank()
Q.run(c, "CREATE TABLE demo_uq (id INTEGER PRIMARY KEY, email TEXT UNIQUE)")
Q.run(c, "INSERT INTO demo_uq (id, email) VALUES (1, '[email protected]')")
c.execute("INSERT INTO demo_uq (id, email) VALUES (2, '[email protected]')")

# 三个空都能进
for i in (3, 4, 5):
    Q.run(c, "INSERT INTO demo_uq (id) VALUES (%d)" % i)

# 复合唯一：两列一起才不许重复
Q.run(c, "CREATE TABLE demo_uq2 (id INTEGER PRIMARY KEY, "
          "a TEXT, b TEXT, UNIQUE (a, b))")

# 唯一约束背后是一个索引
Q.show(c, "PRAGMA index_list(demo_uq)")

# 撞了重复就跳过
Q.run(c, "INSERT OR IGNORE INTO demo_uq (id, email) VALUES (6, '[email protected]')")

实跑输出：

--------------------------------------------------------------------
第四十七课：唯一键——不重复，但可以空
--------------------------------------------------------------------

  单列唯一
  SQL> CREATE TABLE demo_uq (id INTEGER PRIMARY KEY, email TEXT UNIQUE)
  完成
  SQL> INSERT INTO demo_uq (id, email) VALUES (1, '[email protected]')
  影响 1 行
  SQL> INSERT INTO demo_uq (id, email) VALUES (2, '[email protected]')
  报错：UNIQUE constraint failed: demo_uq.email
  **报错 `UNIQUE constraint failed: demo_uq.email`。**
  **MySQL 那边是 `Duplicate entry '[email protected]' for key 'email'`，而且会直接把那个重复的值打出来。**

  可以有空，而且可以有好几个空
  SQL> INSERT INTO demo_uq (id) VALUES (3)
  影响 1 行
  SQL> INSERT INTO demo_uq (id) VALUES (4)
  影响 1 行
  SQL> INSERT INTO demo_uq (id) VALUES (5)
  影响 1 行
  SQL> SELECT id, email FROM demo_uq ORDER BY id
  id     email
  ---------------
  1      [email protected]
  3      NULL
  4      NULL
  5      NULL
  （4 行）
  **三行都没有邮箱，全部进去了。** **唯一约束不拦 NULL，因为 NULL 不等于 NULL，两个空不算重复。**
  **这一点的实际后果**：
  **「邮箱不许重复」不等于「邮箱必须有值」。** **要两个都保证，写 `email TEXT UNIQUE NOT NULL`。**
  **这是建表时最常漏的一个组合。**

  复合唯一：两列一起不许重复
  SQL> CREATE TABLE demo_uq2 (id INTEGER PRIMARY KEY, a TEXT, b TEXT, UNIQUE (a, b))
  完成
  SQL> INSERT INTO demo_uq2 (id, a, b) VALUES (1, 'p', 'q')
  影响 1 行
  SQL> INSERT INTO demo_uq2 (id, a, b) VALUES (2, 'p', 'q')
  报错：UNIQUE constraint failed: demo_uq2.a, demo_uq2.b
  SQL> INSERT INTO demo_uq2 (id, a, b) VALUES (3, 'p', 'r')
  影响 1 行
  SQL> SELECT id, a, b FROM demo_uq2 ORDER BY id
  id     a     b
  ----------------
  1      p     q
  3      p     r
  （2 行）
  **`(p, q)` 重复被拦，`(p, r)` 放行。** **复合唯一管的是「这两列的组合」，不是每一列各自唯一。**
  **典型用途是「同一个用户对同一个商品只能有一条评价」**——单看 `user_id` 会重复，
  单看 `goods_id` 也会重复，**两个一起才唯一。**

  唯一键背后是一个索引
  SQL> PRAGMA index_list(demo_uq)
  seq     name                           unique     origin     partial
  ---------------------------------------------------------------------
  0       sqlite_autoindex_demo_uq_1     1          u          0
  （1 行）
  **一个唯一约束会自动建一个索引**，
  **所以「按邮箱查人」这种查询会快很多**——**校验重复和加速查询，是同一个东西的两面。**
  **代价是写入变慢一点**：每插一行都要去索引里查一次有没有重复。
  **索引不是白给的，第 12 章专门讲这笔账。**

  插入冲突的两个处理方式
  SQL> INSERT OR IGNORE INTO demo_uq (id, email) VALUES (6, '[email protected]')
  影响 0 行
  SQL> SELECT COUNT(*) AS rows_now FROM demo_uq
  rows_now
  ---------
  4
  （1 行）
  **`INSERT OR IGNORE` 撞了重复就跳过，不报错也不插入。** **MySQL 那边写 `INSERT IGNORE`。**
  **另一种是「撞了就改」**：MySQL 的 `ON DUPLICATE KEY UPDATE`、
  SQLite 的 `ON CONFLICT DO UPDATE`。
  **两个都能做到「有就更新、没有就插入」，区别在于 MySQL 那个写法更啰嗦但更灵活。**

第一段撞唯一，报 UNIQUE constraint failed: demo_uq.email。 第二段三个 NULL 全部进去了，表里四行、三个空。 第三段复合唯一：(p, q) 重复被拦、(p, r) 放行。 第四段看到 sqlite_autoindex_demo_uq_1，unique 是 1——唯一约束就是一个唯一索引。 第五段 INSERT OR IGNORE 影响 0 行，行数还是 4。

30 秒实验

  1. email TEXT UNIQUE 的表里，能存几行 email 为 NULL 的记录？
  2. 要「同一个用户对同一个商品只能一条」，建表时该怎么写？
  3. INSERT OR IGNORE 和 ON CONFLICT DO UPDATE 分别适合什么场景？

常见误区

  1. 以为 UNIQUE 就是「必须有值且不重复」。 它只管不重复，多个 NULL 照样进。 要非空得加 NOT NULL。
  2. 在程序里「先查再插」防重复。 并发时两个请求会双双通过检查，该交给唯一约束。
  3. 用 INSERT IGNORE 掩盖冲突。 冲突往往说明业务出问题了，悄悄跳过等于把问题留到以后。
自测三问（点开对答案） 1. 唯一约束为什么允许 NULL？ —— 因为 NULL 不等于 NULL，两个空值不算「重复」。所以多行 NULL 都能进去。 2. 复合唯一管的是什么？举个典型场景。 —— 管几列的组合不重复，单看每一列都能重复。典型场景是「同一用户对同一商品只能一条评价」。 3. 唯一键除了保证不重复，还带来什么？ —— 自动建一个唯一索引，按这一列查询会快很多；代价是写入时要多查一次。

唯一键再往上一级就是主键。 下一篇 9.7 主键——一行的身份证，也是约束里最要紧的一个。

相关笔记