1 of 38

Data Definition (DDL)

Learn data definition language (DDL) statements in MariaDB Server. This section covers SQL commands for creating, altering, and dropping databases, tables, indexes, and other schema objects.

Atomic DDL

Learn about crash-safe DDL operations in MariaDB. This feature ensures data definition statements are either fully committed or completely rolled back, preventing metadata inconsistency.

We improved readability for DDL (Data Definition Language) operations to make most of them atomic, and the rest crash-safe, even if the server crashes in the middle of an operation.

These improvements were made in MariaDB 10.6.1.

The design of Atomic/Crash-safe DDL (MDEV-17567) allows it to work with all storage engines.

Definitions

Atomic means that either the operation succeeds (and is logged to the or is completely reversed.
Crash-safe means that in case of a crash, after the server has restarted, all tables are consistent, there are no temporary files or tables on disk and the binary log matches the status of the server.
DDL Data definition language.
DML Data manipulation language.

Background

Before 10.6, in case of a crash, there was a small possibility that one of the following things could happen:

There could be temporary tables starting with #sql-alter or #sql-shadow or temporary files ending with '' left.
The table in the storage engine and the table's .frm file could be out of sync.
During a multi-table rename, only some of the tables were renamed.

Which DDL Operations are Now Atomic

, except when used with , which is only crash safe.
and .

Which DDL Operations are Now Crash Safe

DROP TABLE of Multiple Tables.

over multiple tables is treated as if every DROP is a separate, atomic operation. This means that after a crash, all fully, or partly, dropped tables will be dropped and logged to the binary log. The undropped tables will be left untouched.

CREATE OR REPLACE TABLE

is implemented as:

This means that if there is a crash during CREATE TABLE then the original table 'foo' will be dropped even if the new table was not created. If the table was not re-created, the binary log will contain theDROP TABLE.

DROP DATABASE

is implemented as:

Each is atomic, but in case of a crash, things will work the same way as with multiple tables.

Atomic with Different Storage Engines

Atomic/Crash-safe DDL works with all storage engines that either have atomic DDLs internally or are able to re-execute DROP or RENAME in case of failure.

This should be true for most storage engines. The ones that still need some work are:

The .
The . Partitioning should be atomic for most cases, but there are still some known issues that need to be tested and fixed.

The DDL Log Recovery File

The new startup option (ddl_recovery.log by default) can be used to specify the place for the DDL log file. This is mainly useful in the case when one has a filesystem on persistent memory, as there is a lot of sync on this file during DDL operations.

This file contains all DDL operations that are in progress.

At MariaDB server startup, the DDL log file is copied to a file with the same base name but with a backup.log suffix. This is mainly done to be able to find out what went wrong if recovery fails.

If the server crashes during recovery (unlikely but possible), the recovery will continue where it was before. The recovery will retry each entry up to 3 times before giving up and proceeding with the next entry.

Conclusions

We believe that a clean separation of layers leads to an easier-to-maintain solution. The Atomic DDL implementation in introduced minimal changes to the storage engine API, mainly for native ALTER TABLE.
In our InnoDB implementation, no file format changes were needed on top of the RENAME undo log that was introduced in for a backup-safe TRUNCATE re-implementation. Correct use of sound design principles (write-ahead logging and transactions; also file creation now follows the ARIES protocol) is sufficient. We removed the hacks (at most one CREATE or DROP per transaction) and correctly implemented rollback and purge triggers for the InnoDB SYS_INDEXES table.
Numerous DDL recovery bugs in InnoDB were found and fixed quickly thanks to

Thanks to Atomic/Crash-safe DDL, the MariaDB server is now much more stable and reliable in unstable environments. There is still ongoing work to fix the few remaining issues mentioned above to make all DDL operations Atomic.

CONSTRAINT

Understand the syntax and usage of constraints in table definitions. This guide covers primary keys, foreign keys, unique, and check constraints to enforce data integrity.

MariaDB supports the implementation of constraints at the table-level using either or statements. A table constraint restricts the data you can add to the table. If you attempt to insert invalid data on a column, MariaDB throws an error.

Syntax

Renaming Databases

Learn the supported methods for renaming a database. Since RENAME DATABASE is not available, this guide outlines safe workarounds like dumping and reloading or moving tables.

There is no RENAME DATABASE statement. To rename a database, use one of the following procedures.

Using RENAME TABLE

Use this procedure only if your tables have neither triggers nor views or events.

To move your tables from a database named db1 to one named db2, run these statements:

Privileges are neither dropped for db1, nor are they "copied" to db2. Use to see which privileges are related to db1, then to db2.

Renaming Databases Manually

To overcome the limitations of the previous procedure, you can do the following.

In the following steps, the source database is named PROD , and the destination database TEST.

Full Backup and Restore: If restoring to a different server, use to create a full backup, then restore it to the intended server. (This step is optional. It is not necessary if your renamed database is to reside on the same computer.)

Dump Logical Objects.

RENAME TABLE does not work for triggers, events, and routines. You need to dump these logical objects separately.

Generate RENAME TABLE Commands.

Run the following query to generate a script with the necessary RENAME TABLE statements. This is much faster than a full logical dump.

ALTER

Access the reference for ALTER statements. This section lists commands to modify existing database objects, including tables, databases, users, and servers.

ALTER DATABASE

Modify database characteristics. Learn how to change global properties like the default character set and collation for a specific database.

Syntax

Description

ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE.

The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. See for more.

You can see what character sets and collations are available using, respectively, the and statements.

Changing the default character set/collation of a database does not change the character set/collation of any or that were previously created, and relied on the defaults. These need to be dropped and recreated in order to apply the character set/collation changes.

The database name can be omitted from the first syntax, in which case the statement applies to the default database.

The syntax that includes the UPGRADE DATA DIRECTORY NAME clause. It updates the name of the directory associated with the database to use the encoding implemented in MySQL for mapping database names to database directory names (see ). This clause is for use under these conditions:

It is intended when upgrading MySQL from older versions.
It is intended to update a database directory name to the current encoding format if the name contains special characters that need encoding.
The statement is used by (as invoked by ).

For example, if a database in MySQL 5.0 has a name of a-b-c, the name contains instance of the `-' character. In 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL, the same database name is encoded as a@002db@002dc to produce a file-system-neutral directory name.

When a MySQL installation is upgraded from an older version, the server displays a name such as a-b-c (which is in the old format) as #mysql50#a-b-c, and you must refer to the name using the #mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to explicitly tell the server to re-encode the database directory name to the current encoding format:

After executing this statement, you can refer to the database as a-b-c without the special #mysql50 prefix.

MariaDB starting with

From , it is possible to add a comment of a maximum of 1024 bytes. If the comment length exceeds this length, a error/warning code 4144 is thrown. The database comment is also added to the db.opt file, as well as to the .

Examples

ALTER FUNCTION

Change stored function characteristics. This statement allows modifying the security context or comments of a stored function without dropping it.

Syntax

Description

This statement can be used to change the characteristics of a stored function. More than one change may be specified in an ALTER FUNCTION statement. However, you cannot change the parameters or body of a stored function using this statement; to make such changes, you must drop and re-create the function using and .

You must have the ALTER ROUTINE privilege for the function. (That privilege is granted automatically to the function creator.) If binary logging is enabled, the ALTER FUNCTION statement might also require the SUPER privilege, as described in .

Example

ALTER LOGFILE GROUP

Understand the support status of this statement. Originally designed for NDB Cluster, it is not currently supported in MariaDB Server.

The ALTER LOGFILE GROUP statement is not supported by MariaDB. It was originally inherited from MySQL NDB Cluster. See MDEV-19295 for more information.

Syntax

ALTER LOGFILE GROUP logfile_group
    ADD UNDOFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
    ENGINE [=] engine_name

_{This page is licensed: GPLv2, originally from}

ALTER SERVER

Modify server definitions. Update connection information for external servers defined with CREATE SERVER, primarily used by the Federated engine.

Syntax

ALTER SERVER server_name
    OPTIONS (option [, option] ...)

Description

Alters the server information for server_name, adjusting the specified options as per the CREATE SERVER command. The corresponding fields in the are updated accordingly. This statement requires the privilege or, from , the privilege.

ALTER SERVER is not written to the , irrespective of the being used. From MariaDB 10.1.13, replicates the , ALTER SERVER and statements.

Examples

ALTER TABLESPACE

Understand the status of tablespace management. This statement, originally for NDB, is not supported in MariaDB for InnoDB tablespaces.

The ALTER TABLESPACE statement is not supported by MariaDB. It was originally inherited from MySQL NDB Cluster. In MySQL 5.7 and later, the statement is also supported for InnoDB. However, MariaDB has chosen not to include that specific feature. See for more information.

_{This page is licensed: GPLv2, originally from}

CREATE

Explore the CREATE statements used to define new database objects. This guide covers syntax for creating databases, tables, indexes, views, and stored routines.

CREATE DATABASE

Create a new database container. This command initializes a new database namespace with optional character set and collation settings.

Syntax

Description

CREATE DATABASE

CREATE TABLESPACE

Create a tablespace for data storage. This command defines a physical file container for storing table data, primarily for InnoDB or NDB engines.

The CREATE TABLESPACE statement is not supported by MariaDB. It was originally inherited from MySQL NDB Cluster. In MySQL 5.7 and later, the statement is also supported for InnoDB. However, MariaDB has chosen not to include that specific feature. See MDEV-19294 for more information.

_{This page is licensed: GPLv2, originally from}_{fill_help_tables.sql}

DROP

Find statements to remove database objects. This section details the syntax for deleting databases, tables, users, and other entities when they are no longer needed.

DROP DATABASE

Delete a database and all its contents. This command permanently removes the database directory and all tables, routines, and data within it.

Syntax

Description

DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the on the database. DROP SCHEMA is a synonym for DROP DATABASE.

Important: When a database is dropped, user privileges on the database are not automatically dropped. See .

IF EXISTS

Use IF EXISTS to prevent an error from occurring for databases that do not exist. A NOTE is generated for each non-existent database when using IF EXISTS. See .

Atomic DDL

supports .DROP DATABASE is implemented as

Each individual is atomic while DROP DATABASE as a whole is crash-safe.

Atomic DROP is not available.

Examples

DROP EVENT

Remove a scheduled event from the server. This command stops the event from executing and deletes its definition from the system tables.

Syntax

Description

This statement drops the named event_name

DROP INDEX

Remove an existing index from a table. This command deletes the index structure, potentially impacting query performance but freeing storage.

Syntax

Description

DROP INDEX drops the named index_name from the table tbl_name. This statement is mapped to an ALTER TABLE statement to drop the index.

If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

See .

Another shortcut, , allows the creation of an index.

To remove the primary key, PRIMARY must be specified as index_name. Note that are necessary, because PRIMARY is a keyword.

Privileges

Executing the DROP INDEX statement requires the privilege for the table or the database.

Online DDL

Online DDL is used by default with InnoDB, when the drop index operation supports it.

See for more information on online DDL with .

DROP INDEX IF EXISTS ...

If the IF EXISTS clause is used, then MariaDB will return a warning instead of an error if the index does not exist.

WAIT/NOWAIT

Sets the lock wait timeout. See .

Progress Reporting

MariaDB provides progress reporting for DROP INDEX statement for clients that support the new progress reporting protocol. For example, if you were using the client, then the progress report might look like this::

DROP LOGFILE GROUP

Remove a log file group. This statement, primarily for NDB Cluster, deletes the undo log files associated with the specified log file group.

The DROP LOGFILE GROUP statement is not supported by MariaDB. It was originally inherited from MySQL NDB Cluster. See MDEV-19295 for more information.

_{This page is licensed: CC BY-SA / Gnu FDL}

DROP PACKAGE BODY

Delete the body of a stored package. This command removes the implementation logic while preserving the package specification and interface.

Syntax

Description

The DROP PACKAGE BODY

DROP PACKAGE

Remove a stored package completely. This command deletes both the package specification (interface) and its body (implementation) from the database.

Syntax

Description

The DROP PACKAGE

DROP SERVER

Remove a server definition. This command deletes the connection details for a remote server used by the FEDERATED or SPIDER storage engines.

Syntax

Description

Drops the server definition for the server named server_name. The corresponding row within the will be deleted. This statement requires the

DROP TABLESPACE

Delete a tablespace. This command removes the physical file container used for storing table data, applicable to engines like InnoDB or NDB.

The DROP TABLESPACE statement is not supported by MariaDB. It was originally inherited from MySQL NDB Cluster. In MySQL 5.7 and later, the statement is also supported for InnoDB. However, MariaDB has chosen not to include that specific feature. See for more information.

_{This page is licensed: GPLv2, originally from}

DROP TRIGGER

Remove a trigger from a table. This command deletes the trigger definition, preventing it from firing on future INSERT, UPDATE, or DELETE events.

Syntax

Description

This statement drops a . The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. Its use requires the

CREATE EVENT

Schedule a new event for automatic execution. This statement defines a scheduled task that runs SQL commands at specific times or intervals.

Syntax

Description

This statement creates and schedules a new event. It requires the EVENT privilege for the schema in which the event is to be created.

The minimum requirements for a valid CREATE EVENT statement are as follows:

The keywords CREATE EVENT plus an event name, which uniquely identifies the event in the current schema.
An ON SCHEDULE clause, which determines when and how often the event executes.
A DO clause, which contains the SQL statement to be executed by an event.

Here is an example of a minimal CREATE EVENT statement:

The previous statement creates an event named myevent. This event executes once — one hour following its creation — by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1.

The event_name must be a valid MariaDB identifier with a maximum length of 64 characters. It may be delimited using back ticks, and may be qualified with the name of a database schema. An event is associated with both a MariaDB user (the definer) and a schema, and its name must be unique among names of events within that schema. In general, the rules governing event names are the same as those for names of stored routines. See .

If no schema is indicated as part of event_name, the default (current) schema is assumed.

For valid identifiers to use as event names, see .

OR REPLACE

The OR REPLACE clause works like this: If the event already exists, instead of an error being returned, the existing event will be dropped and replaced by the newly defined event.

The OR REPLACE clause is not available.

IF NOT EXISTS

If the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the event already exists. Cannot be used together with OR REPLACE.

ON SCHEDULE

The ON SCHEDULE clause can be used to specify when the event must be triggered.

AT

If you want to execute the event only once (one time event), you can use the AT keyword, followed by a timestamp. If you use , the event acts as soon as it is created. As a convenience, you can add one or more intervals to that timestamp. You can also specify a timestamp in the past, so that the event is stored but not triggered, until you modify it via .

The following example shows how to create an event that will be triggered tomorrow at a certain time:

You can also specify that an event must be triggered at a regular interval (recurring event). In such cases, use the EVERY clause followed by the interval.

If an event is recurring, you can specify when the first execution must happen via the STARTS clause and a maximum time for the last execution via the ENDS clause. STARTS and ENDS clauses are followed by a timestamp and, optionally, one or more intervals. The ENDS clause can specify a timestamp in the past, so that the event is stored but not executed until you modify it via .

In the following example, next month a recurring event will be triggered hourly for a week:

Intervals consist of a quantity and a time unit. The time units are the same used for other statements and time functions, except that you can't use microseconds for events. For simple time units, like HOUR or MINUTE, the quantity is an integer number, for example '10 MINUTE'. For composite time units, like HOUR_MINUTE or HOUR_SECOND, the quantity must be a string with all involved simple values and their separators, for example '2:30' or '2:30:30'.

ON COMPLETION [NOT] PRESERVE

The ON COMPLETION clause can be used to specify if the event must be deleted after its last execution (that is, after its AT or ENDS timestamp is past). By default, events are dropped when they are expired. To explicitly state that this is the desired behaviour, you can use ON COMPLETION NOT PRESERVE. Instead, if you want the event to be preserved, you can use ON COMPLETION PRESERVE.

In you specify ON COMPLETION NOT PRESERVE, and you specify a timestamp in the past for AT or ENDS clause, the event will be immediately dropped. In such cases, you will get a Note 1558: "Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation".

ENABLE/DISABLE/DISABLE ON SLAVE

Events are ENABLEd by default. If you want to stop MariaDB from executing an event, you may specify DISABLE. When it is ready to be activated, you may enable it using . Another option is DISABLE ON SLAVE, which indicates that an event was created on a master and has been replicated to the slave, which is prevented from executing the event. If DISABLE ON SLAVE is specifically set, the event will be disabled everywhere. It will not be executed on the master or the replicas.

COMMENT

The COMMENT clause may be used to set a comment for the event. Maximum length for comments is 64 characters. The comment is a string, so it must be quoted. To see events comments, you can query the (the column is named EVENT_COMMENT).

Examples

Minimal CREATE EVENT statement:

An event that will be triggered tomorrow at a certain time:

Next month a recurring event will be triggered hourly for a week:

OR REPLACE and IF NOT EXISTS:

CREATE FUNCTION

Define a stored function. This command creates a routine that accepts parameters, executes logic, and returns a single value for use in SQL expressions.

Syntax

Description

Use the CREATE FUNCTION statement to create a new . You must have the database privilege to use CREATE FUNCTION. A function takes any number of arguments and returns a value from the function body. The function body can be any valid SQL expression as you would use, for example, in any select expression. If you have the appropriate privileges, you can call the function exactly as you would any built-in function. See below for details on privileges.

You can also use a variant of the CREATE FUNCTION statement to install a user-defined function (UDF) defined by a plugin. See for details.

You can use a statement for the function body by enclosing it in parentheses, exactly as you would to use a subselect for any other expression. The SELECT statement must return a single value. If more than one column is returned when the function is called, error 1241 results. If more than one row is returned when the function is called, error 1242 results. Use a LIMIT clause to ensure only one row is returned.

You can also replace the RETURN clause with a compound statement. The compound statement must contain a RETURN statement. When the function is called, the RETURN statement immediately returns its result, and any statements after RETURN are effectively ignored.

By default, a function is associated with the current database. To associate the function explicitly with a given database, specify the fully-qualified name as db_name.func_name when you create it. If the function name is the same as the name of a built-in function, you must use the fully qualified name when you call it.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case-sensitive.

Each parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used.

For valid identifiers to use as function names, see .

RETURN

The RETURN clause can return a function body. In newer versions of MariaDB, it can also return a cursor (using SYS_REFCURSOR).

RETURN func_body

The RETURN clause can return a function body (a valid SQL PROCEDURE statement).

RETURN SYS_REFCURSOR

It can also return a cursor, if the function was declared with a SYS_REFCURSOR variable, like this:

Alternatively, a cursor can be returned in an OUT parameter, see .

AGGREGATE

It is possible to create stored aggregate functions as well. See for details.

IN | OUT | INOUT | IN OUT

OUT, INOUT and its equivalent IN OUT, are only valid if called from SET and not SELECT. These quantifiers are especially useful for creating functions with more than one return value. This allows functions to be more complex and nested.

Quantifiers are not available.

A cursor can be returned, like this:

This feature is not available.

RETURNS

The RETURNS clause specifies the return type of the function. NULL values are permitted with all return types.

What happens if the RETURN clause returns a value of a different type? It depends on the in effect at the moment of the function creation.

If the SQL_MODE is strict (STRICT_ALL_TABLES or STRICT_TRANS_TABLES flags are specified), a 1366 error will be produced.

Otherwise, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN clause returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members.

MariaDB stores the SQL_MODE system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked.

LANGUAGE SQL

LANGUAGE SQL is a standard SQL clause which can be used in MariaDB for portability. However that clause has no meaning, because SQL is the only supported language for stored functions.

A function is deterministic if it can produce only one result for a given list of parameters. If the result may be affected by stored data, server variables, random numbers or any value that is not explicitly passed, then the function is not deterministic. Also, a function is non-deterministic if it uses nondeterministic functions like or . The optimizer may choose a faster execution plan if it known that the function is deterministic. In such cases, you should declare the routine using the DETERMINISTIC keyword. If you want to explicitly state that the function is not deterministic (which is the default) you can use the NOT DETERMINISTIC keywords.

If you declare a non-deterministic function as DETERMINISTIC, you may get incorrect results. If you declare a deterministic function as NOT DETERMINISTIC, in some cases the queries will be slower.

OR REPLACE

If the optional OR REPLACE clause is used, it acts as a shortcut for:

with the exception that any existing for the function are not dropped.

IF NOT EXISTS

If the IF NOT EXISTS clause is used, MariaDB will return a warning instead of an error if the function already exists. Cannot be used together with OR REPLACE.

[NOT] DETERMINISTIC

The [NOT] DETERMINISTIC clause also affects , because the STATEMENT format can not be used to store or replicate non-deterministic statements.

CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA are informative clauses that tell the server what the function does. MariaDB does not check in any way whether the specified clause is correct. If none of these clauses are specified, CONTAINS SQL is used by default.

MODIFIES SQL DATA

MODIFIES SQL DATA means that the function contains statements that may modify data stored in databases. This happens if the function contains statements like , , , or DDL.

READS SQL DATA

READS SQL DATA means that the function reads data stored in databases, but does not modify any data. This happens if statements are used, but there no write operations are executed.

CONTAINS SQL

CONTAINS SQL means that the function contains at least one SQL statement, but it does not read or write any data stored in a database. Examples include or .

NO SQL

NO SQL means nothing, because MariaDB does not currently support any language other than SQL.

Oracle Mode

A subset of Oracle's PL/SQL language is supported in addition to the traditional SQL/PSM-based MariaDB syntax. See for details on changes when running Oracle mode.

Security

You must have the privilege on a function to call it. MariaDB automatically grants the EXECUTE and ALTER ROUTINE privileges to the account that called CREATE FUNCTION, even if the DEFINER clause was used.

Each function has an account associated as the definer. By default, the definer is the account that created the function. Use the DEFINER clause to specify a different account as the definer. You must have the privilege to use the DEFINER clause. See for details on specifying accounts.

The SQL SECURITY clause specifies what privileges are used when a function is called. If SQL SECURITY is INVOKER, the function body will be evaluated using the privileges of the user calling the function. If SQL SECURITY is DEFINER, the function body is always evaluated using the privileges of the definer account. DEFINER is the default.

This allows you to create functions that grant limited access to certain data. For example, say you have a table that stores some employee information, and that you've granted SELECT privileges to the user account roger.

To allow the user the get the maximum salary for a department, define a function and grant the EXECUTE privilege:

Since SQL SECURITY defaults to DEFINER, whenever the user roger calls this function, the subselect will execute with your privileges. As long as you have privileges to select the salary of each employee, the caller of the function will be able to get the maximum salary for each department without being able to see individual salaries.

Character Sets and Collations

Function return types can be declared to use any valid . If used, the COLLATE attribute needs to be preceded by a CHARACTER SET attribute.

If the character set and collation are not specifically set in the statement, the database defaults at the time of creation will be used. If the database defaults change at a later stage, the stored function character set/collation will not be changed at the same time; the stored function needs to be dropped and recreated to ensure the same character set/collation as the database is used.

Examples

The following example function takes a parameter, performs an operation using an SQL function, and returns the result.

You can use a compound statement in a function to manipulate data with statements like INSERT and UPDATE. The following example creates a counter function that uses a temporary table to store the current value. Because the compound statement contains statements terminated with semicolons, you have to first change the statement delimiter with the DELIMITER statement to allow the semicolon to be used in the function body. See for more.

Character set and collation:

Generated Columns

Generated columns can be virtual or persistent (stored).

Syntax

MariaDB's generated columns syntax is designed to be similar to the syntax for Microsoft SQL Server's computed columns and Oracle Database's virtual columns. The syntax is also compatible with the syntax for MySQL's generated columns.

MariaDB's generated columns syntax is designed to be similar to the syntax for Microsoft SQL Server's computed columns and Oracle Database's virtual columns. The syntax is not compatible with the syntax for MySQL's generated columns.

Description

A generated column is a column in a table that cannot explicitly be set to a specific value in a . Instead, its value is automatically generated based on an expression. This expression might generate the value based on the values of other columns in the table, or it might generate the value by calling or .

There are two types of generated columns:

PERSISTENT (a.k.a. STORED): This type's value is actually stored in the table.
VIRTUAL: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default.

Generated columns are also sometimes called computed columns or virtual columns.

Supported Features

Storage Engine Support

Generated columns can only be used with storage engines which support them. If you try to use a storage engine that does not support them, then you will see an error similar to the following:

, , and support generated columns.
A column in a table can be built on a PERSISTENT generated column.
- However, a column in a MERGE table can not be defined as a VIRTUAL and PERSISTENT generated column.

Data Type Support

All data types are supported when defining generated columns.

Using the column option is supported when defining generated columns.

Using the column option is not supported when defining generated columns.

Using the column option is supported when defining generated columns.

It does not work correctly, though. See .

Index Support

Using a generated column as a table's primary key is not supported. See for more information. If you try to use one as a primary key, then you will see an error similar to the following:

Using PERSISTENT generated columns as part of a is supported.

Referencing PERSISTENT generated columns as part of a is also supported.

However, using the ON UPDATE CASCADE, ON UPDATE SET NULL, or ON DELETE SET NULL clauses is not supported. If you try to use an unsupported clause, then you will see an error similar to the following:

Defining indexes on both VIRTUAL and PERSISTENT generated columns is supported.

If an index is defined on a generated column, then the optimizer considers using it in the same way as indexes based on "real" columns.

The optimizer can recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses. See .

The optimizer cannot recognize use of indexed virtual column expressions in the WHERE clause and use them to construct range and ref(const) accesses. See .

Statement Support

Generated columns are used in just as if they were "real" columns.

However, VIRTUAL and PERSISTENT generated columns differ in how their data is stored.
- Values for PERSISTENT generated columns are generated whenever a inserts or updates the row with the special DEFAULT value. This generates the columns value, and it is stored in the table like the other "real" columns. This value can be read by other just like the other "real" columns.

The statement supports generated columns.

Generated columns can be referenced in the , , and statements.

However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or . If a generated column is explicitly set to any other value, then the outcome depends on whether is enabled in . If it is not enabled, then a warning will be raised and the default generated value will be used instead. If it is enabled, then an error will be raised instead.

The statement has limited support for generated columns.

It supports defining generated columns in a new table.
It supports using generated columns to .
It does not support using the with generated columns.

The statement has limited support for generated columns.

It supports the MODIFY and CHANGE clauses for PERSISTENT generated columns.
It does not support the MODIFY clause for VIRTUAL generated columns if is not set to COPY. See for more information.

The statement supports generated columns.

The statement can be used to check whether a table has generated columns.

You can tell which columns are generated by looking for the ones where the Extra column is set to either VIRTUAL or PERSISTENT. For example:

Generated columns can be properly referenced in the NEW and OLD rows in .

support generated columns.

The statement supports generated columns.

Expression Support

Most legal, deterministic expressions which can be calculated are supported in expressions for generated columns.

Most are supported in expressions for generated columns.

However, some can't be supported for technical reasons. For example, If you try to use an unsupported function in an expression, an error is generated similar to the following:

are not supported in expressions for generated columns because the underlying data can change.

Using anything that depends on data outside the row is not supported in expressions for generated columns.

are not supported in expressions for generated columns. See for more information.

Non-deterministic are supported in expressions for not indexed VIRTUAL generated columns.

Non-deterministic are not supported in expressions for PERSISTENT or indexed VIRTUAL generated columns.

are supported in expressions for generated columns.

However, MariaDB can't check whether a UDF is deterministic, so it is up to the user to be sure that they do not use non-deterministic UDFs with VIRTUAL generated columns.

Defining a generated column based on other generated columns defined before it in the table definition is supported. For example:

However, defining a generated column based on other generated columns defined after in the table definition is not supported in expressions for generation columns because generated columns are calculated in the order they are defined.

Using an expression that exceeds 255 characters in length is supported in expressions for generated columns. The new limit for the entire table definition, including all expressions for generated columns, is 65,535 bytes.

Using constant expressions is supported in expressions for generated columns. For example:

Making Stored Values Consistent

When a generated column is PERSISTENT or indexed, the value of the expression needs to be consistent regardless of the flags in the current session. If it is not, then the table will be seen as corrupted when the value that should actually be returned by the computed expression and the value that was previously stored and/or indexed using a different setting disagree.

There are currently two affected classes of inconsistencies: character padding and unsigned subtraction:

For a VARCHAR or TEXT generated column the length of the value returned can vary depending on the PAD_CHAR_TO_FULL_LENGTH flag. To make the value consistent, create the generated column using an RTRIM() or RPAD() function. Alternately, create the generated column as a CHAR column so that its data is always fully padded.
If a SIGNED generated column is based on the subtraction of an UNSIGNED value, the resulting value can vary depending on how large the value is and the NO_UNSIGNED_SUBTRACTION flag. To make the value consistent, use to ensure that each UNSIGNED

A fatal error is generated when trying to create a generated column whose value can change depending on the when its data is PERSISTENT or indexed. For an existing generated column that has a potentially inconsistent value, a warning about a bad expression is generated the first time it is used (if warnings are enabled).

For an existing generated column that has a potentially inconsistent value, a warning about a bad expression is generated the first time it is used (if warnings are enabled).

A potentially inconsistent generated column outputs a warning when created or first used (without restricting the creation).

A potentially inconsistent generated column does not output a warning when created or first used.

Here is an example of two tables that are warned about:

The warnings look like this:

To work around the issue, force the padding or type to make the generated column's expression return a consistent value. For example:

Here is an example of two tables whose creation is rejected:

MySQL Compatibility Support

The STORED keyword is supported as an alias for the PERSISTENT keyword.
Tables created with MySQL 5.7 or later that contain can be imported into MariaDB without a dump and restore.

Implementation Differences

Generated columns are subject to various constraints in other DBMSs that are not present in MariaDB's implementation. Generated columns may also be called computed columns or virtual columns in different implementations. The various details for a specific implementation can be found in the documentation for each specific DBMS.

Implementation Differences Compared to Microsoft SQL Server

MariaDB's generated columns implementation does not enforce the following restrictions that are present in implementation:

MariaDB allows in generated column expressions, including those that change dynamically, such as .
MariaDB allows the function to be called with a named as an argument, even though time zone names and time offsets are configurable.
MariaDB allows the function to be used with non-unicode , even though character sets are configurable and differ between binaries/versions.

Microsoft SQL Server enforces the above restrictions by doing one of the following things:

Refusing to create computed columns.
Refusing to allow updates to a table containing them.
Refusing to use an index over such a column if it can not be guaranteed that the expression is fully deterministic.

In MariaDB, as long as the , language, and other settings that were in effect during the CREATE TABLE remain unchanged, the generated column expression will always be evaluated the same. If any of these things change, then please be aware that the generated column expression might not be evaluated the same way as it previously was.

If you try to update a virtual column, you will get an error if the default is enabled in , or a warning otherwise.

Development History

Generated columns was originally developed by Andrey Zhakov. It was then modified by Sanja Byelkin and Igor Babaev at Monty Program for inclusion in MariaDB. Monty did the work on to lift some of the limitations.

Examples

Here is an example table that uses both VIRTUAL andPERSISTENT virtual columns:

If you describe the table, you can easily see which columns are virtual by looking in the "Extra" column:

To find out what function(s) generate the value of the virtual column you can use SHOW CREATE TABLE:

If you try to insert non-default values into a virtual column, you will receive a warning and what you tried to insert will be ignored and the derived value inserted instead:

If the ZEROFILL clause is specified, it should be placed directly after the type definition, before the AS (<expression>):

You can also use virtual columns to implement a "poor man's partial index". See example at the end of .

ALTER TABLE

Modify table structures. This guide covers adding columns, changing data types, managing indexes, and other schema changes for existing tables.

Syntax

In the syntax, these options are the same as for the CREATE TABLE statement:

table_option
column_definition
partition_options
partition_definition

Description

ALTER TABLE enables you to change the structure of an existing table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change the comment for the table and the storage engine of the table.

If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

When adding a UNIQUE index on a column (or a set of columns) which have duplicated values, an error will be produced and the statement will be stopped. To suppress the error and force the creation of UNIQUE indexes, discarding duplicates, the option can be specified. This can be useful if a column (or a set of columns) should be UNIQUE but it contains duplicate values; however, this technique provides no control on which rows are preserved and which are deleted. Also, note that IGNORE is accepted but ignored in ALTER TABLE ... EXCHANGE PARTITION statements.

This statement can also be used to rename a table. For details see .

When an index is created, the storage engine may use a configurable buffer in the process. Incrementing the buffer speeds up the index creation. and allocate a buffer whose size is defined by or , also used for . allocates three buffers whose size is defined by .

Privileges

Executing the ALTER TABLE statement generally requires at least the privilege for the table or the database. If you are renaming a table, this also requires the , and privileges for the table or the database.

Online DDL

Online DDL is supported with the and clauses.

See for more information on online DDL with .

ALTER ONLINE TABLE

ALTER ONLINE TABLE also works for partitioned tables.

Online ALTER TABLE is available by executing the following:

See the alter specification for more information.

WAIT/NOWAIT

Set the lock wait timeout. See .

IF EXISTS

The IF EXISTS and IF NOT EXISTS clauses are available for the following clauses:

When IF EXISTS and IF NOT EXISTS are used in clauses, queries will not report errors when the condition is triggered for that clause. A warning with the same message text will be issued and the ALTER will move on to the next clause in the statement (or end if finished).

If this is directive is used after ALTER ... TABLE, you don't get an error if the table doesn't exist. Example:

Column Definitions

See for information about column definitions.

Index Definitions

See for information about index definitions.

The and statements can also be used to add or remove an index.

Character Sets and Collations

See for details on setting the .

Alter Specifications

Table Options

See for information about table options.

ADD COLUMN

Adds a column to the table. The syntax is the same as in . If you are using IF NOT_EXISTS the column will not be added if it was not there already. This is very useful when doing scripts to modify tables.

The FIRST and AFTER clauses affect the physical order of columns in the datafile. Use FIRST to add a column in the first (leftmost) position, or AFTER followed by a column name to add the new column in any other position. Note that, nowadays, the physical position of a column is usually irrelevant.

DROP COLUMN

Drops the column from the table. If you are using IF EXISTS you will not get an error if the column didn't exist. If the column is part of any index, the column will be dropped from them, except if you add a new column with identical name at the same time. The index will be dropped if all columns from the index were dropped. If the column was used in a view or trigger, you will get an error next time the view or trigger is accessed. Dropping a column that is part of a multi-column UNIQUE constraint is not permitted. For example:

The reason is that dropping column a would result in the new constraint that all values in column b be unique. In order to drop the column, an explicit DROP PRIMARY KEY and ADD PRIMARY KEY would be required.

MariaDB supports instant DROP COLUMN. DROP COLUMN of an indexed column would imply (and in the case of a non-UNIQUE multi-column index, possibly ADD INDEX). These will not be allowed with , but unlike prior versions, they can be allowed with

RESTRICT and CASCADE are allowed to make porting from other database systems easier. In MariaDB, they do nothing.

MODIFY COLUMN

Allows you to modify the type of a column. The column will be at the same place as the original column and all indexes on the column will be kept. Note that when modifying column, you should specify all attributes for the new column.

CHANGE COLUMN

Works like MODIFY COLUMN except that you can also change the name of the column. The column will be at the same place as the original column and all index on the column will be kept.

ALTER COLUMN

This lets you change column options.

RENAME INDEX/KEY

{% tabs %} {% tab title="Current" %} You can rename an index using the RENAME INDEX (or RENAME KEY) syntax, for example:

{% endtab %}

{% tab title="< 10.5.3" %} RENAME INDEX/KEY is not available. {% endtab %} {% endtabs %}

RENAME COLUMN

{% tabs %} {% tab title="Current" %} You can rename a column using the RENAME COLUMN syntax, for example:

{% endtab %}

{% tab title="< 10.5.3" %} RENAME COLUMN is not available. {% endtab %} {% endtabs %}

ADD PRIMARY KEY

Adds a primary key. For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY. See for more information.

DROP PRIMARY KEY

Drops a primary key. For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY. See for more information.

ADD FOREIGN KEY

Adds a foreign key. For FOREIGN KEY indexes, a reference definition must be provided. For FOREIGN KEY indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages.

First, you have to specify the name of the target (parent) table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH clause is accepted to improve the compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE and ON UPDATE clauses specify what must be done when a DELETE (or a REPLACE) statements attempts to delete a referenced row from the parent table, and when an UPDATE statement attempts to modify the referenced foreign key columns in a parent table row, respectively. The following options are allowed:

RESTRICT: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').
NO ACTION: Synonym for RESTRICT.
CASCADE: The delete/update operation is performed in both tables.

If either clause is omitted, the default behavior for the omitted clause is RESTRICT.

See for more information.

DROP FOREIGN KEY

Drops a foreign key. See for more information.

ADD INDEX

Adds a plain index. Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT or SPATIAL indexes. For limits on InnoDB indexes, see . See for more information.

DROP INDEX

Drops a plain index. Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT or SPATIAL indexes. See for more information.

ADD UNIQUE INDEX

Adds a unique index. The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index. For UNIQUE indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages. See for more information.

DROP UNIQUE INDEX

Drops a unique index. The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index. For UNIQUE indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages. See for more information.

ADD FULLTEXT INDEX

Adds a FULLTEXT index. See for more information.

DROP FULLTEXT INDEX

Drops a FULLTEXT index. See for more information.

ADD SPATIAL INDEX

Adds a SPATIAL index. See for more information.

DROP SPATIAL INDEX

Drops a SPATIAL index. See for more information.

ENABLE/ DISABLE KEYS

DISABLE KEYS disables all non unique keys for the table for storage engines that support this (at least MyISAM and Aria). This can be used to into empty tables. ENABLE KEYS enables all disabled keys.

RENAME TO

Renames a table. See also .

ADD CONSTRAINT

Modifies the table adding a on a particular column or columns.

Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraint fails, then the row will not be updated. One can use most deterministic functions in a constraint, including .

The constraint_name is optional. If you don't provide one in the ALTER TABLE statement, MariaDB auto-generates a name for you. This is done so that you can remove it later using clause.

You can disable all constraint expression checks by setting the variable to OFF. You may find this useful when loading a table that violates some constraints that you want to later find and fix in SQL.

To view constraints on a table, query :

DROP CONSTRAINT

DROP CONSTRAINT for UNIQUE and FOREIGN KEY and DROP CONSTRAINT for CHECK constraints were introduced in an earlier version of MariaDB.

Modifies the table, removing the given constraint.

When you add a constraint to a table, whether through a or statement, you can either set a constraint_name yourself, or allow MariaDB to auto-generate one for you. To view constraints on a table, query . For instance,

To remove a constraint from the table, issue an ALTER TABLE...DROP CONSTRAINT statement. For example:

ADD SYSTEM VERSIONING

Adds system versioning. See .

DROP SYSTEM VERSIONING

Drops system versioning. See .

ADD PERIOD FOR

See , or .

FORCE

ALTER TABLE ... FORCE forces MariaDB to rebuild the table.

With InnoDB, the table rebuild only reclaims unused space (i.e. the space previously used for deleted rows) if the system variable is set to ON (the default). If the system variable is OFF, the space will not be reclaimed, but it will be re-used for new data that's later added.

The rebuild may fail if conditions are violated due to a change in the . For example:

Partitions

ADD PARTITION

See for details.

ANALYZE PARTITION

See for details.

CHECK PARTITION

See for details.

COALESCE PARTITION

Reduces the number of HASH or KEY partitions in a table. See .

CONVERT PARTITION / TABLE

{% tabs %} {% tab title="Current" %} CONVERT PARTITION can be used to remove a partition from a table and make this an ordinary table. For example:

CONVERT TABLE will take an existing table and move this to another table as its own partition with a specified . For example the following moves normal_table to a partition of partitioned_table with a definition that its values, based on the PARTITION BY of the partitioned_table, are less than 12345.

The optional [{WITH | WITHOUT} VALIDATION] is permitted.

See for more details. {% endtab %}

{% tab title="< 11.4" %} CONVERT PARTITION can be used to remove a partition from a table and make this an ordinary table. For example:

The optional clause [{WITH | WITHOUT} VALIDATION] is not available.

See for more details. {% endtab %}

{% tab title="< 10.7.1" %} CONVERT PARTITION and CONVERT TABLE are not available. {% endtab %} {% endtabs %}

DROP PARTITION

Used to drop specific partitions (and discard all data within the specified partitions) for and partitions. See .

EXCHANGE PARTITION

{% tabs %} {% tab title="Current" %} This clause is used to exchange the contents of a partition with another table. This is performed by swapping the tablespaces of the partition with the other table.

The optional [{WITH | WITHOUT} VALIDATION] is permitted.

See for more details.

OPTIMIZE PARTITION

See for details.

REMOVE PARTITIONING

See .

REORGANIZE PARTITION

See .

TRUNCATE PARTITION

See .

DISCARD TABLESPACE

This is used to discard an InnoDB table's tablespace.

See for more information.

IMPORT TABLESPACE

This is used to import an InnoDB table's tablespace. The tablespace should have been copied from its original server after executing .

See for more information.

ALTER TABLE ... IMPORT only applies to InnoDB tables. Most other popular storage engines, such as Aria and MyISAM, will recognize their data files as soon as they've been placed in the proper directory under the datadir, and no special DDL is required to import them.

ALGORITHM

The ALTER TABLE statement supports the ALGORITHM clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE supports several different algorithms. An algorithm can be explicitly chosen for an ALTER TABLE operation by setting the ALGORITHM clause. The supported values are:

ALGORITHM=DEFAULT - This implies the default behavior for the specific statement, such as if no ALGORITHM clause is specified.
ALGORITHM=COPY
ALGORITHM=INPLACE

See for information on how the ALGORITHM clause affects InnoDB.

ALGORITHM=DEFAULT

The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if ALGORITHM is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.

The system variable is deprecated. Instead, the system variable defines the default algorithm for ALTER TABLE operations. This was removed in for the following reasons:

alter_algorithm was introduced as a replacement for the old_alter_table that was used to force the usage of the original alter table algorithm (copy) in cases where the new alter algorithm did not work. The new option was added as a way to force the usage of a specific algorithm when it should instead have made it possible to disable algorithms that would not work for some reason.
alter_algorithm introduced some cases where ALTER TABLE would not work without specifying the ALGORITHM=XXX option together with ALTER TABLE.
Having different values of alter_algorithm on the primary and replica could cause replicas to stop unexpectedly.

ALGORITHM=COPY

ALGORITHM=COPY is the name for the original algorithm from early MariaDB versions.

When ALGORITHM=COPY is set, MariaDB essentially does the following operations:

This algorithm is very inefficient, but it is generic, so it works for all storage engines.

If ALGORITHM=COPY is specified, then the copy algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single statement, so that the table is only rebuilt once.

{% tabs %} {% tab title="Current" %} ALTER TABLE can perform most operations with ALGORITHM=COPY, LOCK=NONE. See . {% endtab %}

{% tab title="< 11.2" %} ALTER TABLE cannot perform operations with ALGORITHM=COPY, LOCK=NONE. {% endtab %} {% endtabs %}

ALGORITHM=INPLACE

ALGORITHM=COPY can be incredibly slow, because the whole table has to be copied and rebuilt. ALGORITHM=INPLACE was introduced as a way to avoid this by performing operations in-place and avoiding the table copy and rebuild, when possible.

When ALGORITHM=INPLACE is set, the underlying storage engine uses optimizations to perform the operation while avoiding the table copy and rebuild. However, INPLACE is a bit of a misnomer, since some operations may still require the table to be rebuilt for some storage engines. Regardless, several operations can be performed without a full copy of the table for some storage engines.

A more accurate name would have been ALGORITHM=ENGINE, where ENGINE refers to an "engine-specific" algorithm.

If an operation supports ALGORITHM=INPLACE, then it can be performed using optimizations by the underlying storage engine, but it may rebuilt.

See for more.

ALGORITHM=NOCOPY

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to rebuild the clustered index, because when the clustered index has to be rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY was introduced as a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then it can be performed without rebuilding the clustered index.

If ALGORITHM=NOCOPY is specified for an ALTER TABLE operation that does not support ALGORITHM=NOCOPY, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to rebuild the clustered index, and perform unexpectedly slowly.

See for more.

ALGORITHM=INSTANT

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has to modify data files. ALGORITHM=INSTANT was introduced as a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=INSTANT, then it can be performed without modifying any data files.

If ALGORITHM=INSTANT is specified for an ALTER TABLE operation that does not support ALGORITHM=INSTANT, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to modify data files, and perform unexpectedly slowly.

See for more.

LOCK

The ALTER TABLE statement supports the LOCK clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE supports several different locking strategies. A locking strategy can be explicitly chosen for an ALTER TABLE operation by setting the LOCK clause. The supported values are:

DEFAULT

Acquire the least restrictive lock on the table that is supported for the specific operation. Permit the maximum amount of concurrency that is supported for the specific operation.

NONE

Acquire no lock on the table. Permit all concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised. From , ALTER TABLE can do most operations with ALGORITHM=COPY, LOCK=NONE, that is, in most cases, unless the algorithm and lock level are explicitly specified, ALTER TABLE will be performed using the COPY algorithm while simultaneously allowing concurrent DML statements on the altered table. If this is not desired, one can explicitly specify a different lock level or set old_mode to that will make ALGORITHM=COPY use LOCK=SHARED by default (but still allowing LOCK=NONE to be specified explicitly).

SHARED

Acquire a read lock on the table. Permit read-only concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised.

EXCLUSIVE

Acquire a write lock on the table. Do not permit concurrent DML.

Different storage engines support different locking strategies for different operations. If a specific locking strategy is chosen for an ALTER TABLE operation, and that table's storage engine does not support that locking strategy for that specific operation, then an error will be raised.

If the LOCK clause is not explicitly set, then the operation uses LOCK=DEFAULT.

is equivalent to LOCK=NONE. Therefore, the statement can be used to ensure that your ALTER TABLE operation allows all concurrent DML.

See for information on how the LOCK clause affects InnoDB.

Index Options

See page for meaning of the index options.

Progress Reporting

MariaDB provides progress reporting for ALTER TABLE statement for clients that support the new progress reporting protocol. For example, if you were using the client, then the progress report might look like this::

The progress report is also shown in the output of the statement and in the contents of the table.

See for more information.

Aborting ALTER TABLE Operations

If an ALTER TABLE operation is being performed and the connection is killed, the changes will be rolled back in a controlled manner. The rollback can be a slow operation as the time it takes is relative to how far the operation has progressed.

Aborting ALTER TABLE ... ALGORITHM=COPY was made faster by removing excessive undo logging (). This significantly shortened the time it takes to abort a running ALTER TABLE operation, compared with earlier releases.

Atomic ALTER TABLE

{% tabs %} {% tab title="Current" %} ALTER TABLE is atomic for most engines, including InnoDB, MyRocks, MyISAM and Aria (). This means that if there is a crash (server down or power outage) during an ALTER TABLE operation, after recovery, either the old table and associated triggers and status will be intact, or the new table will be active. In older MariaDB versions one could get leftover #sql-alter..', '#sql-backup..' or 'table_name.frm˝' files if the system crashed during the ALTER TABLE operation.

See for more information. {% endtab %}

{% tab title="< 10.6.1" %} Atomic ALTER TABLE is not available. {% endtab %} {% endtabs %}

Replication

{% tabs %} {% tab title="Current" %} ALTER TABLE got fully executed on the primary first, and only then was it replicated and started executing on replicas. was added to replicate sooner and begin executing on replicas, directly when it starts executing on the primary, not when it finishes. This way the replication lag caused by a heavy ALTER TABLE can be completely eliminated (). {% endtab %}

{% tab title="< 10.8.1" %} The option is not available. {% endtab %} {% endtabs %}

Examples

Adding a new column:

Dropping a column:

Modifying the type of a column:

Changing the name and type of a column:

Combining multiple clauses in a single ALTER TABLE statement, separated by commas:

Changing the storage engine and adding a comment:

Rebuilding the table (the previous example will also rebuild the table if it was already InnoDB):

Dropping an index:

Adding a unique index:

Adding a primary key for an with a constraint:

{% tabs %} {% tab title="Current" %} An ALTER query can be replicated faster with this statement, which must be run before the ALTER statement:

Binlog would contain two event groups, of which the first one gets delivered to replicas before ALTER is taken to actual execution on the primary:

{% endtab %}

{% tab title="< 10.8.1" %} This statement is not available:

CREATE TABLE

Define a new table structure. This fundamental command specifies columns, data types, indexes, and storage engine options for storing data.

Syntax

Description

Use the CREATE TABLE statement to create a table with the given name.

In its most basic form, the CREATE TABLE statement provides a table name followed by a list of columns, indexes, and constraints. By default, the table is created in the default database. Specify a database with db_name.tbl_name. If you quote the table name, you must quote the database name and table name separately as `db_name`.`tbl_name`. This is particularly useful for , because it allows to create a table into a database, which contains data from other databases. See .

If a table with the same name exists, error 1050 results. Use to suppress this error and issue a note instead. Use to see notes.

The CREATE TABLE statement automatically commits the current transaction, except when using the keyword.

For valid identifiers to use as table names, see .

If the default_storage_engine is set to ColumnStore , it needs setting on all UMs. Otherwise when the tables using the default engine are replicated across UMs, they will use the wrong engine. You should therefore not use this option as a session variable with ColumnStore.

can be between 0-6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

Privileges

Executing the CREATE TABLE statement requires the privilege for the table or the database.

CREATE OR REPLACE

If the OR REPLACE clause is used and the table already exists, then instead of returning an error, the server will drop the existing table and replace it with the newly defined table.

This syntax was originally added to make more robust if it has to rollback and repeat statements such as CREATE ... SELECT on replicas.

is basically the same as:

with the following exceptions:

If table_name was locked with it will continue to be locked after the statement.
Temporary tables are only dropped if the TEMPORARY keyword was used. (With , temporary tables are preferred to be dropped before normal tables).

Things to be Aware of With CREATE OR REPLACE

The table is dropped first (if it existed), after that the CREATE is done. Because of this, if the CREATE fails, then the table will not exist anymore after the statement. If the table was used with LOCK TABLES it will be unlocked.
One can't use OR REPLACE together with IF EXISTS.

CREATE TABLE IF NOT EXISTS

If the IF NOT EXISTS clause is used, then the table will only be created if a table with the same name does not already exist. If the table already exists, then a warning will be triggered by default.

CREATE TEMPORARY TABLE

Use the TEMPORARY keyword to create a temporary table that is only available to the current session. Temporary tables are dropped when the session ends. Temporary table names are specific to the session. They will not conflict with other temporary tables from other sessions even if they share the same name. They will shadow names of non-temporary tables or views, if they are identical. A temporary table can have the same name as a non-temporary table which is located in the same database. In that case, their name will reference the temporary table when used in SQL statements. You must have the privilege on the database to create temporary tables. If no storage engine is specified, the setting will determine the engine.

temporary tables cannot be created by setting the system variable, or using CREATE TEMPORARY TABLE LIKE. If you try, an error is returned. Explicitly creating a temporary table with ENGINE=ROCKSDB has never been permitted.

temporary tables cannot be created by setting the system variable, or using CREATE TEMPORARY TABLE LIKE. They can be specified, but fail silently, and a MyISAM table is created instead. Explicitly creating a temporary table with ENGINE=ROCKSDB has never been permitted.

Replicating temporary tables

By default, temporary tables are only created on the replica if the primary is using the .

The new deterministic rules for logging of temporary tables are:

The STATEMENT binlog format is used. If it is binlogged, 1 is stored in TABLE_SHARE->table_creation_was_logged. The user can change this behavior by setting to MIXED, STATEMENT in which case the create is logged in statement format also in MIXED mode (as before).

CREATE TABLE ... LIKE

Use the LIKE clause instead of a full table definition to create an empty table with the same definition as another table, including columns, indexes, and table options. Foreign key definitions, as well as any DATA DIRECTORY or INDEX DIRECTORY table options specified on the original table, will not be created.

LIKE does not preserve the TEMPORARY status of the original table. To make the new table TEMPORARY as well, use CREATE TEMPORARY TABLE ... LIKE.

LIKE does not work with , only base tables. Attempting to use it on a view will result in an error:

The same version of the table storage format as found in the original table is used for the new table.

CREATE TABLE ... LIKE performs the same checks as CREATE TABLE. So a statement may fail if a change in the renders it invalid. For example:

CREATE TABLE ... SELECT

You can create a table containing data from other tables using the CREATE ... SELECT statement. Columns will be created in the table for each field returned by the SELECT query.

You can also define some columns normally and add other columns from a SELECT. You can also create columns in the normal way and assign them some values using the query, this is done to force a certain type or other field characteristics. The columns that are not named in the query will be placed before the others. For example:

Remember that the query just returns data. If you want to use the same indexes, or the same columns attributes ([NOT] NULL, DEFAULT, AUTO_INCREMENT, CHECK constraints) in the new table, you need to specify them manually. Types and sizes are not automatically preserved if no data returned by the SELECT requires the full size, and VARCHAR could be converted into CHAR. The function can be used to force the new table to use certain types.

Aliases (AS) are taken into account, and they should always be used when you SELECT an expression (function, arithmetical operation, etc).

If an error occurs during the query, the table will not be created at all.

If the new table has a primary key or UNIQUE indexes, you can use the or REPLACE keywords to handle duplicate key errors during the query. IGNORE means that the newer values must not be inserted an identical value exists in the index. REPLACE means that older values must be overwritten.

If the columns in the new table are more than the rows returned by the query, the columns populated by the query will be placed after other columns. Note that if the strict SQL_MODE is on, and the columns that are not names in the query do not have a DEFAULT value, an error will raise and no rows will be copied.

are not used during the execution of a CREATE ... SELECT.

If the table already exists, an error similar to the following will be returned:

If the IF NOT EXISTS clause is used and the table exists, a note will be produced instead of an error.

To insert rows from a query into an existing table, can be used.

Column Definitions

Note:

MariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:

MariaDB will attempt to apply the constraint. See .

MariaDB accepts the shortcut format with a REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. For example:

Each definition either creates a column in the table or specifies and index or constraint on one or more columns. See below for details on creating indexes.

Create a column by specifying a column name and a data type, optionally followed by column options. See for a full list of data types allowed in MariaDB.

NULL and NOT NULL

Use the NULL or NOT NULL options to specify that values in the column may or may not be NULL, respectively. By default, values may be NULL. See also .

DEFAULT Column Option

Specify a default value using the DEFAULT clause. If you don't specify DEFAULT then the following rules apply:

If the column is not defined with NOT NULL, AUTO_INCREMENT or TIMESTAMP, an explicit DEFAULT NULL will be added. Note that in MySQL, you may get an explicit DEFAULT for primary key parts, if not specified with NOT NULL.

The default value will be used if you a row without specifying a value for that column, or if you specify for that column.

may also be used as the default value for a

You can use most functions in DEFAULT. Expressions should have parentheses around them. If you use a non deterministic function in DEFAULT then all inserts to the table will be in . You can even refer to earlier columns in the DEFAULT expression (excluding AUTO_INCREMENT columns):

The DEFAULT clause cannot contain any or , and a column used in the clause must already have been defined earlier in the statement.

It is possible to assign or columns a DEFAULT value.

You can also use DEFAULT ().

AUTO_INCREMENT Column Option

Use to create a column whose value can be set automatically from a simple counter. You can only use AUTO_INCREMENT on a column with an integer type. The column must be a key, and there can only be one AUTO_INCREMENT column in a table. If you insert a row without specifying a value for that column (or if you specify 0, NULL, or as the value), the actual value will be taken from the counter, with each insertion incrementing the counter by one. You can still insert a value explicitly. If you insert a value that is greater than the current counter value, the counter is set based on the new value. An AUTO_INCREMENT column is implicitly NOT NULL. Use to get the value most recently used by an statement.

ZEROFILL Column Option

If the ZEROFILL column option is specified for a column using a data type, then the column will be set to UNSIGNED and the spaces used by default to pad the field are replaced with zeros. ZEROFILL is ignored in expressions or as part of a , , or . ZEROFILL is a non-standard MariaDB and MySQL extension.

PRIMARY KEY Column Option

Use PRIMARY KEY to make a column a primary key. A primary key is a special type of a unique key. There can be at most one primary key per table, and it is implicitly NOT NULL.

Specifying a column as a unique key creates a unique index on that column. See the section below for more information.

UNIQUE KEY Column Option

Use UNIQUE KEY (or just UNIQUE) to specify that all values in the column must be distinct from each other. Unless the column is NOT NULL, there may be multiple rows with NULL in the column.

When any inserts or updates occur in the table, reading the binlog shows the hidden column (@3). it causes confusion for the user; we can document these behaviours.

See the section below for more information.

COMMENT Column Option

You can provide a comment for each column using the COMMENT clause. The maximum length is 1024 characters. Use the statement to see column comments.

REF_SYSTEM_ID

REF_SYSTEM_ID can be used to specify Spatial Reference System IDs for spatial data type columns. For example:

Generated Columns

There are two types of generated columns:

PERSISTENT or STORED: This type's value is actually stored in the table.
VIRTUAL: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default.

Generated columns are also sometimes called computed columns or virtual columns.

For a complete description about generated columns and their limitations, see .

COMPRESSED

Certain columns may be compressed. See .

INVISIBLE

Columns may be made invisible, and hidden in certain contexts. See .

WITH SYSTEM VERSIONING Column Option

Columns may be explicitly marked as included from system versioning. See for details.

WITHOUT SYSTEM VERSIONING Column Option

Columns may be explicitly marked as excluded from system versioning. See for details.

Index Definitions

INDEX and KEY are synonyms.

Index names are optional, if not specified an automatic name will be assigned. Index name are needed to drop indexes and appear in error messages when a constraint is violated.

For limits on InnoDB indexes, see .

Index Categories

Plain Indexes

Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT or SPATIAL indexes.

See for more information.

PRIMARY KEY

For PRIMARY KEY indexes, you can specify a name for the index, but it is ignored, and the name of the index is always PRIMARY. A warning is explicitly issued if a name is specified. Before then, the name was silently ignored.

See for more information.

UNIQUE

The UNIQUE keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.

For UNIQUE indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages.

Unique, if index type is not specified, is normally a BTREE index that can also be used by the optimizer to find rows. If the key is longer than the max key length for the used storage engine, a HASH key will be created. This enables MariaDB to enforce uniqueness for any type or number of columns.

See for more information.

FOREIGN KEY

For FOREIGN KEY indexes, a reference definition must be provided.

For FOREIGN KEY indexes, you can specify a name for the constraint, using the CONSTRAINT keyword. That name will be used in error messages.

RESTRICT: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300').
NO ACTION: Synonym for RESTRICT.
CASCADE: The delete/update operation is performed in both tables.

If either clause is omitted, the default behavior for the omitted clause is RESTRICT.

See for more information.

FULLTEXT

Use the FULLTEXT keyword to create full-text indexes.

See for more information.

SPATIAL

Use the SPATIAL keyword to create geometric indexes.

See for more information.

Index Options

KEY_BLOCK_SIZE Index Option

The KEY_BLOCK_SIZE index option is similar to the table option.

With the storage engine, if you specify a non-zero value for the KEY_BLOCK_SIZE table option for the whole table, then the table will implicitly be created with the table option set to COMPRESSED. However, this does not happen if you just set the KEY_BLOCK_SIZE index option for one or more indexes in the table. The storage engine ignores the KEY_BLOCK_SIZE index option. However, the statement may still report it for the index.

For information about the KEY_BLOCK_SIZE index option, see the table option below.

Index Types

Each storage engine supports some or all index types. See for details on permitted index types for each storage engine.

Different index types are optimized for different kind of operations:

BTREE is the default type, and normally is the best choice. It is supported by all storage engines. It can be used to compare a column's value with a value using the =, >, >=, <, <=, BETWEEN, and LIKE operators. BTREE can also be used to find NULL values. Searches against an index prefix are possible.
HASH is only supported by the MEMORY storage engine. HASH indexes can only be used for =, <=, and >= comparisons. It can not be used for the

Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no length is specified, the whole column will be indexed. ASC and DESC can be specified. Individual columns in the index can be explicitly sorted in ascending or descending order. This can be useful for optimizing certain ORDER BY cases (, , , ). Not only ascending, but also descending, indexes can be used to optimize and ().

Index columns names are listed between parenthesis. After each column, a prefix length can be specified. If no length is specified, the whole column will be indexed. ASC and DESC can be specified. Prior to , this was only for compatibility with other DBMSs, but had no meaning in MariaDB. From , individual columns in the index can now be explicitly sorted in ascending or descending order. This can be useful for optimizing certain ORDER BY cases (, , , ). From , not only ascending, but also descending, indexes can now be used to optimize

The maximum number of parts in an index is 32.

WITH PARSER Index Option

The WITH PARSER index option only applies to indexes and contains the fulltext parser name. The fulltext parser must be an installed plugin.

VISIBLE Index Option

Indexes can be declared visible. This is the default and it shows up in .

Indexes cannot be declared visible.

COMMENT Index Option

A comment of up to 1024 characters is permitted with the COMMENT index option.

The COMMENT index option allows you to specify a comment with user-readable text describing what the index is for. This information is not used by the server itself.

CLUSTERING Index Option

The CLUSTERING index option is only valid for tables using the storage engine.

IGNORED / NOT IGNORED

Indexes can be specified to be ignored by the optimizer. See .

Periods

MariaDB supports , or .

Constraint Expressions

MariaDB introduced two ways to define a constraint:

CHECK(expression) given as part of a column definition.
CONSTRAINT [constraint_name] CHECK (expression)

Before a row is inserted or updated, all constraints are evaluated in the order they are defined. If any constraints fails, then the row will not be updated. One can use most deterministic functions in a constraint, including .

If you use the second format and you don't give a name to the constraint, then the constraint will get a auto generated name. This is done so that you can later delete the constraint with .

One can disable all constraint expression checks by setting the variable check_constraint_checks to OFF. This is useful for example when loading a table that violates some constraints that you want to later find and fix in SQL.

See for more information.

Table Options

For each individual table you create (or alter), you can set some table options. The general syntax for setting options is:

The equal sign is optional.

Some options are supported by the server and can be used for all tables, no matter what storage engine they use; other options can be specified for all storage engines, but have a meaning only for some engines. Also, engines can .

If the IGNORE_BAD_TABLE_OPTIONS is enabled, wrong table options generate a warning; otherwise, they generate an error.

[STORAGE] ENGINE

[STORAGE] ENGINE specifies a for the table. If this option is not used, the default storage engine is used instead. That is, the session option value if it is set, or the value specified for the --default-storage-engine , or the default storage engine, . If the specified storage engine is not installed and active, the default value will be used, unless the NO_ENGINE_SUBSTITUTION is set (default). This is only true for CREATE TABLE, not for ALTER TABLE. For a list of storage engines that are present in your server, issue a .

AUTO_INCREMENT

AUTO_INCREMENT specifies the initial value for the primary key. This works for MyISAM, Aria, InnoDB, MEMORY, and ARCHIVE tables. You can change this option with ALTER TABLE, but in that case the new value must be higher than the highest value which is present in the AUTO_INCREMENT column. If the storage engine does not support this option, you can insert (and then delete) a row having the wanted value - 1 in the AUTO_INCREMENT column.

AVG_ROW_LENGTH

AVG_ROW_LENGTH is the average rows size. It only applies to tables using and storage engines that have the table option set to FIXED format.

MyISAM uses MAX_ROWS and AVG_ROW_LENGTH to decide the maximum size of a table (default: 256TB, or the maximum file size allowed by the system).

[DEFAULT] CHARACTER SET/CHARSET

[DEFAULT] CHARACTER SET (or [DEFAULT] CHARSET) is used to set a default character set for the table. This is the character set used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT is specified, the database's default character set will be used (except for the , which is utf8mb4 by default). See for details on setting the .

CHECKSUM/TABLE_CHECKSUM

CHECKSUM (or TABLE_CHECKSUM) can be set to 1 to maintain a live checksum for all table's rows. This makes write operations slower, but will be very fast. This option is only supported for and .

[DEFAULT] COLLATE

[DEFAULT] COLLATE is used to set a default collation for the table. This is the collation used for all columns where an explicit character set is not specified. If this option is omitted or DEFAULT is specified, the database's default option will be used (except for the , which uses utf8mb4_bin by default). See for details on setting the

COMMENT

COMMENT is a comment for the table. The maximum length is 2048 characters. Also used to define table parameters when creating a table.

CONNECTION

CONNECTION is used to specify a server name or a connection string for a , , .

DATA DIRECTORY/INDEX DIRECTORY

DATA DIRECTORY and INDEX DIRECTORY are supported for MyISAM and Aria, and DATA DIRECTORY is also supported by InnoDB if the server system variable is enabled, but only in CREATE TABLE, not in . So, carefully choose a path for InnoDB tables at creation time, because it cannot be changed without dropping and re-creating the table. These options specify the paths for data files and index files, respectively. If these options are omitted, the database's directory will be used to store data files and index files. Note that these table options do not work for tables (use the partition options instead), or if the server has been invoked with the . To avoid the overwriting of old files with the same name that could be present in the directories, you can use (an error will be issued if files already exist). These options are ignored if the NO_DIR_IN_CREATE is enabled (useful for replicas). Also note that symbolic links cannot be used for InnoDB tables.

DATA DIRECTORY works by creating symlinks from where the table would normally have been (inside the ) to where the option specifies. For security reasons, to avoid bypassing the privilege system, the server does not permit symlinks inside the datadir. Therefore, DATA DIRECTORY cannot be used to specify a location inside the datadir. An attempt to do so will result in an error 1210 (HY000) Incorrect arguments to DATA DIRECTORY.

DELAY_KEY_WRITE

DELAY_KEY_WRITE is supported by MyISAM and Aria, and can be set to 1 to speed up write operations. In that case, when data are modified, the indexes are not updated until the table is closed. Writing the changes to the index file altogether can be much faster. However, note that this option is applied only if the delay_key_write server variable is set to 'ON'. If it is 'OFF' the delayed index writes are always disabled, and if it is 'ALL' the delayed index writes are always used, disregarding the value of DELAY_KEY_WRITE.

ENCRYPTED

The ENCRYPTED table option can be used to manually set the encryption status of an table. See for more information.

Aria does not support the ENCRYPTED table option. See .

See for more information.

ENCRYPTION_KEY_ID

The ENCRYPTION_KEY_ID table option can be used to manually set the encryption key of an table. See for more information.

Aria does not support the ENCRYPTION_KEY_ID table option. See .

See for more information.

IETF_QUOTES

For the storage engine, the IETF_QUOTES option, when set to YES, enables IETF-compatible parsing of embedded quote and comma characters. Enabling this option for a table improves compatibility with other tools that use CSV, but is not compatible with MySQL CSV tables, or MariaDB CSV tables created without this option. Disabled by default.

INSERT_METHOD

INSERT_METHOD is only used with tables. This option determines in which underlying table the new rows should be inserted. If you set it to 'NO' (which is the default) no new rows can be added to the table (but you will still be able to perform INSERTs directly against the underlying tables). FIRST means that the rows are inserted into the first table, and LAST means that thet are inserted into the last table.

KEY_BLOCK_SIZE

KEY_BLOCK_SIZE is used to determine the size of key blocks, in bytes or kilobytes. However, this value is just a hint, and the storage engine could modify or ignore it. If KEY_BLOCK_SIZE is set to 0, the storage engine's default value will be used.

MIN_ROWS/MAX_ROWS

MIN_ROWS and MAX_ROWS let the storage engine know how many rows you are planning to store as a minimum and as a maximum. These values will not be used as real limits, but they help the storage engine to optimize the table. MIN_ROWS is only used by MEMORY storage engine to decide the minimum memory that is always allocated. MAX_ROWS is used to decide the minimum size for indexes.

PACK_KEYS

PACK_KEYS can be used to determine whether the indexes will be compressed. Set it to 1 to compress all keys. With a value of 0, compression will not be used. With the DEFAULT value, only long strings will be compressed. Uncompressed keys are faster.

PAGE_CHECKSUM

PAGE_CHECKSUM is only applicable to tables, and determines whether indexes and data should use page checksums for extra safety.

PAGE_COMPRESSED

PAGE_COMPRESSED is used to enable for tables.

PAGE_COMPRESSION_LEVEL

PAGE_COMPRESSION_LEVEL is used to set the compression level for for tables. The table must also have the table option set to 1.

Valid values for PAGE_COMPRESSION_LEVEL are 1 (the best speed) through 9 (the best compression), .

PASSWORD

PASSWORD is unused.

RAID_TYPE

RAID_TYPE is an obsolete option, as the raid support has been disabled since MySQL 5.0.

ROW_FORMAT

The ROW_FORMAT table option specifies the row format for the data file. Possible values are engine-dependent.

Supported MyISAM Row Formats

For , the supported row formats are:

FIXED
DYNAMIC
COMPRESSED

The COMPRESSED row format can only be set by the command line tool.

See for more information.

Supported Aria Row Formats

For , the supported row formats are:

PAGE
FIXED
DYNAMIC.

See for more information.

Supported InnoDB Row Formats

For , the supported row formats are:

COMPACT
REDUNDANT
COMPRESSED
DYNAMIC

If the ROW_FORMAT table option is set to FIXED for an InnoDB table, then the server will either return an error or a warning depending on the value of the system variable. If the system variable is set to OFF, then a warning is issued, and MariaDB will create the table using the default row format for the specific MariaDB server version. If the system variable is set to ON, then an error will be raised.

See for more information.

Other Storage Engines and ROW_FORMAT

Other storage engines do not support the ROW_FORMAT table option.

SEQUENCE

If the table is a , then it will have the SEQUENCE set to 1.

STATS_AUTO_RECALC

STATS_AUTO_RECALC indicates whether to automatically recalculate persistent statistics (see STATS_PERSISTENT, below) for an InnoDB table. If set to 1, statistics will be recalculated when more than 10% of the data has changed. When set to 0, stats will be recalculated only when an is run. If set to DEFAULT, or left out, the value set by the system variable applies. See .

STATS_PERSISTENT

STATS_PERSISTENT indicates whether the InnoDB statistics created by will remain on disk or not. It can be set to 1 (on disk), 0 (not on disk, the pre-MariaDB 10 behavior), or DEFAULT (the same as leaving out the option), in which case the value set by the system variable will apply. Persistent statistics stored on disk allow the statistics to survive server restarts, and provide better query plan stability. See .

STATS_SAMPLE_PAGES

STATS_SAMPLE_PAGES indicates how many pages are used to sample index statistics. If 0 or DEFAULT, the default value, the value is used. See .

TRANSACTIONAL

TRANSACTIONAL is only applicable for Aria tables. In future Aria tables created with this option will be fully transactional, but currently this provides a form of crash protection. See for more details.

UNION

UNION must be specified when you create a MERGE table. This option contains a comma-separated list of MyISAM tables which are accessed by the new table. The list is enclosed between parenthesis. Example: UNION = (t1,t2)

WITH SYSTEM VERSIONING

WITH SYSTEM VERSIONING is used for creating .

Partitions

If the PARTITION BY clause is used, the table will be . A partition method must be explicitly indicated for partitions and subpartitions. Partition methods are:

[LINEAR] creates a hash key which will be used to read and write rows. The partition function can be any valid SQL expression which returns an INTEGER number. Thus, it is possible to use the HASH method on an integer column, or on functions which accept integer columns as an argument. However, VALUES LESS THAN and VALUES IN clauses can not be used with HASH. An example:

[LINEAR] can be used for subpartitions, too.

[LINEAR] is similar to HASH, but the index has an even distribution of data. Also, the expression can only be a column or a list of columns. VALUES LESS THAN and VALUES IN clauses can not be used with KEY.
partitions the rows using on a range of values, using the VALUES LESS THAN operator. VALUES IN is not allowed with RANGE

Only and can be used for subpartitions, and they can be [LINEAR].

It is possible to define up to 8092 partitions and subpartitions.

The number of defined partitions can be optionally specified as PARTITION count. This can be done to avoid specifying all partitions individually. But you can also declare each individual partition and, additionally, specify a PARTITIONS count clause; in the case, the number of PARTITIONs must equal count.

Also see .

The PARTITION keyword is optional as part of the partition definition. Instead of this:

The following can be used:

The PARTITION keyword is not optional as part of the partition definition. You must use this syntax:

Sequences

CREATE TABLE can also be used to create a . See and .

Atomic DDL

MariaDB supports . CREATE TABLE is atomic, except for CREATE OR REPLACE, which are only crash-safe.

Examples

This example shows a couple of things:

Usage of IF NOT EXISTS; If the table already existed, it will not be created. There will not be any error for the client, just a warning.
How to create a PRIMARY KEY that is .
How to specify a table-specific and another for a column.

The following clauses will work:

Data Definition (DDL)

Atomic DDL

Definitions

Background

Which DDL Operations are Now Atomic

Which DDL Operations are Now Crash Safe

DROP TABLE of Multiple Tables.

CREATE OR REPLACE TABLE

DROP DATABASE

Atomic with Different Storage Engines

The DDL Log Recovery File

Conclusions

See Also

CONSTRAINT

Syntax

Renaming Databases

Using RENAME TABLE

Renaming Databases Manually

ALTER

ALTER DATABASE

Syntax

Description

Examples

See Also

ALTER FUNCTION

Syntax

Description

Example

See Also

ALTER LOGFILE GROUP

Syntax

ALTER SERVER

Syntax

Description

Examples

See Also

ALTER TABLESPACE

CREATE

CREATE DATABASE

Syntax

Description

CREATE TABLESPACE

DROP

DROP DATABASE

Syntax

Description

IF EXISTS

Atomic DDL

Examples

See Also

DROP EVENT

Syntax

Description

DROP INDEX

Syntax

Description

Privileges

Online DDL

DROP INDEX IF EXISTS ...

WAIT/NOWAIT

Progress Reporting

See Also

DROP LOGFILE GROUP

DROP PACKAGE BODY

Syntax

Description

DROP PACKAGE

Syntax

Description

DROP SERVER

Syntax

Description

DROP TABLESPACE

DROP TRIGGER

Syntax

Description

Data Definition (DDL)

ALTER FUNCTION

Syntax

Description