网站首页学无止境MySQL

插入与更新同时进行的神奇sql：on duplicate key update

发布时间：2016-05-30 16:17:15编辑：songlin阅读（90）

    这个神圣的SQL语句，可以帮你减少很多代码量，比如插入微信用户到数据表中，那么openid是唯一的。按简单的方法是先用select语句查询一下有没有这个openid插入过，有则update，没有则insert。需要写3条sql语句代码。

    那么有没有办法用一条SQL语句搞定这个呢?

    假如我们有一张表

    1. CREATE TABLE `duplicate_test` ( 
    2.   `a` int(11) NOT NULL, 
    3.   `b` varchar(255) NOT NULL, 
    4.   `c` varchar(255) NOT NULL 
    5. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; 
    6.  
    7.  
    8. ALTER TABLE `duplicate_test` 
    9.   ADD PRIMARY KEY (`a`); 
    10.  
    11.  
    12. ALTER TABLE `duplicate_test` 
    13.   MODIFY `a` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=4; 
    14.    
    15. INSERT INTO `duplicate_test` (`a`, `b`, `c`) VALUES 
    16. (2, '2', '5'), 
    17. (3, '6', '2'); 

    想实现如果存在`a`字段为2和3的则修改，其他字段，不存在则添加。

    可以联想到`a`字段是微信用户的openid,那之前插入过这个用户的数据那这次只需要更新除了openid之外的其它字段信息。

    insert语句如下:

    1. insert into duplicate_test (a,b,c) values  
    2. (1,2,3), 
    3. (2,5,7), 
    4. (3,3,6), 
    5. (4,8,2) 
    6. on duplicate key update a = values(a),b = values(b),c = values(c) 

    执行前后数据对比

    a b c
    2 2 5
    3 6 2

     

    a b c
    1 2 3
    2 5 7
    3 3 6
    4 8 2

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

相关文章