如果你想做到数据库中没有数据的话插入数据、有数据的话更新数据，那么你可以选择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标准语法！
