MySQL эквивалент Oracle SEQUENCE.NEXTVAL

Мне нужно создать запрос запуска, который вернет следующее значение ID в следующей таблице:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
)

В Oracle вы можете вызвать NEXTVAL в последовательности и дать следующую последовательность (обратите внимание: без необходимости вставки в таблицу).

После googling я обнаружил, что вы можете найти текущее значение auto_increment, используя следующий запрос:

SELECT Auto_increment 
FROM information_schema.tables 
WHERE table_name='animals';

Проблема в том, что я хотел бы, чтобы значение увеличивалось каждый раз при запросе значения. В Oracle, когда вы вызываете nextval, значение последовательности увеличивается, даже если вы не вставляете строку в таблицу.

Можно ли каким-либо образом изменить указанный выше запрос так, чтобы возвращаемое значение всегда отличалось от последнего запроса запроса? то есть Auto_increment увеличивается каждый раз, когда он проверяется, и при использовании в запросе он будет использовать новое значение.

Я использую Spring JDBCTemplate, поэтому, если это можно сделать в одном запросе, тем лучше.

Ответ 1

Этот пример с InnoDB демонстрирует способ реализации собственного счетчика с использованием взаимосвязанных запросов:

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Что вам нужно для создания пробела? Чтобы зарезервировать идентификаторы? Я предпочел бы "исправить" дизайн любой ценой и обновить другие модули, а не касаться последовательности.

Вместо того, чтобы просто увеличивать последовательность явным образом, я подразумевал бы ее, вставив строку по умолчанию (помеченную недействительно) для каждого идентификатора, чтобы выделить и вернуть идентификатор. Этот подход является последовательным и переносимым. Позже вместо принудительного вставки с использованием явного значения последовательности вы можете обновить эти строки по умолчанию по их соответствующим значениям последовательности. Для этого требуется больше памяти, но нет замков. Здесь может помочь сбор мусора на истекших рядах. Операторы "вставки или обновления" могут воссоздавать собранные строки мусора, я бы этого не сделал.

Ответ 2

http://docs.oracle.com/cd/E17952_01/refman-5.5-en/example-auto-increment.html

3.6.9. Использование AUTO_INCREMENT

Атрибут AUTO_INCREMENT может использоваться для создания уникального идентификатора для новых строк:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;
Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

В столбце AUTO_INCREMENT не указано значение, поэтому MySQL автоматически назначил порядковые номера. Вы также можете явно назначить NULL или 0 столбцу для генерации порядковых номеров.

Вы можете получить последнее значение AUTO_INCREMENT с помощью функции LAST_INSERT_ID() SQL или функции API mysql_insert_id() C. Эти функции специфичны для подключения, поэтому их возвращаемые значения не зависят от другого соединения, которое также выполняет вставки.

Используйте наименьший целочисленный тип данных для столбца AUTO_INCREMENT, который является достаточно большим для хранения максимального значения последовательности, которое вам нужно. Когда столбец достигает верхнего предела типа данных, следующая попытка сгенерировать порядковый номер терпит неудачу. Используйте атрибут UNSIGNED, если это возможно, чтобы разрешить больший диапазон. Например, если вы используете TINYINT, максимальный допустимый порядковый номер - 127. Для TINYINT UNSIGNED максимальный - 255. См. Раздел 11.2.1, "Целочисленные типы (точное значение) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT" для диапазонов всех целых типов.

Примечание Для многострочной вставки LAST_INSERT_ID() и mysql_insert_id() фактически возвращают ключ AUTO_INCREMENT из первой вставленной строки. Это позволяет корректно воспроизводить вставки нескольких строк на других серверах в настройке репликации.

Если столбец AUTO_INCREMENT является частью нескольких индексов, MySQL генерирует значения последовательности, используя индекс, который начинается с столбца AUTO_INCREMENT, если таковой имеется. Например, если таблица животных содержит индексы PRIMARY KEY (grp, id) и INDEX (id), MySQL будет игнорировать PRIMARY KEY для генерации значений последовательности. В результате таблица будет содержать одну последовательность, а не последовательность на значение grp.

Чтобы начать с значения AUTO_INCREMENT, отличного от 1, установите это значение с помощью CREATE TABLE или ALTER TABLE, например:

mysql > ALTER TABLE tbl AUTO_INCREMENT = 100; Примечания InnoDB

Для таблиц InnoDB будьте осторожны, если вы измените столбец, содержащий значение автоматического прироста, в середине последовательности инструкций INSERT. Например, если вы используете инструкцию UPDATE для размещения нового большего значения в столбце автоинкремент, последующий INSERT может столкнуться с ошибкой "Duplicate entry". Тест, если значение auto-increment уже присутствует, возникает, если вы выполняете DELETE, за которым следуют другие инструкции INSERT, или когда вы COMMIT транзакции, но не после инструкции UPDATE.

Заметки MyISAM

Для таблиц MyISAM вы можете указать AUTO_INCREMENT для вторичного столбца в индексе с несколькими столбцами. В этом случае генерируемое значение для столбца AUTO_INCREMENT вычисляется как MAX (auto_increment_column) + 1 WHERE prefix = given-prefix. Это полезно, если вы хотите поместить данные в упорядоченные группы.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;
Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

В этом случае (когда столбец AUTO_INCREMENT является частью индекса с несколькими столбцами), значения AUTO_INCREMENT используются повторно, если вы удаляете строку с самым большим значением AUTO_INCREMENT в любой группе. Это происходит даже для таблиц MyISAM, для которых значения AUTO_INCREMENT обычно не используются повторно.

Дополнительная литература

Дополнительная информация об AUTO_INCREMENT доступна здесь:

Как назначить атрибут AUTO_INCREMENT для столбца: Раздел 13.1.17, "Синтаксис CREATE TABLE" и Раздел 13.1.7, "ALTER TABLE Syntax".

Как работает AUTO_INCREMENT в зависимости от режима NO_AUTO_VALUE_ON_ZERO SQL: раздел 5.1.7, "Режимы сервера SQL".

Как использовать функцию LAST_INSERT_ID(), чтобы найти строку, содержащую самое последнее значение AUTO_INCREMENT: Раздел 12.14, "Информационные функции".

Установка значения AUTO_INCREMENT, которое будет использоваться: Раздел 5.1.4, "Системные переменные сервера".

AUTO_INCREMENT и репликация: раздел 16.4.1.1, "Репликация и AUTO_INCREMENT".

Переменные серверной системы, связанные с AUTO_INCREMENT (auto_increment_increment и auto_increment_offset), которые могут использоваться для репликации: Раздел 5.1.4, "Системные переменные сервера".

http://search.oracle.com/search/search?q=auto_increment&group=Documentation&x=0&y=0

Ответ 3

Вы можете добавить свою собственную функцию MySQL - как показано на http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/ - вы можете сделать что-то вроде этого:

SELECT nextval('sq_my_sequence') as next_sequence;

Ответ 4

MySQL использует AUTO_INCREMENT, который служит целям. Но есть различия:

MySQL AUTO_INCREMENT для Oracle SEQUENCE Различия

AUTO_INCREMENT ограничивается одним столбцом на таблицу
AUTO_INCREMENT должен быть назначен определенному table.column(не разрешая использование нескольких таблиц)
AUTO_INCREMENT INSERTed как не заданный столбец или значение NULL

если вы хотите увидеть реализацию SEQUENCE с MySQL, можете сделать с SP.

См. ниже ссылку, объясняющее все, что вы хотите.

http://ronaldbradford.com/blog/sequences-in-mysql-2006-01-26/

Ответ 5

Вам нужно следующее значение в таблице THAT, чтобы вы могли создавать строки, которые еще не вставлены, не нарушая другие процессы, которые используют автоинкремент?

Некоторые параметры:

Вперед и просто вставьте строки, чтобы "использовать последовательность", пометить их как ожидающие, а затем обновить их позже.

Вставить транзакцию и прервать транзакцию - последовательность автоматического номера должна исчерпаться и сделать "пробел" в таблице, как правило, - этот номер теперь свободен для использования.

В Oracle последовательность полностью не зависит от таблицы, поэтому процессы могут использовать последовательность или нет (и они также могут использовать одну и ту же последовательность для разных таблиц). В этом ключе вы можете реализовать таблицу только для последовательности, к которой вы обращаетесь через какую-то функцию, а также для других процессов, которые должны полагаться на автоматическое приращение, удалить автоинкремент и использовать триггер, который присваивает от одного и того же если функция id не указана.

Ответ 6

Посмотрите на MariaDBs SEQUENCE Engine. С его помощью вы можете генерировать последовательности так же легко, как

SELECT seq FROM seq_1_to_5;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
+-----+

Подробности здесь: https://mariadb.com/kb/en/mariadb/sequence/

С этим вы сможете сделать что-то вроде

SELECT min(seq) as nextVal FROM seq_1_to_500 
WHERE seq NOT IN (SELECT ID FROM customers)