理解 MySQL 中 `ALTER TABLE` 的行为：为什么某些约束在修改列时消失？

在使用 MySQL 进行数据库操作时，我们经常需要修改表的结构，例如更改列的数据类型或添加新的列。在这个过程中，我们可能会遇到一些令人困惑的问题，例如为什么某些约束在使用 ALTER TABLE ... MODIFY COLUMN 修改列时会消失，而另一些则不会。本文将详细解释这一现象，并提供确保约束保留的方法。

创建表和定义约束

假设我们有以下两个表：dept 表和 employees 表。

创建 dept 表

CREATE TABLE dept
(
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(15) NOT NULL UNIQUE
);

创建 employees 表

CREATE TABLE employees
(
    id           INT PRIMARY KEY,
    name         VARCHAR(50) NOT NULL,
    age          INT CHECK (age BETWEEN 1 AND 150),
    phone        VARCHAR(11) UNIQUE,
    id_increment INT AUTO_INCREMENT,
    dept_id      INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id)
);

查看 employees 表的结构和约束

通过以下 SQL 语句，我们可以查看 employees 表的结构和定义的约束：

SHOW CREATE TABLE employees;

结果如下:

CREATE TABLE `employees` (
  `id` int NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `age` mediumtext,
  `phone` varchar(12) DEFAULT NULL,
  `describe` varchar(300) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`),
  CONSTRAINT `employees_chk_1` CHECK ((`age` between 1 and 150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

结果显示表结构和约束正确定义。接下来，我们尝试修改表结构。

修改表结构

我们希望修改 employees 表的几个列的数据类型：

ALTER TABLE employees
    MODIFY COLUMN id INT,
    MODIFY COLUMN name VARCHAR(60),
    MODIFY COLUMN age LONG,
    MODIFY COLUMN phone VARCHAR(12),
    MODIFY COLUMN `describe` VARCHAR(300),
    MODIFY COLUMN dept_id INT;

再次查看表结构：

SHOW CREATE TABLE employees;

结果如下:

CREATE TABLE `employees` (
  `id` int NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `age` mediumtext,
  `phone` varchar(12) DEFAULT NULL,
  `describe` varchar(300) DEFAULT NULL,
  `dept_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`),
  KEY `dept_id` (`dept_id`),
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`),
  CONSTRAINT `employees_chk_1` CHECK ((`age` between 1 and 150))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我们发现 NOT NULL 和 DEFAULT，在修改后消失了。

原因分析

MODIFY COLUMN 的行为

ALTER TABLE ... MODIFY COLUMN 语句实际上是重新定义该列。当你修改列的数据类型时，如果没有重新指定列的约束条件（如 NOT NULL、DEFAULT 等），这些约束条件会被移除。这是因为 MySQL 将修改视为对列的重新定义，而不是增量修改。

约束的保留和移除

• NOT NULL 和 DEFAULT 约束：这些是列级约束，需要在 MODIFY COLUMN 语句中重新指定。如果不重新指定，这些约束会被移除。

• UNIQUE 和 PRIMARY KEY 约束：这些通常创建索引，可能跨多个列。在修改单个列时，这些约束不会被移除，因为它们的定义和索引涉及表级别。

• CHECK 约束：CHECK 约束可以是列级或表级约束。列级 CHECK 约束只应用于单个列，而表级 CHECK 约束可以应用于多个列。MySQL 可能会保留 CHECK 约束，但这取决于具体的 MySQL 版本和实现方式。

• FOREIGN KEY 约束：外键约束涉及引用完整性，通常不会在修改单个列时被移除。这是因为外键约束通常在表级别定义，并与引用的表相关联。

CHECK 约束的示例

列级 CHECK 约束

列级 CHECK 约束只应用于单个列。例如：

CREATE TABLE employees (
    age INT CHECK (age BETWEEN 1 AND 150)
);

表级 CHECK 约束

表级 CHECK 约束可以应用于多个列。例如：

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    birth_date DATE,
    hire_date DATE,
    CHECK (hire_date > birth_date)
);

在这个示例中，CHECK (hire_date > birth_date) 是一个表级约束，因为它涉及多个列。

修改列时保留约束

为了确保所有约束都被保留，建议在 MODIFY COLUMN 时重新指定所有约束条件。例如：

ALTER TABLE employees
    MODIFY COLUMN id INT PRIMARY KEY,
    MODIFY COLUMN name VARCHAR(60) NOT NULL,
    MODIFY COLUMN age LONG CHECK (age BETWEEN 1 AND 150),
    MODIFY COLUMN phone VARCHAR(12) UNIQUE,
    MODIFY COLUMN `describe` VARCHAR(300),
    MODIFY COLUMN dept_id INT,
    ADD CONSTRAINT chk_hire_date CHECK (hire_date > birth_date),
    ADD CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES dept(id);

这样可以确保所有原有的约束条件都被重新应用在修改后的列定义上。

总结

在使用 MySQL 的 ALTER TABLE ... MODIFY COLUMN 语句修改列时，需要注意约束条件的保留问题。NOT NULL 和 DEFAULT 约束通常需要在修改时重新指定，而 UNIQUE、PRIMARY KEY 和 FOREIGN KEY 等表级约束通常不会在修改单个列时被移除，因为它们涉及表级别的索引和引用完整性。CHECK 约束可以是列级或表级，具体取决于它的定义。为了确保所有约束条件都正确应用，建议在修改列定义时明确重新指定所有需要的约束条件。