如何避免重复插入数据库？

本文介绍了如何避免重复插入数据库？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我为插入记录写了一个查询，但有时它会插入重复的记录 我的查询是这样的 DECLARE @cnt AS INT SET @ cnt =（SELECT COUNT（*）FROM tbltest WHERE Empcode = @Uid AND SID = @Sid AND PID = @Pid AND EmpID = @ EmpId AND RMID = @RMId AND EndTime IS NULL） IF @ cnt = 0 BEGIN 插入到tbltest（EmpCode，sid，Pid，Empid，RmId，Param1，Starttime，Transtype）值（ @Uid， @Sid， @Pid， @EmpId， @RMId， @ processname_param1，@ CURRDATE ，'输入'） 结束 i使用代码避免重复，但有时会插入重复记录。

解决方案

在此表中添加一个主键。 这个主键是这些列的组合，它们共同定义一个唯一的字段。 偶你不想定义主键，你可以定义一个唯一约束 - 创建唯一约束 [ ^ ]。

尝试在您的选择查询中删除 AND EndTime为空 过滤并再次检查

SET @cnt =（ SELECT COUNT（*） FROM tbltest WHERE Empcode = @ Uid AND SID = @ Sid AND PID = @ Pid AND EmpID = @ EmpId AND RMID = @ RMId ）

I write a query for insert records but some times it insert duplicate records my query like this DECLARE @cnt AS INT SET @cnt=(SELECT COUNT(*) FROM tbltest WHERE Empcode= @Uid AND SID= @Sid AND PID= @Pid AND EmpID=@EmpId AND RMID= @RMId AND EndTime IS NULL) IF @cnt=0 BEGIN insert into tbltest (EmpCode,sid,Pid,Empid,RmId,Param1,Starttime,Transtype) values( @Uid, @Sid, @Pid, @EmpId, @RMId, @processname_param1,@CURRDATE,'Input') END i used code for avoiding duplication but some times it insert duplicate records.

解决方案

Add a primary key to this table. This primary key would be a combination of those columns that together define a unique field. Even inf you don't want to define a primary key, you can define a unique constraint - Create Unique Constraints[^].

Try removing AND EndTime is NULL filter in your select query and check again

SET @cnt=(SELECT COUNT(*) FROM tbltest WHERE Empcode= @Uid AND SID= @Sid AND PID= @Pid AND EmpID=@EmpId AND RMID= @RMId)