Каждый программист, которому приходилось работать с базами данных, сталкивался с DML-операторами (пер. с англ. – "язык манипулирования данными"), такими как Select, Insert, Delete и Update. MySQL-среда также использует в своем арсенале все вышеперечисленные команды.
Указанные операторы логически говорят о своем назначении – выборе записей, вставке новых значений, обновлении существующих данных, полному, или согласно заданным условиям, удалению информации в базе. В обучающих теоретических материалах подробно описаны принцип работы каждой команды и их синтаксис, однако нигде не встретить упоминаний о трудностях, которые могут возникнуть на практике во время использования. Данный материал будет посвящен рассмотрению некоторым из них.
Кратко об DML-операторах (Insert)
Прежде чем перейти дальше, необходимо ещё раз более подробно напомнить о предназначении каждой из функций. Нас будут более подробно интересовать два оператора: Insert и Update, так как именно от них вытекают главные трудности при обработке больших объемов данных.
Начать необходимо с команды Insert, а затем плавно перейти к Update. MySQL система, как и любая другая современная СУБД, использует операцию Insert для добавления новых записей в существующие таблицы баз данных. Синтаксис указанной операции очень прост и понятен. Она содержит в себе перечисление полей, куда будут вноситься значения, место назначения – имя таблицы - и непосредственно список вносимых данных. При каждом выполнении команды Insert база данных будет пополняться новыми значениями.
Оператор Update
Однако на практике довольно часто возникают ситуации, что для некоторого набора данных необходимо обновить одно или несколько значений атрибутов. В качестве примера можно привести ситуацию, когда на предприятии произошло реформирование с дальнейшим переименованием основных отделов. В таком случае необходимо внести изменения по каждому отделу. Если меняется только названия, то проблема решается очень быстро. Но если меняется кодировка каждой составляющей всего производства, которая, как правило, служит в качестве первичного ключа, то это, в свою очередь, влечет изменения в информации и по каждому сотруднику.
Для решения рассматриваемой задачи может быть применен 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();».
Такой запрос может быть использован для регистрации действий сотрудников, например, определения времени пересечения проходной предприятия с последующим подсчетом времени перерыва и выявления опозданий. Чтобы не вносить в таблицу несколько записей, достаточно по каждому сотруднику вести учет с постоянным обновлением. Именно конструкция проверки дубликата позволяет это сделать.
Собственно о проблемах…
Рассматривая приведенный выше пример о регистрации действий сотрудников на проходной, в качестве проблемы можно привести использование самоувеличивающихся (auto_increment) полей, которые обычно используются для заполнения значений первичных ключей (primary_key). При использовании команды MySQL Update в конструкции с Insert auto_increment, поля постоянно увеличиваются.
Аналогичным образом все происходит, когда используется конструкция замены, в случае обнаружения дубликатов. «Автоинкрементное» значение увеличивается даже в том случае, когда в этом нет необходимости. Из-за этого возникают проблемы пропуска значений либо переполнения диапазона, которые в последующем приводят к нарушениям работоспособности систем управления базами данных.
Наибольшая вероятность возникновения проблемы
Описываемую проблему необходимо учитывать Web-разработчикам, так как она наиболее распространена в многопользовательских системах (интернет-сайты, порталы и т. п.), когда в системе происходит выполнение большого количества процедур Insert и Update MySQL.
PHP-обращения к базе выполняются очень часто. Поэтому достижение максимального значения полями, определяемыми как auto_increment, происходит быстро, и при разборе возникших сложностей установить причины сразу не получится.
Поэтому разработчикам рекомендуется внимательно подходить к использованию конструкции on duplicate key в команде mysql update. select - запросы при обращении к серверу базы данных отработают без ошибок, но вот добавление новых записей в базу чревато неприятными ситуациями, в последующем приводящими к серьезным проблемам. В качестве альтернативы рекомендуется для автоинкрементных полей изначально проверять наличие для них записей, а после проводить их обновление.
