Мой sql auto_increment не работает для фиксированного столбца

SQL Справочник

SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

LAST_INSERT_ID()

И так имеем таблички

Таблица авторов:

CREATE TABLE `author` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	`birthday` DATE NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1;

Таблица книг авторов:

CREATE TABLE `books` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`id_author` INT(10) NULL DEFAULT NULL,
	`book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
	PRIMARY KEY (`id`),
	INDEX `id_author` (`id_author`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=1;

Добавляем данные, обратите внимание на запросы тут :

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Николай Николаевич', 'Носов', '2008-11-23');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка учится');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка-путешественник');

и тут :

INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (5, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');

Ну разница не только в значениях полей, а тут главная фишка, что при добавлении автора в первой тройке запросов, автоинкремент делал сама БД, а во второй тройке я принудительно указал номер в поле автоикремент, вот что получилось:

mysql> SELECT * FROM author;
+----+-------------------------------------+------------+------------+
| id | name                                | fam        | birthday   |
+----+-------------------------------------+------------+------------+
|  1 | Николай Николаевич               | Носов     | 2008-11-23 |
|  5 | Артур Конан                           | Дойль     | 1859-05-22 |
+----+-------------------------------------+------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM books;
+----+-----------+-----------------------------------------------+
| id | id_author | book                                          |
+----+-----------+-----------------------------------------------+
|  1 |         1 | Незнайка учится                 |
|  2 |         1 | Незнайка-путешественник |
|  3 |         2 | Затерянный мир                   |
|  4 |         2 | Шерлок Холмс                       |
+----+-----------+-----------------------------------------------+
4 rows in set (0.00 sec)

Как вы заметили, с таблицей авторов все окей, 1 и 5 запись добавились с теми номерами ключей которые указала БД и я соот.
Во второй таблице — книги, все хуже. У книг с ИД 1 и 2 ИД_автора все ок, привязаны к автору с ИД1 в таблице авторов, а вот книги с ИД 3 и 4 привязаны к автору ИД которого в таблице авторов просто нет?!

Почему произошла такая ситуация. Разбираем.

Оказывается эта функция LAST_INSERT_ID() возвращает ИД поля с атрибутом автоинкремент если запись была вставлена успешно и автоинкремент работал!!!! Что у нас и было в первой тройке запросов, а вот во второй тройке запросов увы такого не было, т.к. я указал ИД, а значение 2 взялось, т.к. последний успешный запрос был при добавлении второй книги первого автора!

Какие еще приколы скрывает в себе эта функция, оказывается если вы делаете многократное добавление в рамках одного запроса, то ф-ция LAST_INSERT_ID() выдаст ИД только первой успешно добавленной записи!!! .

Вот пример

TRUNCATE `author`;
TRUNCATE `books`;
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) 
VALUES (NULL, @lastID, 'Затерянный мир'),
(NULL, @lastID, 'Шерлок Холмс');

Результат

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

А если сделать так

TRUNCATE `author`;
TRUNCATE `books`;
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
SET @lastID := LAST_INSERT_ID();
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');
SELECT LAST_INSERT_ID();

Результат

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

Век живи — век учись ( Народная мудрость).

В заключении еще решил разобрать поведение атрибута AUTO_INCREMENT.

Примеры сбрасывания значения автоматического приращения в MySQL

Сначала создайте таблицу с именем   tmp и присвойте  атрибут AUTO_INCREMENT столбцу id первичного ключа.

CREATE TABLE tmp (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (id)
);

Во-вторых, вставьте пример данных в таблицу tmp:

INSERT INTO tmp(name)
VALUES('test 1'),
('test 2'),
('test 3');

В- третьих, запрос к таблице tmp для проверки операции вставки:

SELECT
*
FROM
tmp;

У нас есть три строки со значениями столбца ID: 1, 2 и 3. Отлично! Пора попрактиковаться в сбросе значения автоинкремента столбца ID.

Использование инструкции ALTER TABLE

Вы можете сбросить значение автоинкремента с помощью оператора ALTER TABLE. Синтаксис оператора ALTER TABLE  для сброса значения автоинкремента выглядит следующим образом:

ALTER TABLE table_name AUTO_INCREMENT = value;

Вы указываете имя таблицы после оператора ALTER TABLE и имя value, которое вы хотите сбросить в выражении AUTO_INCREMENT=value.

Обратите внимание, что значение value должно быть больше или равно текущему максимальному значению столбца автоинкремента. Давайте удалим последнюю запись в таблице tmp с id значением 3:

Давайте удалим последнюю запись в таблице tmp с id значением 3:

DELETE FROM tmp
WHERE
ID = 3;

Если вы вставите новую строку, MySQL назначит 4  столбцу id новой строки. Однако вы можете сбросить число, сгенерированное MySQL, на 3, используя следующую инструкцию ALTER TABLE:

ALTER TABLE tmp AUTO_INCREMENT = 3;

Теперь давайте попробуем вставим новую строку в таблицу tmp и запросить данные из нее, чтобы увидеть эффект:

INSERT INTO tmp(name)
VALUES ('MySQL example 3');

SELECT
*
FROM
tmp;

У нас есть три строки с последним значением автоинкремента, равным 3 вместо 4, что мы и ожидали.

Использование оператора TRUNCATE TABLE

Оператор TRUNCATE TABLE удаляет все данные из таблицы и сбрасывает значение автоинкремента на ноль.

Следующее иллюстрирует синтаксис оператора TRUNCATE TABLE:

TRUNCATE TABLE table_name;

Используя оператор TRUNCATE TABLE, вы удаляете все данные из таблицы навсегда и сбрасываете значение автоинкремента на ноль.

Использование операторов DROP TABLE и CREATE TABLE

Вы можете использовать пару операторов: DROP TABLE и CREATE TABLE, чтобы сбросить столбец автоинкремента. Обратите внимание, что этот метод удаляет все данные из таблицы навсегда. Как и оператор TRUNCATE TABLE, эти операторы удаляют таблицу и воссоздают ее, поэтому значение автоинкремента сбрасывается на ноль

Как и оператор TRUNCATE TABLE, эти операторы удаляют таблицу и воссоздают ее, поэтому значение автоинкремента сбрасывается на ноль.

DROP TABLE table_name;
CREATE TABLE table_name(...);

В этой статье вы узнали, как различными способами сбросить значение автоинкремента в MySQL. Первый способ предпочтительнее, потому что он самый простой и не имеет побочных эффектов.

Изменение/сброс счётчика AUTO_INCREMENT без очистки таблицы

Другим вариантом является следующий SQL запрос:

ALTER TABLE имяВашейТаблицы AUTO_INCREMENT = 1

Обратите внимание, что AUTO_INCREMENT можно установить на любое значение — но помните об ограничении для непустых таблиц InnoDB.

И ещё очень важное замечание об ALTER TABLE. Дело в том, что этот запрос приводит к перестроению всей таблицы

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

Если нужно увеличить значение счётчика AUTO_INCREMENT, то проще вставить ненужную строку (а затем удалить, если нужно). Это займёт долю секунды, в то время как ALTER TABLE может потребовать дни для больших таблиц.

Допустим, я у меня есть таблица со столбцом auto_increment ID и другими столбцами col1, col2…:

INSERT INTO имяВашейТаблицы SET ID = 10000000;
DELETE FROM имяВашейТаблицы WHERE ID = 10000000;

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

5 ответов

Лучший ответ

Сброс auto-inc — это редкая операция. При обычной повседневной работе просто позвольте ей увеличиваться.

Я сделал сброс auto-inc в экземплярах MySQL, используемых для автоматического тестирования. Данный набор таблиц загружается с данными снова и снова и впоследствии удаляет их тестовые данные. Сброс auto-inc может быть лучшим способом сделать тесты повторяемыми, если они ищут конкретные значения в результатах.

Другой сценарий — при создании архивных таблиц. Предположим, у вас есть огромная таблица, и вы хотите эффективно очистить данные (не используя DELETE), но вы хотите заархивировать данные, и вы хотите, чтобы новые данные использовали значения идентификатора выше, чем ваши старые данные.

Вышеприведенная серия операторов позволяет вам атомарно перетасовать новую пустую таблицу на место, чтобы ваше приложение могло продолжать запись в таблицу под именем, к которому оно привыкло. Значение auto-inc, которое вы сбрасываете в новой таблице, должно быть значением, превышающим максимальное значение id в старой таблице, плюс некоторый удобный промежуток, чтобы избежать наложения в моменты между утверждениями.

2

Bill Karwin
16 Апр 2018 в 15:42

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

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

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

1

symcbean
16 Апр 2018 в 15:27

Сброс автоматического приращения обычно помогает с точки зрения организации, вы не видите разрыва между id 6 и 60, если строки между ними были удалены.

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

По моему мнению, просто обрежьте все это после ваших тестов и заполните базу правильной информацией. Если это производство, пусть оно будет работать свободно и свободно, это может принести больше вреда и никакой полезной продукции.

1

abr
16 Апр 2018 в 15:15

К сожалению, я видел это поведение. И из того, что я заметил, это не по технической причине — это ближе к ОКР.

Некоторым действительно не нравятся пробелы в столбце идентификатора — им нравится идея плавного увеличения на 1 для каждой записи. Идея, что некоторые ручные манипуляции с данными, которые они делают, облажает их, не очень приятна — поэтому они проходят через некоторые обручи, чтобы убедиться, что они не вызывают пробелов в числах.

Но да, это ужасная практика. Это просто вопрос о проблемах целостности данных.

2

Kevin
16 Апр 2018 в 16:49

Я не думаю, что когда-либо необходимо сбрасывать auto_increment, если у вас не заканчиваются значения.

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

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

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

В общем, однако, сброс автоприращения не рекомендуется.

3

Gordon Linoff
16 Апр 2018 в 15:14

AUTO_INCREMENT

Это такой атрибут который генерирует порядковый номер к указанному полю. Полей должно быть только с типом integer или float (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT) и имеющий индекс типа (UNIQUE, PRIMARY, KEY). Причем у каждого из типа поля есть свой предел автоинкремента, например для поля TINYINT это значение 127 или 255 для TINYINT UNSIGNED и тд смотрите документацию. Каждая последующая запись будет +1 (по-дефолту) к максимальному числу до добавления в этом поле, наверное не так лучше сказать.
В MySQL есть вспомогательная БД information_schema в которой хранится необходимая информация о всех таблицах всех БД сервера. И вот именно там храниться следующий номер для автоинкремента, т.е. перед добавление записи в таблицу БД не ищет максимальный номер записи, на это ведь будет тратиться время, которое дорого.
В таблице может быть только одно поле которое имеет автоинкремент, и причем это поле не должно иметь дефолтного значения.
Если мы хотим указать какой-то свой номер для поля с автоинкрементом, то мы должны просто передать этому полю значение. Если значение уже есть в таблице, то будет ошибка если тип индекса в поле UNIQUE или PRIMARY, но есть тип индекса KEY то запись спокойно добавится.
Если по каким-то причинам нам надо указывать поле с автоинкрементом в запросе, но мы хотим чтобы этому полю было присвоено значение автоинкремента автоматом, то в это поле надо передать 0 (ноль) или NULL:

INSERT INTO`tablename`VALUE (0);
или
INSERT INTO`tablename`VALUE (NULL);
или
INSERT INTO`tablename`VALUE (NULL), (0); // два запроса на добавления

Если по каким-то причинам вы хотите добавить значение 0 в поля автоинкремента, для этого необходимо прописать в файле-настроке (my.ini / my.cnf) следующую строчку

sql-mode = NO_AUTO_VALUE_ON_ZERO

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

Если по каким-то причинам вам необходимо сделать чтобы следующая запись начиналась с какого-нибудь друго числа, просто напишите команду:

ALTER TABLE `tablename` AUTO_INCREMENT = тут_пишем_нужное_число;

не забывайте об используемом типе поля, чтобы ваше число не вышло за рамки.

А например возникла задача делать автоинкремент не ++1 а через 5, т.е. 1,6,11 т.д. как быть в таком?
Для этого имеются две переменные auto_increment_increment и auto_increment_offset. auto_increment_increment — указывает шаг приращения, а auto_increment_offset указывает стартовую точку.
Для просмотра что у вас используется команда, которая показывает как у вас настроен автоинкремент:

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.00 sec)

Поменять можно 2 путями — только на время работы сервера БД (до перезагрузки) и навсегда — смена конфигурационного файла

1 путь : 
SET @@auto_increment_increment=5; SET @@auto_increment_offset=1;

2 путь в файле my.ini / my.cnf пишем строки :
auto_increment_increment=5; 
auto_increment_offset=1;

Более детальней об изменении AUTO_INCREMENT читать .

Обнаружил в интернете интересный сайт с подкастами. Советую скачать podcast, подкасты на различные темы.Также советую послушать подкасты на сайте dev.mysql.com по тематике данной статьи.

Статья просмотренна 229932 раз, зашло посетителей 68887

Remarks

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

• Каждое новое значение будет сформировано на основе текущих аргументов seed и increment.

• Каждое новое значение для определенной транзакции будет отлично от других параллельных транзакций для таблицы.

Свойство идентификаторов столбца не гарантирует следующее.

Уникальность значения — уникальность значения следует обеспечить с помощью ограничения PRIMARY KEY или UNIQUE либо индекса UNIQUE.

Примечание

Azure Synapse Analytics не поддерживает ограничения PRIMARY KEY или UNIQUE либо индекс UNIQUE. Дополнительные сведения см. в статье .

• Последовательные значения в пределах транзакции ― при вставке транзакцией нескольких строк не гарантируется, что для них будут назначены последовательные значения. Это связано с тем, что в таблице могут выполняться другие параллельные операции вставки. Если значения должны быть последовательными, то транзакция должна использовать монопольную блокировку для таблицы или уровень изоляции SERIALIZABLE.

• Последовательные значения после перезапуска сервера или других ошибок — SQL Server может сохранять значения идентификаторов в кэше для обеспечения высокой производительности, и некоторые из присвоенных значений могут быть потеряны при сбое базы данных или перезагрузке сервера. Это может вызвать пропуски в значениях идентификатора при вставке. Если пропуски недопустимы, приложение должно использовать собственный механизм для создания значений ключей. Использование генератора последовательностей с параметром NOCACHE может привести к ограничению пропусков в незафиксированных транзакциях.

• Повторное использование значений — свойства идентификаторов, созданные конкретным свойством идентификатора с заданными аргументами seed и increment, не используются повторно подсистемой. Если определенная инструкция вставки завершается с ошибкой или производится ее откат, использованные значения идентификаторов не будут созданы повторно. Это может привести к появлению пропусков при создании последующих значений идентификаторов.

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

Если таблица со столбцом идентификаторов опубликована для репликации, этот столбец должен обслуживаться в соответствии с типом репликации. Дополнительные сведения см. в статье Репликация столбцов идентификаторов.

Для каждой таблицы можно создать только один столбец идентификаторов.

В таблицах, оптимизированных для памяти, в качестве начального значения и значения приращения должно быть задано 1,1. Установка для параметров seed или increment значения, отличного от 1, приводит к следующей ошибке: «Использование для параметров seed и increment значений, отличных от 1, не поддерживается в таблицах, оптимизированных для памяти».

5 ответов

24

Из-за этого при перезапуске службы (или сервера) произойдет следующее:

Итак, на простом английском языке, после запуска службы MySQL, он не знает, что такое значение автоматического прироста для вашей таблицы. Поэтому, когда вы сначала вставляете строку, она находит максимальное значение поля, которое использует auto-increment, добавляет 1 к этому значению и использует результирующее значение. Если строк нет, оно начинается с 1.

Это была проблема для нас, поскольку мы использовали таблицу и функцию автоматического инкремента mysql для аккуратного управления идентификаторами в многопоточной среде, где пользователи перенаправлялись на сторонний сайт оплаты. Таким образом, мы должны были убедиться, что идентификатор, полученный третьей стороной и отправленный нам, был уникальным и остался бы таким образом (и, конечно, есть вероятность, что пользователь отменит транзакцию после того, как они были перенаправлены).

Итак, мы создавали строку, получая сгенерированное значение автоинкремента, удаляя строку, чтобы сохранить таблицу чистой и пересылаем значение на сайт оплаты. То, что мы решили сделать, чтобы исправить проблему того, как InnoDB обрабатывает значения AI, было следующим:

Это всегда ведет к тому, что последняя транзакция генерируется как строка в таблице, без лишнего раздувания таблицы.

Надеюсь, что это поможет любому другому, кто может столкнуться с этим.

Изменить (2018-04-18) :

Как упоминалось ниже в Finesse, поведение этого файла было изменено в MySQL 8.0+.

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

-Gremio

2

Просто снимок в темноте — если приложение использует , чтобы выгрузить таблицу после завершения обработки, это сбросит поле автоматического увеличения. Ниже приведено краткое обсуждение . Хотя в этой ссылке упоминается, что InnoDB не сбрасывает auto_increments на trunc, который был указан как ошибка и исправлено несколько лет назад.

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

2

Мы столкнулись с этой проблемой и обнаружили, что при оптимизации таблицы в пустой таблице значение автоинкремента также было сброшено. См. этот отчет об ошибке MySQL .

В качестве обходного пути вы можете:

Вместо .

Кажется, это то, что MySQL делает внутренне (без установки значения Auto increment, очевидно)

1

Только явный сброс этого значения или падение /повторное создание этого поля или другая подобная насильственная операция должны когда-либо сбросить счетчик auto_increment. (TRUNCATE была действительно хорошей теорией.) Кажется невозможным, что вы внезапно обертываете 32-битный INT, когда последнее значение, которое вы видите, составляет всего 600 тыс. Он определенно не должен сбрасываться только потому, что таблица опустошается. У вас либо есть ошибка mysql, либо что-то в вашем PHP-коде. Или парень в соседней комнате играет трюк на вас.

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

Затем, по крайней мере, вы можете увидеть каждую деталь того, что происходит с этой таблицей, в том числе непосредственно перед сбросом счетчика.

InnoDB не сохраняет значение автоматического прироста на диске, поэтому забывает его при выключении сервера MySQL. Когда MySQL запускается снова, движок InnoDB восстанавливает значение автоматического увеличения таким образом: . Это ошибка , которая исправлено в MySQL версии 8.0.

Измените механизм таблицы, чтобы решить проблему:

Или обновите сервер MySQL до версии 8.0, когда он будет выпущен.

Синтаксис для MySQL

Следующая инструкция SQL определяет, что столбец «Personid»,
который должен быть полем первичного ключа с автоматическим приращением в поле первичного ключа в таблице «Persons»:

CREATE TABLE Persons
(
   
Personid int NOT NULL AUTO_INCREMENT,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
    Age int,
   
PRIMARY KEY (Personid)
);

MySQL использует ключевое слово AUTO_INCREMENT для выполнения функции автоматического приращения.

По умолчанию начальное значение для AUTO_INCREMENT равно 1, и оно будет увеличиваться на 1 для каждой новой записи.

Чтобы последовательность AUTO_INCREMENT начиналась с другого значения, используйте следующую инструкцию SQL:

ALTER TABLE Persons AUTO_INCREMENT=100;

Чтобы вставить новую запись в таблицу «Persons»,
нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):

INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);

Приведенная выше инструкция SQL вставит новую запись в таблицу «Persons».
Столбцу «Personid» будет присваивается уникальное значение.
Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».