tencent cloud

Feedback

Sequence

Last updated: 2023-04-18 16:59:27

    The sequence keyword syntax is compatible with MariaDB/Oracle, but a sequence must be globally incremental and unique. The specific usage is as follows:

    Note:

    • When using a sequence in TDSQL for MySQL, the keyword must be prefixed with tdsql_, and the proxy version must be later than 1.19.5-M-V2.0R745D005. You can view the proxy version by running the /*Proxy*/show status statement. If the proxy is on an earlier version, you can submit a ticket for upgrade.
    • Currently, sequence is used to guarantee the global uniqueness of the distributed transaction, which has a relatively low performance and is therefore only suitable for scenarios with low concurrency.

    Creating a sequence requires the CREATE SEQUENCE system permission. The creation syntax is as follows:

      CREATE TDSQL_SEQUENCE sequence name
      [START WITH n]
      [{TDSQL_MINALUE/ TDSQL_MAXMINVALUE n| TDSQL_NOMAXVALUE}]
      [TDSQL_INCREMENT BY n]
      [{TDSQL_CYCLE|TDSQL_NOCYCLE}]
    

    Creating a Sequence

    create tdsql_sequence test.s1 start with 12 tdsql_minvalue 10 maxvalue 50000 tdsql_increment by 5 tdsql_nocycle
    create tdsql_sequence test.s2 start with 12 tdsql_minvalue 10 maxvalue 50000 tdsql_increment by 1 tdsql_cycle
    
    • The above SQL statements include six parameters: start value, minimum value, maximum value, increment, buffer size, and whether to cycle, all of which should be positive integers.
    • The default values of these parameters are as follows: start value (1), minimum value (1), maximum value (LONGLONG_MAX-1), increment (1), and whether to cycle (0).

    Deleting a Sequence

    drop tdsql_sequence test.s1
    

    Querying a Sequence

    show tdsql_sequence
    

    Using a Sequence

    Getting the next value

    select tdsql_nextval(test.s2)
    select next value for test.s2
    
    mysql> select tdsql_nextval(test.s1);
    +----+
    | 12 |
    +----+
    | 12 |
    +----+
    1 row in set (0.18 sec)
    
    mysql> select tdsql_nextval(test.s2);
    +----+
    | 12 |
    +----+
    | 12 |
    +----+
    1 row in set (0.13 sec)
    
    mysql> select tdsql_nextval(test.s1);
    +----+
    | 17 |
    +----+
    | 17 |
    +----+
    1 row in set (0.01 sec)
    
    mysql> select tdsql_nextval(test.s2);
    +----+
    | 13 |
    +----+
    | 13 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> select next value for test.s1;
    +----+
    | 22 |
    +----+
    | 22 |
    +----+
    1 row in set (0.01 sec)
    

    Using nextval in INSERT and other statements

    mysql> select * from test.t1;
    +----+------+
    | a  | b    |
    +----+------+
    | 11 |    2 |
    +----+------+
    1 row in set (0.00 sec)
    
    mysql> insert into test.t1(a,b) values(tdsql_nextval(test.s2),3);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from test.t1;
    +----+------+
    | a  | b    |
    +----+------+
    | 11 |    2 |
    | 14 |    3 |
    +----+------+
    2 rows in set (0.00 sec)
    

    The last value is needed to join relevant data. If it has never been obtained with the nextval command, 0 will be returned.

    select tdsql_lastval(test.s1)
    select tdsql_previous value for test.s1;
    
    mysql> select tdsql_lastval(test.s1);
    +----+
    | 22 |
    +----+
    | 22 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> select tdsql_previous value for test.s1;
    +----+
    | 22 |
    +----+
    | 22 |
    +----+
    1 row in set (0.00 sec)
    

    Set the next value for the sequence, which must be greater than the current value; otherwise, 0 will be returned.

    select tdsql_setval(test.s2,1000,bool use)  // `use` is 1 by default, indicating that the value of 1,000 has been used and will not be included next time. If this is 0, the next return will start from 1,000.
    

    If the next value of the sequence is smaller than the current value, the system will make no response.

    mysql> select tdsql_nextval(test.s2);
    +----+
    | 15 |
    +----+
    | 15 |
    +----+
    1 row in set (0.01 sec)
    
    mysql> select tdsql_setval(test.s2,10);
    +---+
    | 0 |
    +---+
    | 0 |
    +---+
    1 row in set (0.03 sec)
    
    mysql> select tdsql_nextval(test.s2);
    +----+
    | 16 |
    +----+
    | 16 |
    +----+
    

    If the next value set is larger than the current value, the current value will be successfully returned.

    mysql> select tdsql_setval(test.s2,20);
    +----+
    | 20 |
    +----+
    | 20 |
    +----+
    1 row in set (0.02 sec)
    mysql> select tdsql_nextval(test.s2);
    +----+
    | 21 |
    +----+
    | 21 |
    +----+
    1 row in set (0.01 sec)
    

    Forcibly set the next sequence value (you can set a value smaller than the current value).

    select tdsql_resetval(test.s2,1000)
    

    If the forced setting is successful, the set value will be returned, from which the next sequence value will start.

    mysql> select tdsql_resetval(test.s2,14);
    +----+
    | 14 |
    +----+
    | 14 |
    +----+
    1 row in set (0.00 sec)
    
    mysql> select tdsql_nextval(test.s2);
    +----+
    | 14 |
    +----+
    | 14 |
    +----+
    1 row in set (0.01 sec)
    

    Note that some sequence keywords are prefixed with TDSQL_:

    Note:

    If the oldstyle configuration item is enabled, the proxy will be compatible with the standard Sequence keyword; that is, the TDSQL_ prefix can be omitted for the keyword.

     TDSQL_CYCLE
     TDSQL_INCREMENT
     TDSQL_LASTVAL  
     TDSQL_MINVALUE 
     TDSQL_NEXTVAL  
     TDSQL_NOCACHE  
     TDSQL_NOCYCLE  
     TDSQL_NOMAXVALUE
     TDSQL_NOMINVALUE
     TDSQL_PREVIOUS 
     TDSQL_RESTART  
     TDSQL_REUSE    
     TDSQL_SEQUENCE 
     TDSQL_SETVAL   
    
    Contact Us

    Contact our sales team or business advisors to help your business.

    Technical Support

    Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

    7x24 Phone Support