mysql 已有表中增加主键

在MySQL中，为一个已存在的表添加主键是一个常见的操作，尤其是在表结构设计初期未考虑主键或者需要更改主键时。以下是关于这个问题的基础概念、优势、类型、应用场景以及可能遇到的问题和解决方案。

基础概念

主键（Primary Key）是数据库表中用于唯一标识每一条记录的一个或多个字段。它具有以下特性：

• 唯一性：主键的值必须是唯一的，不能有重复。
• 非空性：主键的值不能为空。
• 唯一索引：主键字段上会自动创建唯一索引。

优势

• 唯一性保证：主键确保了表中每一行数据的唯一性。
• 快速查找：主键上的索引可以加快数据的检索速度。
• 数据完整性：通过主键可以维护表与表之间的关系，保证数据的完整性。

类型

主键可以是单个字段，也可以是多个字段的组合。常见的主键类型包括：

• 单字段主键：使用一个字段作为主键。
• 复合主键：使用多个字段的组合作为主键。

应用场景

• 用户表：每个用户有一个唯一的用户ID。
• 订单表：每个订单有一个唯一的订单号。
• 产品表：每个产品有一个唯一的产品编号。

增加主键的操作步骤

假设我们有一个已存在的表 users，结构如下：

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(50)
);

我们希望将 id 字段设置为主键。可以使用以下SQL语句：

ALTER TABLE users ADD PRIMARY KEY (id);

可能遇到的问题及解决方案

问题1：主键冲突

如果 id 字段中已经存在重复的值，添加主键时会报错。

解决方案：

1. 删除重复的记录。
2. 使用 ALTER IGNORE 语句（不推荐，因为会丢失数据）。
3. 先删除主键，再重新添加。

-- 删除重复记录
DELETE FROM users WHERE id IN (
    SELECT id FROM (
        SELECT id, COUNT(*) as cnt
        FROM users
        GROUP BY id
        HAVING cnt > 1
    ) as t
) AND ctid NOT IN (
    SELECT min(ctid)
    FROM users
    GROUP BY id
    HAVING count(*) > 1
);

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

问题2：表中已有数据

如果表中已经有大量数据，添加主键可能会比较慢。

解决方案：

1. 在低峰期进行操作。
2. 使用 ALTER TABLE 语句时，可以考虑使用 ALGORITHM=INPLACE 和 LOCK=NONE 选项（如果数据库支持）。

ALTER TABLE users ADD PRIMARY KEY (id) ALGORITHM=INPLACE LOCK=NONE;

参考链接

• MySQL官方文档 - ALTER TABLE
• MySQL官方文档 - 主键

通过以上步骤和解决方案，可以有效地在已有的MySQL表中增加主键，并解决可能遇到的问题。