Встроенный функционал MySQL

В 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.

#1

Спасибо за статью, помогла при освоение предумдростей MySQL:)

Combox, 13.01.2010 - 00:05
#2

Спасибо.
Я только начинаю - читал книжку - но не совсем понимал как работать с UNIQUE
А тут на тебе - в двух строках все объяснили.
Ещё раз спасибо.

Иван, 1.03.2010 - 16:15
#3

Спасибо. Избавил меня как минимум от одного велосипеда, да.

Д., 27.05.2010 - 12:37
#4

Про INSERT IGNORE незнал, спасибо.

Евгений Че, 13.06.2010 - 13:52
#5

Вау! Спасибо за пост! Где-то в мануалах встречал про IGNORE, но не обращал должного внимания.

>К сожалению, не все программисты знают об этом и используют свои «велосипеды».

Не многие изучают доки досконально. А в книгах о таком пишут редко, т.к. большинство книг рассчитано на новичков для поднятия базовых знаний Mysql.

Kreker, 7.07.2010 - 09:48
#6

лучше использовать PRIMARY KEY, у меня с UNIQUE избавиться от дублирования не получилось

Альберт Иванов, 20.08.2010 - 14:19
#7

Very true! Makes a chnage to see someone spell it out like that. :)

Cady, 19.07.2011 - 13:15
#8

Спасибо! Прямо 'велосипед' с плеч долой!

Dinchel, 30.11.2011 - 18:35
#9

Молодец! И мне помог! Только не insert ignore, а другой функцией. Ты - хороший человек.

Alish, 26.12.2011 - 09:41
#10

а что если мне надо не на 1 увеличить а обновить значение...

dvcarrot, 14.06.2012 - 14:31
#11

уже разобрался:

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);

dvcarrot, 14.06.2012 - 14:38
#12

INSERT IGNORE INTO registration (mail,pol,adress,fio) VALUES ('mail@mail.sru','s','add','fio');

Как сделать так чтобы при вставке игнорировалась вставка записи, если такой mail уже существут? Запись что выше, вставляет дубликаты не глядя.

Алексей, 20.09.2012 - 21:51
#13

для начала нужно сделать уникальный (UNIQUE) индекс для поля email. проще всего сделать это через phpMyAdmin (во вкладке Структура таблицы внизу выбрать "Создать индекс для")

admin, 20.09.2012 - 22:14
#14

Сюда ещё REPLACE напрашивается...

я, 27.09.2012 - 22:04
#15

хм, сколько работал - впервые узнал, принимайте Благодарности!

Николай, 27.12.2012 - 10:03
#16

низкий поклон

short, 3.08.2013 - 10:13
#17

мля.... вот а потом спрашивают, что за быдлокодерство... Спасибо вашей статье =))) для уродов ;)

Lacmus, 8.08.2013 - 16:26
#18

INSERT IGNORE, в случаях когда есть (допустим) мыло то оно его не записывает но и теряет id то есть будто присвоило id но в таблицу не добавило

nrkz, 15.08.2013 - 16:09
#19

да, есть такое. видимо такая фича.

admin, 15.08.2013 - 18:44
#20

INSERT IGNORE INTO выполняет свою роль.
А вот как узнать сколько строк было добавлено и сколько проигнорировано??

atmark, 23.10.2013 - 00:51
#21

имелось ввиду когда проход в цикле.
Брать число записей в начале и в конце, и вычислить разницу не покатит, т.к. могут быть в это время другие записи и удаления строк в таблице.
Нужен именно признак что запись добавлена или нет именно данным запросом INSERT

atmark, 23.10.2013 - 03:03
#22

Спасибо большое!!!!!!!!!!

Lisa, 2.12.2013 - 09:37
#23

atmark, affected_rows используй.

qwe, 31.01.2014 - 21:40
#24

QWE
affected_rows - он же вернёт просто число затронутых строк например при UPDATE.

А как при использовании IGNORE узнать что например строка с id = 5 не была обновлена? Как узнать например Id именно того элемента который не обновлен а не просто узнать что какой-то один не обновился а какой хз?

Sulim, 31.07.2015 - 21:24
#25

Обычно не оставляю комменты на сайтах, но тут реально помог! Спасибо!

Влад, 27.08.2015 - 13:05
#26

Отметьте в статье, что использование ON DUPLICATE KEY имеет подводный камень, который чреват проблемами на серьезных сайтах: https://intsystem.org/sql/neyavnaya-problema-on-duplicate-key-i-auto-incriment/

trololo, 18.10.2015 - 18:31
#27

Не верьте комментатору trololo №26
Нет там никаких подводных камней.

kivagant, 24.12.2015 - 20:34
#28

UPDATE: не верьте моему предыдущему комментарию. :D

kivagant, 25.12.2015 - 11:14
#29

Первые комментарии писал сам автор? Не читайте инструкций по MySQL на русском языке :).

Льюис, 20.10.2016 - 12:23
Оставить комментарий