MySQL 5.5 INSERT ... ON DUPLICATE KEY UPDATE语句说明

绝地灬酷狼 2024-04-18 00:49 48阅读 0赞

#### 执行INSERT ... ON DUPLICATE KEY UPDATE语句，如果INSERT的语句插入的值和已有的UNIQUE索引或主键重复的话，MySQL会更新已存在的行。 ####

测试没有主键和UNIQUE索引的表  
  
mysql> select \* from dept2;  
 \+--------+-------------+-------------+  
 | deptno | dname       | report\_date |  
 \+--------+-------------+-------------+  
 |     10 | Research    | 2016-06-03  |  
 |     20 | Maintenance | 2016-06-03  |  
 |     30 | Leader      | 2016-06-03  |  
 |     40 | Market      | 2015-08-02  |  
 \+--------+-------------+-------------+  
 4 rows in set (0.00 sec)  
  
 mysql> desc dept2;  
 \+-------------+-------------+------+-----+---------+-------+  
 | Field       | Type        | Null | Key | Default | Extra |  
 \+-------------+-------------+------+-----+---------+-------+  
 | deptno      | int(5)      | NO   | MUL | NULL    |       |  
 | dname       | varchar(14) | YES  |     | NULL    |       |  
 | report\_date | date        | YES  | MUL | NULL    |       |  
 \+-------------+-------------+------+-----+---------+-------+  
 3 rows in set (0.00 sec)  
  
 mysql> INSERT INTO dept2 (deptno,dname,report\_date) VALUES (20,'Development','2010-10-30')  
     ->   ON DUPLICATE KEY UPDATE report\_date='2010-10-30';  
 Query OK, 1 row affected (0.01 sec)  
  
 mysql> select \* from dept2;  
 \+--------+-------------+-------------+  
 | deptno | dname       | report\_date |  
 \+--------+-------------+-------------+  
 |     10 | Research    | 2016-06-03  |  
 |     20 | Maintenance | 2016-06-03  |  
 |     30 | Leader      | 2016-06-03  |  
 |     40 | Market      | 2015-08-02  |  
 |     20 | Development | 2010-10-30  |  
 \+--------+-------------+-------------+  
 5 rows in set (0.00 sec)  
  
 mysql> delete from dept2 where deptno=20 and report\_date=date'2010-10-30';  
 Query OK, 1 row affected (0.01 sec)  
  
 mysql> select \* from dept2;  
 \+--------+-------------+-------------+  
 | deptno | dname       | report\_date |  
 \+--------+-------------+-------------+  
 |     10 | Research    | 2016-06-03  |  
 |     20 | Maintenance | 2016-06-03  |  
 |     30 | Leader      | 2016-06-03  |  
 |     40 | Market      | 2015-08-02  |  
 \+--------+-------------+-------------+  
 4 rows in set (0.00 sec)  
  
 增加主键，再进行测试  
  
mysql> alter table dept2 add primary key(deptno);  
 Query OK, 0 rows affected (0.28 sec)  
 Records: 0  Duplicates: 0  Warnings: 0  
  
 mysql> INSERT INTO dept2 (deptno,dname,report\_date) VALUES (20,'Development','2010-10-30')  
     ->   ON DUPLICATE KEY UPDATE report\_date='2010-10-30';  
 Query OK, 2 rows affected (0.14 sec)  
  
 mysql> select \* from dept2;  
 \+--------+-------------+-------------+  
 | deptno | dname       | report\_date |  
 \+--------+-------------+-------------+  
 |     10 | Research    | 2016-06-03  |  
 |     20 | Maintenance | 2010-10-30  |  
 |     30 | Leader      | 2016-06-03  |  
 |     40 | Market      | 2015-08-02  |  
 \+--------+-------------+-------------+  
 4 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2113722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2113722/