Вопросы по теме «DML в SQL: INSERT, UPDATE и DELETE»

Всего: 7 вопросов

1. 

Как добавить строки командой INSERT INTO: со списком столбцов, без него и несколько строк сразу?

INSERT — команда группы DML (вместе с UPDATE и DELETE), которая добавляет строки и не меняет структуру таблицы.

INSERT INTO users (username, email) VALUES ('Anna', '[email protected]');   -- со списком столбцов
INSERT INTO users VALUES (NULL, 'Oleg', '[email protected]', NULL);          -- без списка
INSERT INTO tags (name) VALUES ('java'), ('sql'), ('mysql');             -- три строки
  • Со списком столбцов значения подставляются в перечисленные столбцы по порядку, остальные получают значение по умолчанию. Число значений должно совпадать с числом столбцов.
  • Без списка нужно перечислить значения для всех столбцов строго в порядке объявления, включая id. Запись хрупкая: после ALTER TABLE ... ADD COLUMN старая команда падает с ERROR 1136: Column count doesn't match value count at row 1. В коде приложения столбцы перечисляют всегда.
  • Несколько строк перечисляют через запятую после одного VALUES: один запрос к серверу и одна запись в журнал, поэтому быстрее отдельных команд. Команда атомарна: если одна строка нарушает ограничение (например, ERROR 1062), в InnoDB не вставляется ни одна. Размер команды ограничен max_allowed_packet (64 МБ по умолчанию в MySQL 8), поэтому большие объёмы вставляют порциями.

Строки и даты пишут в одинарных кавычках, числа — без кавычек.

2. 

Какое значение получает столбец, не указанный в INSERT, и почему возникает ошибка 1364 «Field doesn't have a default value»?

  • Пропущенный столбец получает своё значение DEFAULT; столбец, допускающий NULL и без явного DEFAULT, получает NULL.
  • Столбцу с AUTO_INCREMENT база выдаёт следующий номер, если он пропущен или в него передан NULL (или 0).
  • Ключевое слово DEFAULT в списке значений явно просит значение по умолчанию:
INSERT INTO topics (title, author_id) VALUES ('Зимняя рыбалка', 1);
-- то же самое:
INSERT INTO topics (id, title, author_id, created_at, views)
    VALUES (NULL, 'Зимняя рыбалка', 1, DEFAULT, DEFAULT);

Столбец NOT NULL без значения по умолчанию пропустить нельзя:

INSERT INTO users (username) VALUES ('Petrov');
-- ERROR 1364 (HY000): Field 'email' doesn't have a default value

Ошибку выдаёт строгий режим STRICT_TRANS_TABLES, включённый в MySQL 8 по умолчанию; без него MySQL вставил бы пустую строку с предупреждением. Явный NULL в столбце NOT NULL даёт другую ошибку — 1048.

3. 

Как узнать id только что вставленной строки с помощью LAST_INSERT_ID() и в чём подвох этой функции?

LAST_INSERT_ID() возвращает значение AUTO_INCREMENT, выданное последней успешной вставкой в текущем соединении:

  • вставки в других соединениях на неё не влияют, поэтому функция безопасна при параллельной работе;
  • после многострочного INSERT она возвращает id первой из вставленных строк;
  • значение перезаписывает любая следующая вставка с AUTO_INCREMENT — в том числе вставка в другую таблицу.

Подвох: если после вставки темы добавить сообщение с LAST_INSERT_ID(), функция начнёт возвращать id сообщения, и второе сообщение с тем же выражением получит несуществующий topic_id — ERROR 1452 или запись не туда. Нужный id сохраняют сразу:

INSERT INTO topics (title, author_id) VALUES ('Зимняя рыбалка', 1);
SET @topic_id = LAST_INSERT_ID();
INSERT INTO messages (topic_id, author_id, body) VALUES (@topic_id, 1, 'Лёд уже держит?');
INSERT INTO messages (topic_id, author_id, body) VALUES (@topic_id, 3, 'Пока тонкий.');

В Java тот же номер возвращает getGeneratedKeys() у PreparedStatement, созданного с флагом Statement.RETURN_GENERATED_KEYS.

4. 

Как вставить в таблицу результат запроса с помощью INSERT ... SELECT?

Вместо VALUES после списка столбцов пишут SELECT: каждая строка его результата вставляется в таблицу. Пометим тегом «спорт» (id = 2) все темы пользователя 1:

INSERT INTO topic_tags (topic_id, tag_id)
SELECT id, 2 FROM topics WHERE author_id = 1;
-- Query OK, 2 rows affected
-- Records: 2  Duplicates: 0  Warnings: 0
  • Столбцы сопоставляются по позиции, а не по имени: количество и порядок столбцов в SELECT должны совпадать со списком в INSERT.
  • Константа в SELECT (здесь 2) подставляется в каждую строку.
  • Ограничения проверяются как при обычной вставке: повторный запуск той же команды упадёт с ERROR 1062: Duplicate entry '1-2' for key 'topic_tags.PRIMARY' (пропустить повторы можно через INSERT IGNORE).
  • Таблица в SELECT может совпадать с таблицей вставки: MySQL сначала сохраняет результат запроса во временную таблицу.
5. 

Чем отличаются INSERT IGNORE, INSERT ... ON DUPLICATE KEY UPDATE и REPLACE в MySQL?

Все три обрабатывают повтор первичного или уникального ключа без ошибки 1062, но по-разному:

  • INSERT IGNORE пропускает строку, вместо ошибки — предупреждение (0 rows affected на строку). Опасен тем, что гасит и другие ошибки: NULL в NOT NULL, нарушение внешнего ключа, слишком длинную строку — данные молча портятся или теряются.
  • INSERT ... ON DUPLICATE KEY UPDATE (upsert) обновляет существующую строку. Rows affected: 1 — вставка, 2 — обновление, 0 — значения не изменились. Номер AUTO_INCREMENT расходуется и при обновлении.
  • REPLACE удаляет старую строку и вставляет новую (2 rows affected и больше). Это настоящий DELETE: срабатывают ON DELETE CASCADE / SET NULL и триггеры удаления, у родительской строки со ссылками без такого действия — ERROR 1451; столбцы, не указанные в команде, получают DEFAULT.
INSERT INTO users (username, email)
VALUES ('Vasya', '[email protected]') AS new
ON DUPLICATE KEY UPDATE email = new.email;

Псевдоним строки AS new появился в MySQL 8.0.19; функция VALUES(email) из старых примеров с 8.0.20 устарела и выдаёт предупреждение 1287 (MariaDB понимает только VALUES()). Для обновления существующей строки выбирают ON DUPLICATE KEY UPDATE или UPDATE, а не REPLACE.

6. 

Как работают UPDATE и DELETE в MySQL: WHERE, Rows matched и Changed, ORDER BY и LIMIT?

  • UPDATE меняет столбцы из SET (несколько через запятую, значение может быть выражением, например views = views + 1) во всех строках, подходящих под WHERE; DELETE удаляет такие строки, таблица остаётся. Без WHERE затрагиваются все строки.
  • Rows matched — сколько строк нашло условие, Changed — сколько реально изменилось. Повторный UPDATE с теми же значениями даёт Changed: 0.
  • В MySQL однотабличные UPDATE и DELETE принимают ORDER BY и LIMIT; LIMIT без ORDER BY выбирает произвольные строки. Большие объёмы удаляют пачками: DELETE ... ORDER BY id LIMIT 1000 повторяют до 0 rows affected.
  • Если на изменяемый ключ или удаляемую строку ссылается внешний ключ, результат зависит от его действия (возможна ERROR 1451).

Защита от команды без WHERE — режим safe updates (SET SQL_SAFE_UPDATES = 1): UPDATE и DELETE без условия по ключу и без LIMIT отклоняются с ERROR 1175.

7. 

Какие ошибки чаще всего допускают в INSERT, UPDATE и DELETE и как их избежать?

  • UPDATE/DELETE без WHERE меняют или удаляют все строки. Сначала выполняйте SELECT с тем же условием, рискованное изменение делайте в транзакции.
  • INSERT INTO t VALUES (...) без списка столбцов ломается после добавления столбца (ERROR 1136) — перечисляйте столбцы.
  • WHERE phone = NULL не находит ни одной строки (Rows matched: 0): сравнение с NULL никогда не истинно. Нужно WHERE phone IS NULL.
  • Апостроф внутри строки 'O'Brien' даёт ERROR 1064 — удвойте его ('O''Brien'), а в Java используйте параметры PreparedStatement вместо склейки строк.
  • INSERT IGNORE «на всякий случай» молча пропускает строки с нарушением внешнего ключа и превращает NULL в пустую строку или ноль — используйте ON DUPLICATE KEY UPDATE или обрабатывайте ошибку 1062.
  • REPLACE вместо upsert удаляет строку: срабатывают каскады, возможна ошибка 1451, без указанного id строка получает новый номер.
  • Повторное использование LAST_INSERT_ID() после следующей вставки возвращает id другой строки — сохраняйте его в переменную сразу.
Страница 1 из 1