MySQL DDL: Statements, Atomic DDL, Online DDL, Algorithms

In MySQL, DDL (Data Definition Language) refers to a subset of SQL statements that define, modify, and delete database schema objects including databases, tables, indexes, views, and stored procedures. DDL operations form the foundation of database structure management and are essential for schema evolution as applications grow and requirements change.

MySQL DDL statements include CREATE, ALTER, DROP, RENAME, and TRUNCATE. These statements differ fundamentally from Data Manipulation Language (DML) statements in their purpose, transactional behavior, and system-level impact.

This blog provides an in-depth technical exploration of MySQL DDL. You will learn statements, locking behavior, algorithms, replication implications, privilege requirements, tooling strategies best practices for performing safe schema changes at scale.

5 MySQL DDL Statements with Examples

CREATE Statement

The CREATE statement creates new database or its objects like tables, indexes, views, stored procedures, functions, triggers, and events.

• Create a Database

The CREATE DATABASE statement initializes a new database with specified character set and collation options:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

The optional IF NOT EXISTS clause prevents errors when attempting to create a database that already exists. Character set and collation options define how text data is stored and compared within the database.

• Create a Table

The CREATE TABLE statement defines a new table structure including column names, data types, and constraints:

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    columnN datatype [constraints]
) ENGINE=storage_engine;

Common constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, and AUTO_INCREMENT. The ENGINE clause specifies the storage engine, with InnoDB as the default since MySQL 5.5. InnoDB provides ACID transaction support, referential integrity through foreign keys, and crash recovery capabilities.

• Creat an Index

The CREATE INDEX statement generates indexes to accelerate data retrieval operations:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column1 [, column2, ...]);

Indexes improve query performance by enabling faster data location, though they increase overhead for INSERT, UPDATE, and DELETE operations since indexes must also be updated. 

MySQL supports multiple index types including standard B-tree indexes, unique indexes that enforce uniqueness constraints, fulltext indexes for text searches, and spatial indexes for geometric data.

ALTER Statement

The ALTER TABLE statement modifies existing table structures by adding, removing, or changing columns, indexes, constraints, and table properties.

ALTER TABLE table_name
[ADD COLUMN column_name datatype [constraints]]
[DROP COLUMN column_name]
[MODIFY COLUMN column_name datatype]
[CHANGE COLUMN old_name new_name datatype]
[ADD INDEX index_name (column_list)]
[DROP INDEX index_name]
[RENAME TO new_table_name]
[ALGORITHM=INSTANT|INPLACE|COPY]
[LOCK=NONE|SHARED|EXCLUSIVE];

The ALGORITHM clause lets MySQL choose how the ALTER operation is executed internally. It determines whether changes are metadata-only, performed in place, or require a full table rebuild. Detailed behavior of each algorithm (INSTANT, INPLACE, COPY) is covered in the “DDL Algorithms” section.

DROP Statement

The DROP statement permanently deletes database objects including their structure and all contained data.

DROP TABLE [IF EXISTS] table_name [, table_name2, ...];
DROP DATABASE [IF EXISTS] database_name;
DROP INDEX index_name ON table_name;

DROP operations are irreversible and immediately delete both the object definition and all associated data. The IF EXISTS clause prevents errors when attempting to drop non-existent objects. Multiple tables can be dropped in a single statement by listing them comma-separated.

Important considerations for DROP operations:

• Always maintain backups before dropping objects containing critical data;
• Dropping tables referenced by foreign keys may cause integrity issues unless constraints are addressed;
• User privileges associated with dropped tables persist and apply if a table with the same name is recreated;
• The operation requires DROP privilege on the table and CONTROL permissions on the schema.

RENAME Statement

The RENAME TABLE statement changes table names without altering structure or data.

RENAME TABLE old_table_name TO new_table_name
[, old_table_name2 TO new_table_name2, ...];

RENAME operations execute atomically, meaning multiple rename operations in a single statement complete together or fail together. This enables safe table swapping patterns where a production table can be replaced with a pre-populated version:

RENAME TABLE products TO products_old,
             products_new TO products;

The RENAME statement can also move tables between databases:

RENAME TABLE db1.table_name TO db2.table_name;

Alternatively, ALTER TABLE provides rename functionality:

ALTER TABLE old_table_name RENAME TO new_table_name;

Users require ALTER and DROP privileges on the original table, plus CREATE and INSERT privileges on the new table.

TRUNCATE Statement

The TRUNCATE TABLE statement removes all rows from a table while preserving the table structure:

TRUNCATE [TABLE] table_name;

TRUNCATE differs significantly from DELETE in its implementation and behavior:

• TRUNCATE deallocates data pages rather than deleting rows individually, making it substantially faster;
• TRUNCATE is a DDL operation that implicitly commits, preventing rollback outside explicit transactions;
• TRUNCATE does not activate DELETE triggers;
• TRUNCATE resets AUTO_INCREMENT counters to their initial values;
• TRUNCATE requires DROP privilege rather than DELETE privilege;

Foreign key constraints can prevent TRUNCATE operations unless temporarily disabled:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE table_name;
SET FOREIGN_KEY_CHECKS = 1;

MySQL 8.0 Atomic DDL

MySQL 8.0 introduced atomic DDL for the InnoDB storage engine, ensuring that each DDL statement executes as an indivisible unit that either fully completes or fully rolls back in case of failure. This eliminates the inconsistent states that could occur in earlier MySQL versions when DDL operations failed partway through.

Atomic DDL Characteristics:

• DDL operations complete atomically at the statement level, not at the transaction level;
• Data dictionary updates, storage engine operations, and binary log writes commit together as a single unit;
• Failed DDL statements automatically rollback all changes including metadata and file system modifications;
• Crash recovery ensures DDL operations complete or rollback properly upon server restart.

Implementation Phases:

Atomic DDL operations proceed through four phases:

1. Prepare Phase: Creates required objects and writes DDL logs to the mysql.innodb_ddl_log table defining rollback procedures;
2. Perform Phase: Executes the DDL operation, potentially repeating multiple times for complex operations like ALTER TABLE;
3. Commit Phase: Updates the data dictionary and commits the transaction;
4. Post-DDL Phase: Replays and removes DDL logs, performs file operations like renaming or removing data files, and cleans up dynamic metadata.

Recovery Behavior:

During crash recovery, MySQL determines whether to commit or rollback a DDL operation based on the presence of the data dictionary transaction in the redo log and binary log. If present, the operation commits; otherwise, it rolls back.

Critical Limitation:

Despite atomic DDL, each DDL statement implicitly commits any active transaction before execution. This means DDL cannot be combined with DML in user-controlled transactions for later rollback as a group. This behavior differs from PostgreSQL, which supports transactional DDL where multiple DDL and DML statements can be grouped in BEGIN/COMMIT blocks and rolled back together.

>> Explore more: How to Work with PostgreSQL in Golang using pgx Package?

MySQL Online DDL and Schema Evolution

MySQL Online DDL enables schema modifications while maintaining table availability for concurrent reads and writes, dramatically reducing downtime during schema changes.

Benefits:

• Tables remain accessible for queries and DML operations during DDL execution;
• Reduced locking and resource contention improves overall system scalability;
• Instant operations modify only metadata without touching table data;
• In-place operations avoid disk I/O and CPU overhead associated with table copying.

Limitations and Challenges

Performance Impact:

• CPU and memory usage increases, particularly for large tables and complex operations;
• Disk I/O can strain storage subsystems, increasing latency for other operations;
• Metadata locks during preparation and finalization can block concurrent operations.

Failure Conditions:

DDL operations may fail due to:

• Specified ALGORITHM incompatible with operation type or storage engine;
• Specified LOCK level incompatible with operation;
• Timeout waiting for exclusive metadata lock;
• Insufficient disk space in tmpdir or innodb_tmpdir for temporary files;
• Online alter log overflow from excessive concurrent DML exceeding innodb_online_alter_log_max_size;
• Concurrent DML creating duplicate values in unique indexes or NULL values in primary key columns.

Replication Considerations:

• DDL operations replicate to slaves where they execute sequentially, potentially causing replication lag on busy systems;
• Long-running DDL on the master creates corresponding delays on slaves;
• Row-based replication handles certain scenarios better than statement-based replication;
• Setting read_only=1 on slaves prevents accidental data modifications that break replication.

How Locking Works in MySQL Online DDL?

Online DDL aims to minimize blocking through controlled locking strategies:

Metadata Locks (MDL):

Metadata locks protect schema objects during DDL operations, preventing concurrent schema modifications that could cause data corruption. MySQL acquires shared metadata locks on tables accessed within transactions and defers release until transaction completion to ensure serializability.

DDL operations require exclusive metadata locks, which can only be acquired after all existing metadata locks are released. Long-running transactions holding metadata locks can cause DDL operations to wait, potentially blocking subsequent queries.

Lock Clause Options:

The LOCK clause controls concurrency during in-place operations:

• LOCK=NONE: Permits concurrent reads and writes during DDL.
• LOCK=SHARED: Allows concurrent reads but blocks writes.
• LOCK=EXCLUSIVE: Blocks all table access during DDL.
• LOCK=DEFAULT: Uses minimum locking required for the specific operation.

Operations halt immediately if the requested concurrency level is incompatible with the operation type.

Locking Phases:

Even with online DDL, brief exclusive locks occur:

• Initial Phase: Acquires brief metadata lock to prepare operation.
• Execution Phase: May briefly acquire exclusive metadata lock depending on operation.
• Final Phase: Always requires exclusive metadata lock to update table definition.

3 MySQL DDL Algorithms

MySQL 8 supports three primary ALTER TABLE algorithms with distinct performance and locking characteristics:

INSTANT Algorithm

The INSTANT algorithm performs metadata-only changes in the data dictionary without modifying table data or acquiring extended metadata locks. Operations complete in fractions of a second regardless of table size.

ALTER TABLE table_name
ADD COLUMN new_column datatype,
ALGORITHM=INSTANT;

MySQL 8.0.12 initially supported INSTANT for adding columns at the end of tables. MySQL 8.0.29 expanded support to adding or dropping columns at any position. However, tables support a maximum of 64 instant changes before requiring a full rebuild.

MySQL INSTANT DDL algorithm limitations:

• Columns cannot be added to tables using ROW_FORMAT=COMPRESSED;
• Columns cannot be added to tables with FULLTEXT indexes;
• Temporary tables do not support INSTANT;
• Tables in shared tablespaces cannot use INSTANT;
• Not all column types and operations qualify for INSTANT processing.

INPLACE Algorithm

The INPLACE algorithm modifies the original table without creating full copies when possible. It uses temporary files during operations and tracks concurrent DML changes in an online alter log:

ALTER TABLE table_name
ADD INDEX index_name (column_name),
ALGORITHM=INPLACE, LOCK=NONE;

Key INPLACE mechanisms:

• Temporary sort files written to the directory specified by innodb_tmpdir (defaults to /tmp);
• Online alter log captures concurrent DML during DDL execution, with maximum size controlled by innodb_online_alter_log_max_size (default 128MB);
• Brief exclusive metadata locks acquired at operation start and end;
• Operation fails if concurrent DML exceeds the online alter log capacity.

COPY Algorithm:

The COPY algorithm creates a temporary table with the modified structure, copies all rows from the original table, and swaps tables upon completion. This approach blocks concurrent DML and requires double the disk space:

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype,
ALGORITHM=COPY;

COPY is the most disruptive algorithm and should only be used when INSTANT and INPLACE are unavailable.

Algorithm Selection:

When ALGORITHM is not explicitly specified, MySQL attempts algorithms in order of preference: INSTANT first, then INPLACE, and finally COPY. Explicitly specifying an algorithm ensures predictable behavior and prevents unintended fallback to more expensive approaches.

DDL Privileges and Permissions

Executing DDL operations requires specific privileges depending on the statement type:

Common DDL Privileges:

• CREATE: Required to create databases, tables, indexes, and views;
• ALTER: Required to modify table structures;
• DROP: Required to delete databases, tables, and other objects;
• INDEX: Required to create or drop indexes;
• REFERENCES: Required to create foreign key constraints.

Granting Privileges:

GRANT CREATE, ALTER, DROP ON database_name.*
TO 'username'@'host';

GRANT ALL PRIVILEGES ON database_name.*
TO 'username'@'host';

Administrative privilege or GRANT OPTION is required to grant privileges to other users.

Implicit Commits in MySQL DDL

All DDL statements cause implicit commits before and after execution. This means if you have an open transaction with uncommitted data and you run a DDL command, your data is saved to the database immediately, and you can no longer roll it back. 

Statements that trigger an implicit commit include:

• Schema Objects: All CREATE, ALTER, and DROP statements for databases, tables, indexes, views, procedures, functions, triggers, events, and users.
• Table Management: TRUNCATE TABLE, RENAME TABLE, and RENAME USER.
• Access Control: GRANT, REVOKE, and SET PASSWORD.
• Administrative: ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE.

Key Consequences:

• Active transactions commit automatically before DDL execution;
• DDL statements cannot be rolled back in MySQL, even within explicit transactions;
• Each DDL statement executes in its own isolated transaction.:

Exceptions to the Rule: 

Implicit commits do not occur for:

• CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE;
• CREATE FUNCTION and DROP FUNCTION for User-Defined Functions (not stored functions);
• UNLOCK TABLES when used only with transactional tables.

Alternative Tools for Large-Scale DDL

For production environments with large tables or strict availability requirements, specialized tools provide safer alternatives to native MySQL DDL:

pt-online-schema-change (Percona Toolkit):

• Uses trigger-based synchronous approach to track changes
• Supports foreign keys and older MySQL versions (5.5+)
• Atomic RENAME TABLE for final cut-over
• Simpler setup but triggers add overhead on write-heavy workloads

gh-ost (GitHub):

• Triggerless design using binary log streaming
• Lower impact on master through asynchronous replication
• Fine-grained throttling, pause/resume capabilities, and interactive cut-over
• Requires row-based replication and MySQL 5.7+
• Does not support foreign keys

Best Practices for Safe DDL Operations

Planning and Testing:

• Test schema changes in staging environments mirroring production configurations
• Perform extensive testing to identify potential issues before production deployment
• Analyze resource impact on CPU, memory, and disk I/O
• Schedule disruptive operations during low-traffic maintenance windows

Algorithm Selection:

• Prefer INSTANT when supported for metadata-only changes
• Use INPLACE for operations that avoid table copies
• Reserve COPY for operations that cannot be performed otherwise
• Explicitly specify ALGORITHM to prevent unexpected fallback behavior

Monitoring and Safety:

• Monitor system metrics (CPU, memory, disk I/O) during DDL execution
• Set up alerts for resource spikes and bottlenecks
• Use tools like Prometheus and Grafana for real-time monitoring
• Track replication lag on slaves during DDL operations

Data Protection:

• Always maintain current backups before executing DDL on production systems
• Enable binary logging to support point-in-time recovery
• Test rollback procedures in non-production environments
• Document DDL changes for audit trails and troubleshooting

Metadata Lock Management:

• Identify and terminate long-running transactions before DDL execution to prevent blocking
• Monitor metadata lock wait states using Performance Schema metadata_locks table
• Implement connection timeouts to prevent indefinite DDL blocking

Concurrency Control:

• Use ALGORITHM and LOCK clauses to control operation behavior
• Specify LOCK=NONE for maximum concurrency on supported operations
• Be aware that brief metadata locks still occur even with LOCK=NONE

>> Read more: SQL vs NoSQL Databases: What's the Difference & When to Use?

Conclusion

MySQL DDL operations have evolved significantly with MySQL 8.0, introducing atomic DDL for crash-safe schema changes and expanding online DDL capabilities with the INSTANT algorithm. Understanding the distinctions between DDL algorithms (INSTANT, INPLACE, COPY), their locking behavior, and transactional characteristics is essential for effective database administration.

The implicit commit behavior of DDL statements remains a fundamental characteristic that distinguishes MySQL from databases like PostgreSQL. This design choice prioritizes DDL safety and crash recovery over transactional grouping of DDL and DML operations.

By following established best practices, database administrators can execute schema changes safely and efficiently while maintaining system availability and data integrity.

>>> Follow and Contact Relia Software for more information!