代码有时会创建重复的行？(Code sometimes creates duplicate rows?)

我有以下代码：

$sth = $dbh->prepare("SELECT * FROM stats WHERE player_id = :player_id AND data_type = :data_type"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':data_type', $total_time_data_type_id); $sth->execute(); $result = $sth->fetch(); if(!$result){ $sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0')"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':offset', $offset); $sth->bindParam(':total_time_data_type_id', $total_time_data_type_id); $sth->execute(); if(!$sth){ return false; } $sth = $dbh->prepare("SELECT * FROM stats WHERE player_id = :player_id AND data_type = :data_type"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':data_type', $total_time_data_type_id); $sth->execute(); $result = $sth->fetch(); if(!$result){ return false; } }else{ $sth = $dbh->prepare("UPDATE stats SET ....."); //Do more stuff }

现在，偶尔会创建重复的行（大约600行，有23个重复行）。这让我感到困惑，因为在插入行之前我明确地检查了具有相同player_id和data_type 。

对于相同的player_id或data_type ，可以存在多行，但不能相同。

IE这将是有效的：

ID | PLAYER_ID | DATA_TYPE ---|-----------|---------- 1 | 15 | 7 2 | 15 | 18 3 | 92 | 7 4 | 115 | 23

虽然这不会：

ID | PLAYER_ID | DATA_TYPE ---|-----------|---------- 1 | 15 | 7 2 | 32 | 18 3 | 15 | 7 4 | 115 | 23

因此，我不能简单地将player_id字段声明为唯一。

我唯一可以想到的可能是导致这个问题的事实是上面的代码片段在一个平均大约115次迭代的foreach循环内部，并且这个代码在几秒钟内再次被调用。有没有办法以编程方式阻止这种情况？

I have the following code:

$sth = $dbh->prepare("SELECT * FROM stats WHERE player_id = :player_id AND data_type = :data_type"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':data_type', $total_time_data_type_id); $sth->execute(); $result = $sth->fetch(); if(!$result){ $sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0')"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':offset', $offset); $sth->bindParam(':total_time_data_type_id', $total_time_data_type_id); $sth->execute(); if(!$sth){ return false; } $sth = $dbh->prepare("SELECT * FROM stats WHERE player_id = :player_id AND data_type = :data_type"); $sth->bindParam(':player_id', $player_id); $sth->bindParam(':data_type', $total_time_data_type_id); $sth->execute(); $result = $sth->fetch(); if(!$result){ return false; } }else{ $sth = $dbh->prepare("UPDATE stats SET ....."); //Do more stuff }

Now, occasionally this is creating duplicate rows (out of ~600 rows, there are 23 duplicates). This confuses me since before inserting the row I explicitly check for a row with the same player_id and data_type.

Multiple rows can exist for either the same player_id or data_type, but not the same of each.

I.E. This would be valid:

ID | PLAYER_ID | DATA_TYPE ---|-----------|---------- 1 | 15 | 7 2 | 15 | 18 3 | 92 | 7 4 | 115 | 23

While this would not:

ID | PLAYER_ID | DATA_TYPE ---|-----------|---------- 1 | 15 | 7 2 | 32 | 18 3 | 15 | 7 4 | 115 | 23

Because of this I can't simply declare the player_id field as unique.

The only thing I can think of that may be causing this problem is the fact that the code snippet above is inside of a foreach loop averaging around 115 iterations, and that this code is called again within seconds. Is there a way to prevent this programmatically?

最满意答案

感谢大家的帮助，特别是@ nikita2206。这就是我解决问题的方法：

丢弃的列ID是主键。在（Player_id，data_type）上创建了新的主键。更改INSERT语句：

从

$sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0')");

至

$sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0') ON DUPLICATE KEY UPDATE player_id = player_id");

通过几次非常快速地调用代码进行测试，并且没有创建重复项。

Thanks to everyone for your help, especially @nikita2206. This is how I solved my problem:

Dropped column ID which was Primary Key. Created new primary key on (Player_id, data_type). Changed INSERT Statement:

From

$sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0')");

to

$sth = $dbh->prepare("INSERT INTO stats (player_id, offset, created, modified, last_check, data_type, data) VALUES (:player_id, :offset, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), '1', :total_time_data_type_id, '0') ON DUPLICATE KEY UPDATE player_id = player_id");

Tested by calling the code several times very quickly and no duplicates were created.

更多推荐