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

灰太狼 2022-06-01 14:05 175阅读 0赞

批量更新

方法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>