Введение в триггеры MySQL

Скорее всего, вы знаете, что такое триггер базы данных, по крайней мере, в концептуальном плане. Скорее всего, вы знаете, что MySQL поддерживает триггеры и поддерживает их в течение достаточно долгого времени. Я предполагаю, даже вооружившись этим знанием, что многие из вас не используют триггеры с MySQL. Это одна из тех вещей, которые обязательно должны быть в вашем наборе инструментов разработки, поскольку они действительно могут изменить ваш взгляд на ваши данные.

Введение: что такое триггер

«Однако, по мере того, как приложения становятся все более и более сложными, чем дальше мы можем абстрагировать слои приложения для обработки того, что им нужно, тем выше становится удобство использования нашей внутренней разработки».

Для непосвященных триггер — это правило, которое вы помещаете в таблицу, которое в основном говорит, что когда бы вы ни УДАЛЯЛИ, ОБНОВЛЯЛИ или ВСТАВЛЯЛИ что-то в этой таблице, также делали что-то еще. Например, мы можем захотеть зарегистрировать изменение, но вместо того, чтобы писать два отдельных запроса, один для изменения и один для журнала, мы можем вместо этого написать триггер, который говорит: «Всякий раз, когда эта строка обновляется, создайте новую строку в другой таблице, чтобы сказать мне, что обновление было сделано «. Это добавляет немного накладных расходов к начальному запросу, но поскольку в вашу базу данных не поступает два пакета для выполнения двух отдельных задач, общий прирост производительности (в любом случае, теоретически).

Триггеры были введены в MySQL в версии 5.0.2. Синтаксис триггера на первый взгляд немного чужд. MySQL использует стандарт ANSI SQL: 2003 для процедур и других функций. Если вы знакомы с языком программирования в целом, это не так сложно понять. Спецификация не находится в свободном доступе, поэтому я постараюсь использовать простые структуры и объяснить, что происходит внутри триггера. Вы будете иметь дело с теми же логическими структурами, которые предоставляет любой язык программирования.

Как я упоминал выше, триггеры будут выполняться процедурно при событиях UPDATE, DELETE и INSERT. Что я не упомянул, так это то, что они могут быть выполнены до или после определенного события. Поэтому у вас может быть триггер, который сработает до УДАЛЕНИЯ или после УДАЛЕНИЯ, и так далее, и так далее. Это означает, что у вас может быть один триггер, который срабатывает до ВСТАВКИ, и другой, который срабатывает ПОСЛЕ ВСТАВКИ, который может быть очень мощным.

Я собираюсь рассмотреть три варианта использования, которые вы могли бы рассмотреть, добавив их в набор инструментов. Есть несколько применений, в которые я не буду углубляться, так как считаю, что есть лучшие методы для получения одинаковых результатов, или они заслуживают своего собственного руководства. Каждое из этих применений, которые я изучаю, имеет аналог на уровне логики на стороне сервера и не является новой концепцией. Однако, по мере того как приложения становятся все более и более сложными, чем дальше мы можем абстрагировать слои приложения для обработки того, что им нужно, тем выше становится удобство использования нашей внутренней разработки.

Начало : структура моего стола, инструменты и примечания

Я работаю с мифической системой тележек, с предметами, которые имеют цены. Я постарался сделать структуру данных максимально простой, просто для иллюстрации. Я называю столбцы и таблицы для понимания, а не для производственного использования. Я также использую TIMESTAMPS, а не другие альтернативы для простоты. Для тех, кто играет в домашнюю версию сегодняшней игры, я использую таблицу имен тележек, cart_items, cart_log, items, items_cost.

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

Я использую PHP Quick Profiler для Particle Tree, чтобы увидеть время выполнения. Я также использую слой абстракции базы данных, предоставленный в инструменте, только для собственной выгоды. Это хороший инструмент, и он делает гораздо больше, чем просто обеспечивает время выполнения SQL.

Я также использую Chive для иллюстрации эффектов DB и создания моих триггеров. Chive только для MySQL 5+ и очень похож на PHPMyAdmin. Это красивее, но в то же время гораздо более шумно. Я использую Chive, просто потому, что он дает хорошие снимки экрана о том, что происходит с запросами.

Еще одно быстрое примечание. Возможно, вам придется изменить разделитель для MySQL при создании триггера. Естественный разделитель для MySQL: но так как мы будем использовать этот разделитель для наших добавленных запросов, вам может понадобиться явно переименовать разделитель при создании их с помощью командной строки. Я решил не показывать это, потому что, используя Chive, нет необходимости менять разделитель.

Чтобы изменить разделитель, вы должны просто сделать это перед командой триггера:

И это после вашей триггерной команды:

Easy Trigger : целостность данных

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

То, что вы, вероятно, сделали в прошлом, примерно так (упрощенно для иллюстрации):

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

Введите наш триггер. Я собираюсь создать очень простой триггер, чтобы всякий раз, когда я удалял корзину, мой триггер срабатывал для удаления любых элементов корзины с одинаковым cart_id:

Очень простой синтаксис, как я уже говорил выше. Давайте пройдемся по каждой строке.

Моя первая строка гласит: «CREATE TRIGGER` tutorial«before_delete_carts` «. Я говорю MySQL для создания триггера в базе данных «учебник» с именем «before_delete_carts». Я склонен называть свои триггеры формулой «When_How_Table». Это работает для меня, но есть много других способов сделать это.

Моя вторая строка сообщает MySQL об определении этого триггера: «ПЕРЕД УДАЛЕНИЕМ НА« ИГРЕК_КАРТА »ДЛЯ КАЖДОЙ СТРОКИ». Я говорю MySQL, что перед тем, как удалить эту таблицу, для каждой строки сделайте что-нибудь. Это что-то объясняется дальше, в пределах нашего НАЧАЛА и КОНЦА. Msgstr «УДАЛИТЬ ИЗ СООТВЕТСТВУЮЩИХ СООТВЕТСТВУ Я говорю MySQL перед тем, как вы удалите из trigger_carts, возьмите OLD.cart_id, а также удалите из trigger_cart_items. Старый синтаксис является определенной переменной. Мы обсудим это в следующем разделе, где мы объединим СТАРЫЙ и НОВЫЙ.

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

Два Запроса:

Один запрос с триггером:

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

Одно замечание о производительности: при первом запуске триггера он может быть намного медленнее, чем в последующие времена. Я не использую триггеры не обязательно для повышения производительности, а скорее для перемещения моей логики данных на уровень данных, точно так же, как вы хотите переместить вашу презентацию из разметки на уровень презентации, иначе известный как CSS.

Довольно легкий триггер : регистрация и аудит

Следующий пример, который мы рассмотрим, будет касаться ведения журнала. Скажем, я хочу отслеживать каждый товар, помещенный в корзину. Возможно, я хочу отслеживать скорость покупки товаров в корзине. Возможно, я просто хочу, чтобы копия каждого товара была помещена в корзину, не обязательно продана, просто для того, чтобы понять мнение моих клиентов. Возможно, вы создали элементы корзины в виде таблицы MEMORY и хотите записать все элементы в таблицу InnoDB. Независимо от причины, давайте посмотрим на триггер INSERT, который откроет некоторые хорошие возможности для регистрации или аудита наших данных.

Перед триггерами мы, вероятно, сделали что-то вроде этого (опять же, упрощенно для иллюстрации):

Теперь мы можем создать очень простой триггер для этого процесса регистрации:

Давайте снова пройдемся по этому вопросу, чтобы было яснее понять, что делает этот триггер. Сначала мы начнем со строки «CREATE TRIGGER` after_insert_cart_items` ». Я снова говорю MySQL создать триггер с именем «after_insert_cart_items». Имя может быть «Foo» или «BullWinkle», или как вы хотите это называть, но опять же, я предпочитаю проиллюстрировать мои имена триггеров. Далее мы видим, «ПОСЛЕ ВСТАВКИ НА` trigger_cart_items` ДЛЯ КАЖДОГО РЯДА «. Опять же, это говорит о том, что после того, как мы вставим что-то в trigger_cart_items, для каждой вставленной строки выполните то, что находится между моими BEGIN и END.

Наконец, у нас просто есть «INSERT INTO trigger_cart_log (cart_id, item_id) VALUES (NEW.cart_id, NEW.item_id);» который является стандартным запросом за исключением двух моих значений. Я использую значение NEW, которое вставляется в таблицу cart_items.

И мы сократили наши запросы пополам с небольшим увеличением производительности:

И просто чтобы убедиться, что наш триггер работает, я вижу значения в моей таблице:

Это опять-таки, относительно легко, но мы работаем с парой значений, которые могут немного усложнить ситуацию. Давайте посмотрим на что-то немного сложнее.

Более сложный триггер : бизнес-логика

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

В бизнес-логике постоянно появляются ошибки. Независимо от того, насколько мы осторожны или организованы, что-то всегда проскальзывает сквозь трещины. Триггеры на ОБНОВЛЕНИЕ смягчают это только немного. У нас есть некоторая сила в триггере, чтобы оценить, какое значение было OLD, и установить значение NEW на основе оценки. Скажем, например, что мы хотим, чтобы цена наших товаров всегда была наценкой 30% от стоимости товаров. Поэтому вполне естественно, что когда мы ОБНОВЛЯЕМ наши затраты, нам также необходимо ОБНОВЛЯТЬ нашу цену. Давайте справимся с этим с помощью триггера.

Что мы делаем, так это обновляем таблицу товаров с ценой, основанной на NEW.cost times 1.3. Я ввел цену в 50 долларов, поэтому моя новая цена должна составлять 65 долларов.

Конечно же, этот триггер сработал.

Нам нужно взглянуть на более продвинутый пример. У нас уже есть правило об изменении цены товара в зависимости от его стоимости. Допустим, мы хотим немного уточнить нашу стоимость. Если стоимость составляет менее 50 долларов, наша стоимость на самом деле 50 долларов. Если стоимость превышает 50 долларов, но меньше 100 долларов, то наша стоимость составляет 100 долларов. Хотя мой пример, вероятно, не соответствует истинному бизнес-правилу, мы каждый день корректируем стоимость в зависимости от факторов. Я просто пытаюсь сделать пример легким для понимания.

Чтобы сделать это, мы снова собираемся работать с ОБНОВЛЕНИЕМ, но на этот раз мы запустим его, прежде чем выполнить наш запрос. Мы также собираемся работать с заявлением IF, которое доступно нам.

Вот новый триггер:

То, что мы делаем сейчас, это не вызов запроса, а просто переопределение значения. Я говорю, что если стоимость меньше 50 долларов, просто сделайте 50 долларов. Если стоимость составляет от 50 до 100 долларов, сделайте 100 долларов. Если это выше этого, тогда я просто оставлю это прежним. Мой синтаксис здесь не такой уж чуждый от любого другого серверного языка. Нам нужно закрыть наше предложение IF с помощью END IF; но кроме этого, это действительно не сложно.

Просто чтобы проверить, работает ли наш триггер, я ввел значение 30 долларов США, а оно должно быть 50 долларов:

Когда я ввожу стоимость в 85 долларов, вот значение:

И просто чтобы проверить, работает ли мой триггер AFTER UPDATE, моя цена должна теперь составлять $ 130:

Жизнь хороша.

Вывод

Я коснулся только вершины айсберга триггерами и MySQL. В то время как существует множество способов использования триггеров, в прошлом я прекрасно обходился без них, работая с моими данными на моем логическом уровне. Тем не менее, возможность добавлять правила к моим данным на уровне данных просто имеет смысл. Когда вы добавляете в скромные улучшения производительности, преимущество еще больше.

Сейчас нам приходится иметь дело со сложными веб-приложениями с высоким трафиком. Хотя использование триггера на одностраничном сайте тщеславия может быть не лучшим использованием времени и энергии; триггер в сложном веб-приложении может изменить мир. Я надеюсь, вам понравились примеры, и, пожалуйста, дайте мне знать, что требует дальнейшего объяснения.