保存或更新
在MySQL数据库中，如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句，而要插入的行与表中现有记录的惟一索引或主键中产生重复值，那么就会发生旧行的更新；如果插入的行数据与现有表中记录的唯一索引或者主键不重复，则执行新纪录插入操作。另外，ON DUPLICATE KEY UPDATE不能写where条件。
示例：
create table kid_score(id tinyint unsigned not null,birth_day date not null,score int unsigned not null,primary key(id, birth_day) --唯一索引是由 id + birth_day 两个字段组成) engine = InnoDB;--初始化数据insert into kid_score(id, birth_day, score) values (1,'-01-15',10),(2,'-01-16',20);
下面开始验证执行INSERT ··· ON DUPLICATE KEY UPDATE语法的规则：如果你插入的记录导致一个UNIQUE索引或者primary key(主键)出现重复，那么就会认为该条记录存在，则执行update语句而不是insert语句，反之，则执行insert语句而不是更新语句。
1. 唯一索引重复
insert into kid_score(id, birth_day, score) values (1,'-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
结果：
2. 唯一索引不重复
insert into kid_score(id, birth_day, score) values (2,'-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;
结果：
3. 唯一索引重复，插入完全相同数据
insert into kid_score(id, birth_day, score) values (2,'-01-16',20) ON DUPLICATE KEY UPDATE score = 20;
结果：
4. 影响行数
需要注意的是：如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2，如果更新的数据和已有的数据一模一样，则受影响的行数是0。
mysql> select * from kid_score;+----+------------+-------+| id | birth_day | score |+----+------------+-------+| 1 | -01-15 | 10 || 2 | -01-16 | 20 |+----+------------+-------+2 rows in set-- 唯一索引重复，执行更新mysql> insert into kid_score(id, birth_day, score) values (1,'-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;Query OK, 2 rows affectedmysql> select * from kid_score;+----+------------+-------+| id | birth_day | score |+----+------------+-------+| 1 | -01-15 | 60 || 2 | -01-16 | 20 |+----+------------+-------+2 rows in set-- 唯一索引不重复，执行插入mysql> insert into kid_score(id, birth_day, score) values (2,'-01-15',30) ON DUPLICATE KEY UPDATE score = score + 50;Query OK, 1 row affectedmysql> select * from kid_score;+----+------------+-------+| id | birth_day | score |+----+------------+-------+| 1 | -01-15 | 60 || 2 | -01-15 | 30 || 2 | -01-16 | 20 |+----+------------+-------+3 rows in set-- 唯一索引重复，应该执行更新，但更新值与原值相同mysql> insert into kid_score(id, birth_day, score) values (2,'-01-16',20) ON DUPLICATE KEY UPDATE score = 20;Query OK, 0 rows affectedmysql> select * from kid_score;+----+------------+-------+| id | birth_day | score |+----+------------+-------+| 1 | -01-15 | 60 || 2 | -01-15 | 30 || 2 | -01-16 | 20 |+----+------------+-------+3 rows in set
