Table Options with MariaDB Enterprise Server

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

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