Подводные камни DML-команды Update MySQL

Каждый программист, которому приходилось работать с базами данных, сталкивался с DML-операторами (пер. с англ. – "язык манипулирования данными"), такими как Select, Insert, Delete и Update. MySQL-среда также использует в своем арсенале все вышеперечисленные команды.

Указанные операторы логически говорят о своем назначении – выборе записей, вставке новых значений, обновлении существующих данных, полному, или согласно заданным условиям, удалению информации в базе. В обучающих теоретических материалах подробно описаны принцип работы каждой команды и их синтаксис, однако нигде не встретить упоминаний о трудностях, которые могут возникнуть на практике во время использования. Данный материал будет посвящен рассмотрению некоторым из них.


mysql insert update select

Кратко об DML-операторах (Insert)

Прежде чем перейти дальше, необходимо ещё раз более подробно напомнить о предназначении каждой из функций. Нас будут более подробно интересовать два оператора: Insert и Update, так как именно от них вытекают главные трудности при обработке больших объемов данных.

mysql insert update

Начать необходимо с команды Insert, а затем плавно перейти к Update. MySQL система, как и любая другая современная СУБД, использует операцию Insert для добавления новых записей в существующие таблицы баз данных. Синтаксис указанной операции очень прост и понятен. Она содержит в себе перечисление полей, куда будут вноситься значения, место назначения – имя таблицы - и непосредственно список вносимых данных. При каждом выполнении команды Insert база данных будет пополняться новыми значениями.


Оператор Update

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

update mysql

Для решения рассматриваемой задачи может быть применен DML-операторUpdate. MySQL-сервер, оперируя с большим количеством записей, при помощи оператора обновления, выполнит требуемый запрос и решит проблему. Но иногда во время обновления возникают не совсем понятные и труднообъяснимые сложности. Именно о том, какие сложности вызывает обновление записей, речь пойдет далее.

О чем мало сказано в теории…

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

Чтобы этого не происходило, в СУБД предусмотрена специальная конструкция MySQL - Insert * Update, в которой вставка либо обновление могут выполняться независимо друг от друга. То есть когда в таблице имеется запись для определенного условия, будет происходить обновление. Если данных по рассматриваемому условию обнаружено не будет, MySQL-сервер сможет выполнить запрос добавления данных.


Обновление данных при наличии дубликатов

Важная составляющая подобного Insert-запроса в системе управления базами данных MySQL – «On Duplicate Key Update» приставка. Полный синтаксис запроса выглядит следующим образом: «insert into test_table (employer_id,name) values (1,'Abramov') on duplicate key update last_modified=NOW();».

MySQL on duplicate key update

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

Собственно о проблемах…

Рассматривая приведенный выше пример о регистрации действий сотрудников на проходной, в качестве проблемы можно привести использование самоувеличивающихся (auto_increment) полей, которые обычно используются для заполнения значений первичных ключей (primary_key). При использовании команды MySQL Update в конструкции с Insert auto_increment, поля постоянно увеличиваются.

Аналогичным образом все происходит, когда используется конструкция замены, в случае обнаружения дубликатов. «Автоинкрементное» значение увеличивается даже в том случае, когда в этом нет необходимости. Из-за этого возникают проблемы пропуска значений либо переполнения диапазона, которые в последующем приводят к нарушениям работоспособности систем управления базами данных.

Наибольшая вероятность возникновения проблемы

Описываемую проблему необходимо учитывать Web-разработчикам, так как она наиболее распространена в многопользовательских системах (интернет-сайты, порталы и т. п.), когда в системе происходит выполнение большого количества процедур Insert и Update MySQL.

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

 update mysql php

Поэтому разработчикам рекомендуется внимательно подходить к использованию конструкции on duplicate key в команде mysql update. select - запросы при обращении к серверу базы данных отработают без ошибок, но вот добавление новых записей в базу чревато неприятными ситуациями, в последующем приводящими к серьезным проблемам. В качестве альтернативы рекомендуется для автоинкрементных полей изначально проверять наличие для них записей, а после проводить их обновление.

MySQL JOIN: краткое описание, пример использования команды и ...
Разработка баз данных интернет-ресурсов практически ни в чем не отличается от стандартных баз данных, разработанных в системе MS SQL SERVER. Как правило, для такого рода ресурсов используется язык MY SQL, хотя его можно применять и к разработке ...
далее
Узнаем как использовать WHERE в MYSQL?
Если вы не знаете, как использовать where в MYSQL, то данная статье научит вас использовать все по правилам структурированных запросов. Если вы готовы получать знания, то эта статья, которая обучит вас правильному использованию данного оператора, к ...
далее
SQL CREATE DATABASE Statement
Чтобы создать базу данных в СУБД SQL применяется оператор CREATE. Детали уточняются в аргументах, некоторые из них доступны для использования только в последних версиях языка. Имена баз данных должны быть уникальными, они могут содержать только ...
далее
Установка PHP на Windows
Оптимизация установки и настройки PHP - перспективное занятие. Это делается быстро, особенных затрат не несет, но ориентируясь на использование PHP как основы системы объектов, созданной для решения задачи, трудно достичь желаемого результата.
далее
SQL для чайников: что нужно знать начинающим?
На сегодняшний день курсы SQL "для чайников" становятся все более популярными. Это можно очень просто объяснить, ведь в современном мире все чаще можно встретить так называемые "динамичные" веб-сервисы.
далее
Функция суммирования в SQL: SUM
Использование базы данных способствует максимальному упрощению работы с большими массивами информации. Помимо удобного хранилища, SQL-язык позволяет производить некоторые операции над данными. Одно из возможных их преобразований - суммирование. За него отвечает в SQL функция SUM.
далее
Функция суммирования в SQL: SUM
MySQL - что это - и где применяется?
MySQL - эффективная база данных, предоставляющая разработчику мощь реляционной модели информации в простой, компактной, надежной и безопасной форме. Основное использование - локальные и распределенные разработки сетевых и локальных ресурсов. Основная особенность - полная функциональность в своей сфере компетенции.
далее
MySQL - что это - и где применяется?
Основные операторы SQL
Обзор технологии СУБД SQL и описание основных операторов для создания, управления, обработки данных в базе.
далее
Основные операторы SQL
Создание таблицы в базе данных MySQL Create Table
Базы данных, как варианты концентрации информации, жизненно важны, и без них не обходится даже незначительный проект. Насколько эффективно продумана структура, правильно сконструированы таблицы, настроены связи - от этого зависит скорость выборки/записи информации, надежность хранения данных, гарантия невозможности случайно нанести ущерб, доступность отката, завершенность каждой транзакции - и успех в целом.
далее
Создание таблицы в базе данных MySQL Create Table
Функция создания таблицы в SQL - Create Table
Работа с базами данных непосредственно связана с изменением таблиц и содержащихся в них данных. Но перед началом проведения действий таблицы необходимо создать. Для автоматизации этого процесса существует специальная функция SQL - "CREATE TABLE".
далее
Функция создания таблицы в SQL - Create Table