The Ultimate Guide to High Availability with MariaDB

SETVAL()

You are viewing an old version of this article. View the current version here.
MariaDB starting with 10.3.1

SEQUENCEs are being introduced in MariaDB 10.3.

Syntax

SETVAL(sequence_name, next_value, [is_used, [round]])

Description

Set the next value to be returned for a SEQUENCE.

This function is compatible with PostgreSQL syntax, extended with the 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 then the next generated value will be the given value.

if round is used then it will set the round value for the sequence. If round is not used, it's assumed to be 0.

For SEQUENCE tables defined with CYCLE 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.

Example

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 42

The result returned by SETVAL() is next_value or NULL if the given next_value and round is smaller than the current value.

Notes

SETVAL() will not set the SEQUENCE value to a something that is less than it's 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 slaves that a SEQUENCE has changed value. The slave may get SETVAL() statements out of order, but this is ok as only the biggest one will have affect.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.