3.3.2 修改数据表-MySQL从入门到精通(一)

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

### 3.3.2 修改数据表

在MySQL数据库中，数据表是存储数据的基本结构单元，它定义了数据的组织方式和存储规则。随着应用的发展，我们可能需要对已存在的数据表进行修改，以适应新的数据需求或优化数据库性能。MySQL提供了丰富的语句来支持数据表的修改操作，包括但不限于添加列、删除列、修改列属性、重命名列、重命名表、修改表的存储引擎等。本章节将详细介绍如何在MySQL中执行这些修改数据表的操作。

#### 3.3.2.1 添加列（Column）

当需要存储新的数据类型或信息时，可以向表中添加新的列。使用`ALTER TABLE`语句结合`ADD COLUMN`或简单的`ADD`关键字来实现。

**基本语法**：

```sql
ALTER TABLE table_name
ADD column_name datatype [NOT NULL | NULL] [DEFAULT default_value] [AFTER existing_column];
```

- `table_name`：要修改的表名。
- `column_name`：新列的名称。
- `datatype`：新列的数据类型。
- `[NOT NULL | NULL]`：指定列是否允许空值。
- `[DEFAULT default_value]`：为新列指定默认值。
- `[AFTER existing_column]`：可选，指定新列添加的位置，位于哪个已存在的列之后。如果不指定，新列将默认添加到表的末尾。

**示例**：

假设我们有一个名为`employees`的表，现在需要添加员工的电子邮件地址作为新列。

```sql
ALTER TABLE employees
ADD email VARCHAR(255) NOT NULL AFTER name;
```

这条语句在`employees`表中`name`列之后添加了一个名为`email`的新列，数据类型为`VARCHAR(255)`，且不允许为空。

#### 3.3.2.2 删除列（Column）

当某个列不再需要时，可以将其从表中删除以节省空间或简化表结构。使用`ALTER TABLE`语句结合`DROP COLUMN`关键字来实现。

**基本语法**：

```sql
ALTER TABLE table_name
DROP COLUMN column_name;
```

- `table_name`：要修改的表名。
- `column_name`：要删除的列名。

**示例**：

继续上面的例子，如果我们决定不再需要`employees`表中的`email`列，可以执行以下操作：

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

#### 3.3.2.3 修改列属性

随着数据需求的变化，可能需要修改现有列的数据类型、是否允许为空、默认值等属性。使用`ALTER TABLE`语句结合`MODIFY COLUMN`或`CHANGE COLUMN`（后者还能更改列名）来实现。

**MODIFY COLUMN 基本语法**：

```sql
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype [NOT NULL | NULL] [DEFAULT default_value];
```

**CHANGE COLUMN 基本语法**：

```sql
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype [NOT NULL | NULL] [DEFAULT default_value] [AFTER existing_column];
```

**示例**：

如果我们需要将`employees`表中的`salary`列的数据类型从`INT`更改为`DECIMAL(10, 2)`（以支持精确到小数点后两位的薪资表示），可以使用`MODIFY COLUMN`：

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

如果要同时更改列名和数据类型，可以使用`CHANGE COLUMN`：

```sql
ALTER TABLE employees
CHANGE COLUMN salary annual_salary DECIMAL(10, 2) NOT NULL;
```

#### 3.3.2.4 重命名列（Column）

虽然`ALTER TABLE`没有直接的`RENAME COLUMN`选项，但可以通过`CHANGE COLUMN`语句实现列名的更改，如上例所示。

#### 3.3.2.5 重命名表（Table）

当需要更改表的名称时，可以使用`RENAME TABLE`语句。

**基本语法**：

```sql
RENAME TABLE old_table_name TO new_table_name;
```

- `old_table_name`：原表名。
- `new_table_name`：新表名。

**示例**：

将`employees`表重命名为`staff`表：

```sql
RENAME TABLE employees TO staff;
```

#### 3.3.2.6 修改表的存储引擎

MySQL支持多种存储引擎，每种引擎都有其特点和适用场景。在某些情况下，可能需要更改表的存储引擎以适应特定的性能要求或功能需求。

**基本语法**：

```sql
ALTER TABLE table_name ENGINE = engine_name;
```

- `table_name`：要修改的表名。
- `engine_name`：新的存储引擎名称，如`InnoDB`、`MyISAM`等。

**示例**：

将`staff`表的存储引擎更改为`InnoDB`：

```sql
ALTER TABLE staff ENGINE = InnoDB;
```

#### 3.3.2.7 修改表的字符集和校对规则

字符集（Charset）定义了表中使用哪些字符，而校对规则（Collation）定义了字符的比较和排序方式。根据应用需求，可能需要修改表的字符集或校对规则。

**基本语法**：

```sql
ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
```

- `table_name`：要修改的表名。
- `charset_name`：新的字符集名称。
- `[COLLATE collation_name]`：可选，指定新的校对规则。如果不指定，MySQL将使用与该字符集关联的默认校对规则。

**示例**：

将`staff`表的字符集更改为`utf8mb4`，并指定校对规则为`utf8mb4_unicode_ci`：

```sql
ALTER TABLE staff CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

#### 总结

通过本章的学习，我们了解了在MySQL中如何对数据表进行多种修改操作，包括添加列、删除列、修改列属性、重命名列和表、更改表的存储引擎以及修改表的字符集和校对规则。这些操作是数据库管理和维护中不可或缺的一部分，能够帮助我们灵活应对应用需求的变化，优化数据库的结构和性能。在实际应用中，应谨慎进行这些操作，确保不会破坏数据的完整性和一致性。

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

MySQL从入门到精通(四)

MySQL从入门到精通(五)

MySQL 实战 45 讲

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

MySQL必会核心问题

MySQL从入门到精通(三)

MySQL从入门到精通(二)

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

MySQL8.0入门与实践