MySQL :: MySQL 8.4 参考手册 :: 15.1.1 原子数据定义语句支持

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 15 条 SQL 语句 / 15.1 数据定义语句 / 15.1.1 原子数据定义语句支持

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

15.1.1 原子数据定义语句支持15.1.1 Atomic Data Definition Statement Support

MySQL 8.4 支持原子数据定义语言（DDL）语句。此功能称为原子 DDL。原子 DDL 语句将与 DDL 操作相关的数据字典更新、存储引擎操作以及二进制日志写入合并为单个原子操作。该操作要么提交，并将适用的更改持久保存到数据字典、存储引擎和二进制日志，要么回滚，即使服务器在操作过程中停止运行。MySQL 8.4 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation. The operation is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.

Note

原子 DDL 不是 事务性 DDL。DDL 语句，无论是否原子，都会隐式终止当前会话中任何活动事务，就好像在执行该语句之前你已经做了一个COMMIT。这意味着 DDL 语句不能在另一个事务中执行，也不能在事务控制语句如START TRANSACTION ... COMMIT中执行，或者与其他语句在同一事务中组合执行。Atomic DDL is not transactional DDL. DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION ... COMMIT, or combined with other statements within the same transaction.

原子 DDL 是由 MySQL 数据字典实现的，它提供了集中式、事务性的元数据存储。Atomic DDL is made possible by the MySQL data dictionary, which provides centralized, transactional metadata storage.

本节中关于原子 DDL 功能的描述如下主题：The atomic DDL feature is described under the following topics in this section:

支持的 DDL 语句Supported DDL Statements

原子 DDL 功能支持表和非表 DDL 语句。与表相关的 DDL 操作需要存储引擎的支持，而非表 DDL 操作则不需要。目前，只有InnoDB存储引擎支持原子 DDL。The atomic DDL feature supports both table and non-table DDL statements. Table-related DDL operations require storage engine support, whereas non-table DDL operations do not. Currently, only the InnoDB storage engine supports atomic DDL.

支持的表 DDL 语句包括数据库、表空间、表和索引的CREATE、ALTER和DROP语句，以及TRUNCATE TABLE语句。Supported table DDL statements include CREATE, ALTER, and DROP statements for databases, tablespaces, tables, and indexes, and the TRUNCATE TABLE statement.
支持的非表 DDL 语句包括：Supported non-table DDL statements include:
- CREATE 和 DROP 语句，以及（如果适用）用于存储程序、触发器、视图和可加载函数的 ALTER 语句。CREATE and DROP statements, and, if applicable, ALTER statements for stored programs, triggers, views, and loadable functions.
- 账户管理语句：CREATE、ALTER、DROP，以及（如果适用）用于用户和角色的 RENAME 语句，以及 GRANT 和 REVOKE 语句。Account management statements: CREATE, ALTER, DROP, and, if applicable, RENAME statements for users and roles, as well as GRANT and REVOKE statements.

以下声明不被原子 DDL 功能支持：The following statements are not supported by the atomic DDL feature:

涉及除InnoDB之外的存储引擎的表相关 DDL 语句。Table-related DDL statements that involve a storage engine other than InnoDB.
INSTALL PLUGIN 和 UNINSTALL PLUGIN 语句。INSTALL PLUGIN and UNINSTALL PLUGIN statements.
INSTALL COMPONENT 和 UNINSTALL COMPONENT 语句。INSTALL COMPONENT and UNINSTALL COMPONENT statements.
CREATE SERVER, ALTER SERVER, 和 DROP SERVER 语句。CREATE SERVER, ALTER SERVER, and DROP SERVER statements.

原子性 DDL 特性Atomic DDL Characteristics

原子 DDL 语句的特性包括如下内容：The characteristics of atomic DDL statements include the following:

元数据更新、二进制日志写入以及存储引擎操作（如适用）被合并为单一的原子操作。Metadata updates, binary log writes, and storage engine operations, where applicable, are combined into a single atomic operation.
在 DDL 操作期间，SQL 层没有中间提交。There are no intermediate commits at the SQL layer during the DDL operation.
如适用：Where applicable:
- 数据字典、例程、事件和可加载函数缓存的状态与 DDL 操作的状态一致，这意味着缓存会更新，以反映 DDL 操作是否成功完成或已回滚。The state of data dictionary, routine, event, and loadable function caches is consistent with the status of the DDL operation, meaning that caches are updated to reflect whether or not the DDL operation was completed successfully or rolled back.
- 参与 DDL 操作的存储引擎方法不会执行中间提交，存储引擎将自身注册为 DDL 操作的一部分。The storage engine methods involved in a DDL operation do not perform intermediate commits, and the storage engine registers itself as part of the DDL operation.
- 存储引擎支持 DDL 操作的重做和回滚，这在 DDL 操作的 DDL 后阶段 执行。The storage engine supports redo and rollback of DDL operations, which is performed in the Post-DDL phase of the DDL operation.
DDL 操作的可见行为是原子的。The visible behaviour of DDL operations is atomic.

DDL 语句行为DDL Statement Behavior

本节描述在使用支持原子 DDL 的存储引擎（如InnoDB）时，DDL 语句行为的一些重要方面。This section describes some important aspects of DDL statement behavior when using a storage engine that support atomic DDL, such as InnoDB.

DROP TABLE 操作在所有指定的表都使用支持原子 DDL 的存储引擎时完全是原子的。该语句要么成功删除所有表，要么回滚。DROP TABLE operations are fully atomic if all named tables use a storage engine which supports atomic DDL. The statement either drops all tables successfully or is rolled back.
DROP TABLE 如果指定的表不存在，则会出错，并且不会进行任何更改，无论使用的存储引擎是什么。DROP TABLE fails with an error if a named table does not exist, and no changes are made, regardless of the storage engine.
DROP DATABASE 是原子操作，如果所有表都使用支持原子 DDL 的存储引擎。该语句要么成功删除所有对象，要么回滚。你应当注意，从文件系统中删除数据库目录发生在最后，并不属于原子操作的一部分；因此，如果由于文件系统错误或服务器停止导致数据库目录删除失败，DROP DATABASE 事务不会回滚。DROP DATABASE is atomic if all tables use a storage engine which supports atomic DDL. The statement either drops all objects successfully or is rolled back. You should be aware that removal of the database directory from the file system occurs last and is not part of the atomic operation; thus, if removal of the database directory fails due to a file system error or server halt, the DROP DATABASE transaction is not rolled back.
对于未使用支持原子 DDL 的存储引擎的表，表删除发生在原子 DROP TABLE 或 DROP DATABASE 事务之外。这类表的删除会单独写入二进制日志，这在中断 DROP TABLE 或 DROP DATABASE 操作时，将存储引擎、数据字典和二进制日志之间的不一致性限制为最多一张表。对于删除多张表的操作，任何未使用支持原子 DDL 存储引擎的表会在使用支持原子 DDL 存储引擎的表之前被删除。For tables that do not use a storage engine which supports atomic DDL, table deletion occurs outside of the atomic DROP TABLE or DROP DATABASE transaction. Such table deletions are written to the binary log individually, which limits the discrepancy between the storage engine, data dictionary, and binary log to one table at most in the case of an interrupted DROP TABLE or DROP DATABASE operation. For operations that drop multiple tables, any tables that do not use a storage engine which supports atomic DDL are dropped before tables that do so.
CREATE TABLE、ALTER TABLE、RENAME TABLE、TRUNCATE TABLE、CREATE TABLESPACE 以及 DROP TABLESPACE 操作，对于使用支持原子 DDL 的存储引擎的表，要么完全提交，要么在服务器操作中断时回滚。RENAME TABLE 操作仅在所有命名表使用支持原子 DDL 的存储引擎时才是原子的。CREATE TABLE, ALTER TABLE, RENAME TABLE, TRUNCATE TABLE, CREATE TABLESPACE, and DROP TABLESPACE operations for tables that use a storage engine which supports atomic DDL are either fully committed or rolled back if the server halts during their operation. RENAME TABLE operations are atomic only if all named tables use a storage engine which supports atomic DDL.
对于支持原子 DDL 的存储引擎，当使用基于行的复制时，CREATE TABLE ... SELECT 语句会以单个事务的形式记录在二进制日志中。For storage engines that support atomic DDL, the CREATE TABLE ... SELECT statement is logged as one transaction in the binary log when row-based replication is in use.
对于同时支持原子 DDL 和外键约束的存储引擎，在使用基于行的复制时，不允许在 CREATE TABLE ... SELECT 语句中创建外键。外键约束可以稍后使用 ALTER TABLE 添加。On storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted in CREATE TABLE ... SELECT statements when row-based replication is in use. Foreign key constraints can be added later using ALTER TABLE.
当 CREATE TABLE ... SELECT 作为原子操作应用时，在插入数据的过程中表上会保持元数据锁，这会阻止在操作期间对表的并发访问。When CREATE TABLE ... SELECT is applied as an atomic operation, a metadata lock is held on the table while data is inserted, which prevents concurrent access to the table for the duration of the operation.
DROP VIEW 如果指定的视图不存在，则操作会失败，并且不会做出任何更改。DROP VIEW fails if a named view does not exist, and no changes are made.
账户管理语句要么对所有命名用户都成功，要么在发生错误时回滚且无任何效果。Account management statements either succeed for all named users or roll back and have no effect if an error occurs.

存储引擎支持Storage Engine Support

目前，只有InnoDB存储引擎支持原子 DDL。不支持原子 DDL 的存储引擎可以免除 DDL 原子性。涉及免除的存储引擎的 DDL 操作仍可能导致在操作中断或仅部分完成时发生的不一致性。Currently, only the InnoDB storage engine supports atomic DDL. Storage engines that do not support atomic DDL are exempted from DDL atomicity. DDL operations involving exempted storage engines remain capable of introducing inconsistencies that can occur when operations are interrupted or only partially completed.

为了支持 DDL 操作的重做和回滚，InnoDB将 DDL 日志写入mysql.innodb_ddl_log表，该表是一个隐藏的数据字典表，位于mysql.ibd数据字典表空间中。To support redo and rollback of DDL operations, InnoDB writes DDL logs to the mysql.innodb_ddl_log table, which is a hidden data dictionary table that resides in the mysql.ibd data dictionary tablespace.

要查看在 DDL 操作期间写入 mysql.innodb_ddl_log 表的 DDL 日志，请启用 innodb_print_ddl_logs 配置选项。更多信息，请参见查看 DDL 日志。To view DDL logs that are written to the mysql.innodb_ddl_log table during a DDL operation, enable the innodb_print_ddl_logs configuration option. For more information, see Viewing DDL Logs.

Note

对 mysql.innodb_ddl_log 表的更改的重做日志会立即刷新到磁盘，而不管 innodb_flush_log_at_trx_commit 设置为何值。立即刷新重做日志可以避免数据文件被 DDL 操作修改，但由这些操作引起的对 mysql.innodb_ddl_log 表的更改的重做日志未持久化到磁盘的情况。这样的情况可能会在回滚或恢复期间导致错误。The redo logs for changes to the mysql.innodb_ddl_log table are flushed to disk immediately regardless of the innodb_flush_log_at_trx_commit setting. Flushing the redo logs immediately avoids situations where data files are modified by DDL operations but the redo logs for changes to the mysql.innodb_ddl_log table resulting from those operations are not persisted to disk. Such a situation could cause errors during rollback or recovery.

InnoDB 存储引擎分阶段执行 DDL 操作。诸如 ALTER TABLE 之类的 DDL 操作可能在提交阶段之前多次执行准备和执行阶段。The InnoDB storage engine executes DDL operations in phases. DDL operations such as ALTER TABLE may perform the Prepare and Perform phases multiple times prior to the Commit phase.

准备：创建所需的对象并将 DDL 日志写入 mysql.innodb_ddl_log 表。DDL 日志定义了如何前滚和回滚 DDL 操作。Prepare: Create the required objects and write the DDL logs to the mysql.innodb_ddl_log table. The DDL logs define how to roll forward and roll back the DDL operation.
执行：执行 DDL 操作。例如，对于 CREATE TABLE 操作，执行创建例程。Perform: Perform the DDL operation. For example, perform a create routine for a CREATE TABLE operation.
提交：更新数据字典并提交数据字典事务。Commit: Update the data dictionary and commit the data dictionary transaction.
DDL 后操作：重放并删除 mysql.innodb_ddl_log 表中的 DDL 日志。为了确保可以安全回滚而不会引入不一致性，在此最后阶段执行诸如重命名或删除数据文件的文件操作。此阶段还会从 mysql.innodb_dynamic_metadata 数据字典表中删除动态元数据，以进行 DROP TABLE、TRUNCATE TABLE 以及其他重建表的 DDL 操作。Post-DDL: Replay and remove DDL logs from the mysql.innodb_ddl_log table. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from the mysql.innodb_dynamic_metadata data dictionary table for DROP TABLE, TRUNCATE TABLE, and other DDL operations that rebuild the table.

DDL 日志在 DDL 后 阶段会被重放并从 mysql.innodb_ddl_log 表中删除，无论 DDL 操作是提交还是回滚。DDL 日志只有在服务器在 DDL 操作期间停止时才会保留在 mysql.innodb_ddl_log 表中。在这种情况下，DDL 日志会在恢复之后被重放并删除。DDL logs are replayed and removed from the mysql.innodb_ddl_log table during the Post-DDL phase, regardless of whether the DDL operation is committed or rolled back. DDL logs should only remain in the mysql.innodb_ddl_log table if the server is halted during a DDL operation. In this case, the DDL logs are replayed and removed after recovery.

在恢复情况下，当服务器重新启动时，DDL 操作可能会被提交或回滚。如果在 DDL 操作的提交阶段执行的数据字典事务存在于重做日志和二进制日志中，则该操作被视为成功并继续执行。否则，当 InnoDB 重放数据字典重做日志时，未完成的数据字典事务将被回滚，DDL 操作也会被回滚。In a recovery situation, a DDL operation may be committed or rolled back when the server is restarted. If the data dictionary transaction that was performed during the Commit phase of a DDL operation is present in the redo log and binary log, the operation is considered successful and is rolled forward. Otherwise, the incomplete data dictionary transaction is rolled back when InnoDB replays data dictionary redo logs, and the DDL operation is rolled back.

查看 DDL 日志Viewing DDL Logs

要查看在涉及 InnoDB 存储引擎的原子 DDL 操作期间写入 mysql.innodb_ddl_log 数据字典表的 DDL 日志，请启用 innodb_print_ddl_logs，让 MySQL 将 DDL 日志写入 stderr。根据主机操作系统和 MySQL 配置，stderr 可能是错误日志、终端或控制台窗口。参见第 7.4.2.2 节，“默认错误日志目标配置”。To view DDL logs that are written to the mysql.innodb_ddl_log data dictionary table during atomic DDL operations that involve the InnoDB storage engine, enable innodb_print_ddl_logs to have MySQL write the DDL logs to stderr. Depending on the host operating system and MySQL configuration, stderr may be the error log, terminal, or console window. See Section 7.4.2.2, “Default Error Log Destination Configuration”.

InnoDB 将 DDL 日志写入 mysql.innodb_ddl_log 表，以支持 DDL 操作的重做和回滚。mysql.innodb_ddl_log 表是一个隐藏的数据字典表，位于 mysql.ibd 数据字典表空间中。与其他隐藏的数据字典表一样，mysql.innodb_ddl_log 表在非调试版本的 MySQL 中无法直接访问。(参见第 16.1 节，“数据字典架构”。) mysql.innodb_ddl_log 表的结构对应以下定义：InnoDB writes DDL logs to the mysql.innodb_ddl_log table to support redo and rollback of DDL operations. The mysql.innodb_ddl_log table is a hidden data dictionary table that resides in the mysql.ibd data dictionary tablespace. Like other hidden data dictionary tables, the mysql.innodb_ddl_log table cannot be accessed directly in non-debug versions of MySQL. (See Section 16.1, “Data Dictionary Schema”.) The structure of the mysql.innodb_ddl_log table corresponds to this definition:

CREATE TABLE mysql.innodb_ddl_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  thread_id BIGINT UNSIGNED NOT NULL,
  type INT UNSIGNED NOT NULL,
  space_id INT UNSIGNED,
  page_no INT UNSIGNED,
  index_id BIGINT UNSIGNED,
  table_id BIGINT UNSIGNED,
  old_file_path VARCHAR(512) COLLATE utf8mb4_bin,
  new_file_path VARCHAR(512) COLLATE utf8mb4_bin,
  KEY(thread_id)
);

id：DDL 日志记录的唯一标识符。id: A unique identifier for a DDL log record.
thread_id：每条 DDL 日志记录都会被分配一个 thread_id，用于重放和删除属于特定 DDL 操作的 DDL 日志。涉及多个数据文件操作的 DDL 操作会生成多条 DDL 日志记录。thread_id: Each DDL log record is assigned a thread_id, which is used to replay and remove DDL logs that belong to a particular DDL operation. DDL operations that involve multiple data file operations generate multiple DDL log records.
type：DDL 操作类型。类型包括 FREE（删除索引树）、DELETE（删除文件）、RENAME（重命名文件）或 DROP（从 mysql.innodb_dynamic_metadata 数据字典表中删除元数据）。type: The DDL operation type. Types include FREE (drop an index tree), DELETE (delete a file), RENAME (rename a file), or DROP (drop metadata from the mysql.innodb_dynamic_metadata data dictionary table).
space_id：表空间 ID。space_id: The tablespace ID.
page_no：包含分配信息的页；例如，索引树的根页。page_no: A page that contains allocation information; an index tree root page, for example.
index_id：索引 ID。index_id: The index ID.
table_id：表的 ID。table_id: The table ID.
old_file_path：旧表空间文件路径。用于创建或删除表空间文件的 DDL 操作；也用于重命名表空间的 DDL 操作。old_file_path: The old tablespace file path. Used by DDL operations that create or drop tablespace files; also used by DDL operations that rename a tablespace.
new_file_path：新表空间文件路径。用于重命名表空间文件的 DDL 操作。new_file_path: The new tablespace file path. Used by DDL operations that rename tablespace files.

此示例演示如何启用 innodb_print_ddl_logs 以查看写入 strderr 的 CREATE TABLE 操作的 DDL 日志。This example demonstrates enabling innodb_print_ddl_logs to view DDL logs written to strderr for a CREATE TABLE operation.

mysql> SET GLOBAL innodb_print_ddl_logs=1;
mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;

[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl : begin for thread id : 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7

首页上一级下一节