MySQL行插入两次 [英] MySQL Rows Inserted Twice
问题描述
- 基本上，我需要保存从远程数据源检索的一些信息的本地副本。所以当用户查看来自远程来源的信息时，我检查一下我是否拥有我需要存储的信息的本地副本。如果我没有本地副本，则添加该信息的记录。
- 我遇到的问题是每次我会说20-30插入我得到一个副本。我使用NOW功能跟踪插入和更新时间，并且两个记录似乎都被同时插入。
这是我的PHP代码，任何帮助将不胜感激，我被困惑：
//我们有位置，看看我们是否有该位置的本地记录
$ idLocation = locationID_for_factualID（$ factual_id）;
if（！$ idLocation）{
//我们没有关于位置的本地信息，添加
$ mysqli = open_mysql_connection（）;
$ stmt = $ mysqli-> prepare（INSERT INTO Location（
factual_id，
dateAdded，
dateModified，
locationName，
latitude，
经度）
VALUES（？，NOW（），NOW（），？，？，？））;
if（$ stmt）{
$ stmt-> bind_param（ssdd，$ factual_id，$ location [name]，doubleval（$ location [latitude]），doubleval位置[ 经度]））;
$ stmt-> execute（）;
//检查位置是否已添加
if（$ stmt-> affected_rows == 1）{
$ idLocation = locationID_for_factualID（$ factual_id）;
}
$ stmt-> close（）;
$ mysqli-> close（）;
}
else {
return FALSE;
}
}
这里有两行似乎被插回返回：
idLocation | factual_id | dateAdded | dateModified | locationName |纬度|经度
520 | 5f79360f-330f-4035-ae75-e872ea14cfdd | 2013-04-09 14:36:55 | 2013-04-09 14:36:55 | Quiznos | 40.1802 | -74.0258
521 | 5f79360f-330f-4035-ae75-e872ea14cfdd | 2013-04-09 14:36:55 | 2013-04-09 14:36:55 | Quiznos | 40.1802 | -74.0258
虽然我看不到你的实现 locationID_for_factualID ，我很确定它正在做一些类似 SELECT idLocation ... WHERE factual_id ='$ factual_id'不要忘记使用查询参数！）。
我可以想象你有一个竞争条件，即两个请求使用相同的输入数据调用相同的脚本几乎相同的时间，然后调用函数 locationID_for_factualID ，并发现$ code> $ idLocation == false 。然后，两个请求都执行 INSERT ，最后得到一个重复的记录。
此问题的标准解决方案是使用交易。看看这是如何完成的 here 。重要的部分是在 locationID_for_factualID 和 INSERT SELECT c>具有相同的交易，即在 START TRANSACTION 和 COMMIT 之间。您可能需要更改 locationID_for_factualID 的实现，以使用与以下 INSERT 相同的数据库连接。 p>
除此之外，您可能需要在factual_id列上创建一个 UNIQUE INDEX 。这将阻止相同的factual_id发生不止一次，如果尝试插入重复，则允许 INSERT 语句失败。
I am having some difficulty with MySQL seeming to insert a row twice. - Basically, I need to save a local copy of some information that is retrieved from a remote data source. So when a user views information that is from the remote source, I check to see if I have a local copy of the information I need to store. If I don't have a local copy I add a record of that information. - The issue I am having is that every I would say 20-30 inserts I get a duplicate. I keep track of the insert and update times using the NOW function, and both records seem to be inserted at the same time. Here is my PHP code, any help would be very appreciated, I am stumped:
// We have the location, see if we have a local record for that location
$idLocation = locationID_for_factualID($factual_id);
if(!$idLocation) {
// We do not have local information about the location, add it
$mysqli = open_mysql_connection();
$stmt = $mysqli->prepare("INSERT INTO Location (
factual_id,
dateAdded,
dateModified,
locationName,
latitude,
longitude)
VALUES (?, NOW(), NOW(), ?, ?, ?)");
if($stmt) {
$stmt->bind_param("ssdd",$factual_id, $location["name"], doubleval($location["latitude"]), doubleval($location["longitude"]));
$stmt->execute();
// Check if the location was added
if($stmt->affected_rows == 1){
$idLocation = locationID_for_factualID($factual_id);
}
$stmt->close();
$mysqli->close();
}
else {
return FALSE;
}
}
Here are two rows that seem to be inserted back to back:
idLocation | factual_id | dateAdded | dateModified | locationName | latitude | longitude
520 | 5f79360f-330f-4035-ae75-e872ea14cfdd | 2013-04-09 14:36:55 | 2013-04-09 14:36:55 | Quiznos | 40.1802 | -74.0258
521 | 5f79360f-330f-4035-ae75-e872ea14cfdd | 2013-04-09 14:36:55 | 2013-04-09 14:36:55 | Quiznos | 40.1802 | -74.0258
Although I don't see your implementation of locationID_for_factualID, I'm pretty sure it's doing something like SELECT idLocation ... WHERE factual_id = '$factual_id' (don't forget to use query params!).
I can imagine you're having a race condition, i.e. two requests callign the same script with the same input data at almost the same time, then calling the function locationID_for_factualID and both finding that $idLocation == false. Then, both requests execute the INSERT and you end up with a duplicate record.
The standard solution for this issue is to use transactions. Have a look here how this is done. The important part is wrapping the SELECT inside of locationID_for_factualID AND the INSERT with the same transaction, i.e. between START TRANSACTION and COMMIT. You'll probably have to change the implementation of locationID_for_factualID to use the same DB connection as the following INSERT.
As an aside, you might want to create a UNIQUE INDEX on column factual_id. This will prevent the same factual_id from occurring more than once by letting the INSERT statement fail if it tries to insert a duplicate.
这篇关于MySQL行插入两次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
