mysql字段改为主键

ALTER TABLE users DROP PRIMARY KEY;

ALTER TABLE users MODIFY COLUMN user_id INT NOT NULL;

ALTER TABLE users ADD PRIMARY KEY (user_id);

-- 删除重复数据
DELETE FROM users WHERE user_id IN (
    SELECT user_id FROM (
        SELECT user_id, COUNT(*) as cnt
        FROM users
        GROUP BY user_id
        HAVING cnt > 1
    ) as t
) AND id NOT IN (
    SELECT MIN(id) FROM users GROUP BY user_id HAVING COUNT(*) > 1
);

-- 确保user_id唯一
ALTER TABLE users ADD UNIQUE (user_id);

ALTER TABLE users MODIFY COLUMN user_id INT NOT NULL;

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

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

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