MariaDB Enterprise Server Create an InnoDB Table

In MariaDB Enterprise Server, the InnoDB storage engine is used for new tables by default.

Examples

default_storage_engine System Variable

The default_storage_engine system variable configures the default storage engine for new tables when the ENGINE table option is not provided in the CREATE TABLE statement. The default_storage_engine system variable is set to InnoDB by default, so tables use the InnoDB storage engine by default:

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)
);

ENGINE Table Option

InnoDB tables can be created using the CREATE TABLE statement with the ENGINE=InnoDB table option:

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)
) ENGINE=InnoDB;

Table Options Supported by InnoDB

InnoDB tables support many different table options, which can determine many different characteristics of the table and how InnoDB stores or operates on the table.

InnoDB supports the following table options:

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

Create an InnoDB Table by Default

The default_storage_engine system variable configures the default storage engine for new tables when the ENGINE table option is not provided in the CREATE TABLE statement. The default_storage_engine system variable is set to InnoDB by default, so tables use the InnoDB storage engine by default:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES
       LIKE 'default_storage_engine';
    
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    
  3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. Create the table using 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)
    );
    
  5. Confirm that the table uses InnoDB by querying the information_schema.TABLES table:

    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='hq_sales'
    AND TABLE_NAME='invoices';
    
    +--------------+------------+--------+
    | TABLE_SCHEMA | TABLE_NAME | ENGINE |
    +--------------+------------+--------+
    | hq_sales     | invoices   | InnoDB |
    +--------------+------------+--------+
    

Creating an InnoDB Table using ENGINE

InnoDB tables can be created using the CREATE TABLE statement with the ENGINE=InnoDB table option:

  1. Connect to the server using MariaDB Client:

    $ mariadb --user=root
    
  2. Confirm that the default storage engine is InnoDB by checking the default_storage_engine system variable using the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES LIKE 'default_storage_engine';
    
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | default_storage_engine | Aria  |
    +------------------------+-------+
    
  3. If the database does not exist, then create the database for the table using the CREATE DATABASE statement:

    CREATE DATABASE hq_sales;
    
  4. Create the table using the CREATE TABLE statement and specify ENGINE=InnoDB as a table option:

    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)
    ) ENGINE=InnoDB;
    
  5. Confirm that the table uses InnoDB by querying the information_schema.TABLES table:

    SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='hq_sales'
    AND TABLE_NAME='invoices';
    
    +--------------+------------+--------+
    | TABLE_SCHEMA | TABLE_NAME | ENGINE |
    +--------------+------------+--------+
    | hq_sales     | invoices   | InnoDB |
    +--------------+------------+--------+