auto increment sql что это

SQL Автоматическое увеличение поля

SQL AUTO INCREMENT

Автоматическое приращение позволяет автоматически генерировать уникальное число при вставке новой записи в таблицу.

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

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

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

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

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

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

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

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

Синтаксис для SQL Server

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

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

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

Совет: Чтобы указать, что столбец «Personid» должен начинаться со значения 10 и увеличиваться на 5, измените его на IDENTITY(10,5).

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

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

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

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

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

Совет: Чтобы указать, что столбец «Personid» должен начинаться со значения 10 и увеличиваться на 5, измените значение autoincrement на AUTOINCREMENT(10,5).

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

В Oracle код немного сложнее.

Вам нужно будет создать поле автоинкремента с объектом SEQUENCE (этот объект генерирует числовую последовательность).

Используйте следующий синтаксис CREATE SEQUENCE:

Приведенный выше код создает объект последовательности под названием «seq_person», который начинается с 1 и будет увеличиваться на 1. Он также будет кэшировать до 10 значений для повышения производительности. Параметр «CACHE» указывает, сколько значений последовательности будет храниться в памяти для более быстрого доступа.

Чтобы вставить новую запись в таблицу «Persons», нам придется использовать функцию «nextval» (эта функция извлекает следующее значение из последовательности «seq_person»):

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

Источник

SQL AUTO INCREMENT Поле

Поле автоматического приращения

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

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

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

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

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

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

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

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

Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «ID» присваивается уникальное значение. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».

Синтаксис для SQL Server

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

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

Совет: Чтобы указать, что столбец «ID» должен начинаться со значения 10 и увеличиваться на 5, измените его на Identity (10, 5).

Читайте также: campari ликер с чем пить

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

The SQL statement above would insert a new record into the «Persons» table. The «ID» column would be assigned a unique value. The «FirstName» column would be set to «Lars» and the «LastName» column would be set to «Monsen».

Синтаксис для доступа

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

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

Совет: Чтобы указать, что столбец «ID» должен начинаться со значения 10 и увеличиваться на 5, измените автоприращение на автоприращение (10, 5).

Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «п_ид» присваивается уникальное значение. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».

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

В Oracle код немного сложнее.

Вам придется создать поле с автоматическим приращением с помощью объекта Sequence (этот объект генерирует номерную серию).

Используйте следующий синтаксис создания последовательности:

Приведенный выше код создает объект Sequence с именем сек_персон, начинающийся с 1 и увеличивающийся на 1. Он также будет кэшировать до 10 значений для производительности. Параметр cache указывает, сколько значений последовательности будет сохранено в памяти для более быстрого доступа.

Чтобы вставить новую запись в таблицу «персоны», мы должны будем использовать функцию NEXTVAL (Эта функция извлекает следующее значение из последовательности сек_персон):

Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «ID» присваивается следующий номер из последовательности сек_персон. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».

Источник

Несколько заметок о MySQL

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

Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.

Для запуска sql-запросов из статьи можно инициализировать таблицы так:

Проверить текущие автоинкременты можно так:

У обоих таблиц они равны 4. При этом имеется такое подключение к БД:

Вставка по уникальному ключу и автоинкремент

Если в таблице есть уникальный ключ, то для вставки и обновления есть три способа переложить проверку уникальности на MySQL: INSERT IGNORE, INSERT… ON DUPLICATE KEY UPDATE, REPLACE. Каждый тип запросов по разному ведет себя с автоинкрементом на разных типах таблиц:

Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:

Там автоинкремент остался 4. Аналогичная ситуация будет с ON DUPLICATE KEY UPDATE:

В user_innodb автоинкремент стал 6, а у user_myisam остался 4.

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

Увеличит автоинкремент до 7, теперь у Петрова >
У MyISAM аналогично:

Автоинкремент стал 5, а Петров получил >
Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.

Получение id изменяемой записи после обновления

Выведет 7 и 7, первый раз запись была добавлена под второй раз изменена.

С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0

Реализация SEQUENCE

У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.

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

Решить эту задачу можно так:

В одной таблице получается сразу несколько последовательностей, в этом примере три.

Далее можно получать следующий ID из нужной последовательности с помощью функции
last_insert_id():

Для автоинкремента есть возможность указать шаг приращения опцией конфигурации auto_increment_increment. В этом примере такую функцию можно реализовать примерно так:

Немного о беззнаковых целых

С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.

История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:

Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.

Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.

Источник

Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL

Причина по которому я написал эту статью — это неожиданное поведение ф-ции LAST_INSERT_ID() при выполнении запроса в котором я в поле с атрибутом AUTO_INCREMENT передавал значение.

LAST_INSERT_ID()

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

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

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

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

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

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

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

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

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

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:

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

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

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

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

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

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

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

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

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

Источник

Снова про AUTO_INCREMENT

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

Действительно, будем все хранить в оперативной памяти. Хотя, нет, ее маловато будет. Да и при неожиданном выключении все данные пропадут. Поставим ИБП. Нет, лучше 2. А еще лучше представим, что память у нас бесконечная и данные при выключении не теряет.

Теперь можно писать что-то типа такого:

И насоздавать хоть 10 таких заказов, хоть 20, хоть 100… Хм, что-то много я насоздавал, надо бы коллекцию какую-нибудь. А к ней поиск по полям. И язык запросов, чтобы все универсально было. Плюс индексы по этим полям для ускорения поиска.

Что у нас получилось? Получилась практически обычная база данных. За одним исключением – у объектов нет первичного ключа. Все объекты идентифицируются по адресу в адресном пространстве.

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

Это подводит к мысли, почему целочисленные ключи удобны в использовании. Причины не только в реализации систем управления БД. База данных – это адресное пространство для размещения объектов. А целочисленный ключ (ID) – это ссылка на объект.

Из этого следует, что auto_increment в пределах одной таблицы — это не совсем правильно. Каждая запись в БД должна иметь уникальный адрес. Получается как бы двухмерное адресное пространство – адреса растут в одном направлении, сами объекты в другом; размер одного объекта не влияет на адреса соседних объектов. При этом неважно, распределенная БД или нет, сколько в ней серверов, баз и таблиц. Это одно адресное пространство, и адресация должна быть однозначной.

Получается, теоретически запись можно найти по ключу без указания таблицы; или наоборот, можно найти саму таблицу, в которой находится запись с таким ключом. Можно даже разбить адресное пространство на диапазоны: например, до 1000000 системные таблицы, настройки, справочники; после 1000000 реальные данные. При переполнении можно добавлять к диапазону заранее определенную достаточно большую константу, или заранее настроить карту диапазонов.

Однако, ID не нужно относить к атрибутам самого объекта как элемента модели данных. Например:

У переменной x есть значение 2 и адрес 0x123456. Но нельзя сказать, что адрес – это атрибут целочисленных значений. Также нельзя сказать, что ID – это атрибут объектов типа User и Order. Он служит просто для связи абстрактной модели и технической реализации.

Все ограничения естественного ключа нужно делать дополнительными техническими средствами, на уровне самой БД или приложения. На самом деле, я даже не могу представить ситуацию, в которой естественный первичный ключ является «естественным». Все варианты типа номер паспорта, телефона, ИНН – это искусственно введенная нумерация объектов, именно потому что нельзя выделить у них или их владельцев уникальный набор признаков. И использовать ее можно только для идентификации именно этих объектов – выданных паспортов, узлов телефонной сети, экономического субъекта, платящего налоги. В реальности всегда есть возможность существования дубликатов с одинаковыми свойствами, и набора разных свойств в разное время у одной сущности.

Источник