MySQL ALTER TABLE语句：修改数据表（非常详细）

修改数据表指的是修改 MySQL 数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改数据表。

常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加或删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。

修改表名

MySQL 是通过 ALTER TABLE 语句来实现表名的修改的，具体的语法格式如下：

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

其中，TO 为可选参数，使用与否均不影响结果。

【实例】将数据表 tb_dept3 改名为 tb_deptment3。
1) 执行修改表名操作之前，使用 SHOW TABLES 查看数据库中所有的表。

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test_db  |
+--------------------+
| tb_dept1            |
| tb_dept2            |
| tb_dept3            |
...省略部分内容

2) 使用 ALTER TABLE 将表 tb_dept3 改名为 tb_deptment3，SQL 语句如下：

ALTER TABLE tb_dept3 RENAME tb_deptment3;

3) 检验表 tb_dept3 是否改名成功。使用 SHOW TABLES 查看数据库中的表，结果如下：

mysql> SHOW TABLES;
+---------------------+
| Tables_in_test_db   |
+---------------------+
| tb_dept             |
| tb_dept2            |
| tb_deptment3        |
...省略部分内容

经过比较可以看到，数据表列表中已经有了名称为 tb_deptment3 的表，表名修改成功。

在修改表名称时，可以使用 DESC 命令查看表修改前后两张表的结构，修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构必然相同。

修改字段的数据类型

修改字段的数据类型，就是把字段的数据类型转换成另一种数据类型。

在 MySQL 中，修改字段数据类型的语法格式如下：

ALTER TABLE <表名> MODIFY <字段名>  <数据类型>

• “表名”指要修改数据类型的字段所在表的名称；
• “字段名”指需要修改的字段；
• “数据类型”指修改后的字段的新数据类型。

【实例】将数据表 tb_dept1 中 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)。
1) 执行修改表名操作之前，使用 DESC 查看 tb_dept1 表结构，结果如下：

mysql> DESC tb_dept1;
+----------+-------------+------+-----+-------------------+-------+
| Field    | Type        | Null | Key | Default           | Extra |
+----------+-------------+------+-----+-------------------+-------+
| id       | int         | NO   | PRI | NULL              |       |
| name     | varchar(22) | YES  |     | NULL              |       |
| location | varchar(50) | YES  |     | NULL              |       |
+----------+-------------+------+-----+-------------------+-------+

可以看到，name 字段的数据类型为 VARCHAR(22)。

2) 修改数据类型，SQL 语句如下：

ALTER TABLE tb_dept1 MODIFY name VARCHAR(30);

3) 再次使用 DESC 查看 tb_dept1 表结构，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | NO   | PRI | NULL    |       |
| name     | varchar(30)  | YES  |     | NULL    |       |
| location | varchar(50)  | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

可以发现，表 tb_dept1 中 name 字段的数据类型已经修改成了 VARCHAR(30)，修改成功。

修改字段名

在 MySQL 中，修改表字段名的语法格式如下：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

• “旧字段名”指修改前的字段名；
• “新字段名”指修改后的字段名；
• “新数据类型”指修改后的数据类型，如果不需要修改字段的数据类型，将新数据类型设置成与原来一样即可，但数据类型不能为空。

【实例】将数据表 tb_dept1 中的 location 字段名称改为 loc，数据类型保持不变，SQL 语句如下：

ALTER TABLE tb_dept1 CHANGE location loc VARCHAR(50);

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+-------+--------------+------+-----+-------------------+-------+
| Field | Type         | Null | Key | Default           | Extra |
+-------+--------------+------+-----+-------------------+-------+
| id    | int          | NO   | PRI | NULL              |       |
| name  | varchar(30)  | YES  |     | NULL              |       |
| loc   | varchar(50)  | YES  |     | NULL              |       |
+-------+--------------+------+-----+-------------------+-------+

可以发现该字段的名称已经修改成功。

【实例】将数据表 tb_dept1 中的 loc 字段名称改为 location，同时将数据类型变为 VARCHAR(60)，SQL 语句如下：

ALTER TABLE tb_dept1 CHANGE loc location VARCHAR(60);

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以发现该字段的名称和数据类型均已经修改成功。

CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。由于不同类型的数据在机器中存储的方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此当数据表中已经有数据时，不要轻易修改数据类型。

添加字段

随着业务需求的变化，可能需要在已经存在的表中添加新的字段。一个完整字段包括字段名、数据类型、完整性约束。

添加字段的语法格式如下：

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在字段名];

• “新字段名”为需要添加的字段的名称；
• “FIRST”为可选参数，其作用是将新添加的字段设置为表的第一个字段；
• “AFTER”为可选参数，其作用是将新添加的字段添加到指定的“已存在字段名”的后面。

“FIRST”或“AFTER已存在字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段放置在数据表的最后一列。

1) 添加无完整性约束条件的字段

【实例】在数据表 tb_dept1 中添加一个没有完整性约束的 INT 类型的字段 managerId（部门经理编号），SQL 语句如下：

ALTER TABLE tb_dept1 ADD managerId INT;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+------------+--------------+------+-----+-------------------+-------+
| Field      | Type         | Null | Key | Default           | Extra |
+------------+--------------+------+-----+-------------------+-------+
| id         | int          | NO   | PRI | NULL              |       |
| name       | varchar(30)  | YES  |     | NULL              |       |
| location   | varchar(60)  | YES  |     | NULL              |       |
| managerId  | int          | YES  |     | NULL              |       |
+------------+--------------+------+-----+-------------------+-------+

可以发现，在表的最后一列添加了一个名为“managerId”的INT类型的字段。

2) 添加有完整性约束条件的字段

【实例】在数据表 tb_dept1 中添加一个不能为空的 VARCHAR(12) 类型的字段 column1，SQL 语句如下：

ALTER TABLE tb_dept1 ADD column1 VARCHAR(12) not null;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
| column1  | varchar(12)  | NO   |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以发现，在表的最后一列添加了一个名为“column1”的 VARCHAR(12) 类型且不为空的字段。

3) 在表的第一列添加一个字段

【实例】在数据表 tb_dept1 中添加一个 INT 类型的字段 column2，SQL 语句如下：

ALTER TABLE tb_dept1 ADD column2 INT FIRST;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| column2  | int          | YES  |     | NULL              |       |
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
| column1  | varchar(12)  | NO   |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以发现，在表的第一列中添加了一个名为“column2”的 INT 类型字段。

4) 在表的指定列之后添加一个字段

【实例】在数据表 tb_dept1 的 name 列后添加一个 INT 类型的字段 column3，SQL 语句如下：

ALTER TABLE tb_dept1 ADD column3 INT AFTER name;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| column2  | int          | YES  |     | NULL              |       |
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| column3  | int          | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
| column1  | varchar(12)  | NO   |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以看到，tb_dept1 表中增加了一个名称为 column3 的字段，其位置在指定的 name 字段后面，添加字段成功。

删除字段

删除字段是将数据表中的某个字段从表中移除，语法规则如下：

ALTER TABLE <表名> DROP <字段名>;

其中“字段名”指需要从表中删除的字段的名称。

【实例】删除数据表 tb_dept1 中的 column2 字段。
1) 删除字段之前，使用 DESC 查看 tb_dept1 表结构，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| column2  | int          | YES  |     | NULL              |       |
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| column3  | int          | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
| column1  | varchar(12)  | NO   |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

2) 删除 column2 字段，SQL 语句如下：

ALTER TABLE tb_dept1 DROP column2;

3) 再次使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| column3  | int          | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
| column1  | varchar(12)  | NO   |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以看到，tb_dept1 表中已经不存在名称为 column2 的字段，说明删除字段成功。

修改字段的排列位置

对于一张数据表来说，在创建的时候，表中字段的排列顺序就已经确定了，但表的结构并不是完全不可以改变的，可以通过 ALTER TABLE 来改变表中字段的相对位置。

语法规则如下：

ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST|AFTER <字段2>;

• “字段1”指要修改位置的字段；
• “数据类型”指“字段1”的数据类型；
• “FIRST”为可选参数，指将“字段1”修改为表的第一个字段；
• “AFTER字段2”指将“字段1”插在“字段2”后面。

1) 修改字段为表的第一个字段

【实例】将数据表 tb_deptl 中的 column1 字段修改为表的第一个字段，SQL 语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) FIRST;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+-------+-----+-------------------+-------+
| Field    | Type         | Null  | Key | Default           | Extra |
+----------+--------------+-------+-----+-------------------+-------+
| column1  | varchar(12)  | YES   |     | NULL              |       |
| id       | int          | NO    | PRI | NULL              |       |
| name     | varchar(30)  | YES   |     | NULL              |       |
| column3  | int          | YES   |     | NULL              |       |
| location | varchar(60)  | YES   |     | NULL              |       |
| managerId| int          | YES   |     | NULL              |       |
+----------+--------------+-------+-----+-------------------+-------+

可以发现字段 column1 已经被移至表的第一列。

2) 修改字段到表的指定列之后

【实例】将数据表 tb_dept1 中的 column1 字段插到 location 字段后面，SQL 语句如下：

ALTER TABLE tb_dept1 MODIFY column1 VARCHAR(12) AFTER location;

使用 DESC 查看表 tb_dept1，结果如下：

mysql> DESC tb_dept1;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| id       | int          | NO   | PRI | NULL              |       |
| name     | varchar(30)  | YES  |     | NULL              |       |
| column3  | int          | YES  |     | NULL              |       |
| location | varchar(60)  | YES  |     | NULL              |       |
| column1  | varchar(12)  | YES  |     | NULL              |       |
| managerId| int          | YES  |     | NULL              |       |
+----------+--------------+------+-----+-------------------+-------+

可以看到，表 tb_dept1 中的字段 column1 已经被移至 location 字段之后。

删除表的外键约束

对于数据库中定义的外键，如果不再需要，可以将其删除。外键一旦删除，就会解除主表和从表间的关联关系。

在 MySQL 中删除外键的语法格式如下：

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

其中，“外键约束名”指在定义表时CONSTRAINT关键字后面的参数。

【实例】删除数据表 tb_emp9 中的外键约束。
1) 创建表 tb_emp9，其外键 deptId 关联 tb_dept1 表的主键 id，SQL 语句如下：

CREATE TABLE tb_emp9
(
id       INT PRIMARY KEY,
name    VARCHAR(25),
deptId   INT,
salary   FLOAT,
CONSTRAINT fk_emp_dept  FOREIGN KEY (deptId) REFERENCES tb_dept1(id)
);

2) 使用 SHOW CREATE TABLE 查看表 tb_emp9 的结构，结果如下：

mysql> SHOW CREATE TABLE tb_emp9 \G
*** 1. row ***
       Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
  `id` INT NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` INT DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptId`),
  CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

可以看到，已经成功添加了表的外键。

3) 删除外键约束，SQL 语句如下：

ALTER TABLE tb_emp9 DROP FOREIGN KEY fk_emp_dept;

上述语句执行完毕之后，将删除表 tb_emp9 的外键约束。

4) 使用 SHOW CREATE TABLE 再次查看表 tb_emp9 结构，结果如下：

mysql> SHOW CREATE TABLE tb_emp9 \G
*** 1. row ***
       Table: tb_emp9
Create Table: CREATE TABLE `tb_emp9` (
  `id` INT NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` INT DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_dept` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

可以看到，表 tb_emp9 中已经不存在 FOREIGN KEY，原有的名称为 fk_emp_dept 的外键约束已被删除。