At the moment, I’m preparing for the core certification exam. Although I thought that I knew almost everything about the new features in MySQL 4.1, I sometimes find some fantastic “goodies” while I’m learning for the exam. One of them is the INSERT … ON DUPLICATE KEY UPDATE command.

This command is ideal for logging. You can insert into a table, but do an update, if the row (based on the primary key) already exists. An example might explain this best:

mysql> CREATE TABLE logdata (
    ->    id INT UNSIGNED NOT NULL,
    ->    count INT NOT NULL,
    ->    PRIMARY KEY (id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (1, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (2, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO logdata (id, count)
    ->    VALUES (1, 1)
    ->    ON DUPLICATE KEY UPDATE
    ->    count = count + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM logdata;
+----+-------+
| id | count |
+----+-------+
|  1 |     2 |
|  2 |     1 |
+----+-------+
2 rows in set (0.00 sec)

mysql>

First, the key values 1 and 2 have been inserted. Then we inserted the value 1 again, the command executed an UPDATE instead incrementing the count value to 2.