This topic describes the concepts that are related to auto-increment columns and how auto-increment columns in PolarDB-X databases work.

Concepts

PolarDB-X supports the AUTO_INCREMENT keyword to allow you to specify a column as an auto-increment column. PolarDB-X automatically generates globally unique values for auto-increment columns.

  • If the INSERT statement that is executed to insert data does not specify a value for the auto-increment column, or the value that is specified for the auto-increment column is 0 or NULL, PolarDB-X automatically generates a globally unique value in the auto-increment column.
  • If the INSERT statement that is executed to insert data specifies a value for the auto-increment column, PolarDB-X uses the specified value for the current operation and then automatically generates subsequent values that are greater than the specified value for the auto-increment column for subsequent operations.
  • Auto-increment columns in databases that are in AUTO mode support all properties of auto-increment columns in MySQL databases, including uniqueness, monotonicity, and value continuity.
Note For information about databases in AUTO mode and DRDS mode, see Databases in AUTO mode and DRDS mode.

How auto-increment columns work

If you specify the AUTO_INCREMENT keyword for a column when you create a table in PolarDB-X, PolarDB-X automatically create a sequence and associates the sequence with the auto-increment column in the table to generate globally unique values for the auto-increment column. When you insert data into the table, the associated sequence is used to generate a globally unique value, and then the value is inserted into the auto-increment column.

PolarDB-X automatically creates a sequence of a specific type for the auto-increment column in a table based on the partitioning mode of the database.
  • If the database that contains the table is in AUTO mode, PolarDB-X automatically creates a NEW sequence and associates the sequence with the auto-increment column in the table. An auto-increment column that is associated with a NEW sequence has the following properties:
    • Uniqueness: Values in the auto-increment column are globally unique.
    • Monotonicity: Values in the auto-increment column are globally monotonically increasing values.
    • Value continuity: Values in the auto-increment column are consecutive. If exceptions occur or the instance is restarted, values in the auto-increment column may be nonconsecutive values.
    • Compatibility: The auto-increment column has all properties of auto-increment columns in MySQL databases.
  • If the database that contains the table is in DRDS mode, PolarDB-X automatically creates a GROUP sequence and associates the sequence with the auto-increment column in the table. An auto-increment column that is associated with a GROUP sequence has the following properties:
    • Uniqueness: Values in the auto-increment column are globally unique.
    • Monotonicity: Values in the auto-increment column that are generated by each compute node are monotonically increasing values. The values that are generated across compute nodes may not be monotonically increasing values.
    • Value continuity: Sequence values that are used in a compute node connection session and within the cache duration are consecutive. If exceptions occur or the instance is restarted, values in the auto-increment column may be nonconsecutive values.
    • Compatibility: The auto-increment column supports the uniqueness property of auto-increment columns in MySQL databases and does not support other features of auto-increment columns in MySQL databases.

You can also specify the type of the sequence that you want to associate with the auto-increment column in a table. For more information, see Associate a sequence with the auto-increment column in a table. For information about the properties of different types of sequences and the common scenarios in which different types of sequences are used, see Overview.