MySQL实现同名文件自动递增命名（基于document_history表）

Hey there! Let's work through how to implement this auto-renaming logic for your file uploads, matching the pattern in your document_history table. Here's a step-by-step breakdown with code examples:

Step 1: Parse the Uploaded File Name #

First, we need to split the uploaded file into its base name (without any numeric suffix or extension) and its file extension. This handles both cases: when the user uploads doc.docx or even doc(2).docx (we still want to base the count on the core doc name).

For example, using a regex to extract these parts:

$uploadedFileName = 'doc.docx'; // Or the uploaded file's name from your form

// Extract base name and extension, even if the file has a numeric suffix
if (preg_match('/^(.+?)(\(\d+\))?\.([^.]+)$/', $uploadedFileName, $matches)) {
    $baseName = $matches[1]; // Gets "doc" for both "doc.docx" and "doc(2).docx"
    $extension = '.' . $matches[3]; // Gets ".docx"
} else {
    // Fallback for files without an extension
    $baseName = $uploadedFileName;
    $extension = '';
}

Step 2: Count Existing Matching Files #

Next, query your document_history table to count how many files already exist with the same base name (either as the exact name or with (n) suffixes). We'll use a regex to match the pattern:

SELECT COUNT(*) AS file_count
FROM document_history
WHERE name REGEXP CONCAT('^', ?, '(\\([0-9]+\\))?', ?, '$');

In parameterized queries (to avoid SQL injection), you'd pass $baseName and $extension as the two parameters. For your example with doc.docx, this query will count doc.docx, doc(1).docx, doc(2).docx — giving you a count of 3.

Step 3: Generate the New File Name #

Use the count to create the new name:

• If the count is 0, use the original uploaded name.
• If the count is greater than 0, append (count) to the base name before the extension.

// After getting $file_count from the database query
if ($file_count === 0) {
    $newFileName = $uploadedFileName;
} else {
    $newFileName = "{$baseName}({$file_count}){$extension}";
}
// For your example, this would produce "doc(3).docx"

Step 4: Handle Concurrency to Avoid Duplicates #

A critical edge case: if two users upload the same file at the same time, both might get the same count and generate duplicate file names. To fix this, wrap the count and insert operations in a database transaction with row locking to make the process atomic.

Here's how that looks in MySQL with PHP:

// Connect to your database (adjust credentials as needed)
$pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    $pdo->beginTransaction();

    // Lock the matching rows to prevent concurrent reads/writes
    $stmt = $pdo->prepare("
        SELECT COUNT(*) AS file_count
        FROM document_history
        WHERE name REGEXP CONCAT('^', :base, '(\\([0-9]+\\))?', :ext, '$')
        FOR UPDATE;
    ");
    $stmt->execute([
        ':base' => $baseName,
        ':ext' => $extension
    ]);
    $file_count = $stmt->fetchColumn();

    // Generate the new file name
    $newFileName = $file_count === 0 
        ? $uploadedFileName 
        : "{$baseName}({$file_count}){$extension}";

    // Insert the new record into document_history
    $stmt = $pdo->prepare("
        INSERT INTO document_history (document_id, name, modified, user_id)
        VALUES (:doc_id, :name, NOW(), :user_id);
    ");
    $stmt->execute([
        ':doc_id' => 83, // Replace with your new document's ID
        ':name' => $newFileName,
        ':user_id' => 1 // Replace with the uploading user's ID
    ]);

    $pdo->commit();
    echo "Success! New file name: {$newFileName}";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

Notes to Consider #

• Case Sensitivity: If you want to treat Doc.DOCX and doc.docx as the same, modify the query to use LOWER(name) and pass lowercase versions of $baseName and $extension.
• Edge Cases: Handle files with multiple dots (like my.report.docx) — the regex we used will correctly extract my.report as the base name and docx as the extension.
• Database Indexes: To speed up the regex query, consider adding an index on the name column (though regex queries can still be slow on large datasets; if you have millions of records, you might want to split the base name and suffix into separate columns for faster counting).

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