В MySQL есть достаточное количество встроенных функций и фич, которые упрощают код. К сожалению, не все программисты знают об этом и используют свои «велосипеды».
ON DUPLICATE KEY UPDATE
Например, есть таблица с какими-то объектами (пользователи, посты и т.д.). Если данный объект с таким-то уникальным свойством уже существует, то апдейтим какое-то свойство у него. Если объекта не существует, то вставляем новую строку. Часто можно встретить такой код:
// находим объект
$row = query('SELECT * FROM table WHERE id=1');
// проверяем есть ли такой объект
if ($row) {
// делаем апдейт
query('UPDATE table SET column=column+1 WHERE id=1');
}
else {
// делаем вставку
query('INSERT INTO table SET column=1, id=1');
}
Подобную конструкцию можно заменить одним запросом без участия php, при условии наличия первичного или уникального ключа по полю id:
INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1
INSERT IGNORE
Зачастую при добавление в таблицу, имеющей UNIQUE индекс или PRIMARY KEY, новой строки, очень полезным бывает синтаксис INSERT IGNORE. Использование данного синтаксиса удобно в случае случайного дублирования ключа при вставке, то есть сама вставка не будет произведена, при этом не будет прекращено выполнение.
Обычный алгоритм:
1) проверить наличие строки в таблице по ключу (SELECT)
2) вставить строку в случае отсутствия дублирования ключа (INSERT)
// находим объект
$row = query('SELECT * FROM table WHERE id=1');
// если такого объекта нет, то вставляем новую запись
if (!$row) {
query('INSERT INTO table …');
}
Теперь напишем только один запрос INSERT IGNORE без участия php
query('INSERT IGNORE INTO table …') // вставка
Не нужно придумывать свои алгоритмы и писать лишний код – пусть за вас думает MySQL.
Спасибо за статью, помогла при освоение предумдростей MySQL:)
Спасибо.
Я только начинаю - читал книжку - но не совсем понимал как работать с UNIQUE
А тут на тебе - в двух строках все объяснили.
Ещё раз спасибо.
Спасибо. Избавил меня как минимум от одного велосипеда, да.
Про INSERT IGNORE незнал, спасибо.
Вау! Спасибо за пост! Где-то в мануалах встречал про IGNORE, но не обращал должного внимания.
>К сожалению, не все программисты знают об этом и используют свои «велосипеды».
Не многие изучают доки досконально. А в книгах о таком пишут редко, т.к. большинство книг рассчитано на новичков для поднятия базовых знаний Mysql.
лучше использовать PRIMARY KEY, у меня с UNIQUE избавиться от дублирования не получилось
Very true! Makes a chnage to see someone spell it out like that. :)
Спасибо! Прямо 'велосипед' с плеч долой!
Молодец! И мне помог! Только не insert ignore, а другой функцией. Ты - хороший человек.
а что если мне надо не на 1 увеличить а обновить значение...
уже разобрался:
INSERT INTO temp (id, name, price, artnumber)
VALUES
(1, 'наименование', 10.00, '0001'),
(2, 'наименование 2', 20.00, '0002')
ON DUPLICATE KEY UPDATE
price = VALUES(price),
name = VALUES(price),
artnumber = values(artnumber);
INSERT IGNORE INTO registration (mail,pol,adress,fio) VALUES ('mail@mail.sru','s','add','fio');
Как сделать так чтобы при вставке игнорировалась вставка записи, если такой mail уже существут? Запись что выше, вставляет дубликаты не глядя.
для начала нужно сделать уникальный (UNIQUE) индекс для поля email. проще всего сделать это через phpMyAdmin (во вкладке Структура таблицы внизу выбрать "Создать индекс для")
Сюда ещё REPLACE напрашивается...
хм, сколько работал - впервые узнал, принимайте Благодарности!
низкий поклон
мля.... вот а потом спрашивают, что за быдлокодерство... Спасибо вашей статье =))) для уродов ;)
INSERT IGNORE, в случаях когда есть (допустим) мыло то оно его не записывает но и теряет id то есть будто присвоило id но в таблицу не добавило
да, есть такое. видимо такая фича.
INSERT IGNORE INTO выполняет свою роль.
А вот как узнать сколько строк было добавлено и сколько проигнорировано??
имелось ввиду когда проход в цикле.
Брать число записей в начале и в конце, и вычислить разницу не покатит, т.к. могут быть в это время другие записи и удаления строк в таблице.
Нужен именно признак что запись добавлена или нет именно данным запросом INSERT
Спасибо большое!!!!!!!!!!
atmark, affected_rows используй.
QWE
affected_rows - он же вернёт просто число затронутых строк например при UPDATE.
А как при использовании IGNORE узнать что например строка с id = 5 не была обновлена? Как узнать например Id именно того элемента который не обновлен а не просто узнать что какой-то один не обновился а какой хз?
Обычно не оставляю комменты на сайтах, но тут реально помог! Спасибо!
Отметьте в статье, что использование ON DUPLICATE KEY имеет подводный камень, который чреват проблемами на серьезных сайтах: https://intsystem.org/sql/neyavnaya-problema-on-duplicate-key-i-auto-incriment/
Не верьте комментатору trololo №26
Нет там никаких подводных камней.
UPDATE: не верьте моему предыдущему комментарию. :D
Первые комментарии писал сам автор? Не читайте инструкций по MySQL на русском языке :).
Поосторожнее с этими фичами, у например ON DUPLICATE KEY кривая реализация из за чего на высоконагруженных базах может заканчиваться PRIMARY_KEY AUTOINCREMENT. IGNORE тоже нужно пользоваться с умом, если не будет никакой ошибки и нужно знать вставилась запись или нет, придется проверять другим способом (например с помощью mysqli_affected_rows).
И вообще злоупотребление фичами не всегда дает прирост производительности и усложняет переход на другую БД. Так что я лучше по старинке.
А если значение полей таблицы не уникальны, но должна быть уникальна комбинация сочетаний значений.
Можно ли в таком случае проделать такой же фокус как с ON DUPLICATE KEY UPDATE