Help Center/ Distributed Database Middleware/ User Guide/ SQL Syntax/ Global Sequence/ Overview
Updated on 2022-09-16 GMT+08:00
View PDF

Overview

Global sequences are mainly database-based global sequences.

  • The start auto-increment SN can be modified.
  • Global sequence provides sequence numbers that are globally unique but may not increase continuously.
Table 1 Table types supported by global sequence

Table Type

Sharded

Broadcast

Unsharded

DB-based

Supported

Supported

Not supported

Creating an Auto-Increment Sequence

  1. Log in to the required DDM instance using a client.
  2. Open the required schema.
  3. Run the following command to create an auto-increment sequence:

    create sequence xxxxx ;

    • xxxxx indicates the sequence name.
    • The auto-increment key should be a BIGINT value. To avoid duplicate values, do not use TINYINT, SMALLINT, MEDIUMINT, INTEGER, or INT as the auto-increment key.
    • Run show sequences to view the usage of the auto-increment sequence. If the usage reaches 100%, do not insert data any more and contact DDM technical support.

Dropping an Auto-Increment Sequence

  1. Log in to the required DDM instance using a client.
  2. Open the required schema.
  3. Run show sequences to view all global sequences.
  4. Run the following command to drop an auto-increment sequence:

    drop sequence xxxxx ;

    drop sequence DB.xxx;

    • The sequence name is case-insensitive.
    • If an auto-increment sequence is inherent to a table, the sequence cannot be deleted.

Modifying the Start Value of an Auto-Increment Sequence

  1. Log in to the required DDM instance using a client.
  2. Open the required schema.
  3. Run show sequences to view all global sequences.
  4. Run the command to change the start value:

    alter sequence xxxxx START WITH yyyyy;

    • xxxxx indicates the sequence name.
    • yyyyy indicates the start value of the target sequence.

Querying an Auto-Increment Sequence

  1. Log in to the required DDM instance using a client.
  2. Open the required schema.
  3. Run show sequences to view all global sequences.

    show sequences;

Modifying the Auto-Increment Cache Value

This feature is only available in kernel 3.0.3 and later versions.

  1. Log in to the required DDM instance using a client.
  2. Open the required schema.
  3. Run command alter sequence test cache 5000 to modify the global sequence cache value of table test.
  4. Run command show sequences to view the cache value (INCREMENT value) of table test.

Updating Auto-Increment Sequences of All Tables

This feature is available only in kernel 3.0.4.1 or later.

  1. Log in to the required DDM instance using a client.
  2. Run command fresh all sequence start value to change sequences of all schemas.

Parent topic: Global Sequence