blocks|key|119739|text|将唯一约束添加到表中，以绝对防止这种情况发生|type|unstyled|depth|inlineStyleRanges|entityRanges|data|119740|ALTER+TABLE+tblMessages+ADD+CONSTRAINT+uq_tblMessages+UNIQUE+(message,ip,timestamp)|code-block|syntax|javascript|119741|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Add a unique constraint to the table to absolutely prevent it from happening

<pre><code>ALTER TABLE tblMessages ADD CONSTRAINT uq_tblMessages UNIQUE (message,ip,timestamp)
</code></pre>

blocks|key|3390070|text|我认为，最简单的方法是使用触发器来检查表中现有记录中的消息的发送者和正文。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3390071|或者，正如Derek所说，您可以使用约束，但有另一个条件：|3390072|ALTER+TABLE+tblMessages+ADD+CONSTRAINT+uq_tblMessages+UNIQUE+(message,ip,username,+parentId)|code-block|syntax|javascript|3390073|但是约束会产生异常(你需要处理它)。|3390074|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I think, the easiest way is to use a triggers to check the sender and body of message in existing records in the table.

or, as Derek said, you can use the constraint, but with another condition:

<pre><code>ALTER TABLE tblMessages ADD CONSTRAINT uq_tblMessages UNIQUE (message,ip,username, parentId)
</code></pre>

but constraint will generate exception (and you will need to handle it).

blocks|key|3675814|text|实际上，Derek+Kromm并不遥远；从本质上讲，您确实需要一个唯一的约束，您只需要其中一列的范围。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3675815|您可以将其表示为一个filtered+index，它强制在您想要的列上保持唯一性，但是使用一个过滤器来匹配10分钟范围内的时间戳。|offset|length|3675816|CREATE+NONCLUSTERED+INDEX+IX_UNC_tblMessages
ON+tblMessages+(message,+ip,+timestamp)
WHERE+datediff(minute,+timestamp,+getdate())+<+10)|code-block|syntax|javascript|3675817|关于unique约束和保持唯一性的过滤索引(MSDN)之间的区别：|3675818|3675819|创建UNIQUE约束和创建独立于约束的unique索引没有显著区别。数据验证以相同的方式进行，并且查询优化器不区分由约束创建的唯一索引和手动创建的唯一索引。但是，如果以数据完整性为目标，则应在列上创建唯一键约束或主键约束。通过这样做，索引的目标将是明确的。|blockquote|3675820|3675821|3675822|其中我唯一不确定的是getdate()的使用。我不确定这会对索引和性能产生什么影响--这一点你可以自己测试一下。|3675823|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/cc280372.aspx|1|http://msdn.microsoft.com/en-us/library/ms187019.aspx^0|0|A|E|0|0|0|M|4|1|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@$D|16|E|17|1|18]]|A|$]]|$1|F|3|G|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|1A|8|@]|9|@$D|1B|E|1C|1|1D]]|A|$]]|$1|M|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1I|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]|12|$5|X|Y|Z|A|$10|13]]]]

Actually Derek Kromm isn't far off; Essentially you do want a unique constraint, you just want range for one of the columns.

You can express this as a <a href="http://msdn.microsoft.com/en-us/library/cc280372.aspx" rel="nofollow">filtered index</a> which enforces the uniqueness on the columns you want but with a filter to match timestamps within a 10 minutes range.

<pre><code>CREATE NONCLUSTERED INDEX IX_UNC_tblMessages
ON tblMessages (message, ip, timestamp)
WHERE datediff(minute, timestamp, getdate()) &lt; 10)
</code></pre>

On the difference between a unique constraint and a filtered index which maintains uniqueness (<a href="http://msdn.microsoft.com/en-us/library/ms187019.aspx" rel="nofollow">MSDN</a>):

<blockquote>
 There are no significant differences between creating a UNIQUE
 constraint and creating a unique index independent of a constraint.
 Data validation occurs in the same manner and the query optimizer does
 not differentiate between a unique index created by a constraint or
 manually created. However, you should create a UNIQUE or PRIMARY KEY
 constraint on the column when data integrity is the objective. By
 doing this the objective of the index will be clear.
</blockquote>

The only aspect of this I'm not sure about is the use of getdate(). I'm not sure what effect that will have on the index and performance- this you will want to test for yourself.

blocks|key|3675860|text|我想你需要一个扳机|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3675861|唯一的约束/索引不足以处理给定消息和ip的帖子之间的10分钟间隔。|3675862|CREATE+TRIGGER+TRG_tblMessages_I+FRO+INSERT
AS
SET+NOCOUNT+ON;

IF+EXISTS+(SELECT+*+
+++++++FROM+tblMessages+M
+++++++++++JOIN+INSERTED+I+ON+M.message+=+I.message+and+M.ip+=+I.ip
+++++++WHERE
+++++++++++datediff(minute,+M.timestamp,+I.timestamp)+<+10)
BEGIN
++++RAISERRROR+('blah',+16,+1)
++++ROLLBACK+TRAN
END|code-block|syntax|javascript|3675863|编辑:你需要一个额外的条件来忽略你刚刚插入的同一行(例如使用代理键)|3675864|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

I reckon you need a trigger

A unique constraint/index isn't clever enough to deal with the 10 minute gap between posts for a given message and ip.

<pre><code>CREATE TRIGGER TRG_tblMessages_I FRO INSERT
AS
SET NOCOUNT ON;

IF EXISTS (SELECT * 
 FROM tblMessages M
 JOIN INSERTED I ON M.message = I.message and M.ip = I.ip
 WHERE
 datediff(minute, M.timestamp, I.timestamp) &lt; 10)
BEGIN
 RAISERRROR ('blah', 16, 1)
 ROLLBACK TRAN
END
</code></pre>

Edit: you need an extra condition to ignore the same row you have just inserted (eg using surrogate key)

So I have this stored procedure to insert a message into my database. I wanted to prevent users from posting duplicate messages within a short period of time, whether on accident or on purpose (either a laggy connection or a spammer).

This is what the insert statement looks like:

<pre><code>IF NOT EXISTS (SELECT * FROM tblMessages WHERE message = @message and ip = @ip and datediff(minute,timestamp, getdate()) &lt; 10)
 BEGIN
 INSERT INTO tblMessages (ip, message, votes, latitude, longitude, location, timestamp, flags, deleted, username, parentId)
 VALUES (@ip, @message, 0, @latitude, @longitude, @location, GETDATE(), 0, 0, @username, @parentId)
 END
</code></pre>

You can see I check to see if the same user has posted the same message within 10 minutes, and if not, I post it. I still saw one dupe come through yesterday. When I checked the timestamp of both messages in the database, they were exactly the same, down to the second, so I'm guessing when this 'exists' check ran on each insert, both came back empty, so they both inserted fine (at basically the same exact time).

What's a way I can prevent this from happening correctly?

Prevent dupe records in SQL Server

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

因此，我使用此存储过程将一条消息插入到数据库中。我想防止用户在短时间内发布重复的消息，无论是偶然的还是故意的(无论是滞后的连接还是垃圾邮件发送者)。insert语句如下所示：IF NOT EXISTS (SELECT * FROM tblMessages WHERE message = @message and ip ...

问防止SQL Server中的重复记录
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问防止SQL Server中的重复记录EN