Postgresql和Mysql插入式更新区别

Postgresql和Mysql插入式更新区别

 单条语句

//mysql 单条语句
<insert id="save"
		parameterType="com.demo.User">
		insert into t_user
		(id,level)
		values
		(
		#{id},
		#{level}
		)
		ON DUPLICATE KEY UPDATE
		    id =VALUES(id),
		    level = VALUES(level)
	</insert>
// postgresql 单条操作
<insert id="insertAndUpdate">
        insert into t_user (id, level)
        values (1, 0)
        ON CONFLICT (id) DO UPDATE
        set level = t_user.level + 1;// 如id存在则level加1
</insert>

 批量操作

// mysql 批量操作
<insert id="batchUpdate" parameterType="java.util.List">
		insert into t_user
		(id, level)
		values
		<foreach collection="list" item="item" index="index"
			separator=",">
			(
			#{item.id,jdbcType=INTEGER},
			#{item.level,jdbcType=INTEGER}
			)
		</foreach>
		ON DUPLICATE KEY UPDATE
		    id =VALUES(id),
		    level = VALUES(level)
	</insert>
// postgresql 批量操作
<insert id="batchUpdate">
        insert into t_user (id, level)
        values (1, 3), (2, 1)
        on CONFLICT (id) DO UPDATE
        set level = excluded.level;// excluded用来引用前面的值
</insert>