6.3 修改表结构（ALTER TABLE语句）-MySQL从入门到精通(二)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(二)

### 6.3 修改表结构（ALTER TABLE语句）

在数据库设计和管理的过程中，随着业务需求的变化，经常需要对已存在的表结构进行修改。MySQL 提供了强大的 `ALTER TABLE` 语句，允许我们添加、删除或修改表中的列（字段）、索引、外键约束等。本章节将详细探讨 `ALTER TABLE` 语句的使用，包括其语法、常见操作及注意事项，帮助读者从入门到精通如何高效地修改 MySQL 表结构。

#### 6.3.1 `ALTER TABLE` 语句基础

`ALTER TABLE` 语句的基本语法如下：

```sql
ALTER TABLE table_name
[MODIFY COLUMN column_definition [FIRST | AFTER another_column]]
[ADD column_definition [FIRST | AFTER another_column]]
[DROP COLUMN column_name]
[RENAME COLUMN old_column_name TO new_column_name]
[CHANGE COLUMN old_column_name new_column_definition [FIRST | AFTER another_column]]
[ADD INDEX index_name (column_name)]
[DROP INDEX index_name]
[ADD CONSTRAINT constraint_name ...]
[DROP CONSTRAINT constraint_name]
[ALTER COLUMN column_name SET DEFAULT value]
[DROP DEFAULT column_name]
[MODIFY COLUMN column_name datatype]
...
```

注意：并非所有数据库系统都支持上述所有选项，但 MySQL 支持大部分功能。具体使用时，请参考 MySQL 的官方文档。

#### 6.3.2 添加列

要向表中添加新列，可以使用 `ADD COLUMN` 关键字。例如，假设我们有一个名为 `employees` 的表，现在想为其添加一个 `email` 字段：

```sql
ALTER TABLE employees
ADD COLUMN email VARCHAR(255);
```

如果希望新列位于特定列之后，可以使用 `AFTER column_name` 语法。

#### 6.3.3 删除列

要删除表中的列，可以使用 `DROP COLUMN` 关键字。例如，如果我们不再需要 `employees` 表中的 `email` 字段，可以执行：

```sql
ALTER TABLE employees
DROP COLUMN email;
```

删除列是永久性的，操作前请确保已做好数据备份。

#### 6.3.4 修改列的数据类型或属性

使用 `MODIFY COLUMN` 或 `CHANGE COLUMN` 可以修改现有列的数据类型、长度、默认值等属性。`MODIFY COLUMN` 直接修改列定义，而 `CHANGE COLUMN` 则允许同时更改列名和数据类型。

- 修改数据类型：

```sql
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(10, 2);
```

- 同时修改列名和数据类型：

```sql
ALTER TABLE employees
CHANGE COLUMN emp_id id INT AUTO_INCREMENT;
```

注意，当使用 `CHANGE COLUMN` 时，如果列名未变，也应明确指定新列名（即原列名）。

#### 6.3.5 重命名列

虽然 `ALTER TABLE` 没有直接的 `RENAME COLUMN` 语法，但 MySQL 提供了 `CHANGE COLUMN` 作为重命名列的方法，如上例所示。

#### 6.3.6 修改列的默认值

使用 `ALTER COLUMN ... SET DEFAULT` 可以为列设置新的默认值。如果之前已设置默认值，且想移除它，可以使用 `DROP DEFAULT`。

```sql
-- 设置默认值
ALTER TABLE employees
ALTER COLUMN department_id SET DEFAULT 1;

-- 移除默认值
ALTER TABLE employees
ALTER COLUMN department_id DROP DEFAULT;
```

#### 6.3.7 添加和删除索引

索引对于提高查询效率至关重要。`ALTER TABLE` 允许我们为表添加或删除索引。

- 添加索引：

```sql
ALTER TABLE employees
ADD INDEX idx_name (last_name);
```

- 删除索引：

```sql
ALTER TABLE employees
DROP INDEX idx_name;
```

注意：在删除索引前，请确保该索引不再被使用，或已有其他索引可以替代其功能。

#### 6.3.8 添加和删除外键约束

外键约束用于维护两个表之间的数据一致性。虽然 `ALTER TABLE` 允许添加和删除列，但直接添加或删除外键约束的语法依赖于具体数据库系统。在 MySQL 中，通常通过 `ADD CONSTRAINT` 和 `DROP CONSTRAINT` 来操作，但需注意，MySQL 早期版本可能不支持直接删除约束，需要通过删除并重新创建表或修改表的定义来实现。

#### 6.3.9 注意事项

- 在执行 `ALTER TABLE` 操作时，特别是修改大型表的结构，可能会对数据库性能产生显著影响。建议在低峰时段进行此类操作，并考虑使用在线DDL（Data Definition Language）工具以减少停机时间。
- 在修改表结构之前，务必做好数据备份，以防万一操作不当导致数据丢失。
- 当修改列的数据类型或结构时，确保新类型与现有数据兼容，否则可能会导致数据转换错误。
- 索引的添加和删除应基于实际的查询需求和数据量来决定，过多或不必要的索引可能会降低写入性能。

#### 6.3.10 结论

`ALTER TABLE` 语句是 MySQL 中用于修改表结构的重要工具，它提供了丰富的选项来满足不同的数据库设计和管理需求。通过掌握 `ALTER TABLE` 的使用，可以有效地应对业务需求的变化，优化数据库性能，确保数据的准确性和完整性。然而，由于其对数据库性能的潜在影响，建议在实际操作中谨慎行事，确保每一步操作都经过充分的考虑和测试。

该分类下的相关小册推荐：

MySQL从入门到精通(五)

MySQL从入门到精通(四)

MySQL从入门到精通(三)

SQL零基础到熟练应用(增删改查)

MySQL8.0入门与实践

MySQL 实战 45 讲

MySQL从入门到精通(一)

MySQL必会核心问题

细说MySQL(零基础到高级应用)