INSERTing z opcją ON DUPLICATE KEY UPDATE w MySQLu

Zwróciliście kiedyś uwagę na działanie polecenia INSERT ... ON DUPLICATE KEY UPDATE i mały problem z 'luką' w wartościach kolumny, która jest AUTO_INCREMENT dla InnoDB. Pewnie nie, ale jeśli dziennie wstawiamy tysiące rekordów przy pomocy tej opcji, to po jakimś czasie być może będziemy stać przed problemem wyczerpania dozwolonych wartości dla takiej kolumny. Ale zacznijmy od przykładowej tabeli:

(owa@localhost) [test]> show create table multi_insert_test \G
*************************** 1. row ***************************
       Table: multi_insert_test
Create Table: CREATE TABLE `multi_insert_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_c1_c2` (`c1`,`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Tabela posiada dwa klucze unikalne: dla klucza głównego (kolumna id) i dla klucza unikalnego uniq_c1_c2 (kolumny c1,c2). Zacznę teraz wstawianie nowych rekordów dla wszystkich wartości kolumn oprócz tej z AUTO_INCREMENT:

(owa@localhost) [test]> select * from multi_insert_test;
Empty set (0.00 sec)

(owa@localhost) [test]> INSERT  INTO multi_insert_test (c1,c2) VALUES (2,3),(3,2);
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 | NULL |
|  2 |    3 |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

(owa@localhost) [test]> INSERT  INTO multi_insert_test (c1,c2,c3) VALUES (2,3,333) ON DUPLICATE KEY UPDATE c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.05 sec)

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  333 |
|  2 |    3 |    2 | NULL |
+----+------+------+------+
2 rows in set (0.00 sec)

Do tej pory nie stało się nic co mogło by nas zaskoczyć. Ale po wstawieniu rekordu z wartościami istniejącymi już na unikalnym kluczu, 'luka' w kolumnie AUTO_INCREMENT jest już zauważalna:

(owa@localhost) [test]> insert into multi_insert_test (c1,c2,c3) VALUES (5,6,22);
Query OK, 1 row affected (0.03 sec)

(owa@localhost) [test]> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  333 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
+----+------+------+------+
3 rows in set (0.00 sec)

Wstawiamy tym razem rekord z danymi już istaniejącymi dla naszych kluczy z zdefinicją jak zupdatować klucz główny:

mysql> INSERT  INTO multi_insert_test (id,c1,c2,c3) VALUES (1,2,3,334) ON DUPLICATE KEY UPDATE id=VALUES(id),c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.04 sec)

mysql> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  334 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
+----+------+------+------+
3 rows in set (0.00 sec)

mysql> insert into multi_insert_test (c1,c2,c3) VALUES (6,6,23);
Query OK, 1 row affected (0.04 sec)

mysql> select * from multi_insert_test;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |    2 |    3 |  334 |
|  2 |    3 |    2 | NULL |
|  4 |    5 |    6 |   22 |
|  5 |    6 |    6 |   23 |
+----+------+------+------+
4 rows in set (0.00 sec)

Tym razem luka nie powstała, ale tylko dlatego, że nowy rekord miał  zdefiniowaną wartość dla kolumny AUTO_ICREMENT.

A czy zauważyliście może ile rekordów 'brało udział' przy insertach z opcją ON DUPLICATE KEY UPDATE?

mysql> INSERT  INTO multi_insert_test (id,c1,c2,c3) VALUES (1,2,3,334) ON DUPLICATE KEY UPDATE id=VALUES(id),c1=VALUES(c1),c2=VALUES(c2),c3=VALUES(c3);
Query OK, 2 rows affected (0.04 sec)

Czyżby MySQL dla InnoDB tak na prawdę nie updatował wiersza tylko go najpierw usuwał, a później wstawiał na nowo?! Tak czy inaczej 'nowa' wartość kolumny auto_increment jest równa wartości zwracanej przez funkcję LAST_INSERT_ID();

Inną ciekawą rzeczą jaką zauważyłam przy tym poleceniu jest ilość updatowanych wierszy gdy tabela ma więcej niż jeden index unikalny:

mysql> show create table multi_insert_test2\G
*************************** 1. row ***************************
       Table: multi_insert_test2
Create Table: CREATE TABLE `multi_insert_test2` (
  `id` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  UNIQUE KEY `uniq_c1_c2` (`c1`,`c2`),
  UNIQUE KEY `uniq_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from multi_insert_test2;
+------+------+------+------+
| id   | c1   | c2   | c3   |
+------+------+------+------+
|    1 |    1 |    1 |    1 |
|    2 |    2 |    2 |    2 |
|    3 |    3 |    3 |    3 |
|    4 |    4 |    4 |    4 |
|    5 |    1 |    2 |    1 |
+------+------+------+------+
5 rows in set (0.00 sec)

mysql> insert into multi_insert_test2 (id, c1,c2,c3) values (5,1,1,1000) ON DUPLICATE KEY UPDATE c3=1000;
Query OK, 2 rows affected (0.05 sec)

Czego byście się spodziewali po wykonaniu powyższego inserta? Żaden z wierszy nie ma jednocześnie wartości 5 dla kolumny id i wartości 1,1 dla kolumn odpowiednio c1,c2:

mysql> select * from multi_insert_test2;
+------+------+------+------+
| id   | c1   | c2   | c3   |
+------+------+------+------+
|    1 |    1 |    1 | 1000 |
|    2 |    2 |    2 |    2 |
|    3 |    3 |    3 |    3 |
|    4 |    4 |    4 |    4 |
|    5 |    1 |    2 |    1 |
+------+------+------+------+
5 rows in set (0.00 sec)

Tak na prawdę to MySQL wykonał następujące polecenie:

UPDATE multi_insert_test2 SET c3 = 1001 WHERE id = 5 OR (c1 = 1 AND c2 = 1) LIMIT 1;

Dla zaciekawionych polecam zaznajomienie się z dokumentacją, którą możecie znaleść tutaj.