Help Center/ Distributed Database Middleware/ User Guide/ SQL Syntax/ Global Sequence/ Overview
Updated on 2024-07-30 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.
  • If the auto-increment sequence is used, its value must be null. If you do not specify the value or set it to null, DDM will assign a value by default. The user-defined value may conflict with the assigned auto-increment key value.
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. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  2. Open the required schema.
  3. Run the following command to create an auto-increment sequence:

    create sequence <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 customer service.

Dropping an Auto-Increment Sequence

  1. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  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 <sequence name >

    drop sequence DB.***;

    • 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. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  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 <sequence name > START WITH <start value of the target sequence >

Querying an Auto-Increment Sequence

  1. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  2. Log in to the target schema.
  3. Run the following command to view all sequences:

    show sequences;

Modifying the Auto-Increment Cache Value

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

  1. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  2. Log in to the required schema.
  3. Run the following command to change cached values of the global sequence of table test:

    alter sequence test cache 5000

  4. Run the following command to view cached values (or INCREMENT values) of the global sequence of table test:

    show sequences

Updating Auto-Increment Sequences of All Tables

This feature is available only in kernel 3.0.4.1 or later.

  1. Connect to a DDM instance.

    For details, see Connecting to a DDM Instance.

  2. Run the following command to change sequences of all schemas:

    fresh all sequence start value

Parent topic: Global Sequence