INSERT INTO… ON DUPLICATE KEY UPDATE的使用

概述

这条语句的主要作用是插入时若数据库中已存在唯一索引（如主键）相同的数据，则不会再执行插入，而会执行UPDATE之后的更新语句。

SQL写法

INSERT INTO USER (ID,NAME,PASSWORD) VALUES (1,"苏子衿","password") 
ON DUPLICATE KEY UPDATE NAME="闫肆",PASSWORD="222222"

mybatis使用

<insert id="insertUser" parameterType="com.meteor.bean.User">
	INSERT INTO USER
	<trim prefix="(" suffix=")" suffixOverrides=",">
		<if test="id != null">
			ID,
		</if>
		<if test="name != null">
			NAME,
		</if>
		<if test="password != null">
			PASSWORD,
		</if>
	</trim>
	<trim prefix="values (" suffix=")" suffixOverrides=",">
		<if test="id != null">
			#{id ,jdbcType=BIGINT},
		</if>
		<if test="name != null">
			 #{name,jdbcType=VARCHAR},
		</if>
		<if test="password != null">
			 #{password,jdbcType=VARCHAR},
		</if>
	</trim>
	<trim prefix="ON DUPLICATE KEY UPDATE"  suffixOverrides=",">
		<if test="id != null">
			ID = #{id ,jdbcType=BIGINT},
		</if>
		<if test="name != null">
			NAME = #{name,jdbcType=VARCHAR},
		</if>
		<if test="password != null">
			PASSWORD = #{password,jdbcType=VARCHAR},
		</if>
	</trim>
</insert>