CREATE TABLE 中的 PRIMARY KEY、UNIQUE、NOT NULL、CHECK、DEFAULT、AUTO_INCREMENT 各自含义与使用场景？

考察要点

这道题考察的是候选人对数据库**数据完整性约束（Data Integrity Constraints）**的理解深度。面试官想看你是否掌握了以下几点：

1. 核心定义：是否能准确区分 PRIMARY KEY, UNIQUE, NOT NULL 等约束的语义和作用。
2. 底层影响：是否理解这些约束对数据库底层（如索引结构、存储引擎行为）的影响。
3. 设计能力：是否能基于业务场景，合理地选择和组合这些约束来设计出健壮、高效的表结构。

核心概念

数据完整性约束是施加在表或列上的规则，用于保证数据库中数据的准确性、一致性和有效性。以下是 CREATE TABLE 中最核心的几种约束：

1. PRIMARY KEY (主键约束)

• 含义：唯一标识表中的每一行记录。一个表最多只能有一个主键。主键列的值必须是唯一的（UNIQUE）并且非空的（NOT NULL）。因此，PRIMARY KEY 是 UNIQUE 和 NOT NULL 的组合。
• 使用场景：作为记录的唯一身份标识。例如，用户表中的 user_id，订单表中的 order_id。
• 底层影响 (InnoDB)：在 MySQL 的 InnoDB 存储引擎中，主键索引是聚簇索引（Clustered Index）。表中的数据行本身就是按照主键的顺序物理存储在磁盘上的。选择一个好的主键对性能至关重要。

2. UNIQUE (唯一约束)

• 含义：保证一列或多列组合的值在表中是唯一的。一个表可以有多个唯一约束。与主键不同，UNIQUE 约束的列允许存在 NULL 值，并且在大多数数据库实现中（如 MySQL InnoDB），可以存在多个 NULL 值。
• 使用场景：当某列需要保证唯一性，但又不适合做主键时。例如，用户表中的 email 或 id_card_number（身份证号），它们需要唯一，但我们通常会选择一个无业务含义的 user_id 作为主键。
• 底层影响：UNIQUE 约束会创建一个唯一索引（Unique Index），这是一种二级索引（Secondary Index）。

PRIMARY KEY vs. UNIQUE 对比

3. NOT NULL (非空约束)

• 含义：保证列的值不能为 NULL。它是一种列级别约束。
• 使用场景：对于业务上必须存在的字段，如用户表的 username，订单表的 create_time。设置 NOT NULL 可以避免因 NULL 值带来的逻辑复杂性（如 COUNT(column) 忽略 NULL，WHERE column != 'value' 不会匹配 NULL 的行）。

4. CHECK (检查约束)

• 含义：保证列中的值满足指定的布尔表达式。
• 使用场景：用于实现域完整性（Domain Integrity），确保数据在业务逻辑上是有效的。例如，商品价格 price 必须大于 0，用户年龄 age 必须在 0 到 150 之间。
• 版本差异：
  • MySQL 8.0.16 之前：语法上支持 CHECK，但解析后会被忽略，不起任何作用。
  • MySQL 8.0.16 及之后：CHECK 约束开始真正生效。
  • PostgreSQL / Oracle / SQL Server：一直都良好支持 CHECK 约束。

5. DEFAULT (默认值约束)

• 含义：当 INSERT 语句没有为该列提供值时，自动为其赋一个预设的默认值。
• 使用场景：提高数据录入的便利性和一致性。例如，用户注册时间 create_time 默认值为 CURRENT_TIMESTAMP，用户状态 status 默认值为 1（表示“正常”）。

6. AUTO_INCREMENT (自增属性)

• 含义：为新插入的行自动生成一个唯一的、通常是递增的整数。它不是一个标准的 SQL 约束，而是 MySQL 等数据库的一个列属性。
• 使用场景：通常与 PRIMARY KEY 结合使用，创建代理主键（Surrogate Key）。这是最常见的主键生成方式。
• 注意：一个表只能有一个 AUTO_INCREMENT 列，且该列必须被索引（通常是主键或唯一键）。

DDL 示例

 1CREATE TABLE `users` (
 2  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,          -- 主键, 非空, 自增
 3  `user_code` VARCHAR(50) NOT NULL,                       -- 业务编码, 非空
 4  `email` VARCHAR(100) NULL,                              -- 邮箱, 可为空, 但需唯一
 5  `age` TINYINT UNSIGNED NULL CHECK (age >= 18),          -- 年龄, 可为空, 有检查约束 (MySQL 8.0.16+)
 6  `status` TINYINT NOT NULL DEFAULT 1,                    -- 状态, 非空, 默认值为 1
 7  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 创建时间, 非空, 默认当前时间
 8  
 9  PRIMARY KEY (`id`),                                     -- 定义主键
10  UNIQUE KEY `uk_user_code` (`user_code`),                 -- 定义唯一约束
11  UNIQUE KEY `uk_email` (`email`)                         -- 定义唯一约束
12) ENGINE=InnoDB COMMENT='用户表';

常见低分回答

1. 混淆主键和唯一约束：“主键就是一个唯一索引”，这种说法不准确。它忽略了主键强制 NOT NULL 的特性以及在 InnoDB 中作为聚簇索引的特殊地位。
2. 误解 UNIQUE 的 NULL 处理：“唯一键的列不能有重复值，所以也不能有多个 NULL”，这个说法在大多数情况下是错误的。在 MySQL InnoDB 和 PostgreSQL 中，UNIQUE 索引允许存储多个 NULL 值。
3. 不知道 CHECK 约束的版本兼容性：“MySQL 可以用 CHECK 约束来校验数据”，如果在面试中不提及其在 8.0.16 版本之前被忽略，会显得对 MySQL 的演进不了解。
4. 滥用 SELECT * 配合 NOT NULL：认为设置了 NOT NULL 就万事大吉，但在代码中依然不处理 NULL 值的可能性，或者在查询时使用 SELECT * 导致 NULL 相关的 bug。例如 WHERE col != 'X' 无法过滤出 col 为 NULL 的行。
5. 将 AUTO_INCREMENT 视为一种约束：AUTO_INCREMENT 是 MySQL 的一种列属性，用于生成值，而约束（Constraint）是用于强制数据规则的。虽然它们经常一起使用，但概念上需要区分。

工程实践与优化

1. 主键选择：代理键 vs. 自然键

   • 强烈推荐代理键（Surrogate Key）：在 InnoDB 表中，应优先使用与业务无关的、单调递增的 BIGINT AUTO_INCREMENT 列作为主键。
   • 原因：InnoDB 是聚簇索引表，使用窄（占用空间小）、单调递增（避免页分裂和数据移动）的主键，可以最大化 B+ 树的插入性能和空间利用率。所有二级索引都会存储主键的值，窄主键能让二级索引更小。
   • 反例：不要使用 UUID 字符串、身份证号等宽、无序的自然键（Natural Key）作为 InnoDB 主键，这会导致严重的页分裂、磁盘碎片和性能下降。

2. AUTO_INCREMENT 的风险与对策

   • 类型耗尽：INT 类型最大值约为 21 亿，在高并发业务下可能很快用完。应优先使用 BIGINT UNSIGNED。
   • ID 空洞：事务回滚、INSERT IGNORE 等操作会消耗自增 ID，但记录未插入，导致 ID 序列不连续。这是正常现象，业务不应依赖 ID 的连续性。
   • 分布式 ID：在分库分表或分布式系统中，AUTO_INCREMENT 无法保证全局唯一。需要采用雪花算法（Snowflake）、UUID、Redis/Zookeeper 生成等分布式 ID 方案。

3. 约束与应用逻辑的平衡

   • 数据库约束是最后防线：应在数据库层面设置核心的、不变的约束（如 PRIMARY KEY, UNIQUE, NOT NULL），保证最终数据的完整性。
   • 应用层负责灵活的校验：复杂的、易变的业务规则（如“VIP 用户才能购买此商品”）应在应用层代码中校验。
   • CHECK 约束的使用：对于非常稳定且重要的域完整性规则（如 price > 0），使用 CHECK 约束（在支持的版本上）是很好的实践，它比应用层校验更可靠。

4. EXPLAIN 中的体现

   • 对主键或唯一键的等值查询（WHERE id = ?），EXPLAIN 的 type 列通常是 const 或 eq_ref，是最高效的查询类型。
   • key 列会显示查询用到的主键 (PRIMARY) 或唯一索引。
   • ref 列会显示 const，表示与一个常量进行比较。
   • rows 预计扫描行数通常为 1。 这些都证明了正确使用约束带来的性能优势。

常见追问与延伸题目

1. 追问：为什么 InnoDB 表推荐使用自增 ID 作为主键，而不是业务 ID（如身份证号）？

   • 回答要点 1 (聚簇索引结构)：InnoDB 是索引组织表，数据本身存储在主键的 B+ 树叶子节点上。如果主键是单调递增的，新数据总是追加到最后，写性能最好。如果主键是无序的（如身份证号、UUID），新数据会插入到 B+ 树的中间，导致频繁的页分裂和数据移动，造成性能抖动和空间浪费。
   • 回答要点 2 (二级索引开销)：所有二级索引的叶子节点都存储了主键的值。如果主键很宽（如身份证号字符串），会导致所有二级索引都变得臃肿，占用更多磁盘空间，查询时 I/O 也更多。自增 BIGINT（8字节）远比字符串高效。

2. 追问：一个表可以没有主键吗？如果可以，会发生什么？

   • 回答要点 1 (可以，但不推荐)：是的，MySQL 允许创建没有显式主键的表。
   • 回答要点 2 (InnoDB 的隐式主键)：如果表没有 PRIMARY KEY，InnoDB 会按以下顺序选择或创建聚簇索引：
     1. 寻找表中的第一个 UNIQUE NOT NULL 的索引作为主键。
     2. 如果找不到，InnoDB 会自动创建一个隐藏的、长度为 6 字节的、名为 GEN_CLUST_INDEX 的聚簇索引，其值（称为 ROW_ID）是单调递增的。
   • 回答要点 3 (坏处)：这个隐藏的 ROW_ID 对用户不可见，无法在 WHERE 条件中高效使用，也无法被其他表作为外键引用。这使得某些行级操作（如 mysqlbinlog 的 row 格式恢复）变得困难和低效。因此，总是为你的 InnoDB 表显式定义一个主键。

3. 追问：UNIQUE 索引和普通索引（INDEX / KEY）有什么核心区别？优化器会如何区别对待它们？

   • 回答要点 1 (核心区别)：
     • 数据约束：UNIQUE 索引强制其包含的列值必须唯一，而普通索引不要求。
     • NULL 值：UNIQUE 索引允许 NULL（通常是多个），普通索引当然也允许。
   • 回答要点 2 (优化器利用)：
     • 查找效率：对于等值查询，当优化器使用 UNIQUE 索引时，它知道一旦找到第一条匹配记录，就可以立即停止扫描，因为不可能有第二条。对于普通索引，即使找到一条，理论上也可能存在重复值，可能需要继续查找（取决于查询需求）。
     • COUNT(*) 优化：在某些场景下，UNIQUE 索引的存在可以帮助优化器更准确地估算基数（Cardinality），但主要优势还是体现在查找时的“提前终止”上。
     • eq_ref vs ref：在 JOIN 查询中，如果连接条件是 UNIQUE 或 PRIMARY KEY，EXPLAIN 的 type 可能是 eq_ref，这是最高效的连接类型之一。而使用普通索引的连接通常是 ref。