MariaDB Xpand Auto-Increment Columns

For best performance, every MariaDB Xpand table should have a primary key column. If your table does not have a column or a set of columns that could act as a natural primary key, then you can define a single AUTO_INCREMENT column, which can serve as the table's primary key. This page also discusses AUTO_UNIQUE columns, which may perform better than AUTO_INCREMENT for the Xpand Performance Topology.

Overview

MariaDB Xpand supports auto-increment columns:

  • The process to use auto-increment columns with Xpand is identical to the process to use auto-increment columns with InnoDB.

  • A column is designated as an auto-increment column by specifying the AUTO_INCREMENT column option.

  • For Xpand, the SHOW CREATE TABLE statement does not accurately show the table's current AUTO_INCREMENT value.

  • For the Xpand Performance topology, AUTO_UNIQUE is often a better alternative than AUTO_INCREMENT.

This documentation applies to both Xpand topologies. Most examples are designed for the Xpand Performance Topology. However, the examples explicitly mention any differences that are relevant for the Xpand Storage Engine Topology.

Auto-Increment vs. Auto-Unique

Auto-increment values are designed to increase monotonically. It can be very inefficient for Xpand to generate strictly consecutive IDs. To guarantee that an auto-increment value is generated consecutively, the database must find the most recent value, increment that value, and then return the next value. This process is repeated for each subsequent insert so that each transaction gets a unique value. Since this process must be serialized, auto-increment becomes a bottleneck for workloads with high concurrency.

Xpand supports auto-unique as an alternative to auto-increment. Like auto-increment values, auto-unique values are still guaranteed to be unique. However, they are not guaranteed to generate values in a monotonically increasing sequence.

Auto-unique values are only supported in the Xpand Performance topology. In an Xpand Storage Engine topology, please use auto-increment.

Examples

Create an Xpand Table with an Auto-Increment Column

To create an Xpand table with an auto-increment column in either Xpand topology, use the CREATE TABLE statement, and specify the AUTO_INCREMENT column 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)
);

Note

If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Create an Xpand Table with an Auto-Unique Column

To create an Xpand table with an auto-increment column in an Xpand Performance topology, use the CREATE TABLE statement, and specify the AUTO_UNIQUE column option:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_UNIQUE 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)
);

Replace an Auto-Increment Column with Auto-Unique

To replace an auto-increment column with auto-unique in an Xpand Performance topology, use the ALTER TABLE ... MODIFY COLUMN statement, and specify the AUTO_UNIQUE column option:

ALTER TABLE hq_sales.invoices
   MODIFY COLUMN invoice_id BIGINT UNSIGNED AUTO_UNIQUE NOT NULL;

Choosing Data Types for Auto-Increment

When designing a schema, AUTO_INCREMENT columns should use integer data types. The following types can be used:

Data Type

Signed Range

Unsigned Range

TINYINT

-128 - 127

0 - 255

SMALLINT

-32768 - 32767

0 - 65535

MEDIUMINT

-8388608 - 8388607

0 - 16777215

INT

-2147483648 - 2147483647

0 - 4294967295

BIGINT

-9223372036854775808 - 9223372036854775807

0 - 9223372036854775807

Note

Xpand only supports UNSIGNED BIGINT values in the range 0 to 9223372036854775807, rather than 0 to 18446744073709551615.

To determine which type to use, consider the following points:

  1. Do you want to be able to manually insert negative values? If not, then specify the UNSIGNED attribute for the column.

    Xpand can't generate negative AUTO_INCREMENT values, so it is only beneficial to use a signed integer column if you want the option to manually insert negative values, which would bypass the AUTO_INCREMENT handling.

  2. How large will your table grow?

    If your AUTO_INCREMENT column is being used as the Primary Key, then the maximum value for the chosen data type should be considered the maximum number of rows that can fit in the table:

    Data Type

    Maximum Number of Rows (Signed)

    Maximum Number of Rows (Unsigned)

    TINYINT

    127

    255

    SMALLINT

    32767

    65535

    MEDIUMINT

    8388607

    16777215

    INT

    2147483647

    4294967295

    BIGINT

    9223372036854775807

    9223372036854775807

    If you want to give your table the most room to grow, then it would be best to choose BIGINT UNSIGNED.

Creating Tables with Auto-Incrementing Columns

Let's create an Xpand table with an AUTO_INCREMENT column:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    

    The listener port (Configure a Listener) should be specified by the --port option. When the listener uses TCP port 3306, you do not need to specify the port to the client.

    The user account (Create Application Database User) should be specified by the --user option.

    MaxScale's Read/Write Split Router (Configure Read/Write Split Router) will route the connection to one of the Xpand nodes (Configure Server Objects).

  2. Use the CREATE DATABASE statement to create the database:

    CREATE DATABASE hq_sales;
    

    Note

    In a Xpand Storage Engine Topology, the ES nodes do not synchronize databases automatically. MariaDB Replication can be used to synchronize databases and other objects. Without replication, the database needs to be created separately on each ES node.

  3. Use the CREATE TABLE statement to create the table, and specify the AUTO_INCREMENT column option to designate the auto-increment column:

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

    Note

    If you are using an Xpand Storage Engine Topology, you must also specify ENGINE=Xpand, unless default_storage_engine is set to Xpand.

Inserting Rows with Auto-Incrementing Column

If a column is specified as AUTO_INCREMENT, then its value will be automatically generated. There are multiple ways to insert rows with these automatically generated values.

Omitting the Column

If the column is not specified, then Xpand automatically generates the value.

Let's insert a row into the table created in the Creating Table with an Auto-Incrementing Column section:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    
  2. Insert a row with the INSERT statement, but do not specify the AUTO_INCREMENT column:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 1, '2020-05-10 12:35:10', 1087.23, 'CREDIT_CARD');
    
  3. Select the same row with the SELECT statement to confirm that a value was automatically generated:

    SELECT invoice_id
    FROM hq_sales.invoices
    WHERE branch_id = 1
       AND customer_id = 1
       AND invoice_date = '2020-05-10 12:35:10';
    
    +------------+
    | invoice_id |
    +------------+
    |          1 |
    +------------+
    

Specifying the Column's Value as 0

If the column's value is specified as 0, then Xpand automatically generates the value if the sql_mode system variable does not contain NO_AUTO_VALUE_ON_ZERO.

Let's insert a row into the table created in the Creating Tables with Auto-Incrementing Columns section:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    
  2. Confirm that the session's value of the sql_mode system variable does not contain NO_AUTO_VALUE_ON_ZERO with the SHOW SESSION VARIABLES statement:

    SHOW SESSION VARIABLES LIKE 'sql_mode';
    
    +---------------+-------------------------------------------------------------------------------------------+
    | Variable_name | Value                                                                                     |
    +---------------+-------------------------------------------------------------------------------------------+
    | sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +---------------+-------------------------------------------------------------------------------------------+
    
  3. Insert a row with the INSERT statement, and specify the AUTO_INCREMENT column's value as 0:

    INSERT INTO hq_sales.invoices
       (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (0, 1, 2, '2020-05-10 14:17:32', 1508.57, 'WIRE_TRANSFER');
    
  4. Select the same row with the SELECT statement to confirm that a value was automatically generated:

    SELECT invoice_id
    FROM hq_sales.invoices
    WHERE branch_id = 1
       AND customer_id = 2
       AND invoice_date = '2020-05-10 14:17:32';
    
    +------------+
    | invoice_id |
    +------------+
    |          2 |
    +------------+
    

Specifying Column Values as NULL

If the column's value is specified as NULL, then Xpand automatically generates the value if the column is defined as NOT NULL.

Let's insert a row into the table created in the Creating Tables with Auto-Incrementing Columns section:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    
  2. Insert a row with the INSERT statement, and specify the AUTO_INCREMENT column's value as NULL:

    INSERT INTO hq_sales.invoices
       (invoice_id, branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (NULL, 1, 3, '2020-05-10 14:25:16', 227.15, 'CASH');
    
  3. Select the same row with the SELECT statement to confirm that a value was automatically generated:

    SELECT invoice_id
    FROM hq_sales.invoices
    WHERE branch_id = 1
       AND customer_id = 3
       AND invoice_date = '2020-05-10 14:25:16';
    
    +------------+
    | invoice_id |
    +------------+
    |          3 |
    +------------+
    

Getting Last Inserted Auto-Increment Values

After Xpand inserts an automatically generated value into an AUTO_INCREMENT column, the application sometimes needs to know what value it inserted.

For example, the application may need to use the value to insert a foreign key column in a dependent table. The LAST_INSERT_ID() function can be used to get the lasted inserted value for an AUTO_INCREMENT column without re-reading the row from the table.

Let's insert a row into the table created in the Creating Tables with Auto-Incrementing Columns section and then use the LAST_INSERT_ID() function:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    
  2. Insert a row with the INSERT statement, but do not specify the AUTO_INCREMENT column:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 4, '2020-05-10 12:37:22', 104.19, 'CREDIT_CARD');
    
  3. Execute the LAST_INSERT_ID() function to get the AUTO_INCREMENT value for the new row:

    SELECT LAST_INSERT_ID();
    
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |                4 |
    +------------------+
    
  4. Select the same row with the SELECT statement to confirm that the AUTO_INCREMENT column has the same value:

    SELECT invoice_id
    FROM hq_sales.invoices
    WHERE branch_id = 1
       AND customer_id = 4
       AND invoice_date = '2020-05-10 12:37:22';
    
    +------------+
    | invoice_id |
    +------------+
    |          4 |
    +------------+
    

Setting Next Auto-Increment Value

A table's next AUTO_INCREMENT value can be set with the ALTER TABLE statement. The value is set using the AUTO_INCREMENT table option.

Let's alter the AUTO_INCREMENT value for the table created in the Creating Tables with Auto-Incrementing Columns section and then insert a row into the table, so we can confirm that it uses the new value:

  1. Use the MariaDB Client to connect to the listener port on the MaxScale node:

    $ mariadb --host=192.0.2.10 \
       --port=3307 \
       --user=xpand_app_user \
       --password
    
  2. Alter the table's next AUTO_INCREMENT value with the ALTER TABLE statement:

    ALTER TABLE hq_sales.invoices AUTO_INCREMENT = 100;
    
  3. Insert a row with the INSERT statement, but do not specify the AUTO_INCREMENT column:

    INSERT INTO hq_sales.invoices
       (branch_id, customer_id, invoice_date, invoice_total, payment_method)
    VALUES
       (1, 5, '2020-05-10 12:43:19', 1105.98, 'CREDIT_CARD');
    
  4. Execute the LAST_INSERT_ID() function to get the AUTO_INCREMENT value for the new row:

    SELECT LAST_INSERT_ID();
    
    +------------------+
    | LAST_INSERT_ID() |
    +------------------+
    |              100 |
    +------------------+
    
  5. Select the same row with the SELECT statement to confirm that the AUTO_INCREMENT column has the same value:

    SELECT invoice_id
    FROM hq_sales.invoices
    WHERE branch_id = 1
       AND customer_id = 5
       AND invoice_date = '2020-05-10 12:43:19';
    
    +------------+
    | invoice_id |
    +------------+
    |        100 |
    +------------+
    

Resources

MariaDB Knowledge Base

Reference

SQL Statements