упрощение обработки обновления/вставки (upsert) в MySQL

Обычной операцией является вставка новой строки, если ее не существует, или обновление существующей строки. К сожалению, синтаксис операторов SQL вставки и обновления совершенно другой: вставка принимает список столбцов, за которым следует соответствующий список значений, а обновление принимает список пар столбец=значение. Оператор MySQL «вставить... при обновлении дублирующего ключа» (его оператор upsert) не решает эту проблему, поскольку он по-прежнему требует полных списков столбцов/значений вставки, за которыми следует полный список столбцов/значений обновления. [ОБНОВЛЕНИЕ: Комментарий Wrikken ниже указывает на то, что два оператора могут использовать синтаксис столбец = значение, но другие проблемы остаются.]

С этой проблемой связано то, что если вы используете триггеры для проверки данных (как это делаю я), вам нужны два триггера (перед вставкой и перед обновлением), и, поскольку они должны использовать квалификатор «новый», вы нужно либо написать код проверки дважды, по одному разу для каждого триггера, либо поместить его в процедуру. Если вы используете процедуру, вы должны передавать каждый столбец как отдельный параметр, потому что процедура не может использовать «новый», что является большим количеством потенциально подверженных ошибкам ввода, если у вас много столбцов. Каждый столбец должен иметь свой тип в операторе создания таблицы, а затем во второй раз в определении процедуры проверки. Одна маленькая ошибка, и вы создали незаметную, трудно обнаруживаемую ошибку. Мне не нравится любой подход, предполагающий двойное кодирование одного и того же. (Это эквивалент ненормализации.)

Размышляя об этой проблеме вставки/обновления, я обдумывал следующую идею и хотел бы получить отзывы, особенно если кто-то действительно пробовал это:

Используйте вставку только для строки-заполнителя, содержащей только минимальный объем данных, и для получения или установки первичного ключа. Затем поместите все введенные пользователем данные в оператор обновления. Теперь вам не нужно «вставлять... при обновлении дубликата ключа», как это делает обычное обновление. Также проверять данные нужно только на триггере перед обновлением, так как на вставке проверять нечего. (Все введенные пользователем данные из формы ввода обрабатываются обновлением, а не вставкой.)

Главным недостатком этого метода, конечно же, является то, что есть две операции для новой строки: вставка с последующим обновлением вместо вставки. Но это не может быть фактором, потому что:

  1. Вставки могут быть относительно редкими. Например, в приложении для оценивания учащихся, которое я создал несколько лет назад для школьного округа Ричардсон (Техас), каждый год добавлялось всего около пары тысяч учащихся, в то время как обновления вносились десятками тысяч, так как учителя использовали системы в течение всего учебного года.

  2. В нескольких других системах, которые я построил, производительность не имела значения. Например, в текущей системе, над которой я работаю, всего два или три человека обновляют базу данных всего несколько часов в неделю. Нагрузка настолько мала, что накладные расходы, вызванные двумя операциями (вставка + обновление), когда достаточно только одной, незначительны. (Помните, это только для новых строк.)

Итак, кто-нибудь на самом деле пробовал это: вставить только для создания минималистской строки-заполнителя и использовать обновление для всех обновлений данных, предоставленных пользователем?


mysql sql-update insert upsert
person Marc Rochkind    schedule 12.12.2012    source источник
comment
Вы ЗНАЕТЕ, что INSERT INTO tablename SET col1=1, col2=2... и т. д. допустимы? И мне еще предстоит найти написание запроса только один раз громоздким...   -  person Wrikken    schedule 12.12.2012
comment
dev.mysql.com/doc/refman/5.5/en/insert. html   -  person jchapa    schedule 12.12.2012
comment
Спасибо за указание на это. В PHP, если присваивания собраны в виде строки, тогда строка может использоваться в обоих местах в операторе вставки... в дублирующем операторе ключа, что немного помогает. Однако потребность в двух триггерах все же есть. Моя идея по-прежнему допускает только один триггер, что значительно упрощает кодирование триггера. Идеи по этому поводу?   -  person Marc Rochkind    schedule 12.12.2012
comment
разрешает только один триггер, должен был требовать только один триггер.   -  person Marc Rochkind    schedule 12.12.2012

Ответы (1)


arrow_upward
7
arrow_downward

Если мне нужно, чтобы база данных применяла «правила» для достоверности данных, мне все равно понадобится триггер INSERT, а также триггер UPDATE, потому что на уровне базы данных я не смогу гарантировать, что кто-то не собирается выполнить INSERT, который включает недопустимые данные. В любом случае я склоняюсь к обоим триггерам.

Еще один недостаток вставки строки-заполнителя и последующего обновления заключается в том, что (для записей переменной длины) существует аспект фрагментации. Последующее обновление почти гарантирует, что длина строки будет увеличиваться, что приведет к ненужному увеличению фрагментации в базе данных (чего не произошло бы, если бы вы просто вставили строку так, как она должна быть там). )

Мне также нужно рассмотреть случай, когда INSERT заполнителя завершается успешно, но UPDATE терпит неудачу. У меня должен быть какой-то дополнительный механизм для обработки этого условия.

Будет более эффективно запустить один оператор и просто вставить значения, которые, как я знаю, должны быть там, а не запускать два отдельных оператора (один для вставки строки-заполнителя, а затем второй оператор для ее обновления.)

Лично я бы просто использовал INSERT ... ON DUPLICATE KEY UPDATE, но вместо того, чтобы повторять значения в части UPDATE, я бы просто ссылался на значения, предоставленные для этих столбцов в инструкции INSERT, например.

INSERT INTO foo (a,b,c) VALUES (1,'one','won'), (2,'two','too')
   ON DUPLICATE KEY
   UPDATE a = VALUES(a)
        , b = VALUES(b)
        , c = VALUES(c)

ПРИМЕЧАНИЕ. Следует помнить об одном побочном эффекте этого утверждения, особенно если выполняется преимущественно UPDATE. Этот оператор будет увеличивать идентификатор AUTO_INCREMENT для каждой строки, которую пытаются вставить. Это значение идентификатора AUTO_INCREMENT будет, по сути, "потеряно", так как сгенерированное значение не будет вставлено в таблицу, но оно "исчезнет". (Следующее сгенерированное значение будет на единицу выше.)

person spencer7593    schedule 12.12.2012
comment
Отличный момент, который я упустил: размещение проверок (валидации) в базе данных должно гарантировать, что они всегда эффективны, а не зависит от программиста приложения, но требование, чтобы вставка была только заполнителем, оставляет это на усмотрение программист приложений. Я не увидел этого противоречия. - person Marc Rochkind; 12.12.2012