MySQL中的表的主键

MySQL 表中的主键详解

在 MySQL 中，主键（Primary Key, PK） 是数据库表中用于唯一标识每一行数据的列（或列的组合）。主键保证数据的唯一性，并且通常是表中查询、更新、删除数据的重要依据。

1. 什么是主键？

主键是一列或多列的组合，它的主要特点如下：

• 唯一性（Uniqueness）：主键列中的数据必须唯一，不能有重复值。
• 非空（NOT NULL）：主键列不能包含 NULL 值。
• 自动索引（Indexing）：MySQL 自动为主键创建 唯一索引，加速查询效率。
• 一张表只能有一个主键，但可以由多个列组合形成 复合主键。

2. 如何定义主键？

（1）创建表时指定主键

在创建表时，可以使用 PRIMARY KEY 关键字指定主键：

CREATE TABLE users (
    id INT PRIMARY KEY,  -- id 作为主键
    name VARCHAR(100),
    email VARCHAR(100)
);

上面的 id 列就是主键，它的值必须唯一，不能为 NULL。

（2）使用 AUTO_INCREMENT 自动递增

如果主键是数值类型，通常会配合 AUTO_INCREMENT 让其自动增长：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自动递增
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE
);

• AUTO_INCREMENT 让 id 从 1 开始自动增长。
• 每插入一条新记录，id 自动加 1，无需手动指定值。

（3）为已有表添加主键

如果表已经创建，但没有主键，可以用 ALTER TABLE 添加：

ALTER TABLE users ADD PRIMARY KEY (id);

（4）创建复合主键（多个列组成主键）

如果一张表的唯一标识由 多个列 组成，可以使用 复合主键：

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)  -- 复合主键
);

• order_id 和 product_id 组合在一起作为主键。
• 单独的 order_id 或 product_id 允许重复，但它们的组合不能重复。

3. 查看主键

（1）查看表的主键信息

可以使用 SHOW CREATE TABLE 命令查看主键：

SHOW CREATE TABLE users\G

或者：

SHOW INDEX FROM users WHERE Key_name = 'PRIMARY';

（2）查看 INFORMATION_SCHEMA 表

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database' AND CONSTRAINT_NAME = 'PRIMARY';

将 your_database 替换为你的数据库名。

4. 删除和修改主键

（1）删除主键

如果表中已经有主键，并且需要删除它，可以使用：

ALTER TABLE users DROP PRIMARY KEY;

注意：

• 如果主键列有 AUTO_INCREMENT，删除主键不会自动删除 AUTO_INCREMENT 约束，需要单独处理：

ALTER TABLE users MODIFY id INT;

（2）修改主键

修改主键通常需要先删除旧主键，再添加新主键：

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (email);  -- 以 email 作为新主键

5. 主键的存储与索引

在 MySQL 中，不同存储引擎对主键的实现方式不同：

（1）InnoDB 存储引擎

• 聚簇索引（Clustered Index）：InnoDB 采用 B+ 树索引 存储数据，主键就是索引，数据按照主键顺序存储在磁盘上。
• 主键索引加速查询，但更新主键的代价较高，因为需要移动数据位置。

（2）MyISAM 存储引擎

• 非聚簇索引：MyISAM 将主键索引和数据存储分开，主键索引只是指向数据的地址。
• 更新主键不会移动数据，但数据存储与索引是分开的，查询可能略慢。

6. 主键 vs 唯一键（UNIQUE）

示例：

CREATE TABLE users (
    id INT PRIMARY KEY,    -- 主键
    email VARCHAR(100) UNIQUE  -- 唯一键
);

• id 是主键，必须唯一，不能为 NULL。
• email 是唯一键，不能重复，但可以有 多个 NULL 值。

7. 什么时候该使用主键？

✅ 应该使用主键的情况

1. 表中的每一行都必须唯一标识（如用户 ID、订单号）。
2. 需要高效查询数据（主键索引加快查找速度）。
3. 使用 InnoDB 时，选择主键可以优化存储和查询。

❌ 不适合主键的情况

1. 主键列的值会频繁变更（影响 InnoDB 存储结构）。
2. 数据量特别大且主键占用空间较大，可以考虑优化主键设计。

8. 结论

• 主键（Primary Key） 是数据库表的唯一标识，必须唯一且非空。
• MySQL 自动为主键创建索引，加速查询。
• 可以使用单列主键或复合主键（多个列组合）。
• AUTO_INCREMENT 常用于主键，使其自动递增。
• InnoDB 采用聚簇索引存储，影响主键的更新效率。
• 唯一键（UNIQUE）和主键类似，但允许 NULL，且可以有多个。

🚀 最佳实践

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    emp_email VARCHAR(100) UNIQUE
);

这样可以保证：

• emp_id 作为主键，唯一且自动增长。
• emp_email 必须唯一，但允许 NULL。

💡 推荐 在 MySQL 数据库设计时，优先选择 INT 或 BIGINT 类型作为主键，并使用 AUTO_INCREMENT，可以优化查询性能和存储效率！