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.