Обзор последовательностей

Введение

Последовательность — это объект, который генерирует последовательность числовых значений, как указано в инструкции CREATE SEQUENCE.

CREATE SEQUENCE создаст последовательность, которая будет генерировать новые значения при вызове NEXT VALUE FOR sequence_name. Это альтернатива AUTO INCREMENT, когда требуется больший контроль над генерированием чисел. Поскольку последовательность кэширует значения (до значения CACHE в инструкции CREATE SEQUENCE, по умолчанию 1000), в некоторых случаях она может быть значительно быстрее, чем AUTO INCREMENT. Другое преимущество заключается в возможности доступа к последнему сгенерированному значению всех используемых последовательностей, что решает одну из ограничений функции LAST_INSERT_ID().

Создание последовательности

Для создания последовательности используется инструкция CREATE SEQUENCE. Вот пример последовательности, начинающейся с 100 и увеличивающейся на 10 каждый раз:

CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;

Инструкцию CREATE SEQUENCE вместе с значениями по умолчанию можно просмотреть с помощью SHOW CREATE SEQUENCE STATEMENT, например:

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=InnoDB

Использование объектов последовательности

Чтобы получить следующее значение из последовательности, используйте

NEXT VALUE FOR sequence_name

или

NEXTVAL(sequence_name)

или в режиме Oracle (SQL_MODE=ORACLE)

sequence_name.nextval

Для получения последнего значения, использованного текущим подключением из последовательности, используйте:

PREVIOUS VALUE FOR sequence_name

или

LASTVAL(sequence_name)

или в режиме Oracle (SQL_MODE=ORACLE)

sequence_name.currval

Например:

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        100 |
+------------+

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|        110 |
+------------+

SELECT LASTVAL(s);
+------------+
| LASTVAL(s) |
+------------+
|        110 |
+------------+

Использование последовательностей в DEFAULT

Последовательности могут использоваться в 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 |
+---+------+

Изменение последовательности

Для изменения последовательностей используется инструкция ALTER SEQUENCE. Например, чтобы перезапустить последовательность с другого значения:

ALTER SEQUENCE s RESTART 50;

SELECT NEXTVAL(s);
+------------+
| NEXTVAL(s) |
+------------+
|         50 |
+------------+

Функция SETVAL также может использоваться для установки следующего значения, которое будет возвращено для SEQUENCE, например:

SELECT SETVAL(s, 100);
+----------------+
| SETVAL(s, 100) |
+----------------+
|            100 |
+----------------+

SETVAL можно использовать только для увеличения значения последовательности. Попытка установить меньшее значение завершится ошибкой и вернет NULL:

SELECT SETVAL(s, 50);
+---------------+
| SETVAL(s, 50) |
+---------------+
|          NULL |
+---------------+

Удаление последовательности

Для удаления последовательности используется инструкция DROP SEQUENCE, например:

DROP SEQUENCE s;

Репликация

Если вы хотите использовать последовательности в конфигурации master-master или с Galera, следует использовать INCREMENT=0. Это сообщит последовательности использовать auto_increment_increment и auto_increment_offset для генерации уникальных значений для каждого сервера.

Соответствие стандартам

MariaDB поддерживает как синтаксис ANSI SQL, так и Oracle для последовательностей.

Однако, поскольку SEQUENCE реализована как особый тип таблицы, она использует ту же область имен, что и таблицы. Преимуществами являются отображение последовательностей в SHOW TABLES, а также возможность создания последовательности с помощью CREATE TABLE и удаления с помощью DROP TABLE. Можно выполнять SELECT из нее, как и из любой другой таблицы. Это гарантирует, что все старые инструменты, работающие с таблицами, должны работать с последовательностями.

Поскольку объекты последовательностей действуют как обычные таблицы во многих контекстах, на них будет влиять LOCK TABLES. Это не так в других СУБД, таких как Oracle, где LOCK TABLE не влияет на последовательности.

Примечания

Одна из целей реализации последовательностей заключается в том, чтобы все старые инструменты, такие как mariadb-dump (ранее mysqldump), работали без изменений, сохраняя при этом стандартное использование последовательностей.

Для этого sequence в настоящее время реализована как таблица со специфическими свойствами.

Специальные свойства таблиц последовательностей:

• Таблица последовательности всегда содержит одну строку.
• При создании последовательности, либо с помощью CREATE TABLE, либо CREATE SEQUENCE, будет вставлена одна строка.
• Если вы попытаетесь вставить данные в таблицу последовательности, единственная строка будет обновлена. Это позволяет инструменту mariadb-dump работать, но также предоставляет дополнительное преимущество — возможность изменения всех свойств последовательности с помощью одной вставки. Новые приложения, конечно же, также должны использовать ALTER SEQUENCE.
• UPDATE или DELETE не могут быть выполнены для объектов последовательности.
• Выполнение запроса select на последовательности покажет текущее состояние последовательности, за исключением значений, зарезервированных в кэше. Столбец next_value показывает следующее значение, не зарезервированное кэшем.
• FLUSH TABLES закроет последовательность, и следующее сгенерированное число последовательности будет соответствовать тому, что хранится в объекте последовательности. По сути, это отбросит кэшированные значения.
• Множество обычных операций с таблицами работают с таблицами последовательностей. См. следующий раздел.

Операции с таблицами, совместимые с последовательностями

• SHOW CREATE TABLE sequence_name. Это отображает структуру таблицы, которая лежит в основе SEQUENCE, включая имена полей, которые могут использоваться с SELECT или даже CREATE TABLE.
• CREATE TABLE sequence-structure ... SEQUENCE=1
• ALTER TABLE sequence RENAME TO sequence2
• RENAME TABLE sequence_name TO new_sequence_name
• DROP TABLE sequence_name. Это разрешено в основном для того, чтобы старые инструменты, такие как mariadb-dump, работали с таблицами последовательностей.
• SHOW TABLES

Реализация

Внутренне, таблицы последовательностей создаются как обычные таблицы без отката (двигатели InnoDB, Aria и MySAM поддерживают это), обернутые объектом движка последовательностей. Это позволило нам создавать последовательности практически без влияния на производительность обычных таблиц. (Стоимость — одна проверка «if» на вставку, если включен двоичный журнал).

Структура подлежащей таблицы

Следующий пример показывает структуру таблицы последовательностей и то, как она может использоваться как таблица. (Результаты вывода незначительно отредактированы для большей удобочитаемости)

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: 0

Столбец cycle_count увеличивается каждый раз, когда последовательность обнуляется.

Благодарности

• Благодарим Jianwe Zhao из Aliyun за его работу над SEQUENCE в AliSQL, которая дала идеи и вдохновение для этой работы.
• Благодарим Petra Gulutzana за помощь в тестировании и полезные комментарии по реализации.

См. также

• CREATE SEQUENCE
• ALTER SEQUENCE
• DROP SEQUENCE
• NEXT VALUE FOR
• PREVIOUS VALUE FOR
• SETVAL(). Установка следующего значения для последовательности.
• AUTO INCREMENT
• Движок хранения последовательностей