mysql 修改主键

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

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

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

-- 设置新字段为主键
ALTER TABLE users DROP PRIMARY KEY;
ALTER TABLE users ADD PRIMARY KEY (user_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);

-- 查找重复值
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
);

-- 删除外键约束
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);