MySQL中ONDUPLICATEKEYUPDATE 介绍

如果你想做到数据库中没有数据的话插入数据、有数据的话更新数据，那么你可以选择ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE能够在UNIQUE索引或PRIMARY KEY存在的情况下对旧行执行UPDATE操作

例如：如果列ID被定义为Primary key，并且包含值1,4，则以下两个语句具有相同的效果

<pre name="code" class="sql">(root@localhost)[test]> select * from itpub_A;
+------+------+
| id | name |
+------+------+
| 1 | A |
| 2 | B |
| 3 | C |
| 1 | D |
| 4 | D |
+------+------+
5 rows in set (0.00 sec)

<p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">(root@localhost)[test]> show create table itpub_A;</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">+---------+------------------------------------------------------------------------------------------------------------------------------+</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">| Table   | Create Table                                                                                                                 |</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">+---------+------------------------------------------------------------------------------------------------------------------------------+</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">| itpub_A | CREATE TABLE `itpub_A` (</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">  `id` int(11) DEFAULT NULL,</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">  `name` varchar(20) DEFAULT NULL</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">) ENGINE=InnoDB DEFAULT CHARSET=utf8 |</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">+---------+------------------------------------------------------------------------------------------------------------------------------+</p><p style="margin-top: 0px; margin-bottom: 0px; font-size: 11px; font-family: Menlo;">1 row in set (0.00 sec)</p><div>
</div>

(root@localhost)[test]> alter table itpub_A add primary key pk_itpub_a (id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

(root@localhost)[test]> delete from itpub_A where id=1 and name='D';
Query OK, 1 row affected (0.01 sec)

(root@localhost)[test]> alter table itpub_A add primary key pk_itpub_a (id);

Query OK, 0 rows affected (0.02 sec)

Records: 0  Duplicates: 0  Warnings: 0

(root@localhost)[test]> show create table itpub_A ;

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                                                                                               |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

| itpub_A | CREATE TABLE `itpub_A` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

(root@localhost)[test]> insert into itpub_A values(1,'D'),(4,'D') ON DUPLICATE KEY UPDATE name='E';

Query OK, 4 rows affected (0.00 sec)

Records: 2  Duplicates: 2  Warnings: 0

(root@localhost)[test]> select * from itpub_A;

+----+------+

| id | name |

+----+------+

|  1 | E    |

|  2 | B    |

|  3 | C    |

|  4 | E    |

+----+------+

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！