MySQL中插入数据,如果插入的数据在表中已经存在(主键或者唯一键已存在),使用insert into on duplicate key update 语法可以更新重复数据的某些字段值。
1、语法
insert into table_name(field1,field2,...fieldN) values(value1,value2,...valueN) on duplicate key update fieldM = valueM;
限制条件:
- 主键重复
- 唯一键重复
2、案例
表结构及数据如下:
CREATE TABLE table_name(
id int(11) NOT NULL,
name varchar(50) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> select * from table_name;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | Tom | 20 |
+----+------+------+
以下三条SQL,执行结果都一样,只要主键或者唯一键发生重复,就会更新该条记录的age字段值:
- insert into table_name values(1,'Bill',21) on duplicate key update age = 21;
- insert into table_name values(2,'Tom',21) on duplicate key update age = 21;
- insert into table_name values(1,'Tom',21) on duplicate key update age = 21;
mysql> select * from table_name;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | Tom | 21 |
+----+------+------+
1 row in set (0.00 sec)
以上可以看到age字段已经从20被更新为21。