Вопросы по теме «DML в SQL: INSERT, UPDATE и DELETE»
Сначала изучите урок: DML в SQL: INSERT, UPDATE и DELETE
Всего: 7 вопросов
1. Как добавить строки командой INSERT INTO: со списком столбцов, без него и несколько строк сразу?
Как добавить строки командой 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»?
Какое значение получает столбец, не указанный в 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() и в чём подвох этой функции?
Как узнать 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?
Как вставить в таблицу результат запроса с помощью 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?
Чем отличаются 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 и 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 и как их избежать?
Какие ошибки чаще всего допускают в 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 другой строки — сохраняйте его в переменную сразу.