考慮下面的表，主鍵id int unsigned, 唯一索引 username.
CREATE TABLE update_test (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
host_id TINYINT UNSIGNED NOT NULL,
last_modified TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY(id),
UNIQUE KEY(username)
) ENGINE=InnoDB;
然後考慮一種比較極端的情況：
如果每秒向此表中寫入1500條記錄，每天持續24小時，
id字段為int unsigned，取值范圍為1-4294967295，
那麼只需要 4294967295/(1500*86400) 約等於33天，id字段就超出范圍了。
超出范圍會怎樣呢？
測試一下看：
mysql> INSERT INTO update_test (id,username,host_id) VALUES (4294967295, 'bar', 10);
Query OK, 1 ROW affected (0.03 sec)
mysql> SHOW CREATE TABLE update_test;
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE | CREATE TABLE |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| update_test | CREATE TABLE `update_test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(20) NOT NULL,
`host_id` tinyint(3) UNSIGNED NOT NULL,
`last_modified` TIMESTAMP NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=latin1 |
--AUTO_INCREMENT=4294967295表示下一次insert的時候，id字段的值為4294967295。
mysql> INSERT INTO update_test (username,host_id) VALUES ('baz', 10);
ERROR 1062 (23000): Duplicate entry '4294967295' FOR KEY 'PRIMARY'
mysql> SELECT * FROM update_test;
+------------+----------+---------+---------------+
| id | username | host_id | last_modified |
+------------+----------+---------+---------------+
| 4294967295 | bar | 10 | NULL |
+------------+----------+---------+---------------+
1 ROW IN SET (0.00 sec)
可以看到由於AUTO_INCREMENT字段已經達到最大值，導致無法寫入新的記錄。
注意這裡提示的是Duplicate entry 4294967295。
如果使用INSERT … ON DUPLICATE KEY UPDATE 語句會怎樣呢？
清空數據重新測試下：
--truncate table會reset AUTO_INCREMENT字段的值。
mysql> TRUNCATE TABLE update_test;
Query OK, 0 ROWS affected (0.03 sec)
mysql> INSERT INTO update_test (username,host_id) VALUES ( 'foo',3);
Query OK, 1 ROW affected (0.05 sec)
mysql> INSERT INTO update_test (id,username,host_id) VALUES (4294967295, 'bar', 10);
Query OK, 1 ROW affected (0.05 sec)
mysql> SELECT * FROM update_test;
+------------+----------+---------+---------------+
| id | username | host_id | last_modified |
+------------+----------+---------+---------------+
| 1 | foo | 3 | NULL |
| 4294967295 | bar | 10 | NULL |
+------------+----------+---------+---------------+
2 ROWS IN SET (0.00 sec)
mysql> INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW();
Query OK, 2 ROWS affected (0.04 sec)
--為什麼是2 rows affected呢？
--With ON DUPLICATE KEY UPDATE, the affected-rows value per row is
--1 if the row is inserted as a new row,
--2 if an existing row is updated.
mysql> SELECT * FROM update_test;
+------------+----------+---------+---------------------+
| id | username | host_id | last_modified |
+------------+----------+---------+---------------------+
| 1 | foo | 3 | NULL |
| 4294967295 | bar | 7 | 2012-11-01 14:11:39 |
+------------+----------+---------+---------------------+
2 ROWS IN SET (0.00 sec)
很悲劇的是，username=’foo’的記錄沒有被更新，反而是更新了id為4294967295的記錄…
http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
文檔上說:
if column a is declared as UNIQUE and contains the value 1
INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
就相當於
UPDATE table SET c=c+1 WHERE a=1;
If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.
在本文的情況下，
INSERT INTO update_test (username, host_id) VALUES ('foo', 7) ON DUPLICATE KEY UPDATE host_id=7, last_modified=NOW();
就相當於：
UPDATE update_test SET host_id=7, last_modified=NOW() WHERE id=4294967295 OR username='foo' LIMIT 1
然後mysql優先使用了主鍵索引，於是username=’foo’這個條件被無視了。
下面這篇文章也可以順便看一看，是說AUTO_INCREMENT字段的gap現象的：
大意是說insert的時候如果unique key 沖突，仍然會導致AUTO_INCREMENT++。
INSERT … ON DUPLICATE KEY UPDATE 也是如此。
