mysql 修改主键

基础概念

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

唯一性：主键的值必须是唯一的，不能有重复。
非空性：主键的值不能为空。
唯一索引：主键字段上会自动创建唯一索引，以提高查询效率。

修改主键的类型

修改现有字段为主键
添加新字段作为主键
删除现有主键并添加新主键

应用场景

在实际应用中，可能需要修改主键的情况包括：

原始主键设计不合理，导致性能问题。
需要更改主键以适应新的业务需求。
数据迁移或系统升级时需要调整主键。

修改主键的步骤

1. 修改现有字段为主键

假设我们有一个表 users，原主键为 id，现在想将 email 字段作为主键：

-- 删除原主键
ALTER TABLE users DROP PRIMARY KEY;

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

2. 添加新字段作为主键

假设我们有一个表 users，原主键为 id，现在想添加一个新的字段 user_id 作为主键：

-- 添加新字段
ALTER TABLE users ADD COLUMN user_id BIGINT AUTO_INCREMENT;

-- 设置新字段为主键
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (user_id);

3. 删除现有主键并添加新主键

假设我们有一个表 users，原主键为 id，现在想删除 id 字段并添加一个新的字段 new_id 作为主键：

-- 删除原主键
ALTER TABLE users DROP PRIMARY KEY;

-- 删除原主键字段
ALTER TABLE users DROP COLUMN id;

-- 添加新字段
ALTER TABLE users ADD COLUMN new_id BIGINT AUTO_INCREMENT;

-- 设置新字段为主键
ALTER TABLE users ADD PRIMARY KEY (new_id);

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

1. 主键冲突

如果在修改主键时遇到主键冲突（例如，新主键字段中有重复值），可以采取以下措施：

-- 查找重复值
SELECT new_id, COUNT(*)
FROM users
GROUP BY new_id
HAVING COUNT(*) > 1;

-- 删除重复值（假设我们保留第一个重复值）
DELETE FROM users
WHERE new_id IN (
    SELECT new_id
    FROM (
        SELECT new_id,
               ROW_NUMBER() OVER (PARTITION BY new_id ORDER BY id) AS rn
        FROM users
    ) t
    WHERE rn > 1
);

2. 外键约束

如果表中有外键引用，修改主键可能会导致外键约束冲突。可以采取以下措施：

-- 删除外键约束
ALTER TABLE related_table DROP FOREIGN KEY fk_name;

-- 修改主键
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (new_id);

-- 重新添加外键约束
ALTER TABLE related_table ADD FOREIGN KEY (fk_column) REFERENCES users(new_id);