Table Options with MariaDB Enterprise Server

This page is part of MariaDB's Documentation.

The parent of this page is: Table DDL for MariaDB Enterprise Server

Topics on this page:

Overview
Compatibility
Table Options with CREATE TABLE
Table Options with ALTER TABLE
Accepted Table Options

Overview

MariaDB Enterprise Server's table options are used in CREATE TABLE and ALTER TABLE statements to define table characteristics.

Compatibility

MariaDB Enterprise Server 10.2
MariaDB Enterprise Server 10.3
MariaDB Enterprise Server 10.4
MariaDB Enterprise Server 10.5
MariaDB Enterprise Server 10.6

Table Options with `CREATE TABLE`

To set table options for a new table with MariaDB Enterprise Server, set the table options in the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) ROW_FORMAT=DYNAMIC;

Table Options with `ALTER TABLE`

To set table options for an existing table with MariaDB Enterprise Server, set the table options in the ALTER TABLE statement:

ALTER TABLE hq_sales.invoices
   ROW_FORMAT=DYNAMIC;

Accepted Table Options

MariaDB Enterprise Server supports the following table options with InnoDB:

Table Option	Description
`AUTO_INCREMENT`	Specifies the starting value for columns defined as AUTO_INCREMENT.
`COMMENT`	Specifies a user-readable comment for the table.
`DATA DIRECTORY`	Specifies the data directory for the tablespace. Requires the innodb_file_per_table system variable to be enabled. Only supported by the CREATE TABLE statement. Not supported by the ALTER TABLE statement.
`DEFAULT CHARACTER SET`	Specifies the default character set for columns which do not have a different character set explicitly specified.
`DEFAULT COLLATE`	Specifies the default collation for columns which do not have a different collation explicitly specified.
`ENCRYPTED`	Specifies how to encrypt the table. If the value is `DEFAULT`, or if the table option is unspecified, then the table's encryption status will depend on the innodb_encrypt_tables system variable. If the value is `OFF`, then the table will not be encrypted, even if the innodb_encrypt_tables system variable is set to `ON` or `FORCE`. If the value is `ON`, then the table will be encrypted, even if the innodb_encrypt_tables system variable is set to `OFF`.
`ENCRYPTION_KEY_ID`	Specifies which encryption key to use to encrypt the table. If the value is `DEFAULT` or if the table option is unspecified, then the table's encryption key will depend on the innodb_default_encryption_key_id system variable. If the value is any other value, then InnoDB will interpret the value as an encryption key ID, and InnoDB will use the corresponding encryption key to encrypt the table.
`KEY_BLOCK_SIZE`	Specifies the block size to use for the Compressed row format. If this table option is set, then the table will use the Compressed row format, even if `ROW_FORMAT=COMPRESSED` is not set. In general, the Compressed row format has been made obsolete by InnoDB page compression and compressed row format is transitioning to be read-only in the latest releases. This table option can be set to the values: `1`, `2`, `4`, `8`, and `16`. The value specifies the block size in `KB`. To positively affect performance, the specified value should almost always be less than the value of the innodb_page_size system variable. See Compressed Row Format for more details.
`PAGE_COMPRESSED`	Specifies whether the page should be compressed using InnoDB page compression. If the value is `DEFAULT`, or if the table option is unspecified, then the table's compression status will depend on the innodb_compression_default system variable. If the value is `OFF`, then the table will not be compressed, even if the innodb_compression_default system variable is set to `ON` or `FORCE`. If the value is `ON`, then the table will be compressed, even if the innodb_compression_default system variable is set to `OFF`. See Configure InnoDB Page Compression for more details.
`PAGE_COMPRESSION_LEVEL`	Specifies what compression level use to to compress the table. If the value is `DEFAULT`, or if the table option is unspecified, but the table is compressed through the `PAGE_COMPRESSED` table option or the innodb_compression_default system variable, then the table's compression level will depend on the innodb_compression_level system variable. If the value is any other value between `1` and `9`, then it is interpreted as the table's compression level. Higher numbers mean better compression. Compression levels are not supported by all compression algorithms. The compression level only applies if the innodb_compression_algorithm system variable is set to `zlib` or `lzma`. See Configure InnoDB Page Compression for more details.
`ROW_FORMAT`	Specifies the row format for the table. If the value is `DEFAULT`, or if the table option is unspecified, then the table's row format will depend on the innodb_default_row_format system variable. Other valid values are `REDUNDANT`, `COMPACT`, `DYNAMIC`, and `COMPRESSED`. The default and recommended row format is `DYNAMIC`. See InnoDB Row Formats for more details.
`STATS_AUTO_RECALC`	Specifies whether InnoDB's persistent statistics for this table should be automatically recalculated periodically. If the value is `DEFAULT`, or if the table option is unspecified, then the behavior for the table will depend on the innodb_stats_auto_recalc system variable. If the value is `OFF`, then persistent statistics for this table will not be automatically recalculated, even if the innodb_stats_auto_recalc system variable is set to `ON`. To update statistics, run the ANALYZE TABLE statement. If the value is `ON`, then persistent statistics for this table will be automatically recalculated, even if the innodb_stats_auto_recalc system variable is set to `OFF`.
`STATS_PERSISTENT`	Specifies whether InnoDB's persistent statistics should be used for this table. If the value is `DEFAULT`, or if the table option is unspecified, then the behavior for the table will depend on the innodb_stats_persistent system variable. If the value is `OFF`, then persistent statistics will not be used for this table, even if the innodb_stats_persistent system variable is set to `ON`. InnoDB's transient statistics will be used instead. If the value is `ON`, then persistent statistics will be used for this table, even if the innodb_stats_persistent system variable is set to `OFF`.
`STATS_SAMPLE_PAGES`	Specifies how many pages of this table should be read to calculate statistics. If the value is `0` or `DEFAULT`, or if the table option is unspecified, then the number of sample pages for the table will depend on the innodb_stats_sample_pages system variable.
`WITH SYSTEM VERSIONING`	Specifies that this table is a system-versioned table.