MySQL 主键（PRIMARY KEY）详解：如何设置、修复错误及选择最佳设计

1. 引言

在数据库设计中，“主键 (PRIMARY KEY)” 是一个基本概念。
主键在确保每个记录的唯一性方面发挥着关键作用，并作为高效数据管理和完整性的基础。

在本文中，我们将从 MySQL 中主键的基础知识开始，解释如何设置它们、解决错误以及选择良好的设计。这个指南旨在对从初学者到中级用户的一系列用户有用。

当您阅读完本指南时，您将能够：

• 理解主键的基本概念。
• 设置主键并解决相关错误。
• 为数据库设计选择最佳的主键。

现在，让我们从主键的基础开始。

2. 什么是主键 (PRIMARY KEY)？

在数据库中，“主键 (PRIMARY KEY)” 是表中使用的最重要的键类型之一，用于保证唯一性。主键作为数据的标识符，唯一标识每一行。

主键的特性

1. 保证唯一性 设置为主键的列（或列）中的值在表内不能重复。
2. 不允许 NULL 值 主键必须始终有值，不允许 NULL。
3. 每个表只有一个 每个表只能有一个主键，不能定义多个主键。但是，您可以通过将多个列组合成单个主键来创建“复合主键”。

为什么需要主键

设置主键提供了诸如以下的好处：

• 确保数据完整性 ：防止插入重复记录。
• 高效搜索 ：提高搜索、更新和删除数据的性能。
• 管理表之间的关系 ：在使用外键时，主键是参考点。

例如，在管理用户信息表的表中，将“用户 ID” 设置为主键允许您唯一标识每个用户。

3. 如何在 MySQL 中设置主键

在 MySQL 中，您可以轻松设置主键 (PRIMARY KEY)。下面，我们通过具体示例解释在创建新表时如何设置主键以及如何向现有表添加主键。

在创建新表时设置主键

要在创建新表时设置主键，请使用 CREATE TABLE 语句。用于主键的列必须具有 NOT NULL 约束。

基本示例

在以下示例中，我们创建一个名为 users 的表，并将 id 列设置为主键。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (id)
);

解释：

• id 列使用 AUTO_INCREMENT 自动生成顺序唯一值。
• PRIMARY KEY (id) 指定 id 列为主键。

将多个列设置为主键 (复合主键)

通过设置复合主键，您可以使用多个列的组合来保证唯一性。

示例：定义复合主键

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

解释：

• order_id 和 product_id 的组合被定义为主键。
• 即使有相同的 order_id ，您也可以注册具有不同 product_id 值 的记录。

如何向现有表添加主键

要在稍后向现有表添加主键，请使用 ALTER TABLE 语句。

示例：在单个列上添加主键

下面是将 id 添加为现有 users 表的主键的示例。

ALTER TABLE users ADD PRIMARY KEY (id);

示例：添加复合主键

使用多个列的组合设置主键时，同样适用相同的方法。

ALTER TABLE orders ADD PRIMARY KEY (order_id, product_id);

如何删除主键

要删除主键，请使用如下所示的 ALTER TABLE 语句。

ALTER TABLE users DROP PRIMARY KEY;

注意：

• 删除主键也会删除基于该主键创建的索引，请谨慎操作。

使用 AUTO_INCREMENT

与主键常一起使用的常见特性是 AUTO_INCREMENT。使用此设置时，每次插入数据时值会自动递增。

示例：带 AUTO_INCREMENT 的主键

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

4. 使用复合主键

复合主键是一种将多个列组合成单一主键以确保数据唯一性的方法。当单个列无法保证唯一性或需要根据特定业务规则识别数据时，它尤其有用。

复合主键的基本概念

使用复合主键，您可以确保两个或多个列的值组合是唯一的。例如，在管理订单数据的表中，将订单号 (order_id) 与产品号 (product_id) 组合，可唯一标识每个订单中的产品。

如何设置复合主键

在创建新表时设置

以下是对 orders 表设置复合主键的示例。

CREATE TABLE orders (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

说明：

• order_id 和 product_id 一起被指定为主键。
• 即使 order_id 相同，也可以插入 product_id 不同的记录。

为已有表添加复合主键

要为已有表添加复合主键，请使用如下的 ALTER TABLE 语句。

ALTER TABLE orders ADD PRIMARY KEY (order_id, product_id);

复合主键的优势

1. 确保唯一性

• 通过组合多个列可以保持唯一性。

1. 符合业务规则

• 例如，在管理订单信息时，单独的 order_id 可能无法保证唯一性，但将 order_id 与 product_id 组合可以维护数据完整性。

1. 简化数据管理

• 使用复合主键有助于防止重复数据并保持结构简洁。

使用复合主键时需注意的事项

1. 性能影响

• 由于复合主键在搜索和插入时需要比较多个列，成本可能高于单列主键。在大型数据库中尤为重要。

1. 索引使用限制

• 设置复合主键后，自动生成的索引覆盖多个列。如果需要对单个列建立索引，则需另行创建索引。

1. 设计更复杂

• 将多个列组合会使设计更为复杂，选择合适的列至关重要。

何时应使用复合主键

• 当唯一性必须由多个条件管理时 示例：订单号和产品号，或项目号和任务号。
• 当单个列无法保证唯一性时 示例：如果一个客户可以购买多种产品，可将客户号和购买日期组合以管理唯一性。

5. 主键与索引的区别

主键（PRIMARY KEY）和索引（INDEX）都是 MySQL 中的重要概念，但它们的作用和目的不同。本节将说明主键与索引的区别以及如何正确使用它们。

主键（PRIMARY KEY）的特性

1. 保证唯一性

• 主键唯一标识表中的每一行。
• 它不允许重复或 NULL 值。

1. 确保数据完整性

• 设置主键有助于维护表的一致性和数据完整性。

1. 自动创建索引

• 当您设置主键时，会自动创建一个索引，从而提高搜索和数据操作的性能。

主键示例

下面是一个将 id 列设置为主键的示例。

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

索引 (INDEX) 的特性

1. 提高搜索速度

• 索引可以加速数据搜索和查询执行。

1. 不保证唯一性

• 常规索引不保证唯一性（但是，UNIQUE 索引可以）。

1. 可以设置在任何列上

• 您可以在主键以外的列上创建索引，以优化特定查询。

索引示例

下面是一个为 username 列添加索引的示例。

CREATE INDEX idx_username ON users (username);

主键与索引

主键与索引之间的关系

• 主键自动具有索引 当您设置主键时，会自动创建一个唯一索引。这意味着主键列可以快速搜索。
• 您也可以为非主键列添加索引 为主键以外的列添加索引可以提高特定查询的性能。

示例：同时使用主键和索引

在以下示例中，主键和索引设置在不同的列上。

CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(100),
    category_id INT,
    PRIMARY KEY (product_id),
    INDEX idx_category (category_id)
);

说明:

• product_id 是主键，保证唯一性并不允许 NULL。
• 在 category_id 上设置索引，以加速基于类别的产品搜索。

如何决定使用主键还是索引

1. 选择主键

• 在唯一标识数据的列上设置主键。
• 每个表都应该只有一个主键。

1. 使用索引

• 为主键以外的频繁搜索列添加索引。
• 示例：在查询中经常使用的列，如 username 或 email 。

1. 注意事项

• 过多的索引可能会降低插入和更新的性能。建议仅设置必要的索引。

6. 主键的重要注意事项

主键 (PRIMARY KEY) 在数据库设计中起着至关重要的作用，但如果设计和管理不当，可能会导致性能和完整性问题。本节解释主键的重要考虑因素和最佳实践。

删除或更改主键的注意事项

如何删除主键

要删除主键，请使用 ALTER TABLE 语句。

ALTER TABLE users DROP PRIMARY KEY;

注意:

1. 删除主键也会删除基于它的索引。
2. 如果外键约束依赖于主键，则删除操作将失败。您必须提前移除外键约束。

如何更改主键

要更改现有主键，请按照以下步骤操作：

1. 删除当前主键。
2. 添加新主键。

示例：

ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (username);

选择主键时的考虑因素

1. 选择正确的列

• 为主键选择一个可以保证唯一性的列。

• 常见选项包括： wp:list /wp:list

  • 带有自动递增 (AUTO_INCREMENT) 的数值类型（示例：id ）
  • 自然键（示例：员工编号或产品代码）

1. 避免频繁更改的列

• 频繁更新主键值可能会对性能产生负面影响。

1. 避免长字符串

• 使用长字符串（例如：200字符的字符串类型）作为主键会降低搜索和操作的效率。

1. 避免包含 NULL 的列

• 因为主键不允许 NULL 值，必须选择始终有值的列。

设置主键时的常见错误及其解决方法

1. 错误：ERROR 1068 (42000): Multiple primary key defined

• 原因 : wp:list /wp:list
  • 当尝试定义多个主键时会出现此情况。
• 解决方案 : wp:list /wp:list
  • 删除重复的主键定义。 sql ALTER TABLE users DROP PRIMARY KEY; ALTER TABLE users ADD PRIMARY KEY (id);

1. 错误：ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

• 原因 : wp:list /wp:list
  • 当外键约束与主键不匹配时会出现此情况。
• 解决方案 : wp:list /wp:list
  • 确保主键和外键之间的数据类型和约束保持一致。

1. 错误：ERROR 1265 (01000): Data truncated for column

• 原因 : wp:list /wp:list
  • 当主键的数据超出预期的类型或长度时会出现此情况。
• 解决方案 : wp:list /wp:list
  • 适当更改主键列的数据类型或修正插入的数据。

设置主键时的最佳实践

1. 优先使用单列

• 使用单列主键（例如：带 AUTO_INCREMENT 的数值类型）可以实现简洁高效的设计。

1. 适当地使用复合主键

• 在组合多个列时，保持列数在必要的最小范围内。

1. 优化主键列的数据类型

• 选择数值类型（例如：INT）或短字符串（例如：VARCHAR(50)）以提升性能。

1. 保持数据一致性

• 为了保持唯一性和完整性，对主键值实施适当的输入验证。

7. 设计主键的最佳实践

在数据库设计中，正确设计主键（PRIMARY KEY）直接影响数据的一致性和性能。本节阐述了有效设计主键的最佳实践。

自然键 vs. 替代键：该选哪一个？

什么是自然键？

自然键使用现有的业务数据作为主键。
示例：员工编号、产品代码、电子邮件地址。

优势：

• 数据含义直观。
• 与现有系统和业务规则高度匹配。

劣势：

• 如果包含长字符串或经常变更的值，会影响性能。
• 存在数据重复的风险。

什么是替代键？

替代键是数据库内部生成的唯一标识符。
示例：使用 AUTO_INCREMENT 的数值 ID。

优势：

• 唯一性始终得到保证。
• 搜索和更新效率高。
• 不会出现值变更的风险。

劣势：

• 由于该值没有人为含义，无法帮助解释数据。

该选哪一个？

• 推荐使用替代键：在大多数情况下，使用替代键（例如：带 AUTO_INCREMENT 的数值 ID）是最佳选择。
• 何时选择自然键：仅在业务规则要求且数据完整性有保障时才使用。

主键性能优化

1. 使用数值类型

• 对主键使用 INT 或 BIGINT 等数值类型可以加快搜索和比较。
• 示例： sql CREATE TABLE customers ( customer_id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (customer_id) );

1. 尽可能保持小尺寸

• 保持主键尺寸小可以降低存储占用并提升索引性能。
• 常用 INT（4 字节），BIGINT（8 字节）通常仅在超大数据集时才需要。

1. 避免更新主键

• 更新主键还会更新相关的索引和外键约束，这可能会导致显著的性能下降。

设计主键和外键

• 主键通常用作外键 (FOREIGN KEY) 的引用目标。因此，在设计外键约束时，主键的唯一性和完整性至关重要。

示例：设计主键和外键

CREATE TABLE customers (
    customer_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (customer_id)
);

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

解释:

• orders 表中的 customer_id 列引用 customers 表中的 customer_id 列。
• 如果不维护主键完整性，外键约束可能会触发错误。

主键设计检查清单

1. 主键是否保证唯一性？
2. 你是否避免将长字符串或频繁变化的列作为主键？
3. 你是否考虑了代理键是否比自然键更合适？
4. 主键列的数据类型是否合适（例如 INT 或 BIGINT ）？
5. 如果使用复合主键，你是否将列数保持在最小？

正确设计主键可以提高数据库性能并保持数据完整性。在下一节中，我们将在“8. 常见问题解答 (FAQ)”中回答读者的常见问题。

8. 常见问题解答 (FAQ)

在这里，我们回答关于 MySQL 主键 (PRIMARY KEY) 的常见问题。我们以易懂的方式解释初级到中级用户经常疑惑的点。

主键和唯一键有什么区别？

主键 (PRIMARY KEY):

• 唯一标识每一行。
• 每个表只能设置一个。
• 不允许 NULL 值。

唯一键 (UNIQUE KEY):

• 保持每行值唯一，但每个表可以设置多个唯一键。
• 允许 NULL 值（但是，NULL 被视为唯一值）。

示例:

CREATE TABLE example (
    id INT NOT NULL AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

• id 列是主键。
• email 列是唯一键。

如果不设置主键会发生什么？

如果不设置主键：

1. 无法保证数据的唯一性。
2. 无法设置外键约束。
3. 搜索和操作表数据可能会变得低效。

推荐:

• 推荐在每个表上设置主键。

何时应该使用复合主键？

当需要用多个列的组合来保证唯一性时，使用复合主键。

示例:

• 在管理订单数据时，用 order_id 和 product_id 的组合确保唯一性。

  CREATE TABLE orders (
      order_id INT,
      product_id INT,
      quantity INT,
      PRIMARY KEY (order_id, product_id)
  );
  

注意:

• 因为复合主键可能会使设计更复杂，你应该仔细考虑。

AUTO_INCREMENT 是否必需？

它不是必需的，但在这些情况下很有用：

• 当你想为主键列自动生成值时。
• 当你需要一个保证唯一性的数字主键时。

示例:

CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (user_id)
);

如何更改主键值？

你可以直接更改主键值，但请注意以下事项：

1. 如果存在外键约束，相关数据可能会受到影响。
2. 更新成本可能很高，并影响性能。

示例：

UPDATE users SET user_id = 10 WHERE user_id = 1;

主键如何影响性能？

好处：

• 主键会自动创建索引，从而加快搜索和排序。

注意：

• 如果主键的数据类型或长度不合适，性能可能会下降。

主键的最佳数据类型是什么？

• 数值类型（INT 或 BIGINT） 是最推荐的。
• 它们体积小，搜索速度快。
• 可以配合自增使用（AUTO_INCREMENT）。
• 如果使用字符串，推荐使用 VARCHAR，但要保持其长度短。

可以设置多个主键吗？

一个表只能有 一个主键。不过，你可以通过组合多个列来创建 复合主键。

当删除或更改主键时出现错误该怎么办？

常见错误：

• 由于外键约束，无法删除或更改主键。

解决方案：

1. 删除外键约束。
2. 更改主键，然后重新添加外键约束。

示例：

ALTER TABLE orders DROP FOREIGN KEY fk_customer_id;
ALTER TABLE orders DROP PRIMARY KEY;
ALTER TABLE orders ADD PRIMARY KEY (new_id);

9. 结论

本文详细阐述了 MySQL 主键（PRIMARY KEY），包括基础知识、设置方法、重要注意事项、最佳实践以及常见问答。主键是数据库设计的关键，正确设置主键对数据唯一性和性能有着极大的影响。

回顾主键的重要性

1. 保证数据唯一性 主键唯一标识表中的每一行，防止数据重复。
2. 提升搜索和操作效率 由于主键自动拥有索引，搜索和数据操作会更快。
3. 外键约束的基础 设置主键后，可通过外键约束进行完整性管理。

关键设计要点

• 为主键选择合适的列 关键是将主键设置在能够保证唯一且不经常变更的列上。
• 谨慎使用复合主键 仅在必要时使用复合主键，避免设计过于复杂。
• 选择合适的数据类型 使用数值类型如 INT 或 BIGINT 有助于实现高效性能。

接下来要学习的主题

深入了解主键后，学习以下主题将帮助你进行更高效的数据库设计：

1. 设置外键（FOREIGN KEY） 学习如何在数据之间建立关系并维护完整性。
2. 索引优化 学习通过在非主键列上添加索引来提升查询性能的技巧。
3. 规范化与反规范化 学习数据库设计基础，如规范化，并在需要时考虑反规范化以提升性能。

最后说明

正确设计 MySQL 主键是提升整个数据库效率和可靠性的第一步。请以本文为参考，提升设置和管理合适主键的技能。拥有稳健的主键设计，数据库开发与运维将更加顺畅，从而提升整体生产力。

本文到此结束。请将其作为指南，在实际项目中尝试设计有效的主键。