防止重复插入 MySQL [英] Preventing duplicate INSERTS MySQL

问题描述

我有一个表，它使用 INSERTS 来存储日期的整数.然后将每个日期的这些整数与 sum(COLUMN) 相加，并使用总数.因此，日期不能是唯一的，因为每个日期有很多插入.整数值本身也不能唯一.

我使用该系统来计算条目(例如在餐厅、俱乐部等).

一个人在门口拿着一个 iPad，并发送一个 INSERT 命令来输入有多少人进入(比如一组 5 将是一个整数值为 5 和当前日期的行).

如果连接不良并且 iPad 发送请求但未收到答复，则用户将尝试再次执行插入操作，从而导致重复.

添加一个诸如IDENTIFIER"之类的带有随机字符串/数字/哈希等的列是否明智，然后将是唯一的，以便如果用户重试插入并且服务器已经拥有该行，它将给出与插入成功相同的回复.

我在处理此类错误时无法浏览逻辑.如果它是对唯一列的 UPDATE 命令，这将不是问题，但我构建它的方式不太可能.

解决方案

下面的方法呢?

<块引用>

客户端:

1. 为插入请求创建 GUID
2. 发送插入请求(值 + 日期 + GUID)
3. 等待回复
4. 收到响应 --> 向用户显示确认信息(已成功完成")
5. 未收到响应 --> 请求插入响应(包括 guid)
6. 收到插入响应 --> 向用户显示确认(已成功完成")
7. 未收到插入响应 --> 重复 5.
8. 响应 ==未插入"--> 向用户显示错误消息(错误，再试一次")

<块引用>

服务器端:

1. 接收插入请求 --> 将数据(值、日期)插入表中
2. 发送确认 --> GUID，好的
3. 或:接收请求(插入 GUID?)--> 发送响应插入的 guid 是/否

I have a table that takes INSERTS to store integers for a date. These integers are then summed for each date with sum(COLUMN) and the total is used. So, date cannot be unique as there are many inserts per date. Integer value itself cannot be unique either.

I use the system to count entries (for instance at a restaurant, club, whatever).

A person holds an iPad at the door and sends an INSERT command for how many people entered (like a group of 5 would be a row with an integer value of 5 and the current date).

If there is a bad connection and the iPad sends the request but doesn't receive an answer, then the user will attempt to perform the insert again, causing duplicates.

Would it be sensible to add a column such as "IDENTIFIER" with a random string/number/hash etc. that would then be unique, so that if the user retries the insert and the server already has the row, it will give the same reply as if the insert succeeded.

I'm having trouble navigating the logic in handling errors such as these. If it were an UPDATE command on a unique column this wouldn't be an issue, but the way I built this that's not really possible.

解决方案

What about the following approach?

Client side:

1. Create GUID for insert-request
2. Send insert-request (value + date + GUID)
3. Wait for response
4. Response received --> show confirmation to user ("Completed successfully")
5. No response received --> request insert-response (incl. guid)
6. insert-response received --> show confirmation to user ("Completed successfully")
7. No insert-response received --> repeat 5.
8. Response == "not inserted" --> show error message to user ("Error, try again")

Server side:

1. Receive insert-request --> insert data (value, date) into table
2. Send confirmation --> GUID, ok
3. OR: receive request (GUID inserted?) --> send response guid inserted yes/no

这篇关于防止重复插入 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！