MySQL语法之用alter增加删除列

摘要：用MySQL alter table增加、删除或修改字段，设置新字段位置。

综述

  在 MySQL 中，ALTER TABLE 语法糖常常被用于修改已经创建表的结构。工作中，经常遇到在MySQL中新增字段、删除字段或者修改列注释的需求，这时候就要用到alter的魔法了。为了保持表结构的逻辑性和可读性，我们可能需要将新字段插入到特定位置，这时候就要alter 结合FIRST或者after关键字变魔术了。

  本文将详细介绍在 MySQL 中使用 ALTER TABLE 的上述功能，包括实际操作步骤、注意事项及性能分析。

ALTER TABLE 增加删除字段

  如需在表中添加列，请使用下面的语法：

ALTER TABLE table_name ADD column_name datatype not null DEFAULT default_value COMMENT '字段注释';

• column_name：新增列的名称。
• datatype：新增列的数据类型。

  如需删除表中的列，请使用下面的语法：

ALTER TABLE table_name DROP COLUMN column_name;

  温馨提示，有些公司约定数据库系统不允许在数据库表中删除列。如果需要同时删除和新增字段，则可以使用如下同时包含drop和add关键字的语法：

ALTER TABLE table_name DROP COLUMN column_name_a ADD column_name_b datatype not null COMMENT '注释';

  这个SQL可以用于修改字段名（如把column_name_a重命名为column_name_b）、调整字段属性或者注释等。

调整添加字段的位置

  MySQL 默认把通过ALTER语法新增字段放置在表的最后一列。然而，在实际开发中，为了保持表结构的逻辑性和可读性，我们可能需要将新字段插入到特定位置。ALTER 支持两种调整字段位置的语法糖：

• FIRST：将新字段设置为表中的第一个字段；
• AFTER column_name：将新字段放置在指定字段column_name之后。

  下面通过具体的例子来展示如何使用FIRST和AFTER关键字。若要在user表中新增一个age字段，并将其放置在name字段之后，可以执行以下SQL语句：

ALTER TABLE user ADD age INT not null DEFAULT 0 COMMENT '年龄' AFTER name;

  将age字段作为表的第一个字段：

ALTER TABLE user ADD age INT not null DEFAULT 0 COMMENT '年龄' FIRST;

  如果未指定FIRST或AFTER关键字，新字段将自动被添加到表的末尾：

ALTER TABLE user ADD age INT not null DEFAULT 0 COMMENT '年龄';

  温馨提示，有些公司约定只运行在在数据库表末尾添加列，不允许指定位置。

性能影响与优化建议

  ALTER TABLE是一个非常有用的工具，但调整字段或者字段顺序可能会对数据库性能产生负面影响，特别是在大型表或高负载的数据库环境中。如果必须调整字段位置，可以选择在低峰时段执行操作，并结合以下策略：

• 操作影响：字段顺序调整涉及表结构的重新组织，可能导致表锁定和磁盘I/O增加，影响线上服务。为了减少对正常业务的影响，建议在业务低峰期执行。
• 数据备份：修改前务必备份数据，防止意外丢失。
• 测试验证：在测试环境验证语句正确性，确保无语法错误或逻辑问题；同时测试调整后的表结构对查询性能的影响。
• 性能考虑：大表操作可能耗时较长，需评估对系统性能的影响。
• 神兵利器：从 MySQL 5.6 版本开始，可以使用MySQL 原在线 (Native Online ) DDL 技术解决上述性能问题。

  MySQL 原在线DDL 技术是一种在执行表结构变更（比如添加列、创建索引等DDL操作）的同时，不阻塞或尽可能短时间地阻塞读写等DML操作的技术，如果想了解更多内容，请移步博文《MySQL 原在线DDL》。

  执行 ALTER TABLE 操作需要获得alter权限，操作之前请确保具有 ALTER 权限；否则，操作将失败。

  对于Wiener以上的话题，大家又有什么自己的独特见解呢？欢迎在下方评论区留言！