MySQL中的ALTER TABLE：朋友还是敌人？

MySQL中的ALTER TABLE：朋友还是敌人？

ALTER TABLE语句是MySQL世界中最常用的语句之一 –该语句允许您添加、删除或修改表中的列。在这篇博文中，我们将尝试深入了解它是什么、它做什么以及什么时候应该使用它。

什么是ALTER TABLE？它做什么？

如上所述，ALTER TABLE语句使DBA和开发人员能够添加、删除或修改表中的列。简单地说，ALTER TABLE改变了表的结构——它允许您添加、删除列、添加或删除索引、重命名列或更改其类型。

何时以及如何使用ALTER TABLE？

为了使用ALTER TABLE，您通常需要ALTER、CREATE和INSERT权限。要重命名表，所需的权限是旧表的ALTER和DROP，然后是要创建的新表的CREATE、ALTER及INSERT特权。要将所需权限分配给特定用户，可以使用以下查询：

GRANT ALTER, CREATE, INSERT ON database.* TO 'demo_user';

用你的数据库名称替换数据库，如果希望权限仅适用于某些表（通配符使权限适用于所有表），则用表名称替换通配符，用用户名替换demo_user。如果希望所有数据库及所有表中都具有权限，只需将数据库替换为通配符：

GRANT ALTER, CREATE, INSERT ON *.* TO 'demo_user';

为了实际使用ALTER TABLE语句，请运行一个更改表结构的查询–ALTER STABLE用于添加、删除或修改表中的列：该查询还可以用于向列添加索引。以下是一些最常用查询的基本示例：

ALTER TABLE demo_table ADD column_name VARCHAR(255) NOT NULL DEFAULT ‘’; T

该查询将向表demo_table添加列colum_name。

ALTER TABLE demo_table ADD column_2 VARCHAR(255) NOT NULL DEFAULT ‘’ AFTER column_1; T

该查询将在表demo_table的column1之后添加一个列column2。

ALTER TABLE demo_table ADD COLUMN column_2 INT GENERATED ALWAYS AS (column_1 + 1) STORED;

此查询将向表中添加一个生成的列。

ALTER TABLE demo_table DROP COLUMN demo_column; 

此查询将在表demo_table上删除列demo_column。

ALTER TABLE demo_table ADD INDEX demo_index(demo_column); 

此查询将在demo_table表中名为demo_column的列上添加一个名为demon_index的索引（名称可以选择）。

ALTER TABLE demo_table ADD INDEX (demo_column), ADD UNIQUE (demo_unique); 

此查询将在demo_column列上添加索引，并在demo_unique列上添加唯一索引。

ALTER TABLE demo_table MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4; 

此查询将更改特定列的默认字符集。

ALTER TABLE demo_table CONVERT TO CHARACTER SET charset_name; 

此查询将更改表所有字符（CHAR、VARCHAR和TEXT）列的默认字符集。

ALTER TABLE demo_table PARTITION BY HASH(demo_column) PARTITIONS 8; 

此查询将按散列方式对表分区， 根据列demo_column散列值划分为8个分区。

ALTER TABLE demo_table TABLESPACE tablespace_1 STORAGE DISK; 

这个查询会将表demo_table转换为基于磁盘的存储。

如果要添加索引，请记住可以添加不同类型的索引（例如，BTREE索引或FULLTEXT索引），还可以添加一个索引，该索引只包含查询中列中的特定字符数，如下所示：

ALTER TABLE demo_table ADD INDEX demo_index(column_name(10));

上面的查询将在名为demo_table的表中名为column_name的列的前10个字符上添加一个名为demon_index的索引。

MySQL中的索引是一个复杂的庞然大物，它们确实值得一个自己的主题，因此我们不在这里详细讨论，但如果您想了解更多信息，我们之前关于MySQL索引的帖子 应该提供更多的见解。

ALTER TABLE如何工作？

MySQL中的ALTER TABLE有自己的微妙之处。MySQL的最新版本即MySQL 8.0 有3种算法会影响ALTER TABLE对此类更改的执行方式。它们是:

• 拷贝 (副本)
  • 对原始表的副本执行操作，并将表数据逐行从原始表复制到新表。在大多数情况下，该算法在资源使用方面可能非常昂贵，特别是对于大型表。当选择此算法时，不允许所有并发DML，因此引用受影响表的任何后续查询都必须等待或排队到进程列表中。如果连接达到最大值，很可能会导致数据库阻塞。
• INPLACE(就地)
  • 操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段，会短暂获得表上的元数据互斥锁。通常，支持并发DML。
• INSTANT(即时)
  • 操作仅修改数据字典中的元数据。在准备和执行期间，表上没有独占的元数据锁，表数据不受影响，因此操作是即时的。允许并发DML。（MySQL 8.0.12中引入）

MySQL的ALTER TABLE进程对于较小的表来说可能不是问题，但如果数据集较大，则可能会遇到问题 – 许多人都经历过ALTER TABLE查询，需要数小时、数天甚至数周才能完成。在大多数情况下，这是因为上面概述了MySQL的表更改过程。但是，有一种方法可以至少稍微减少完成查询所需的时间：

1. 运行下面脚本创建一个表与原表结构完全一致

 CREATE TABLE demo_table_new LIKE demo_table;

然后调整其结构。在本例中，demo_table是源表，而demo_table_new是新表。
2. 将数据插入新表。
3. 将旧表重命名为demo_table_old（根据需要调整名称）。
4. 将新表重命名为旧表的原名称。
5. 最后，将旧表中的行复制到新表中，如果需要，创建索引。

尽管上面的步骤很好。然而，在实际案例场景中，DBA或开发人员更倾向使用Percona的pt在线模式更改 或使用Github的ghost. 你可以看看我们上一篇文章MySQL和MariaDB迁移的顶级开源工具, 其中概述了这些模式更改工具。

无论如何，我们上面所描述的通常被称为“影子复制”方法：本质上，您使用所需的结构构建一个新表，然后执行重命名和删除以交换这两个表。还有另一种方法：您还可以交换服务器，并在不在生产环境中的服务器上运行ALTER TABLE。对于MyISAM，可以先禁用KEYS，然后加载数据，再启用KEYS。

更改表要点

如果使用ALTER TABLE语句创建索引（也可以使用CREATE INDEX语句），建议在插入数据后创建索引，因为这是一种众所周知的方法，不仅可以在MySQL中加快处理速度，而且可以在其他数据库管理系统（如Oracle）中加快处理。不过，通常要记住，大多数ALTER TABLE操作都会给MySQL带来一些问题（服务中断）。
不过，还有另一种方法可以加快整个过程，尽管它有点高级：如果你能说服MySQL只修改表的.frm文件（.frm文档描述表的定义），而不修改表，那么这个过程会更快：

1. 创建一个与旧表布局相同的空表，但不进行修改。
2. 关闭所有正在使用的表，并防止运行以下命令打开所有新表

   FLUSH TABLES WITH READ LOCK.

3. 交换.frm文件。
4. 通过运行UNLOCK TABLES释放读取锁。

还要记住，如果您想修改一个列，并且语法似乎正确，但仍然会出现错误，那么可能是时候研究一下不同的语法了。例如：

ALTER TABLE demo_table ADD long VARCHAR(255); 

这样的查询会出错，因为long是保留字。为了避免此类错误，请用反勾号转义该单词：

ALTER TABLE demo_TABLE ADD`long`VARCHAR（255）；

还值得注意的是，列名只能使用反勾号转义，不能使用单引号或双引号转义。例如，类似这样的查询也会出错：

ALTER TABLE demo_table CHANGE COLUMN ‘demo_column’ ‘demo_column_2’ VARCHAR(255);

小结

MySQL 使用ALTER TABLE语句添加、删除或修改表中的列。为了成功执行语句，您必须对该表具有ALTER、CREATE和INSERT权限。该语句还有一些自身独有的微妙之处：由于其工作方式的原因，在非常大的表上运行时，它的性能可能会受到影响，但只要您知道该语句的工作方式和功能，就应该可以了。