MySQL - 查詢重複記錄

要透過 PHP 程式查詢重複記錄，我們需要使用 GROUP BY 子句按列對所有行進行分組，然後使用 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.
');
//let's create a table
$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...!\n");
}
//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...!\n");
}
$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...!\n");
}
$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...!\n");
}
$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...!\n");
}
$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...!\n");
}
//display the table records
$sql = "SELECT * FROM PETS";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    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("\n");
    }
}
//now lets group the all rows to find duplicate records...
$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: \n");
    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("\n");
    }
}
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