Оператор INSERT

Оператор INSERT — это оператор языка SQL (Structured Query Language), предназначенный для добавления новых строк (записей) в существующую таблицу реляционной базы данных. Он относится к группе операторов манипуляции данными (Data Manipulation Language, DML) и является одним из фундаментальных инструментов для наполнения базы данных информацией.

Синтаксис и основные варианты использования

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

INSERT ... VALUES

Наиболее распространённая форма, используемая для вставки одной или нескольких строк с явно заданными значениями. Общий синтаксис:

``sql INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); ``

• table_name — имя таблицы, в которую производится вставка.
• (column1, column2, ...) — необязательный список столбцов, в которые будут вставлены значения. Если список опущен, значения должны быть указаны для всех столбцов таблицы в том порядке, в котором они были определены при создании.
• VALUES (value1, value2, ...) — список значений, соответствующих столбцам по порядку. Количество и типы данных значений должны строго совпадать с количеством и типами столбцов.

Пример. Вставка одной строки в таблицу employees:

``sql INSERT INTO employees (first_name, last_name, department, salary) VALUES ('Иван', 'Петров', 'IT', 75000); ``

Пример. Вставка нескольких строк в одной команде (стандарт SQL-92 и выше):

``sql INSERT INTO employees (first_name, last_name, department) VALUES ('Мария', 'Сидорова', 'HR'), ('Алексей', 'Кузнецов', 'Finance'), ('Елена', 'Смирнова', 'IT'); ``

INSERT ... SELECT

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

``sql INSERT INTO target_table (column1, column2, ...) SELECT column1, column2, ... FROM source_table WHERE condition; ``

• target_table — таблица, в которую вставляются данные.
• source_table — таблица (или набор таблиц), из которой выбираются данные.
• WHERE condition — условие фильтрации строк источника.

Пример. Копирование всех сотрудников из отдела IT в архивную таблицу:

``sql INSERT INTO employees_archive (employee_id, first_name, last_name, department) SELECT employee_id, first_name, last_name, department FROM employees WHERE department = 'IT'; ``

Особенности и нюансы

Обработка значений по умолчанию

Если при вставке не указан столбец, для которого определено значение по умолчанию (DEFAULT), то вставляется это значение. Если значение по умолчанию не задано, а столбец допускает NULL, то вставляется NULL. Если столбец не допускает NULL и не имеет значения по умолчанию, вставка вызовет ошибку.

Автоматическая генерация значений

Для столбцов, определённых с атрибутом AUTO_INCREMENT (в MySQL), IDENTITY (в Microsoft SQL Server, PostgreSQL), SERIAL (в PostgreSQL) или GENERATED AS IDENTITY (в стандарте SQL:2003), значение генерируется автоматически при вставке. Обычно можно явно указать значение для такого столбца, но это может нарушить последовательность или вызвать конфликт.

Вставка и ограничения целостности

Оператор INSERT проверяет все ограничения целостности, определённые для таблицы:

• Первичный ключ (PRIMARY KEY): Попытка вставить строку с дублирующимся значением первичного ключа вызовет ошибку.
• Внешний ключ (FOREIGN KEY): Значение внешнего ключа должно существовать в связанной таблице (или быть NULL).
• Уникальность (UNIQUE): Значение в столбце с ограничением UNIQUE не должно дублировать существующее.
• Проверка (CHECK): Значения должны удовлетворять условию, заданному в ограничении CHECK.
• NOT NULL: Столбцы с этим ограничением не могут быть опущены в списке вставки (если нет значения по умолчанию).

Производительность и транзакции

• Вставка нескольких строк одной командой INSERT ... VALUES с несколькими кортежами, как правило, значительно быстрее, чем выполнение нескольких отдельных команд INSERT, так как снижает количество сетевых запросов и операций записи в журнал транзакций.
• Оператор INSERT является частью транзакции. Если транзакция не завершена командой COMMIT, изменения могут быть отменены командой ROLLBACK. В СУБД с автоматическим подтверждением (autocommit) каждая команда INSERT выполняется в отдельной неявной транзакции.

Диалектные различия

Хотя синтаксис INSERT стандартизирован, различные СУБД имеют свои расширения и особенности.

MySQL

• Поддерживает синтаксис INSERT ... VALUES с множеством строк.
• Имеет расширение INSERT ... ON DUPLICATE KEY UPDATE. Если вставка приводит к нарушению ограничения уникальности (например, дублирование первичного ключа), то выполняется операция обновления существующей строки, а не вставка новой.
• Расширение INSERT IGNORE позволяет игнорировать ошибки вставки (например, дубликаты) и продолжать вставку остальных строк.

PostgreSQL

• Поддерживает стандартный синтаксис.
• Имеет расширение INSERT ... ON CONFLICT (column) DO UPDATE SET ... (UPSERT), аналогичное ON DUPLICATE KEY UPDATE в MySQL, но с более гибким управлением конфликтами.
• Поддерживает возврат вставленных данных через предложение RETURNING *, что позволяет получить сгенерированные значения (например, SERIAL).

Microsoft SQL Server

• Поддерживает стандартный синтаксис.
• Имеет расширение OUTPUT для возврата данных из вставленных строк, аналогично RETURNING в PostgreSQL.
• Поддерживает вставку через INSERT INTO ... EXEC для вставки результатов выполнения хранимой процедуры.

Oracle Database

• Поддерживает стандартный синтаксис.
• Имеет расширение INSERT ALL для вставки одной строки в несколько таблиц или нескольких строк на основе условий.
• Поддерживает INSERT FIRST для вставки строки в первую таблицу, удовлетворяющую условию.

Безопасность

Оператор INSERT является объектом атак с использованием SQL-инъекций. Если в запрос напрямую подставляются данные, полученные от пользователя (например, из веб-формы), злоумышленник может внедрить вредоносный код. Для предотвращения этого необходимо использовать параметризованные запросы (prepared statements) или экранирование специальных символов, предоставляемое API базы данных.

Примеры применения

• Добавление нового пользователя: Вставка записи в таблицу users при регистрации на сайте.
• Логирование событий: Вставка записей в таблицу logs при каждом действии пользователя.
• Загрузка данных из внешних источников: Использование INSERT ... SELECT для импорта данных из временных таблиц или файлов.
• Создание тестовых данных: Наполнение таблиц фиктивными данными для тестирования приложений.

Источники

• ISO/IEC 9075-2:2016 (SQL:2016), Part 2: Foundation (SQL/Foundation).
• Документация MySQL 8.0: INSERT Statement.
• Документация PostgreSQL 16: INSERT.
• Документация Microsoft SQL Server 2022: INSERT (Transact-SQL).
• Документация Oracle Database 23c: INSERT.