MySQL保存或更新 saveOrUpdate
博客专区 > 阿K1225 的博客 > 博客详情
MySQL保存或更新 saveOrUpdate
阿K1225 发表于3个月前
MySQL保存或更新 saveOrUpdate
  • 发表于 3个月前
  • 阅读 30
  • 收藏 0
  • 点赞 0
  • 评论 0

在项目开发过程中,有一些数据在写入时候,若已经存在,则覆盖即可。这样可以防止多次重复写入唯一键冲突报错。下面先给出两个MyBatis配置文件中使用saveOrUpdate的示例

<!-- 单条数据保存 -->
<insert id="saveOrUpdate" parameterType="TestVo">
    insert into table_name (
        col1,
        col2,
        col3
    )
    values (
        #{field1},
        #{field2},
        #{field3}
    )
    on duplicate key update
        col1 = #{field1},
        col2 = #{field2},
        col3 = #{field3}
</insert>  

<!-- 批量保存 -->
<insert id="batchSaveOrUpdate" parameterType="java.util.List">
    insert into table_name (
        col1,
        col2,
        col3
    )
    <foreach collection="list" item="item" index="index" separator=",">
        values (
            #{item.field1},
            #{item.field2},
            #{item.field3}
        )
    </foreach>
    on duplicate key update
        col1 = VALUES (col1),
        col2 = VALUES (col2),
        col3 = VALUES (col3)
</insert>

其实对于单行数据on duplicate key update也可以和批量数据保存一样使用VALUES表达式(VALUES指向新数据)。

通过上面的例子初识MySQL ON DUPLICATE KEY UPDATE语法,下面继续学习~~

2. ON DUPLICATE KEY UPDATE 语法

MySQL的ON DUPLICATE KEY UPDATE语法是指包含ON DUPLICATE KEY UPDATE子句的INSERT语句,当新增的这条语句在数据库中已经存在(已经存在是指这条数据包含的主键或者唯一键在数据库已经存在),则会更新数据库对应的老数据。

下面两条sql语句就是等效的,其中table表中a是唯一键

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;
  • 1
  • 2
  • 3
  • 4

若在table表中,不仅仅存在a这个唯一键,b也是唯一键的情况下,以下两条语句就是等效的

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;  

UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
  • 1
  • 2
  • 3
  • 4

上面这条update语句的含义是:从表中取出满足a=1或者b=2的一条数据,进行更新操作。

下面重点了解以下几个问题:

2.1 多个唯一键

对于一张包含多个唯一键(多个唯一键指有多个键,而不是一个键中包含多个字段)的情况下,一定要注意多个唯一键是否会对应多条数据

从上述第二个例子可以看出,ON DUPLICATE KEY UPDATE会根据a=1或b=2匹配出一条数据进行更新,当此时对应多条数据时候,这种更新操作就会有不确定性。(从另一个角度考虑,若多个唯一键都是一一对应,那么更新操作也不会有问题)

2.2 影响行数返回值

数据不存在,新增数据返回1 
数据已存在,修改数据返回2 
数据已存在,但未变化返回0

数据是否存在根据唯一键判断,数据是否修改根据ON DUPLICATE KEY UPDATE后的语句判断

下面是一个ON DUPLICATE KEY UPDATE返回值各种情况的简单实例:

mysql> CREATE TABLE test1 (a INT PRIMARY KEY AUTO_INCREMENT , b INT, c INT);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
+---+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE c = c + 1;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
+---+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 2) ON DUPLICATE KEY UPDATE c = c + 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c = VALUES(c);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO test1(a, b ,c) VALUES (2, 2, 3) ON DUPLICATE KEY UPDATE c = VALUES(c);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    2 |
| 2 |    2 |    3 |
+---+------+------+
2 rows in set (0.00 sec)

注意返回值与新增、修改之间的关系

2.3 新老数据引用

从上面的例子,和触发器做类比,在ON DUPLICATE KEY UPDATE子句后面,直接使用字段名,引用的是老数据;使用VALUES,引用的是要插入更新的新数据。(例如:c=c+1是在老数据的c字段上加1,c=VALUES(c)是拿新数据覆盖老数据)

2.4 批量保存

批量保存使用ON DUPLICATE KEY UPDATE的场景,请回过头参照文章开始的示例中的第二个用法。

参考自官网:http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

 

 

 

共有 人打赏支持
粉丝 4
博文 69
码字总数 26943
×
阿K1225
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: