【重学 MySQL】四十七、表的操作技巧——修改、重命名、删除与清空

修改表
- 添加字段
- - 语法
  - 示例
  - 注意事项
- 删除字段
- - 语法
  - 示例
- 修改字段
- - 使用 MODIFY COLUMN
  - - 语法
    - 示例
  - 使用 CHANGE COLUMN
  - - 语法
    - 示例
重命名表
- 语法
- 示例
删除表
- - 语法
  - 示例
清空表
- 使用 `TRUNCATE TABLE`
- 使用 `DELETE FROM`
- 对比 `TRUNCATE TABLE` 和 `DELETE FROM`
- - 操作方式
  - 性能
  - 事务处理
  - 触发器和外键约束
  - 自增主键
  - 使用建议
注意事项
总结

在这里插入图片描述

修改表

在MySQL中，我们经常需要对已存在的表进行修改，以满足不断变化的数据存储需求。这通常包括添加新列、删除现有列、修改列的数据类型或约束条件等操作。

添加字段

在MySQL中，为已存在的表添加字段（也称为列）是一个常见的操作，这通常是为了满足新的数据存储需求或适应业务逻辑的变化。使用ALTER TABLE语句可以方便地实现这一操作。

语法

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints] [FIRST | AFTER existing_column];

table_name：要添加字段的表的名称。
column_name：新字段的名称。
datatype：新字段的数据类型，如INT、VARCHAR、DATE等。
constraints：对新字段的约束条件，如NOT NULL、DEFAULT值、UNIQUE等（可选）。
[FIRST | AFTER existing_column]：
- FIRST：将新字段添加到表的开头。
- AFTER existing_column：将新字段添加到指定字段 existing_column 之后。如果省略此部分，新字段将默认添加到表的末尾。

示例

将新字段添加到表末尾（默认行为）：

ALTER TABLE employees  
ADD COLUMN phone_number VARCHAR(20);

将新字段添加到表开头：

ALTER TABLE employees  
ADD COLUMN employee_id INT AUTO_INCREMENT FIRST,  
ADD PRIMARY KEY (employee_id); -- 假设这是主键字段

注意：在添加主键字段时，通常也会同时设置 AUTO_INCREMENT 属性和 PRIMARY KEY 约束。

将新字段添加到特定字段之后：
```
ALTER TABLE employees  
ADD COLUMN hire_date DATE AFTER last_name;
```
在这个例子中，hire_date 字段将被添加到 last_name 字段之后。

注意事项

字段位置：在MySQL中，默认情况下新添加的字段会被放置在表的最后。如果需要将新字段添加到特定位置，可以使用AFTER column_name子句来指定位置。例如，将phone_number字段添加到email字段之后：
```
ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20) AFTER email;
```
或者，如果希望将新字段添加到表的最前面，可以使用FIRST关键字：
```
ALTER TABLE employees ADD COLUMN phone_number VARCHAR(20) FIRST;
```
表锁定：在执行ALTER TABLE操作时，MySQL可能会锁定表，这会导致在该表上的其他操作（如查询、更新等）被阻塞，直到ALTER TABLE操作完成。因此，在执行此类操作时应尽量选择在系统负载较低的时候进行。
备份数据：虽然ALTER TABLE操作通常是安全的，但在执行任何可能影响表结构的操作之前，始终建议备份数据以防止意外情况发生。
权限要求：执行ALTER TABLE操作需要相应的权限。通常，只有表的创建者或具有足够权限的用户才能修改表结构。

通过掌握ALTER TABLE ... ADD COLUMN语句的使用，我们可以灵活地根据需求为MySQL数据库中的表添加新的字段。

删除字段

在MySQL中，删除表中的字段（也称为列）是一个需要谨慎操作的任务，因为一旦字段被删除，与该字段相关的所有数据也将被永久移除，且无法恢复（除非你有备份）。使用ALTER TABLE语句可以方便地删除表中的字段。

语法

ALTER TABLE table_name DROP COLUMN column_name;

table_name：要删除字段的表的名称。
column_name：要删除的字段的名称。

示例

假设我们有一个名为employees的表，并且该表包含一个名为middle_name的字段，现在我们想要删除这个字段，可以使用以下SQL语句：

ALTER TABLE employees DROP COLUMN middle_name;

执行上述语句后，middle_name字段及其所有数据将从employees表中被永久删除。

修改字段

在MySQL中，修改表中的字段（也称为列）通常涉及更改字段的数据类型、名称、默认值、约束条件等。这可以通过ALTER TABLE语句结合MODIFY COLUMN或CHANGE COLUMN子句来实现。

使用 MODIFY COLUMN

MODIFY COLUMN 用于更改现有字段的数据类型、约束条件等，但不能更改字段的名称。

语法

ALTER TABLE table_name MODIFY COLUMN column_name datatype [constraints];

table_name：要修改的表的名称。
column_name：要修改的字段的名称。
datatype：新的数据类型。
[constraints]：可选的字段约束条件，如 NOT NULL、DEFAULT 值、UNIQUE 键等。

示例

假设我们有一个名为employees的表，并且该表包含一个名为salary的字段，现在我们想要更改该字段的数据类型为DECIMAL并设置默认值为50000.00，可以使用以下SQL语句：

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10, 2) DEFAULT 50000.00;

使用 CHANGE COLUMN

CHANGE COLUMN 既可以更改字段的数据类型、约束条件，也可以更改字段的名称。

语法

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype [constraints];

table_name：要修改的表的名称。
old_column_name：要修改的现有字段的名称。
new_column_name：新的字段名称（如果不需要更改名称，可以与old_column_name相同）。
datatype：新的数据类型。
[constraints]：可选的字段约束条件。

示例

假设我们有一个名为employees的表，并且该表包含一个名为emp_salary的字段，现在我们想要将字段名称更改为salary，并将其数据类型更改为DECIMAL，同时设置默认值为50000.00，可以使用以下SQL语句：

ALTER TABLE employees CHANGE COLUMN emp_salary salary DECIMAL(10, 2) DEFAULT 50000.00;

重命名表

在MySQL中，重命名表的操作相对简单，你可以使用RENAME TABLE语句来实现。这个语句允许你一次性重命名一个或多个表。

语法

RENAME TABLE old_table_name TO new_table_name;

或者，如果你需要同时重命名多个表，可以使用逗号分隔的列表（注意，在MySQL 8.0及更高版本中，一次性重命名多个表的能力被限制为在同一个数据库内的表）：

RENAME TABLE 
    old_table_name1 TO new_table_name1,
    old_table_name2 TO new_table_name2,
    ...;

old_table_name：当前的表名称。
new_table_name：新的表名称。

示例

假设你有一个名为employees的表，现在你想要将这个表重命名为staff，你可以使用以下SQL语句：

RENAME TABLE employees TO staff;

如果你同时想要将另一个名为departments的表重命名为orgs，你可以这样做：

RENAME TABLE 
    employees TO staff,
    departments TO orgs;

删除表

在MySQL中，删除表的操作是通过DROP TABLE语句来实现的。这个操作是永久性的，一旦执行，表及其包含的所有数据都会被删除，且无法撤销。因此，在执行此操作之前，请务必确保你已经备份了所有需要的数据，或者确认这些数据不再需要。

语法

DROP TABLE IF EXISTS table_name;

table_name：要删除的表的名称。

示例

假设你有一个名为old_table的表，现在你想要删除它，可以使用以下SQL语句：

DROP TABLE IF EXISTS old_table;

注意：DROP TABLE操作是不可逆的，一旦执行，表及其所有数据都将被永久删除，因此在执行此操作前务必备份重要数据。

通过正确地使用DROP TABLE语句，你可以安全地删除MySQL数据库中的表。但请务必谨慎操作，并在执行前做好充分的准备和评估。如果你不确定是否应该删除某个表，或者担心删除操作可能会带来不可预知的后果，建议先咨询数据库管理员或具有相关经验的同事。

清空表

在MySQL中，如果你想要清空表中的所有数据，但保留表结构（即表的定义、索引、约束等），你可以使用TRUNCATE TABLE语句或DELETE FROM语句。这两种方法各有优缺点，适用于不同的场景。

使用 `TRUNCATE TABLE`

TRUNCATE TABLE 是一种快速清空表的方法，它通常比 DELETE FROM 更高效，因为它不会逐行删除数据，而是直接释放表数据所占用的空间并重置表。但是，TRUNCATE TABLE 是一个DDL（数据定义语言）操作，而不是DML（数据操作语言）操作，这意味着它会自动提交，不能回滚，并且会重置表的自增计数器。

TRUNCATE TABLE table_name;

table_name：要清空的表的名称。

注意事项：

TRUNCATE TABLE 不能带有 WHERE 子句，它会删除表中的所有行。
如果表中有外键约束，并且这些外键被其他表引用，则可能无法直接 TRUNCATE 该表。
TRUNCATE TABLE 会重置表的自增计数器（AUTO_INCREMENT）。
TRUNCATE TABLE 通常比 DELETE 快，因为它不生成单独的行删除操作。

使用 `DELETE FROM`

DELETE FROM 语句逐行删除表中的数据，并且可以在 WHERE 子句中指定条件来删除特定的行。由于 DELETE 是DML操作，它可以被事务控制，允许回滚。

DELETE FROM table_name [WHERE condition];

table_name：要清空的表的名称。
[WHERE condition]：可选的条件，用于指定要删除的行。如果不带条件，则会删除表中的所有行。

注意事项：

DELETE FROM 可以带有 WHERE 子句来指定删除条件。
DELETE FROM 操作可以被事务包围，允许回滚。
DELETE FROM 通常比 TRUNCATE TABLE 慢，特别是当表中有大量数据时。
DELETE FROM 不会重置表的自增计数器，除非使用 TRUNCATE TABLE 或手动重置。

对比 `TRUNCATE TABLE` 和 `DELETE FROM`

TRUNCATE TABLE和DELETE FROM是MySQL中用于删除表中数据的两种不同方法，它们之间存在显著的差异。

操作方式

TRUNCATE TABLE：这是一个DDL（数据定义语言）操作，它直接删除表中的所有数据，并重置表的自增计数器（如果存在）。该操作相当于删除表并重新创建一个空表，但不会删除表结构（如列、索引、约束等）。
DELETE FROM：这是一个DML（数据操作语言）操作，它逐行删除表中的数据。可以通过WHERE子句指定删除条件，如果没有条件则删除所有行。此外，DELETE操作会触发相关的触发器和外键约束。

性能

TRUNCATE TABLE：由于TRUNCATE操作不会逐行删除数据，而是直接释放整个表的存储空间，因此通常比DELETE操作更快，特别是在处理大型表时。
DELETE FROM：DELETE操作需要逐行删除数据，并记录每个删除操作的事务日志，以便支持回滚。因此，在处理大量数据时，DELETE操作可能会比较慢，并且会占用更多的磁盘空间来存储事务日志。

事务处理

TRUNCATE TABLE：TRUNCATE操作是一个隐式的提交操作，它会立即提交当前事务并释放锁。因此，它不能在事务中回滚。
DELETE FROM：DELETE操作可以在事务中使用，并且支持回滚。如果在事务中执行DELETE操作后发生错误或需要取消删除，可以使用ROLLBACK命令来撤销该操作。

触发器和外键约束

TRUNCATE TABLE：TRUNCATE操作不会触发与表相关的触发器，也不会检查外键约束。因此，如果表被其他表的外键所引用，则可能无法直接TRUNCATE该表。
DELETE FROM：DELETE操作会触发与表相关的触发器，并且会检查外键约束。如果尝试删除的行被其他表的外键所引用，则DELETE操作会失败并返回错误。

自增主键

TRUNCATE TABLE：执行TRUNCATE操作后，表的自增主键计数器会被重置。这意味着下一次插入数据时，自增主键将从初始值（通常是1）开始。
DELETE FROM：DELETE操作不会重置表的自增主键计数器。即使删除了所有行，自增主键的计数器也会继续递增。

使用建议

如果需要快速清空表中的所有数据，并且不关心自增主键计数器的重置、触发器的触发或外键约束的检查，可以使用TRUNCATE TABLE。
如果需要在事务中控制数据的删除，或者需要基于特定条件删除行，或者希望保留自增主键计数器的当前值，则应该使用DELETE FROM。

综上所述，TRUNCATE TABLE和DELETE FROM在MySQL中各有优缺点，选择哪种方法取决于具体的需求和场景。在使用这些命令时，请务必谨慎操作，并确保已经备份了重要的数据。

在执行任何清空表的操作之前，请务必备份数据，以防万一需要恢复。

注意事项

权限要求：执行RENAME TABLE操作需要相应的权限。通常，只有表的创建者或具有足够权限的用户才能重命名表。
表锁定：在执行RENAME TABLE操作时，MySQL会锁定涉及的表以进行结构修改。这可能会导致在该表（或这些表）上的其他操作被阻塞，直到RENAME TABLE操作完成。因此，建议在系统负载较低且对表的使用较少的时候进行此类操作。
依赖关系：检查要重命名的表是否被其他表的查询、视图、存储过程、触发器等引用，或者是否作为外键的参照表。如果有，你需要先处理这些依赖关系，否则可能会导致数据库完整性问题或查询错误。
应用程序更新：如果你的应用程序直接引用了要重命名的表，你需要确保更新应用程序中的相关代码，以使用新的表名称。
数据库引擎：虽然大多数MySQL存储引擎都支持RENAME TABLE操作，但某些特定的引擎（如Federated、Archive等）可能有特殊的限制或要求。在使用这些引擎时，请查阅相关的文档。
复制和分区：如果你的MySQL服务器配置了复制或使用了分区表，重命名表时可能需要额外的注意。例如，在复制环境中，你需要确保所有相关的从服务器都应用了相应的更改。