AUTO_RANDOM New in v3.1.0

User scenario

Since the value of AUTO_RANDOM is random and unique, AUTO_RANDOM is often used in place of AUTO_INCREMENT to avoid write hotspot in a single storage node caused by TiDB assigning consecutive IDs. If the current AUTO_INCREMENT column is a primary key and the type is BIGINT, you can execute the ALTER TABLE t MODIFY COLUMN id BIGINT AUTO_RANDOM(5); statement to switch from AUTO_INCREMENT to AUTO_RANDOM.

For more information about how to handle highly concurrent write-heavy workloads in TiDB, see Highly concurrent write best practices.

Basic concepts

AUTO_RANDOM is a column attribute that is used to automatically assign values to a BIGINT column. Values assigned automatically are random and unique.

To create a table with an AUTO_RANDOM column, you can use the following statements. The AUTO_RANDOM column must be included in a primary key, and the AUTO_RANDOM column is the first column in the primary key.

CREATE TABLE t (a BIGINT AUTO_RANDOM, b VARCHAR(255), PRIMARY KEY (a)); CREATE TABLE t (a BIGINT PRIMARY KEY AUTO_RANDOM, b VARCHAR(255)); CREATE TABLE t (a BIGINT AUTO_RANDOM(6), b VARCHAR(255), PRIMARY KEY (a)); CREATE TABLE t (a BIGINT AUTO_RANDOM(5, 54), b VARCHAR(255), PRIMARY KEY (a)); CREATE TABLE t (a BIGINT AUTO_RANDOM(5, 54), b VARCHAR(255), PRIMARY KEY (a, b));

You can wrap the keyword AUTO_RANDOM in an executable comment. For more details, refer to TiDB specific comment syntax.

CREATE TABLE t (a bigint /*T![auto_rand] AUTO_RANDOM */, b VARCHAR(255), PRIMARY KEY (a)); CREATE TABLE t (a bigint PRIMARY KEY /*T![auto_rand] AUTO_RANDOM */, b VARCHAR(255)); CREATE TABLE t (a BIGINT /*T![auto_rand] AUTO_RANDOM(6) */, b VARCHAR(255), PRIMARY KEY (a)); CREATE TABLE t (a BIGINT /*T![auto_rand] AUTO_RANDOM(5, 54) */, b VARCHAR(255), PRIMARY KEY (a));

When you execute an INSERT statement:

  • If you explicitly specify the value of the AUTO_RANDOM column, it is inserted into the table as is.
  • If you do not explicitly specify the value of the AUTO_RANDOM column, TiDB generates a random value and inserts it into the table.
tidb> CREATE TABLE t (a BIGINT PRIMARY KEY AUTO_RANDOM, b VARCHAR(255)); Query OK, 0 rows affected, 1 warning (0.01 sec) tidb> INSERT INTO t(a, b) VALUES (1, 'string'); Query OK, 1 row affected (0.00 sec) tidb> SELECT * FROM t; +---+--------+ | a | b | +---+--------+ | 1 | string | +---+--------+ 1 row in set (0.01 sec) tidb> INSERT INTO t(b) VALUES ('string2'); Query OK, 1 row affected (0.00 sec) tidb> INSERT INTO t(b) VALUES ('string3'); Query OK, 1 row affected (0.00 sec) tidb> SELECT * FROM t; +---------------------+---------+ | a | b | +---------------------+---------+ | 1 | string | | 1152921504606846978 | string2 | | 4899916394579099651 | string3 | +---------------------+---------+ 3 rows in set (0.00 sec)

The AUTO_RANDOM(S, R) column value automatically assigned by TiDB has a total of 64 bits:

  • S is the number of shard bits. The value ranges from 1 to 15. The default value is 5.
  • R is the total length of the automatic allocation range. The value ranges from 32 to 64. The default value is 64.

The structure of an AUTO_RANDOM value is as follows:

Total number of bitsSign bitReserved bitsShard bitsAuto-increment bits
64 bits0/1 bit(64-R) bitsS bits(R-1-S) bits
  • The length of the sign bit is determined by the existence of an UNSIGNED attribute. If there is an UNSIGNED attribute, the length is 0. Otherwise, the length is 1.
  • The length of the reserved bits is 64-R. The reserved bits are always 0.
  • The content of the shard bits is obtained by calculating the hash value of the starting time of the current transaction. To use a different length of shard bits (such as 10), you can specify AUTO_RANDOM(10) when creating the table.
  • The value of the auto-increment bits is stored in the storage engine and allocated sequentially. Each time a new value is allocated, the value is incremented by 1. The auto-increment bits ensure that the values of AUTO_RANDOM are unique globally. When the auto-increment bits are exhausted, an error Failed to read auto-increment value from storage engine is reported when the value is allocated again.

Values allocated implicitly to the AUTO_RANDOM column affect last_insert_id(). To get the ID that TiDB last implicitly allocates, you can use the SELECT last_insert_id () statement.

To view the shard bits number of the table with an AUTO_RANDOM column, you can execute the SHOW CREATE TABLE statement. You can also see the value of the PK_AUTO_RANDOM_BITS=x mode in the TIDB_ROW_ID_SHARDING_INFO column in the information_schema.tables system table. x is the number of shard bits.

After creating a table with an AUTO_RANDOM column, you can use SHOW WARNINGS to view the maximum implicit allocation times:

CREATE TABLE t (a BIGINT AUTO_RANDOM, b VARCHAR(255), PRIMARY KEY (a)); SHOW WARNINGS;

The output is as follows:

+-------+------+---------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------+ | Note | 1105 | Available implicit allocation times: 288230376151711743 | +-------+------+---------------------------------------------------------+ 1 row in set (0.00 sec)

Restrictions

Pay attention to the following restrictions when you use AUTO_RANDOM:

  • To insert values explicitly, you need to set the value of the @@allow_auto_random_explicit_insert system variable to 1 (0 by default). It is not recommended that you explicitly specify a value for the column with the AUTO_RANDOM attribute when you insert data. Otherwise, the numeral values that can be automatically allocated for this table might be used up in advance.
  • Specify this attribute for the primary key column ONLY as the BIGINT type. Otherwise, an error occurs. In addition, when the attribute of the primary key is NONCLUSTERED, AUTO_RANDOM is not supported even on the integer primary key. For more details about the primary key of the CLUSTERED type, refer to clustered index.
  • You cannot use ALTER TABLE to modify the AUTO_RANDOM attribute, including adding or removing this attribute.
  • You cannot use ALTER TABLE to change from AUTO_INCREMENT to AUTO_RANDOM if the maximum value is close to the maximum value of the column type.
  • You cannot change the column type of the primary key column that is specified with AUTO_RANDOM attribute.
  • You cannot specify AUTO_RANDOM and AUTO_INCREMENT for the same column at the same time.
  • You cannot specify AUTO_RANDOM and DEFAULT (the default value of a column) for the same column at the same time.
  • WhenAUTO_RANDOM is used on a column, it is difficult to change the column attribute back to AUTO_INCREMENT because the auto-generated values might be very large.