автоинкремент sql что это
SQL AUTO INCREMENT Поле
Поле автоматического приращения
Автоматическое приращение позволяет автоматически генерировать уникальный номер при вставке новой записи в таблицу.
Часто это поле первичного ключа, которое мы хотели бы создать автоматически каждый раз при вставке новой записи.
Синтаксис для MySQL
Следующая инструкция SQL определяет столбец «ID», который будет автоматически увеличивать поле первичного ключа в таблице «лица»:
MySQL использует ключевое слово ауто_инкремент для выполнения функции автоматического приращения.
По умолчанию начальное значение для ауто_инкремент равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Чтобы позволить ауто_инкремент последовательности начать с другого значения, используйте следующую инструкцию SQL:
Чтобы вставить новую запись в таблицу «персоны», нам не нужно будет указывать значение для «ID» столбец (уникальное значение будет добавлено автоматически):
Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «ID» присваивается уникальное значение. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».
Синтаксис для SQL Server
Следующая инструкция SQL определяет столбец «ID», который будет автоматически увеличивать поле первичного ключа в таблице «лица»:
Сервер MS SQL использует ключевое слово Identity для выполнения функции автоматического приращения.
В приведенном выше примере начальное значение Identity равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Совет: Чтобы указать, что столбец «ID» должен начинаться со значения 10 и увеличиваться на 5, измените его на Identity (10, 5).
Чтобы вставить новую запись в таблицу «персоны», нам не нужно будет указывать значение для столбца «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».
Синтаксис для доступа
Следующая инструкция SQL определяет столбец «ID», который будет автоматически увеличивать поле первичного ключа в таблице «лица»:
Для выполнения функции автоматического приращения используется ключевое слово автоприращение.
По умолчанию начальное значение для автоприращения равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Совет: Чтобы указать, что столбец «ID» должен начинаться со значения 10 и увеличиваться на 5, измените автоприращение на автоприращение (10, 5).
Чтобы вставить новую запись в таблицу «персоны», нам не нужно будет указывать значение для столбца «ID» (уникальное значение будет добавлено автоматически):
Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «п_ид» присваивается уникальное значение. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».
Синтаксис для Oracle
В Oracle код немного сложнее.
Вам придется создать поле с автоматическим приращением с помощью объекта Sequence (этот объект генерирует номерную серию).
Используйте следующий синтаксис создания последовательности:
Приведенный выше код создает объект Sequence с именем сек_персон, начинающийся с 1 и увеличивающийся на 1. Он также будет кэшировать до 10 значений для производительности. Параметр cache указывает, сколько значений последовательности будет сохранено в памяти для более быстрого доступа.
Чтобы вставить новую запись в таблицу «персоны», мы должны будем использовать функцию NEXTVAL (Эта функция извлекает следующее значение из последовательности сек_персон):
Вышеприведенная инструкция SQL вставит новую запись в таблицу «персоны». Столбцу «ID» присваивается следующий номер из последовательности сек_персон. В столбце «имя» будет установлено значение «Ларс», а в столбце «Фамилия» будет установлено значение «Монсен Сотрудник IIP».
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).
Чтобы вставить новую запись в таблицу «Persons», нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):
Приведенная выше инструкция SQL вставит новую запись в таблицу» Persons». Столбцу «Personid» будет присвоено уникальное значение. Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».
Синтаксис для Access
Следующая инструкция SQL определяет столбец «Personid» как поле первичного ключа автоинкремента в таблице «Persons»:
MS Access использует ключевое слово AUTOINCREMENT для выполнения функции автоматического приращения.
По умолчанию начальное значение для AUTOINCREMENT равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Совет: Чтобы указать, что столбец «Personid» должен начинаться со значения 10 и увеличиваться на 5, измените значение autoincrement на AUTOINCREMENT(10,5).
Чтобы вставить новую запись в таблицу «Persons», нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):
Синтаксис для Oracle
В Oracle код немного сложнее.
Вам нужно будет создать поле автоинкремента с объектом SEQUENCE (этот объект генерирует числовую последовательность).
Используйте следующий синтаксис CREATE SEQUENCE:
Приведенный выше код создает объект последовательности под названием «seq_person», который начинается с 1 и будет увеличиваться на 1. Он также будет кэшировать до 10 значений для повышения производительности. Параметр «CACHE» указывает, сколько значений последовательности будет храниться в памяти для более быстрого доступа.
Чтобы вставить новую запись в таблицу «Persons», нам придется использовать функцию «nextval» (эта функция извлекает следующее значение из последовательности «seq_person»):
Приведенная выше инструкция SQL вставит новую запись в таблицу «Persons». Столбецу «Personid» будет присвоен следующий номер из последовательности «seq_person». Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».
Снова про AUTO_INCREMENT
Все, кто работает с базами данных, знают, что такое AUTO_INCREMENT. Про него много всего написано, в том числе и на хабре. В этой статье я хочу изложить свои мысли на эту тему, потому что ранее я не встречал рассуждений именно в таком плане. Но сначала давайте определимся, зачем нам вообще база данных.
Действительно, будем все хранить в оперативной памяти. Хотя, нет, ее маловато будет. Да и при неожиданном выключении все данные пропадут. Поставим ИБП. Нет, лучше 2. А еще лучше представим, что память у нас бесконечная и данные при выключении не теряет.
Теперь можно писать что-то типа такого:
И насоздавать хоть 10 таких заказов, хоть 20, хоть 100… Хм, что-то много я насоздавал, надо бы коллекцию какую-нибудь. А к ней поиск по полям. И язык запросов, чтобы все универсально было. Плюс индексы по этим полям для ускорения поиска.
Что у нас получилось? Получилась практически обычная база данных. За одним исключением – у объектов нет первичного ключа. Все объекты идентифицируются по адресу в адресном пространстве.
Теперь можно вспомнить, что у оперативной памяти есть свои ограничения, программы закрываются, компьютеры выключаются, данные могут быть нужны нескольким программам сразу. Значит, нужно сделать некоторую абстракцию над оперативной памятью, чтобы адреса у всех были всегда одни и те же. Примерно как виртуальная адресация в защищенном режиме процессора. И хранить это в файлах на диске. И сделать систему управления.
Это подводит к мысли, почему целочисленные ключи удобны в использовании. Причины не только в реализации систем управления БД. База данных – это адресное пространство для размещения объектов. А целочисленный ключ (ID) – это ссылка на объект.
Из этого следует, что auto_increment в пределах одной таблицы — это не совсем правильно. Каждая запись в БД должна иметь уникальный адрес. Получается как бы двухмерное адресное пространство – адреса растут в одном направлении, сами объекты в другом; размер одного объекта не влияет на адреса соседних объектов. При этом неважно, распределенная БД или нет, сколько в ней серверов, баз и таблиц. Это одно адресное пространство, и адресация должна быть однозначной.
Получается, теоретически запись можно найти по ключу без указания таблицы; или наоборот, можно найти саму таблицу, в которой находится запись с таким ключом. Можно даже разбить адресное пространство на диапазоны: например, до 1000000 системные таблицы, настройки, справочники; после 1000000 реальные данные. При переполнении можно добавлять к диапазону заранее определенную достаточно большую константу, или заранее настроить карту диапазонов.
Однако, ID не нужно относить к атрибутам самого объекта как элемента модели данных. Например:
У переменной x есть значение 2 и адрес 0x123456. Но нельзя сказать, что адрес – это атрибут целочисленных значений. Также нельзя сказать, что ID – это атрибут объектов типа User и Order. Он служит просто для связи абстрактной модели и технической реализации.
Все ограничения естественного ключа нужно делать дополнительными техническими средствами, на уровне самой БД или приложения. На самом деле, я даже не могу представить ситуацию, в которой естественный первичный ключ является «естественным». Все варианты типа номер паспорта, телефона, ИНН – это искусственно введенная нумерация объектов, именно потому что нельзя выделить у них или их владельцев уникальный набор признаков. И использовать ее можно только для идентификации именно этих объектов – выданных паспортов, узлов телефонной сети, экономического субъекта, платящего налоги. В реальности всегда есть возможность существования дубликатов с одинаковыми свойствами, и набора разных свойств в разное время у одной сущности.
Несколько заметок о 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 все правильно сделал.
19) Авто Инкремент
Что такое автоинкремент?
Автоинкремент — это функция, которая работает с числовыми типами данных. Он автоматически генерирует последовательные числовые значения каждый раз, когда запись вставляется в таблицу для поля, определенного как автоинкремент.
Когда использовать автоинкремент?
В уроке по нормализации базы данных мы рассмотрели, как можно хранить данные с минимальной избыточностью, сохраняя данные во многих небольших таблицах, связанных друг с другом с использованием первичного и внешнего ключей.
Первичный ключ должен быть уникальным, поскольку он однозначно идентифицирует строку в базе данных. Но как мы можем гарантировать, что первичный ключ всегда уникален? Одним из возможных решений было бы использование формулы для генерации первичного ключа, который проверяет наличие ключа в таблице перед добавлением данных. Это может хорошо работать, но, как вы видите, подход сложен и не защищен от ошибок. Чтобы избежать такой сложности и гарантировать, что первичный ключ всегда уникален, мы можем использовать функцию автоматического увеличения MySQL для генерации первичных ключей. Автоинкремент используется с типом данных INT. Тип данных INT поддерживает значения со знаком и без знака. Типы данных без знака могут содержать только положительные числа. Рекомендуется определить ограничение без знака для первичного ключа с автоинкрементом.
Синтаксис автоинкремента
Давайте теперь посмотрим на скрипт, используемый для создания таблицы категорий фильмов.
Обратите внимание на «AUTO_INCREMENT» в поле category_id. Это приводит к тому, что идентификатор категории создается автоматически каждый раз, когда в таблицу добавляется новая строка. Он не предоставляется при вставке данных в таблицу, MySQL генерирует его.