fitriadax.blogg.se

The ALTER SEQUENCE statement is used for changing sequences.

Starting from 10.3.3 you can use Sequences in DEFAULT: create sequence s1 create table t1 ( a int primary key default ( next value for s1 ), b int ) insert into t1 ( b ) values ( 1 ),( 2 ) select * from t1 + -+-+ | a | b | + -+-+ | 1 | 1 | | 2 | 2 | + -+-+ Changing a Sequence Or in Oracle mode ( SQL_MODE=ORACLE) sequence_name.currvalįor example: SELECT NEXTVAL ( s ) + -+ | NEXTVAL ( s ) | + -+ | 100 | + -+ SELECT NEXTVAL ( s ) + -+ | NEXTVAL ( s ) | + -+ | 110 | + -+ SELECT LASTVAL ( s ) + -+ | LASTVAL ( s ) | + -+ | 110 | + -+ Using Sequences in DEFAULT Or in Oracle mode ( SQL_MODE=ORACLE) sequence_name.nextvalįor retrieving the last value used by the current connection from a sequence To get the next value from a sequence, use NEXT VALUE FOR sequence_name Increment by 10 cache 1000 nocycle ENGINE=InnoDB The CREATE SEQUENCE statement, along with defaults, can be viewd with the SHOW CREATE SEQUENCE STATEMENT, for example: SHOW CREATE SEQUENCE s\G Here is an example of a sequence starting at 100, incrementing by 10 each time: CREATE SEQUENCE s START WITH 100 INCREMENT BY 10 The CREATE SEQUENCE statement is used to create a sequence. Another benefit is that one can access the last value generated by all used sequences, which solves one of the limitations with LAST_INSERT_ID(). As the SEQUENCE caches values (up to the CACHE value in the CREATE SEQUENCE statement, by default 1000) it can in some cases be much faster than AUTO INCREMENT. It's an alternative to AUTO INCREMENT when one wants to have more control of how the numbers are generated.