C, PHP, VB, .NET

В предишната статия се повдигна един уместен въпрос, който можем да сведем до „поддържане на броячи в MySQL“, т.е. стойности които се инициализират на 0 ако въвежданите данни не съществуват или се увеличават с единица ако ги има.  Разбира се ще използваме примера от предишната статия. Там имахме следната примерна таблица:

mysql> DESC logs;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| uid      | int(10) unsigned | NO   | PRI | 0       |       |
| ip       | int(10) unsigned | NO   | PRI | NULL    |       |
| attempts | int(11)          | NO   |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT uid, INET_NTOA(ip), attempts FROM logs;
+-----+---------------+----------+
| uid | INET_NTOA(ip) | attempts |
+-----+---------------+----------+
|   1 | 1.1.1.1       |       66 |
|   1 | 2.2.2.2       |       10 |
|   2 | 1.1.1.1       |       20 |
|   2 | 3.3.3.3       |     1200 |
|   3 | 2.2.2.2       |       30 |
+-----+---------------+----------+
5 rows in set (0.03 sec)

В нея записвахме неуспешните опити на IP адреси да влизат в системата. Сега искаме да добавяме нови редове в нея. Имаме три възможни случая:

1. Добавяме запис за IP адрес, който не присъства в logs;
2. Добавяме запис за IP адрес, който присъства в logs, но влиза в акаунт, към който не се е пробвал досега;
3. Добавяме запис за IP адрес, който присъства в logs и влиза за пореден път в акаунт, към който вече се е пробвал.

Първите два случая са безпроблемни, защото могат да се изпълнят със съвсем стандартна INSERT заявка:

mysql> INSERT INTO logs(uid, ip, attempts)
    -> VALUES (3, INET_ATON('4.4.4.4'), 1),
    ->        (3, INET_ATON('3.3.3.3'), 1);

mysql> SELECT uid, INET_NTOA(ip), attempts FROM logs;
+-----+---------------+----------+
| uid | INET_NTOA(ip) | attempts |
+-----+---------------+----------+
|   1 | 1.1.1.1       |       66 |
|   1 | 2.2.2.2       |       10 |
|   2 | 1.1.1.1       |       20 |
|   2 | 3.3.3.3       |     1200 |
|   3 | 2.2.2.2       |       30 |
|   3 | 3.3.3.3       |        1 |
|   3 | 4.4.4.4       |        1 |
+-----+---------------+----------+
7 rows in set (0.00 sec)

Виждате, че и двата опита са просто нови редове в таблицата. В третия случай обаче ще имаме проблем. Нека например вече въведения 4.4.4.4 направи още един опит:

mysql> INSERT INTO logs(uid, ip, attempts)
    -> VALUES (3, INET_ATON('4.4.4.4'), 1);
ERROR 1062 (23000): Duplicate entry '3-67372036' for key 'PRIMARY'

Разбира се това не е позволено, защото ние дефинирахме първичен ключ в таблицата (uid, ip), което не позволява да има два реда с една и съща комбинация от тях. При това ние в никакъв случай не искаме да пазим редове с дублиращи се такива комбинации, защото по този начин се опорочава смисъла на колоната attempts – ще се налага да сумираме по тази колона за да броим опитите.

Едно възможно решение на казуса е в програмата първо да изпълняваме SELECT заявка, с която да потърсим дали случайно има такъв ред и ако има – да направим UPDATE, а в противен случай INSERT. Още по-добър вариант е направо да направим UPDATE и ако е успешна заявката да спрем дотам, а ако е неуспешна да направим INSERT. Или обратното – да направим INSERT и ако заявката е успешна да спрем дотам, а ако е неуспешна да направим UPDATE. Освен това ако се досетим, че и в двата варианта двете заявки трябва да се изпълнят в трансакция, защото в противен случай рискуваме паралелни заявки да вмъкнат грешни резултати. Това вече ни се струва непрактично, защото изисква поддръжка и специално внимание.

В ANSI SQL стандарта съществува специална заявка MERGE, която се използва именно в такива случаи. Реално обаче малко системи (Oracle, DB2 и MSSQL) я поддържат в този ѝ вид. Практически в различните СУБД тази функционалност е реализирана с различен синтаксис. Ние ще се фокусираме върху MySQL, където е създадено специално разширение на заявката INSERT:

mysql> INSERT INTO logs(uid, ip, attempts)
    -> VALUES (3, INET_ATON('4.4.4.4'), 1)
    -> ON DUPLICATE KEY UPDATE attempts=attempts+1;
Query OK, 2 rows affected (0.08 sec)

Обърнете внимание, че имаме „2 rows affected“ (два реда бяха засегнати).  Това е така защото при наличие на DUPLICATE KEY реда се изтрива (един засегнат ред) и тогава се вмъква нов ред на негово място (още един засегнат ред). Винаги имайте това предвид ако разчитате на тези стойности. Също така забележете, че ако имате „auto_increment“ колона, то по този начин стойността по нея НЕ се увеличава. Ако направим delete-insert последователност (в MySQL всъщност има команда „replace“), то стойността по auto_increment полето ще се промени, а това рядко е желано.

Естествено можем да използваме това за да вмъкваме всякакви комбинации от записи накуп:

mysql> INSERT INTO logs(uid, ip, attempts)
    -> VALUES (3, INET_ATON('4.4.4.4'), 1),
    ->        (2, INET_ATON('4.4.4.4'), 1),
    ->        (1, INET_ATON('5.5.5.5'), 1)
    -> ON DUPLICATE KEY UPDATE attempts=attempts+1;
Query OK, 4 rows affected (0.13 sec)
Records: 3  Duplicates: 1  Warnings: 0

Разбира се техниката на „on duplicate key update“ може да се използва не само за „броячи“. Ето един пример – спортисти участват в състезание, като всеки един от тях си има уникален номер и поредица от постигнати резултати. В таблицата с крайните резултати ние записваме само най-добрия (в случая най-малко число) постигнат резултат на съответния спортист:

mysql> DESC final_score;
+-------+---------------------+------+-----+---------+
| Field | Type                | Null | Key | Default |
+-------+---------------------+------+-----+---------+
| id    | tinyint(3) unsigned | NO   | PRI | NULL    |
| score | double              | YES  |     | NULL    |
+-------+---------------------+------+-----+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM final_score
    -> ORDER BY ISNULL(score), score;
+----+-------+
| id | score |
+----+-------+
|  4 |  49.8 |
|  1 |  50.5 |
|  5 |  51.3 |
|  2 |  60.3 |
|  3 |  NULL |
+----+-------+
5 rows in set (0.00 sec)

* Използваме сортиране с функцията ISNULL (връща 1 ако няма стойност и 0 ако има), за да може NULL стойностите да отидат в края на резултатната таблица. Стандартно при сортиране по колона, в която има NULL стойности те ще излязат в началото, а ние в случая ги желаем в края, защото имат смисъл на „спортиста въобще няма постигнат резултат“.

Сега да предположим, че състезател №5 е направил нов опит с резултат „50.1“, състезател №1 е направил опит с резултат „55.6“, а състезател №3 е направил опит с резултат „58.5“. Очевидно на №5 и №3 трябва да им се обновят резултатите, докато на №1 не. Ето как можем да направим това с една заявка:

mysql> INSERT INTO final_score(id, score)
    -> VALUES (5, 50.1),
    ->        (1, 55.6),
    ->        (3, 58.5)
    -> ON DUPLICATE KEY
    -> UPDATE score=IF(VALUES(score)>score, score, VALUES(score));
Query OK, 4 rows affected (0.33 sec)
Records: 3  Duplicates: 2  Warnings: 0

mysql> SELECT * FROM final_score
    -> ORDER BY ISNULL(score), score;
+----+-------+
| id | score |
+----+-------+
|  4 |  49.8 |
|  5 |  50.1 |
|  1 |  50.5 |
|  3 |  58.5 |
|  2 |  60.3 |
+----+-------+
5 rows in set (0.00 sec)

Виждаме, че резултатите бяха обновени коректно – 5 и 3 получиха новите си стойности, докато 1 остана непроменен.

Обновено 31.05.2018 г. В MariaDB 10.3.3 и по-нови версии функцията „VALUES“ в UPDATE клаузата е сменена на „VALUE“.