Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about sequences in MariaDB Server. This section details how to create and manage sequences for generating unique numbers, often used for primary keys and other auto-incrementing values.
This page is about sequence objects. For details about the storage engine, see Sequence Storage Engine.
A sequence is an object that generates a sequence of numeric values, as specified by the CREATE SEQUENCE statement.
CREATE SEQUENCE creates a sequence that generates new values when called with NEXT VALUE FOR sequence_name. It's an alternative to AUTO INCREMENT when one wants to have more control of how the numbers are generated. As the SEQUENCE caches values (up to the CACHE value in the statement, by default 1000) it can in some cases be much faster than AUTO INCREMENT. Another benefit is that one can access the last value generated by all used sequences, which solves one of the limitations with .
Sequences should not be used with statement-based logging; see .
The statement is used to create a sequence. Here is an example of a sequence starting at 100, incrementing by 10 each time:
The CREATE SEQUENCE statement, along with defaults, can be viewd with the , for example:
To get the , use
or
or in Oracle mode ()
For used by the current connection from a sequence use:
or
or in Oracle mode ()
For example:
Sequences can be used in DEFAULT:
The statement is used for changing sequences. For example, to restart the sequence at another value:
The can also be used to set the next value to be returned for a SEQUENCE, for example:
SETVAL can only be used to increase the sequence value. Attempting to set a lower value will fail, returning NULL:
The statement is used to drop a sequence, for example:
If you want to use Sequences in a master-master setup or with Galera, you should use INCREMENT=0. This tells the Sequence to use and to generate unique values for each server.
Using SELECT NEXT VALUE causes an error for statement-based logging:
This happens because SELECT modifies objects.
MariaDB supports both ANSI SQL and Oracle syntax for sequences.
However as SEQUENCE is implemented as a special kind of table, it uses the same namespace as tables. The benefits are that sequences show up in , and one can also create a sequence with and drop it with . One can from it as from any other table. This ensures that all old tools that work with tables should work with sequences.
Since sequence objects act as regular tables in many contexts, they will be affected by . This is not the case in other DBMS, such as Oracle, where LOCK TABLE does not affect sequences.
One of the goals with the Sequence implementation is that all old tools, such as (previously mysqldump), should work unchanged, while still keeping the normal usage of sequence standard compatibly.
To make this possible, sequence is currently implemented as a table with a few exclusive properties.
The special properties for sequence tables are:
A sequence table has always one row.
When one creates a sequence, either with or , one row will be inserted.
If one tries to insert into a sequence table, the single row will be updated. This allows to work but also gives the additional benefit that one can change all properties of a sequence with a single insert. New applications should of course also use ALTER SEQUENCE.
. This shows the table structure that is behind the SEQUENCE including the field names that can be used with or even .
Internally, sequence tables are created as a normal table without rollback (the , and engines support this), wrapped by a sequence engine object. This allowed us to create sequences with almost no performance impact for normal tables. (The cost is one 'if' per insert if the is enabled).
The following example shows the table structure of sequences and how it can be used as a table. (Output of results are slightly edited to make them easier to read.)
The cycle_count column is incremented every time the sequence wraps around.
Thanks to Jianwe Zhao from Aliyun for his work on SEQUENCE in AliSQL, which gave ideas and inspiration for this work.
Thanks to Peter Gulutzan, who helped test and gave useful comments about the implementation.
This page is licensed: CC BY-SA / Gnu FDL
Learn about sequence functions in MariaDB Server. This section details SQL functions for retrieving the next or current value from a sequence, crucial for generating unique identifiers.
Doing a select on the sequence shows the current state of the sequence, except the values that are reserved in the cache. The next_value column shows the next value not reserved by the cache.
FLUSH TABLES will close the sequence and the next sequence number generated will be according to what's stored in the Sequence object. In effect, this will discard the cached values.
A number of normal table operations work on Sequence tables. See next section.
DROP TABLE sequence_name. This is allowed mainly to get old tools like mariadb-dump to work with sequence tables.
SETVAL(). Set next value for the sequence.
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;SHOW CREATE SEQUENCE s\G
*************************** 1. row ***************************
Table: s
Create Table: CREATE SEQUENCE `s` start with 100 minvalue 1 maxvalue 9223372036854775806
increment by 10 cache 1000 nocycle ENGINE=InnoDBNEXT VALUE FOR sequence_nameNEXTVAL(sequence_name)sequence_name.nextvalPREVIOUS VALUE FOR sequence_nameLASTVAL(sequence_name)sequence_name.currvalSELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 100 |
+------------+
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 110 |
+------------+
SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
| 110 |
+------------+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 |
+---+------+ALTER SEQUENCE s RESTART 50;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 50 |
+------------+SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
| 100 |
+----------------+SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
| NULL |
+---------------+DROP SEQUENCE s;ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.CREATE SEQUENCE t1;
SHOW CREATE SEQUENCE t1\G
*************************** 1. row ***************************
CREATE SEQUENCE `t1` start with 1 minvalue 1 maxvalue 9223372036854775806
increment by 1 cache 1000 nocycle ENGINE=InnoDB
SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Create Table: CREATE TABLE `t1` (
`next_not_cached_value` bigint(21) NOT NULL,
`minimum_value` bigint(21) NOT NULL,
`maximum_value` bigint(21) NOT NULL,
`start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
`increment` bigint(21) NOT NULL COMMENT 'increment value',
`cache_size` bigint(21) unsigned NOT NULL,
`cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
`cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
) ENGINE=InnoDB SEQUENCE=1
SELECT * FROM t1\G
next_not_cached_value: 1
minimum_value: 1
maximum_value: 9223372036854775806
start_value: 1
increment: 1
cache_size: 1000
cycle_option: 0
cycle_count: 0LASTVAL is a synonym for PREVIOUS VALUE for sequence_name.
This page is licensed: CC BY-SA / Gnu FDL
NEXTVAL is a synonym for .
This page is licensed: CC BY-SA / Gnu FDL
Important: When a table is dropped, user privileges on the table are not automatically dropped. See GRANT.
If another connection is using the sequence, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
For each referenced sequence, DROP SEQUENCE drops a temporary sequence with that name, if it exists. If it does not exist, and the TEMPORARY keyword is not used, it drops a non-temporary sequence with the same name, if it exists. The TEMPORARY keyword ensures that a non-temporary sequence will not accidentally be dropped.
Use IF EXISTS to prevent an error from occurring for sequences that do not exist. A NOTE is generated for each non-existent sequence when using IF EXISTS. See SHOW WARNINGS.
DROP SEQUENCE requires the DROP privilege.
DROP SEQUENCE only removes sequences, not tables. However, DROP TABLE can remove both sequences and tables.
This page is licensed: CC BY-SA / Gnu FDL
DROP [TEMPORARY] SEQUENCE [IF EXISTS] [/*COMMENT TO SAVE*/]
sequence_name [, sequence_name] ...or
or in Oracle mode (SQL_MODE=ORACLE)
NEXT VALUE FOR is ANSI SQL syntax while NEXTVAL() is PostgreSQL syntax.
Generate next value for a SEQUENCE.
You can greatly speed up NEXT VALUE by creating the sequence with the CACHE option. If not, every NEXT VALUE usage will cause changes in the stored SEQUENCE table.
When using NEXT VALUE the value will be reserved at once and will not be reused, except if the SEQUENCE was created with CYCLE. This means that when you are using SEQUENCE
Once the sequence is complete, unless the sequence has been created with the attribute (not the default), calling the function will result in .
This page is licensed: CC BY-SA / Gnu FDL
or
or in Oracle mode (SQL_MODE=ORACLE)
PREVIOUS VALUE FOR is IBM DB2 syntax while LASTVAL() is PostgreSQL syntax.
Gets the most recent value in the current connection generated from a sequence.
If the sequence has not yet been used by the connection, PREVIOUS VALUE FOR returns NULL (the same thing applies with a new connection which doesn't see a last value for an existing sequence).
If a SEQUENCE has been dropped and re-created then it's treated as a new SEQUENCE and PREVIOUS VALUE FOR will return NULL.
Now try to start the new connection and check that the last value is still NULL, before updating the value in the new connection after the output of the new connection gets current value (110 in the example below). Note that first connection cannot see this change and the result of last value still remains the same (100 in the example above).
Returns NULL if the sequence has run out:
This page is licensed: CC BY-SA / Gnu FDL
NEXT VALUE FOR sequenceNEXTVAL(sequence_name)sequence_name.nextvalPREVIOUS VALUE FOR sequence_nameLASTVAL(sequence_name)sequence_name.currvalIf one updates the SEQUENCE with SETVAL() or ALTER SEQUENCE ... RESTART, NEXT VALUE FOR will notice this and start from the next requested value.
FLUSH TABLES will close the sequence and the next sequence number generated will be according to what's stored in the SEQUENCE object. In effect, this will discard the cached values.
A server restart (or closing the current connection) also causes a drop of all cached values. The cached sequence numbers are reserved only for the current connection.
NEXT VALUE requires the INSERT privilege.
You can also use NEXT VALUE FOR sequence for column DEFAULT.
Returns NULL if the sequence is complete.
FLUSH TABLES has no effect on PREVIOUS VALUE FOR.
Previous values for all used sequences are stored per connection until connection ends.
PREVIOUS VALUE FOR requires the SELECT privilege.
CREATE OR REPLACE SEQUENCE s MAXVALUE=2;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 1 |
+------------+
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 2 |
+------------+
SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out
ALTER SEQUENCE s MAXVALUE=2 CYCLE;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 1 |
+------------+CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
| NULL |
+----------------------+
# The function works for sequences only, if the table is used an error is generated
SELECT PREVIOUS VALUE FOR t;
ERROR 4089 (42S02): 'test.t' is not a SEQUENCE
# Call the NEXT VALUE FOR s:
SELECT NEXT VALUE FOR s;
+------------------+
| NEXT VALUE FOR s |
+------------------+
| 100 |
+------------------+
SELECT PREVIOUS VALUE FOR s;
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
| 100 |
+----------------------+$ .mysql -uroot test -e"SELECT PREVIOUS VALUE FOR s; SELECT NEXT VALUE FOR s; SELECT PREVIOUS VALUE FOR s;"
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
| NULL |
+----------------------+
+------------------+
| NEXT VALUE FOR s |
+------------------+
| 110 |
+------------------+
+----------------------+
| PREVIOUS VALUE FOR s |
+----------------------+
| 110 |
+----------------------+CREATE OR REPLACE SEQUENCE s MAXVALUE=2;
SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
| 1 | 1 |
+------------+------------+
SELECT NEXTVAL(s), LASTVAL(s);
+------------+------------+
| NEXTVAL(s) | LASTVAL(s) |
+------------+------------+
| 2 | 2 |
+------------+------------+
SELECT NEXTVAL(s), LASTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run out
SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
| NULL |
+------------+round argument.If the is_used argument is not given or is 1 or true, then the next used value will one after the given value. If is_used is 0 or false then the next generated value will be the given value.
If round is used then it will set the round value (or the internal cycle count, starting at zero) for the sequence. If round is not used, it's assumed to be 0.
next_value must be an integer literal.
For SEQUENCE tables defined with CYCLE (see CREATE SEQUENCE) one should use both next_value and round to define the next value. In this case the current sequence value is defined to be round, next_value.
The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value.
SETVAL() will not set the SEQUENCE value to a something that is less than its current value. This is needed to ensure that SETVAL() is replication-safe. If you want to set the SEQUENCE to a smaller number, use ALTER SEQUENCE.
If CYCLE is used, first round and then next_value are compared to see if the value is bigger than the current value.
Internally, in the MariaDB server, SETVAL() is used to inform replicas that a SEQUENCE has changed value. The replica may getSETVAL() statements out of order, but this is ok as only the biggest one will have an effect.
SETVAL requires the INSERT privilege.
SETVAL setting higher and lower values on a sequence with an increment of 10:
Example demonstrating round:
The following statement returns NULL, as the given next_value and round is smaller than the current value:
Increasing the round from zero to 1 allows next_value to be returned:
This page is licensed: CC BY-SA / Gnu FDL
SETVAL(sequence_name, next_value, [is_used, [round]])SELECT setval(foo, 42); -- Next nextval will return 43
SELECT setval(foo, 42, TRUE); -- Same as above
SELECT setval(foo, 42, FALSE); -- Next nextval will return 42SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 50 |
+------------+
SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
| 100 |
+----------------+
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 110 |
+------------+
SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
| NULL |
+---------------+
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 120 |
+------------+CREATE OR REPLACE SEQUENCE s1
START WITH 1
MINVALUE 1
MAXVALUE 99
INCREMENT BY 1
CACHE 20
CYCLE;
SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
| 99 |
+----------------------+
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1 |
+-------------+SELECT SETVAL(s1, 99, 1, 0);
+----------------------+
| SETVAL(s1, 99, 1, 0) |
+----------------------+
| NULL |
+----------------------+
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 2 |
+-------------+SELECT SETVAL(s1, 99, 1, 1);
+----------------------+
| SETVAL(s1, 99, 1, 1) |
+----------------------+
| 99 |
+----------------------+
SELECT NEXTVAL(s1);
+-------------+
| NEXTVAL(s1) |
+-------------+
| 1 |
+-------------+ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.
ALTER SEQUENCE requires the ALTER privilege.
INCREMENT
1
Increment to use for values. May be negative.
MINVALUE
1 if INCREMENT > 0 and -9223372036854775807 if INCREMENT < 0
Minimum value for the sequence.
MAXVALUE
9223372036854775806 if INCREMENT > 0 and -1 if INCREMENT < 0
Max value for sequence.
START
MINVALUE if INCREMENT > 0 and MAX_VALUE if INCREMENT< 0
The optional clause RESTART [ WITH restart ] sets the next value for the sequence. This is equivalent to calling the SETVAL() function with the is_used argument as 0. The specified value will be returned by the next call of nextval. Using RESTART with no restart value is equivalent to supplying the start value that was recorded by CREATE SEQUENCE or last set by ALTER SEQUENCE START WITH.
ALTER SEQUENCE does not allow to change the sequence so that it's inconsistent:
To allow SEQUENCE objects to be backed up by old tools, like mariadb-dump, one can use SELECT to read the current state of a SEQUENCE object and use an INSERT to update the SEQUENCE object. INSERT is only allowed if all fields are specified:
ALTER SEQUENCE will instantly affect all future SEQUENCE operations. This is in contrast to some other databases where the changes requested by ALTER SEQUENCE will not be seen until the sequence cache has run out.
ALTER SEQUENCE will take a full table lock of the sequence object during its (brief) operation. This ensures that ALTER SEQUENCE is replicated correctly. If you only want to set the next sequence value to a higher value than current, then you should use SETVAL() instead, as this is not blocking.
If you want to change the storage engine or sequence comment, or rename the sequence, you can use ALTER TABLE for this.
. Set next value for the sequence.
This page is licensed: CC BY-SA / Gnu FDL
Create a sequence generator. This statement initializes a sequence object that produces a series of unique numeric values on demand.
The options for CREATE SEQUENCE can be given in any order, optionally followed by table_options.
table_options can be any of the normal table options in CREATE TABLE — the most used ones are ENGINE=... and COMMENT=.
NOMAXVALUE and NOMINVALUE are there to allow one to create SEQUENCEs using the Oracle syntax.
CREATE SEQUENCE creates a sequence that generates new values when called with NEXT VALUE FOR sequence_name. It's an alternative to if you want to have more control of how the numbers are generated. As the SEQUENCE caches values (up to CACHE), it can in some cases be much faster than . Another benefit is that you can access the last value generated by all used sequences, which solves one of the limitations with .
CREATE SEQUENCE requires the .
can be used to drop a sequence, and to change it.
INT type, that is, one of , , , , , . Can be signed or unsigned. Maximum value is based on the data type. The use of BIGINT UNSIGNED with this option extends the possible maximum value from 9223372036854775806 to 18446744073709551614. Default is BIGINT.
The AS option is not available.
Increment to use for values. May be negative. Setting an increment of 0 causes the sequence to use the value of the system variable at the time of creation, which is always a positive number. (see ). Default 1.
Minimum value for the sequence. From , the parser permits much smaller numbers, such as -9999999999999999999999999999, but converts to the minimum permitted for the INT type, with a note. Default 1 if INCREMENT > 0 , and -9223372036854775807 (or based on int type) if INCREMENT < 0.
Maximum value for sequence. From , the parser permits much larger numbers, such as 9999999999999999999999999999 used in Oracle examples, but converts to the maximum permitted for the INT type, with a note. Default 9223372036854775806 (or based on int type) if INCREMENT > 0 , and -1 if INCREMENT < 0.
First value the sequence will generate. Default MINVALUE if INCREMENT > 0, and MAX_VALUE if INCREMENT< 0.
Number of values that should be cached. 0 if no CACHE. The underlying table will be updated first time a new sequence number is generated and each time the cache runs out. Default 1000. , shutting down the server, etc. will discard the cached values, and the next sequence number generated will be according to what's stored in the Sequence object. In effect, this will discard the cached values.
Note that setting the cache to 1 from 1000 can make inserts to tables using sequences for default values 2x slower and increase the binary log sizes up to 7x.
If CYCLE is used, then the sequence should start again from MINVALUE after it has run out of values. Default value is NOCYCLE.
To be able to create a legal sequence, the following must hold:
MAXVALUE >= start
MAXVALUE > MINVALUE
START >= MINVALUE
Note that sequences can't generate the maximum/minimum 64 bit number because of the constraint ofMINVALUE and MAXVALUE.
MariaDB supports and CREATE SEQUENCE is atomic.
MariaDB does not support and CREATE SEQUENCE is atomic.
The following statement fails, as the increment conflicts with the defaults:
The sequence can be created by specifying workable minimum and maximum values:
From :
Parser accepting larger or smaller values:
Flushing the cache:
You can use sequences instead of AUTO_INCREMENT to generate values for a table:
This page is licensed: CC BY-SA / Gnu FDL
ALTER SEQUENCE [IF EXISTS] sequence_name
[ INCREMENT [ BY | = ] number ]
[ MINVALUE [=] number | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] number | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] number ] [ CACHE [=] number ] [ [ NO ] CYCLE ]
[ RESTART [[WITH | =] number]CREATE SEQUENCE s1;
ALTER SEQUENCE s1 MINVALUE 10;
ERROR 4061 (HY000): Sequence 'test.t1' values are conflicting
ALTER SEQUENCE s1 MINVALUE 10 RESTART 10;
ERROR 4061 (HY000): Sequence 'test.t1' values are conflicting
ALTER SEQUENCE s1 MINVALUE 10 START 10 RESTART 10;CREATE SEQUENCE s1;
INSERT INTO s1 VALUES(1000,10,2000,1005,1,1000,0,0);
SELECT * FROM s1;
+------------+-----------+-----------+-------+-----------+-------+-------+-------+
| next_value | min_value | max_value | start | increment | cache | cycle | round |
+------------+-----------+-----------+-------+-----------+-------+-------+-------+
| 1000 | 10 | 2000 | 1005 | 1 | 1000 | 0 | 0 |
+------------+-----------+-----------+-------+-----------+-------+-------+-------+
SHOW CREATE SEQUENCE s1;
+-------+--------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------+
| s1 | CREATE SEQUENCE `s1` start with 1005 minvalue 10 maxvalue 2000 increment by 1 cache 1000 nocycle ENGINE=Aria |
+-------+--------------------------------------------------------------------------------------------------------------+CREATE [OR REPLACE] [TEMPORARY] SEQUENCE [IF NOT EXISTS] sequence_name
[AS { TINYINT | SMALLINT | |MEDIUMINT | INT | INTEGER | BIGINT } [SIGNED | UNSIGNED]]
[ INCREMENT [ BY | = ] number ]
[ MINVALUE [=] number | NO MINVALUE | NOMINVALUE ]
[ MAXVALUE [=] number | NO MAXVALUE | NOMAXVALUE ]
[ START [ WITH | = ] number ]
[ CACHE [=] number | NOCACHE ] [ CYCLE | NOCYCLE]
[table_options](../sql-statements/data-definition/create/create-table.md#table-options)First value that the sequence will generate.
CACHE
1000
Number of values that should be cached. 0 if no CACHE. The underlying table will be updated first time a new sequence number is generated and each time the cache runs out.
CYCLE
0 (= NO CYCLE)
1 if the sequence should start again from MINVALUE
MAXVALUE <= 9223372036854775806 (LONGLONG_MAX-1). From , the parser accepts values beyond this, and converts based on the int type.
MINVALUE >= -9223372036854775807 (LONGLONG_MIN+1). From , the parser accepts values beyond this, and converts based on the int type.
SETVAL(). Set next value for the sequence.
CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
CREATE SEQUENCE s2 START WITH -100 INCREMENT BY -10;CREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10;
ERROR 4082 (HY000): Sequence 'test.s3' values are conflictingCREATE SEQUENCE s3 START WITH -100 INCREMENT BY 10 MINVALUE=-100 MAXVALUE=1000;CREATE SEQUENCE s3 AS BIGINT UNSIGNED START WITH 10;CREATE OR REPLACE SEQUENCE s AS TINYINT SIGNED
MINVALUE=-999999999999999999999999999999999
MAXVALUE=999999999999999999999999999999999
START WITH 100 INCREMENT BY 10;
Query OK, 0 rows affected, 2 warnings (0.037 sec)
SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------+
| Note | 1292 | Truncated incorrect INTEGER value: 'MINVALUE' |
| Note | 1292 | Truncated incorrect INTEGER value: 'MAXVALUE' |
+-------+------+-----------------------------------------------+
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES\G
*************************** 1. row ***************************
SEQUENCE_CATALOG: def
SEQUENCE_SCHEMA: test
SEQUENCE_NAME: s
DATA_TYPE: tinyint
NUMERIC_PRECISION: 8
NUMERIC_PRECISION_RADIX: 2
NUMERIC_SCALE: 0
START_VALUE: 100
MINIMUM_VALUE: -127
MAXIMUM_VALUE: 126
INCREMENT: 10
CYCLE_OPTION: 0CREATE OR REPLACE SEQUENCE s START WITH 1 INCREMENT BY 1 MAXVALUE=10 CACHE=5;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 1 |
+------------+
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 2 |
+------------+
FLUSH TABLES s;
SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
| 6 |
+------------+
FLUSH TABLES s;
SELECT NEXTVAL(s);
ERROR 4084 (HY000): Sequence 'test.s' has run outCREATE SEQUENCE s1;
CREATE TABLE t1 (a INT PRIMARY KEY DEFAULT nextval(s1), b INT);
INSERT INTO t1 (b) VALUES(1);
SELINT * FROM t1;
+---+------+
| a | b |
+---+------+
| 1 | 1 |
+---+------+