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