PHP：基于指定列检查二维数组重复项并定位重复行索引的高效实现方案

Hey there! Let's break down how to fix your duplicate-checking bottleneck efficiently—no nested loops required. Your O(n²) approach is definitely the culprit for slowdowns with large Excel files, so we'll use hash-based strategies (O(n*m) time complexity, where m is the number of columns you're checking) which are way more scalable.

1. Get Unique Rows Based on Specific Columns #

Instead of comparing every row to every other row, we'll build a unique key from your target columns (given name + birth date) and track which keys we've already seen as we iterate through the array once.

Here's a PHP function that does this:

function getUniqueRowsByColumns($data, $targetColumns) {
    $seenKeys = [];
    $uniqueRows = [];

    foreach ($data as $row) {
        // Build a unique key using the specified columns
        $keyComponents = [];
        foreach ($targetColumns as $col) {
            // Use a fallback if the column is missing (adjust based on your needs)
            $keyComponents[] = $row[$col] ?? '';
        }
        // Use a separator that won't appear in your data (or serialize for safety)
        $uniqueKey = implode('|||', $keyComponents);

        if (!isset($seenKeys[$uniqueKey])) {
            $seenKeys[$uniqueKey] = true;
            $uniqueRows[] = $row;
        }
    }

    return $uniqueRows;
}

How to use it: #

If your Excel data uses keys like given_name and birth_date for the target columns:

$targetCols = ['given_name', 'birth_date'];
$uniqueData = getUniqueRowsByColumns($yourExcel2DArray, $targetCols);

Notes: #

• The ||| separator is chosen to avoid conflicts (pick something that will never appear in your name/birth date values). If you're worried about collisions, use serialize($keyComponents) instead of implode—it's slightly slower but 100% collision-proof.
• This runs in O(n*m) time, which is way faster than O(n²) for large n (like thousands of rows).

2. Locate Duplicate Row Indices #

To track which rows are duplicates, we'll modify the hash approach to store arrays of row indices instead of just a boolean flag. This lets us collect all rows that share the same target column values.

Here's the function:

function findDuplicateRowIndices($data, $targetColumns) {
    $keyToIndices = [];
    $duplicateResults = [];

    foreach ($data as $rowIndex => $row) {
        $keyComponents = [];
        foreach ($targetColumns as $col) {
            $keyComponents[] = $row[$col] ?? '';
        }
        $uniqueKey = implode('|||', $keyComponents);

        // Add the current row index to the key's list
        if (!isset($keyToIndices[$uniqueKey])) {
            $keyToIndices[$uniqueKey] = [];
        }
        $keyToIndices[$uniqueKey][] = $rowIndex;
    }

    // Filter out keys that only have one row (no duplicates)
    foreach ($keyToIndices as $key => $indices) {
        if (count($indices) > 1) {
            // Parse the key back into readable person info
            list($givenName, $birthDate) = explode('|||', $key);
            $duplicateResults[] = [
                'person' => "$givenName (Date of Birth: $birthDate)",
                'duplicate_row_indices' => $indices
            ];
        }
    }

    return $duplicateResults;
}

How to use it: #

$duplicates = findDuplicateRowIndices($yourExcel2DArray, $targetCols);

// Output the results to your user
foreach ($duplicates as $dup) {
    echo "⚠️ {$dup['person']} has duplicate entries in rows: " . implode(', ', $dup['duplicate_row_indices']) . "<br>";
}

Why this works: #

We only iterate through the array once, storing indices for each unique key. At the end, we just check which keys have multiple indices—those are your duplicates. This is just as efficient as the unique rows function, and gives you the exact row numbers to report to users.

Bonus: Memory Optimization #

If you're dealing with extremely large arrays, you can combine the duplicate check and data validation into a single loop. This way you don't have to process the array multiple times, which saves memory and time. For example:

function validateAndCheckDuplicates($data, $targetCols, $validationRules) {
    $seenKeys = [];
    $errors = [];
    $validRows = [];

    foreach ($data as $rowIndex => $row) {
        // First run your format/value validation
        $validationError = validateRow($row, $validationRules); // Implement your existing validation here
        if ($validationError) {
            $errors[] = "Row $rowIndex: {$validationError} (Person: {$row['given_name']})";
            continue;
        }

        // Then check for duplicates
        $keyComponents = [];
        foreach ($targetCols as $col) {
            $keyComponents[] = $row[$col];
        }
        $uniqueKey = implode('|||', $keyComponents);

        if (isset($seenKeys[$uniqueKey])) {
            $errors[] = "Row $rowIndex: Duplicate entry for {$row['given_name']} (DOB: {$row['birth_date']})—already exists in row {$seenKeys[$uniqueKey]}";
        } else {
            $seenKeys[$uniqueKey] = $rowIndex;
            $validRows[] = $row;
        }
    }

    return [
        'valid_rows' => $validRows,
        'errors' => $errors
    ];
}

This way you handle validation and duplicate checking in one pass, minimizing memory overhead.

内容的提问来源于stack exchange，提问作者Razgriz