通过php脚本删除sql行中的重复值 - Delete duplicate values in a sql row via a php script?

我有这个脚本，删除令牌，如果令牌过期或坏。我的老朋友成功了，我再也没有他的联系方式了。我想通过能够删除列令牌中的重复值来改进它。

try {
    $database = new PDO("mysql:host={$db['host']};dbname={$db['name']}", $db['user'], $db['pass']);
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo $e->getMessage();
}
function check_token($token){
    $headers = get_headers("https://graph.facebook.com/me?access_token={$token}");
    if($headers[0] == "HTTP/1.0 200 OK" or $headers[0] == "HTTP/1.1 200 OK"){
        return true;
    } else {
        return false;
    }
}
$tokens = $database->query("SELECT * FROM pinodex_autoliker");
$bad_tokens = 0;
foreach($tokens as $row){
    if(!check_token($row['token'])){
        $token = $database->prepare("DELETE FROM pinodex_autoliker WHERE token = :token");
        $token->execute(
            array(
                "token" => $row['token']
            )
        );
        $bad_tokens++;
    }
}
header("Content-type: text/plain");
echo "TOKEN CLEAN DONE! REMOVED {$bad_tokens} BAD TOKENS FROM THE DATABASE.";
exit;

有一种纯SQL方法，它将在1个SQL查询中删除重复项

这将删除所有的行，如果行id不是这个标记最小的

 DELETE FROM pinodex_autoliker WHERE id NOT IN ( SELECT * FROM ( select min(pa.id) from pinodex_autoliker pa group by token ) pa2 )

两个查询的结果应该相同。

我认为这比选择重复的行，导入到php中，然后对每个id执行delete查询要好，想象一下，如果有100000个坏令牌，你必须来回执行100000次，而没有任何意义

对于删除重复的令牌，您需要使用COUNT和Group by编写查询。

select id,count(token) as tokenCount,token from pinodex_autoliker group by token

then用DELETE Query遍历输出。

foreach($recordSet as $rs) { 
$token = $rs['token'];
  $smt = $database->prepare("DELETE FROM pinodex_autoliker WHERE token = :token");
    $smt->execute(
        array(
            ":token" => $token
        )
    );
}

上面的查询将给出如下输出:

id  tokenCount  token
2   2           325gqsdgrgrqgrgrg
1   2           3b3kj4b31jbj421b431fdsafdfdfs
4   1           n3rlk3nrkl213nrk1nrk1rk

SQL演示

用于添加删除重复令牌代码。

try {
    $database = new PDO("mysql:host={$db['host']};dbname={$db['name']}", $db['user'], $db['pass']);
    $database->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    echo $e->getMessage();
}
function check_token($token)
{
    $headers = get_headers("https://graph.facebook.com/me?access_token={$token}");
    if ($headers[0] == "HTTP/1.0 200 OK" or $headers[0] == "HTTP/1.1 200 OK") {
        return true;
    } else {
        return false;
    }
}
$tokens = $database->query("SELECT * FROM pinodex_autoliker");
$bad_tokens = 0;
foreach ($tokens as $row) {
    if (!check_token($row['token'])) {
        $token = $database->prepare("DELETE FROM pinodex_autoliker WHERE token = :token");
        $token->execute(
            array(
                ":token" => $row['token']
            )
        );
        $bad_tokens++;
    }
}

// Delete duplicate tokens
$duplicatetokens = $database->query("select id,count(token) as tokenCount,token from pinodex_autoliker group by token");
$duplicate_tokens = 0;
foreach ($duplicatetokens as $dprow) {
    $tokenCol = $dprow['token'];
    $tokenCount = $dprow['tokenCount'];
    if ($tokenCount > 1) {
        $smt = $database->prepare("DELETE FROM pinodex_autoliker WHERE token = :token");
        $smt->execute(
            array(
                ":token" => $tokenCol
            )
        );
        $duplicate_tokens++;
    }
}

header("Content-type: text/plain");
echo "DELETED {$duplicate_tokens} Duplicate TOKENS FROM THE DATABASE. <br />";
echo "TOKEN CLEAN DONE! REMOVED {$bad_tokens} BAD TOKENS FROM THE DATABASE.";
exit;

你不需要编辑这个，

WHERE token = :token足以删除具有此令牌值的所有行。