MySQL - 查找重复记录

要通过 PHP 程序查找重复记录，我们需要使用 GROUPBY 子句按列对所有行进行分组，然后使用 COUNT 函数来统计重复项。为此，我们需要使用 mysqli 函数 query() 执行 SELECT 语句，如下所示 -

$sql = "SELECT SALARY, COUNT(SALARY) AS "COUNT" FROM CUSTOMERS GROUP BY SALARY ORDER BY SALARY";
$mysqli->query($sql);

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
//让我们创建一个表
$sql = "CREATE TABLE Pets (ID int,DOG_NAME varchar(30) not null,AGE int not null,OWNER_NAME varchar(30) not null)";
if($mysqli->query($sql)){
    printf("Pets table created successfully...!
");
}
//now lets insert some duplicate records;
$sql = "INSERT IGNORE INTO Pets(ID, DOG_NAME, AGE, OWNER_NAME) VALUES(1, 'Fluffy', 1, 'Micheal')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!
");
}
$sql = "INSERT IGNORE INTO Pets(ID, DOG_NAME, AGE, OWNER_NAME) VALUES(1, 'Fluffy', 1, 'Micheal')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!
");
}
$sql = "INSERT IGNORE INTO Pets(ID, DOG_NAME, AGE, OWNER_NAME) VALUES(2, 'Harry', 2, 'Jack')";
if($mysqli->query($sql)){
    printf("Third records inserted successfully...!
");
}
$sql = "INSERT IGNORE INTO Pets(ID, DOG_NAME, AGE, OWNER_NAME) VALUES(3, 'Sheero', 1, 'Rose')";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully...!
");
}
$sql = "INSERT IGNORE INTO Pets(ID, DOG_NAME, AGE, OWNER_NAME) VALUES(4, 'Simba', 2, 'Rahul')";
if($mysqli->query($sql)){
    printf("Fifth record inserted successfully...!
");
}
//显示表记录
$sql = "SELECT * FROM PETS";
if($result = $mysqli->query($sql)){
    printf("Table records: 
");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, DOG_NAME %s, AGE: %d,OWNER_NAME: %s ", 
            $row['ID'], 
            $row['DOG_NAME'], 
            $row['AGE'], 
            $row['OWNER_NAME']);
            printf("
");
    }
}
//现在让我们对所有行进行分组以查找重复的记录...
$sql = "SELECT ID, DOG_NAME, AGE, OWNER_NAME, COUNT(*) AS 'Count' FROM PETS GROUP BY ID, DOG_NAME, OWNER_NAME ORDER BY ID";
if($result = $mysqli->query($sql)){
    printf("Table duplicate records: 
");
    while($row = mysqli_fetch_array($result)){
        printf("ID: %d, DOG_NAME %s, AGE: %d, OWNER_NAME: %s ", 
            $row['ID'], 
            $row['DOG_NAME'], 
            $row['AGE'], 
            $row['OWNER_NAME'], 
            $row['Count']);
            printf("
");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

输出

获得的输出如下所示 -

Pets table created successfully...!
First record inserted successfully...!
Second record inserted successfully...!
Third records inserted successfully...!
Fourth record inserted successfully...!
Fifth record inserted successfully...!
Table records:
ID: 1, DOG_NAME Fluffy, AGE: 1,OWNER_NAME: Micheal
ID: 1, DOG_NAME Fluffy, AGE: 1,OWNER_NAME: Micheal
ID: 2, DOG_NAME Harry, AGE: 2,OWNER_NAME: Jack
ID: 3, DOG_NAME Sheero, AGE: 1,OWNER_NAME: Rose
ID: 4, DOG_NAME Simba, AGE: 2,OWNER_NAME: Rahul
Table duplicate records:
ID: 1, DOG_NAME Fluffy, AGE: 1,OWNER_NAME: Micheal
ID: 2, DOG_NAME Harry, AGE: 2,OWNER_NAME: Jack
ID: 3, DOG_NAME Sheero, AGE: 1,OWNER_NAME: Rose
ID: 4, DOG_NAME Simba, AGE: 2,OWNER_NAME: Rahul