MySQL AUTO_INCREMENT не откатывается

Позвольте мне отметить кое-что очень важное:

Никогда не следует полагаться на числовые характеристики автоматически сгенерированных клавиш.

То есть, кроме сравнения их на равенство (=) или неравенство (‹›), вам больше ничего не следует делать. Никаких операторов отношения (‹,›), сортировки по индексам и т. Д. Если вам нужно отсортировать по «дате добавления», сделайте столбец «дата добавления».

Относитесь к ним как к яблокам и апельсинам. Имеет ли смысл спрашивать, совпадает ли яблоко с апельсином? да. Есть ли смысл спрашивать, больше ли яблоко апельсина? Нет (на самом деле, это так, но вы меня поняли.)

Если вы будете придерживаться этого правила, пробелы в непрерывности автоматически сгенерированных индексов не вызовут проблем.

Так не может быть. Учитывать:

• В первой программе вы открываете транзакцию и вставляете в таблицу FOO, которая имеет первичный ключ autoinc (произвольно, мы говорим, что он получает значение ключа 557).
• Программа 2 запускается, она открывает транзакцию и вставляет в таблицу FOO, получая 558.
• Запрограммируйте две вставки в таблицу BAR, в которой есть столбец, являющийся внешним ключом для FOO. Итак, теперь 558 находится как в FOO, так и в BAR.
• Программа 2 теперь фиксируется.
• Программа 3 запускается и генерирует отчет из таблицы FOO. Распечатывается запись 558.
• После этого программный откат.

Как база данных восстанавливает значение 557? Переходит ли он в FOO и уменьшает все остальные первичные ключи больше 557? Как исправить БАР? Как он стирает 558, напечатанный в отчете программы три вывода?

Порядковые номера Oracle также не зависят от транзакций по той же причине.

Если вы можете решить эту проблему за постоянное время, я уверен, что вы можете заработать много денег в области базы данных.

Теперь, если у вас есть требование, чтобы в вашем поле автоматического увеличения никогда не было пробелов (например, для целей аудита). Тогда вы не сможете откатить свои транзакции. Вместо этого вам нужно иметь флаг статуса в ваших записях. При первой вставке статус записи - «Незавершенная», затем вы запускаете транзакцию, выполняете свою работу и обновляете статус до «завершена» (или что вам нужно). Затем, когда вы делаете коммит, запись становится активной. Если транзакция откатывается, неполная запись все еще присутствует для аудита. Это вызовет у вас много других головных болей, но это один из способов справиться с контрольными журналами.

У меня был клиент, который нуждался в идентификаторе для отката в таблице счетов-фактур, где заказ должен быть последовательным

Мое решение в MySQL заключалось в том, чтобы удалить AUTO-INCREMENT и вытащить последний идентификатор из таблицы, добавить один (+1), а затем вставить его вручную.

Если таблица названа «ТаблицаA», а столбец «Автоинкремент» - «Id»

INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)

Почему вас волнует откат? Ключевые поля AUTO_INCREMENT не должны иметь никакого значения, поэтому вам действительно не нужно заботиться о том, какое значение используется.

Если у вас есть информация, которую вы пытаетесь сохранить, возможно, вам понадобится еще один неключевой столбец.

Я не знаю, как это сделать. Согласно документации MySQL, это является ожидаемым поведением и будет происходить со всеми режимами блокировки innodb_autoinc_lock_mode. Конкретный текст:

Во всех режимах блокировки (0, 1 и 2), если транзакция, которая генерировала значения автоинкремента, откатывается, эти значения автоинкремента «теряются». После того, как значение сгенерировано для столбца с автоматическим приращением, его нельзя откатить, независимо от того, завершен ли оператор, подобный INSERT, и независимо от того, откатывается ли содержащая транзакция. Такие утраченные ценности не используются повторно. Таким образом, могут быть пробелы в значениях, хранящихся в столбце AUTO_INCREMENT таблицы.

Если вы установите auto_increment в 1 после отката или удаления, при следующей вставке MySQL увидит, что 1 уже используется, и вместо этого получит значение MAX() и прибавит к нему 1.

Это гарантирует, что в случае удаления строки с последним значением (или отката вставки) она будет повторно использована.

Чтобы установить auto_increment равным 1, сделайте что-то вроде этого:

ALTER TABLE tbl auto_increment = 1

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

Имейте в виду, что это не предотвращает пропуски из записей, удаленных в середине, или если другая вставка должна произойти до того, как вы установите auto_increment обратно в 1.

INSERT INTO prueba(id) 
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

Если таблица не содержит значений или нулевых строк

добавить цель для обновления типа ошибки mysql FROM на SELECT

Конкретный ответ на эту конкретную дилемму (которая была у меня тоже) заключается в следующем:

1) Создайте таблицу, содержащую разные счетчики для разных документов (счетов-фактур, квитанций, RMA и т. Д.); Вставьте запись для каждого из ваших документов и добавьте начальный счетчик к 0.

2) Перед созданием нового документа выполните следующие действия (например, для счетов-фактур):

UPDATE document_counters SET counter = LAST_INSERT_ID(counter + 1) where type = 'invoice'

3) Получите последнее значение, которое вы только что обновили, например:

SELECT LAST_INSERT_ID()

или просто используйте свою функцию PHP (или что-то еще) mysql_insert_id (), чтобы получить то же самое

4) Вставьте новую запись вместе с основным идентификатором, который вы только что получили из БД. Это переопределит текущий индекс автоинкремента и обеспечит отсутствие пропусков в идентификаторах между вашими записями.

Конечно, все это должно быть заключено в транзакцию. Прелесть этого метода в том, что при откате транзакции ваш оператор UPDATE из шага 2 будет откатиться, и счетчик больше не изменится. Другие параллельные транзакции будут блокироваться до тех пор, пока первая транзакция не будет зафиксирована или откатана, поэтому у них не будет доступа ни к старому, ни к новому счетчику, пока все остальные транзакции не будут завершены первыми.

РЕШЕНИЕ:

Давайте использовать tbl_test в качестве примера таблицы и предположим, что поле Id имеет атрибут AUTO_INCREMENT.

CREATE TABLE tbl_test (
Id int NOT NULL AUTO_INCREMENT ,
Name varchar(255) NULL ,
PRIMARY KEY (`Id`)
)
;

Предположим, что в таблицу уже вставлены сотни или тысячи строк, и вы больше не хотите использовать AUTO_INCREMENT; потому что при откате транзакции поле «Id» всегда добавляет +1 к значению AUTO_INCREMENT. Чтобы избежать этого, вы можете сделать следующее:

• Давайте удалим значение AUTO_INCREMENT из столбца 'Id' (это не удалит ваши вставленные строки):

ALTER TABLE tbl_test MODIFY COLUMN Id  int(11) NOT NULL FIRST;

• Наконец, мы создаем триггер BEFORE INSERT для автоматической генерации значения Id. Но использование этого способа не повлияет на значение вашего идентификатора, даже если вы откатите любую транзакцию.

CREATE TRIGGER trg_tbl_test_1
BEFORE INSERT ON tbl_test
FOR EACH ROW
  BEGIN
    SET NEW.Id= COALESCE((SELECT MAX(Id) FROM tbl_test),0) + 1;
  END;

Вот и все! Готово!

You're welcome.

Если вам нужно назначать идентификаторы в числовом порядке без пробелов, вы не можете использовать столбец с автоинкрементом. Вам нужно будет определить стандартный целочисленный столбец и использовать хранимую процедуру, которая вычисляет следующее число в последовательности вставки и вставляет запись в транзакцию. Если вставка не удалась, то при следующем вызове процедуры она пересчитает следующий идентификатор.

Сказав это, было бы плохой идеей полагаться на то, что идентификаторы находятся в определенном порядке без пробелов. Если вам нужно сохранить порядок, вам, вероятно, следует поставить метку времени для строки при вставке (и, возможно, при обновлении).