今天被朋友问到一个问题，在用SQL对mysql进行操作时，如何用一条sql实现不存在则插入，存在则更新（存在是针对主键或者unique来说的）。说实话，以前比较懒，每次都直接在程序中写一个if...else...，不免觉得代码太丑陋，而且在性能上也不好。因此对这个问题跟朋友进行了深入的讨论，加上网络上的资料，对这个问题进行了一个总结。假定我们有如下的一张表，id是主键。
| Field | Type | ||
|---|---|---|---|
| id | int(11) | ||
| name | varchar(20) | ||
| isForS | tinyint(1) | ||
| money | int(11) |
| create table |
|---|
| CREATE TABLE `ceshi` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `isForS` tinyint(1) DEFAULT NULL, `money` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
| id | name | isForS | money |
| 4 | ddd | 1 | 200 |
| 3 | zhangsan | 0 | 300 |
| 2 | lisi | 0 | 200 |
| 1 | zhangsan | 1 | 201 |
1.使用replace
REPLACE INTO ceshi(id,NAME,isForS,money) VALUES(1,"zhangsan",1,100)，当执行这条命令是由于数据库中已经存在了该条数据（id主键重复），所以数据库中的数据没有改变。实际上当数据库中不存在要插入的数据时，replace跟insert语句具有同样的效果。而当数据库中有已经存在的数据行时，mysql先把以前的记录删除掉，接着再执行insert.
2.使用dual虚拟表
当我们在mysql中输入命令select 1+1时，实际上就是select 1+1 from dual。dual这个表不过是让你的sql语句写起来更符合传统的sql语句的格式。我们可以这样来写我们的sql语句，INSERT INTO ceshi SELECT 1,"zhang",0,200 FROM DUAL WHERE NOT EXISTS (SELECT * FROM ceshi WHERE id=1) 。
3.使用ON DUPLICATE KEY UPDATE
执行如下的sql语句，INSERT INTO ceshi VALUE(1,"zhang",1,100) ON DUPLICATE KEY UPDATE money=money+100
如果数据库中不存在id为1的记录，则相当于执行INSERT INTO ceshi VALUE(1,"zhang",1,100)，此时返回的受影响的行数为1.
如果数据库中存在相应的记录，相当于执行update ceshi set money=money+100 where id=1.此时返回的受影响的行数为2.
4.使用ignore关键字
INSERT IGNORE INTO ceshi VALUE(1,"zhang",1,100)，当执行这条sql语句时，如果数据库中已经存在相同的记录，则数据库会忽略该条命令，返回的受影响的行数为0.