postgresql数据库的一些优雅的sql操作

批量更新

方法1：

UPDATE acc_device_group 
SET uuid=tmp.uuid,group_name=tmp.group_name ,create_user=tmp.create_user,update_user=tmp.update_user,court_uuid=tmp.court_uuid,remark=tmp.remark ,delete_flag=tmp.delete_flag 
FROM (VALUES ('7fa6b9d2483c410a86aa94a22a873633', '用户体验测试', 'admin', 'admin', '123465', '用户体验', 1) , 
             ('e588b58f54e045c1a670ea17b86d1927', '用户体验测试', 'admin', 'admin', '123465', '用户体验', 1) ) 
AS tmp(uuid, group_name, create_user,update_user,court_uuid,remark,delete_flag) 
WHERE acc_device_group.uuid = tmp.uuid  

方法2:

update acc.acc_device_group 
set create_user =case 
when uuid='966310efbf4447b7b810e5704b5cd303' then 'admin1' 
when uuid='76de8324c6bc4841aa82c9f305e49d13' then 'admin2' 
end, 
update_user =case 
when uuid='966310efbf4447b7b810e5704b5cd303' then 'admin1' 
when uuid='76de8324c6bc4841aa82c9f305e49d13' then 'admin2' 
end 
where uuid in ( '966310efbf4447b7b810e5704b5cd303' , '76de8324c6bc4841aa82c9f305e49d13' )

mybatis映射

	<update id="batchUpdate1" parameterType="java.util.List">
		update acc.acc_device_group
		<trim prefix="set" suffixOverrides=",">
			<trim prefix="create_user =case" suffix="end,">
				<foreach collection="list" item="item" index="index">
					<if test="item.createUser !=null">
						when uuid=#{item.uuid} then #{item.createUser}
					</if>
				</foreach>
			</trim>
			<trim prefix="update_user =case" suffix="end,">
				<foreach collection="list" item="item" index="index">
					<if test="item.updateUser !=null">
						when uuid=#{item.uuid} then #{item.updateUser}
					</if>
				</foreach>
			</trim>
		</trim>
		where uuid in
		<foreach collection="list" index="index" item="item"
			separator="," open="(" close=")">
			#{item.uuid}
		</foreach>
	</update>

单条记录避免重复插入，当有重复时，改为更新，或不改动。uuid为主键

insert into acc_device_group(uuid, group_name,create_time,update_time,create_user,update_user,court_uuid,remark,delete_flag)
values ('7fa6b9d2483c410a86aa94a22a873636','用户体验测试','2018-01-22 16:13:32.65','2018-01-22 16:13:32.65','admin','admin','11','用户体验',1))
on conflict (uuid) 
do update SET 
group_name=tmp(group_name),create_time='2018-01-22 16:13:32.65',update_time='2018-01-22 16:13:32.65',create_user='admin',update_user='admin',court_uuid='11',remark='用户体验',delete_flag=0

不改动只要把后面改为do nothing

根据不是主键或唯一的字段判断是否重复：

INSERT INTO acc_device_auth(uuid, create_time, update_time, create_user, update_user,
		court_uuid,start_date, end_date,user_id, user_type,delete_flag,enable_flag) 
SELECT '8c81b411bed349f9975154ce6d66636','2018-01-22 16:16:58.687','2018-01-23 19:27:41.943','admin','admin','testCourtUuid',
    '2018-01-01 16:14:30','2027-01-31 16:14:58','74788ee0d70943ceaf2',1,1,1
	WHERE
		NOT EXISTS (
			SELECT 1 FROM acc_device_auth
			WHERE user_id = '74788ee0d70943ceaf2' AND delete_flag = 1
		);

根据插入数据的user_id和delete_flag判断该条记录是否重复

批量更新时重复改为更新：

INSERT INTO test (id, name, age)
VALUES
	('123456', 'test', 1),
	('1234567', 'test2', 0)
 ON CONFLICT(id)
	DO UPDATE
	SET name =excluded.name,age=excluded.age

mysql可用：

if not exists(select 1 from tb where 重复列名一='xx' and 重复列名二='xx' and 重复列名三='xx' and 重复列名四='xx')
begin
    insert into ...
end

mysql批量插入重复时更新：

<insert id="insertStatFlow" parameterType="java.util.List" >
	
	INSERT  INTO house (house_id,community_id,area_code,building_id,building_code,building_name,
                   unit_id,unit_code,unit_name,house_code,house_name,floor,is_delete)
	 VALUES
  
     <foreach collection="list" item="item" separator=",">
		( #{item.house_id}, #{item.community_id},#{item.area_code},#{item.building_id},
		#{item.building_code}, #{item.building_name},#{item.unit_id},#{item.unit_code},
		 #{item.unit_name}, #{item.house_code},#{item.house_name},#{item.floor},#{item.is_delete})
     </foreach>
	
	 ON DUPLICATE KEY UPDATE
	 
	 community_id =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.community_id}
     </foreach>
	 END,

	 area_code =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.area_code}
     </foreach>
	 END,
    
     building_id =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.building_id}
     </foreach>
	 END,

	 building_code =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.building_code}
     </foreach>
	 END,
	 
	  building_name =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.building_name}
     </foreach>
	 END,

	 unit_id =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.unit_id}
     </foreach>
	 END,
	 
	  unit_code =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.unit_code}
     </foreach>
	 END,

	 unit_name =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.unit_name}
     </foreach>
	 END,
	 
	  house_code =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.house_code}
     </foreach>
	 END,

	 area_code =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.area_code}
     </foreach>
	 END,
	 
	  house_name =CASE   house_id  
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.house_name}
     </foreach>
	 END,

	 floor =CASE house_id
	<foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.floor}
     </foreach>
	 END,

     is_delete =CASE house_id
	 <foreach collection="list" item="item" index="index">
		WHEN #{item.house_id} THEN #{item.is_delete}
     </foreach>

	END
   </insert>