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

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

3.3.2 修改数据表

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

3.3.2.1 添加列（Column）

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

基本语法：

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的表，现在需要添加员工的电子邮件地址作为新列。

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关键字来实现。

基本语法：

ALTER TABLE table_name
DROP COLUMN column_name;

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

示例：

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

ALTER TABLE employees
DROP COLUMN email;

3.3.2.3 修改列属性

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

MODIFY COLUMN 基本语法：

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

CHANGE COLUMN 基本语法：

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：

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

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

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语句。

基本语法：

RENAME TABLE old_table_name TO new_table_name;

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

示例：

将employees表重命名为staff表：

RENAME TABLE employees TO staff;

3.3.2.6 修改表的存储引擎

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

基本语法：

ALTER TABLE table_name ENGINE = engine_name;

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

示例：

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

ALTER TABLE staff ENGINE = InnoDB;

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

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

基本语法：

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：

ALTER TABLE staff CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

总结

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