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特权。要将必需的特权分配给特定用户，可以使用以下查询：

如果您希望特权仅适用于某些表（通配符使特权适用于所有表），则将数据库名替换为数据库名，将通配符替换为表名，并将demo_user替换为用户名。如果要在所有数据库及其中的所有表中使用特权，只需用通配符替换数据库：

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

他的查询将在列demo_table中添加一列column_name。将FIRST添加到查询的末尾以使该列成为表中的第一列。

他的查询将在表demo_table的column_1列之后添加column_2列。

该查询会将生成的列添加到表中。

此查询会将列demo_column放在表demo_table上。

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

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

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

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

该查询将按列将demo_column列划分为8个分区。

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

如果要添加索引，请记住可以添加不同类型的索引（例如BTREE索引或FULLTEXT索引），也可以添加仅覆盖查询列中一定数量字符的索引像这样：

上述查询将增加称为索引demo_index在柱称为的前10个字符列名在一个叫做表demo_table。

MySQL中的索引是一个复杂的野兽，它们确实应有其自身的主题，因此我们在这里不做详细介绍，但是如果您想了解更多信息，我们前面有关MySQL索引的文章应该提供更多的见解。

ALTER TABLE如何工作？

MySQL中的ALTER TABLE有其自身的微妙之处。MySQL的最新版本，即MySQL 8.0。有3种算法会影响ALTER TABLE进行此类更改的方式。这些是：

• 复制

• 在原始表的副本上执行操作，并将表数据从原始表逐行复制到新表。在大多数情况下，就资源使用而言，此算法可能非常昂贵，尤其是对于大型表而言。选择或选择此算法时，不允许所有并发DML，因此，引用受影响的表的任何后续查询都必须等待或排队进入进程列表。如果连接最大化，您很可能会卡住数据库。

• 到位

• 操作避免复制表数据，但可以在适当位置重建表。在操作的准备和执行阶段可以简短地获取表上的独占元数据锁定。通常，支持并发DML。

• 瞬间

• 操作仅修改数据字典中的元数据。在准备和执行期间，不会在表上获取任何独占元数据锁，并且表数据不受影响，从而使操作立即进行。允许并发DML。（在MySQL 8.0.12中引入）

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

1. 通过运行，创建一个具有所需结构的新表（如源表）

   1个	CREATE TABLE demo_table_new LIKE demo_table;

   然后调整其结构。在这种情况下，demo_table是源表，demo_table_new是新表。

2. 将数据插入到新表中。

3. 将旧表重命名为demo_table_old（根据需要调整名称）。

4. 将新表重命名为旧表的旧名称。

5. 最后，将行从旧表复制到新表，并在需要时创建索引。

尽管上述步骤可以正常工作。但是，在实际情况下，DBA或开发人员倾向于使用Percona的pt-online-schema-change或Github的gh-ost。您可以查看我们以前的文章《 MySQL和MariaDB迁移的顶级开源工具》，其中概述了这些架构更改工具。

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

ALTER TABLE陷阱

如果您使用ALTER TABLE语句创建索引（也可以使用CREATE INDEX语句），建议在插入数据后创建索引，因为这是一种不仅在MySQL中而且在加快处理速度方面众所周知的方法在其他数据库管理系统中，例如Oracle。不过，一般而言，请记住，大多数ALTER TABLE操作都应引起MySQL的某些问题（服务中断）。

尽管还有一点高级，但是还有另一种方法可以加快整个过程的速度：如果您可以说服MySQL仅修改表的.frm文件（.frm文件描述表的定义），而不管表，过程会更快：

1. 创建一个具有与旧表相同布局的空表，而无需对其进行修改。

2. 关闭正在使用的所有表，并防止通过运行打开所有新表 

   1个	FLUSH TABLES WITH READ LOCK.

3. 交换.frm文件。

4. 通过运行UNLOCK TABLES释放读取锁。

还请记住，如果您要修改列并且语法似乎正确，但是仍然出现错误，那么可能是时候考虑使用其他语法了。例如：

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

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

概要

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