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

Кожен програміст, якому доводилося працювати з базами даних, стикався з 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 - запити при зверненні до сервера бази даних відпрацюють без помилок, але ось додавання нових записів в базу загрожує неприємними ситуаціями, в подальшому призводять до серйозних проблем. В якості альтернативи рекомендується для автоинкрементних полів спочатку перевіряти наявність для них записів, а після проводити їх оновлення.