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.

The AUTO_RANDOM_BASE parameter in the CREATE TABLE statement is used to set the initial incremental part value of auto_random. This option can be considered as a part of the internal interface. You can ignore this parameter.

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)) /*T! PRE_SPLIT_REGIONS=2 */ ; 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) tidb> SHOW CREATE TABLE t; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `a` bigint(20) NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `b` varchar(255) DEFAULT NULL, PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! PRE_SPLIT_REGIONS=2 */ | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) tidb> SHOW TABLE t REGIONS; +-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | REGION_ID | START_KEY | END_KEY | LEADER_ID | LEADER_STORE_ID | PEERS | SCATTERING | WRITTEN_BYTES | READ_BYTES | APPROXIMATE_SIZE(MB) | APPROXIMATE_KEYS | SCHEDULING_CONSTRAINTS | SCHEDULING_STATE | +-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ | 62798 | t_158_ | t_158_r_2305843009213693952 | 62810 | 28 | 62811, 62812, 62810 | 0 | 151 | 0 | 1 | 0 | | | | 62802 | t_158_r_2305843009213693952 | t_158_r_4611686018427387904 | 62803 | 1 | 62803, 62804, 62805 | 0 | 39 | 0 | 1 | 0 | | | | 62806 | t_158_r_4611686018427387904 | t_158_r_6917529027641081856 | 62813 | 4 | 62813, 62814, 62815 | 0 | 160 | 0 | 1 | 0 | | | | 9289 | t_158_r_6917529027641081856 | 78000000 | 48268 | 1 | 48268, 58951, 62791 | 0 | 10628 | 43639 | 2 | 7999 | | | +-----------+-----------------------------+-----------------------------+-----------+-----------------+---------------------+------------+---------------+------------+----------------------+------------------+------------------------+------------------+ 4 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 with a signed bit is as follows:

Signed bitReserved bitsShard bitsAuto-increment bits
1 bit64-R bitsS bitsR-1-S bits

The structure of an AUTO_RANDOM value without a signed bit is as follows:

Reserved bitsShard bitsAuto-increment bits
64-R bitsS bitsR-S bits
  • Whether a value has a signed bit depends on whether the corresponding column has the UNSIGNED attribute.
  • 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.
  • Value range: the maximum number of bits for the final generated value = shard bits + auto-increment bits. The range of a signed column is [-(2^(R-1))+1, (2^(R-1))-1], and the range of an unsigned column is [0, (2^R)-1].
  • You can use AUTO_RANDOM with PRE_SPLIT_REGIONS. When a table is created successfully, PRE_SPLIT_REGIONS pre-splits data in the table into the number of Regions as specified by 2^(PRE_SPLIT_REGIONS).

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)

Implicit allocation rules of IDs

TiDB implicitly allocates values to AUTO_RANDOM columns similarly to AUTO_INCREMENT columns. They are also controlled by the session-level system variables auto_increment_increment and auto_increment_offset. The auto-increment bits (ID) of implicitly allocated values conform to the equation (ID - auto_increment_offset) % auto_increment_increment == 0.

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.