mysql新增主键列

基础概念

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

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

类型

MySQL中的主键可以是以下几种类型：

单字段主键：使用单个字段作为主键。
复合主键：使用多个字段组合成一个主键。
自增主键：使用自增的整数作为主键，通常用于标识新插入的记录。

应用场景

主键广泛应用于各种数据库设计中，特别是在需要确保数据唯一性和完整性的场景中。例如：

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

新增主键列的步骤

假设我们有一个名为students的表，现在需要新增一个主键列student_id。

步骤1：创建表（如果表不存在）

CREATE TABLE students (
    id INT AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id)
);

步骤2：新增主键列

如果表已经存在，可以使用ALTER TABLE语句来新增主键列：

ALTER TABLE students ADD COLUMN student_id INT AUTO_INCREMENT;
ALTER TABLE students ADD PRIMARY KEY (student_id);

步骤3：删除旧的主键列（可选）

如果需要删除旧的主键列并使用新的主键列，可以先删除旧的主键列，再添加新的主键列：

ALTER TABLE students DROP PRIMARY KEY;
ALTER TABLE students ADD COLUMN student_id INT AUTO_INCREMENT;
ALTER TABLE students ADD PRIMARY KEY (student_id);

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

问题1：主键冲突

原因：新增的主键列值与现有数据冲突。

解决方法：确保新增的主键列值在整个表中是唯一的。可以使用AUTO_INCREMENT自动生成唯一值。

问题2：表锁定

原因：在修改表结构时，可能会导致表被锁定，影响其他操作。

解决方法：在低峰期进行表结构的修改，或者使用在线DDL（Data Definition Language）工具，如MySQL 5.6及以上版本支持的ALGORITHM=INPLACE选项。

ALTER TABLE students ADD COLUMN student_id INT AUTO_INCREMENT, ALGORITHM=INPLACE;
ALTER TABLE students ADD PRIMARY KEY (student_id), ALGORITHM=INPLACE;

问题3：数据迁移

原因：新增主键列可能需要迁移现有数据。

解决方法：在新增主键列之前，可以先备份数据，然后进行数据迁移。

-- 备份数据
CREATE TABLE students_backup AS SELECT * FROM students;

-- 新增主键列并迁移数据
ALTER TABLE students_backup ADD COLUMN student_id INT AUTO_INCREMENT;
ALTER TABLE students_backup ADD PRIMARY KEY (student_id);
INSERT INTO students_backup SELECT * FROM students;

-- 删除原表并重命名备份表
DROP TABLE students;
RENAME TABLE students_backup TO students;