基于Node.js实现目录TXT文件名去重存入数据库的技术咨询

Hey there! You can absolutely build this application—let's flesh out your code and add all the necessary logic for directory scanning, duplicate checking, and safe database insertion.

Step 1: Database Table Setup #

First, create a table in your sms database to store filenames. We'll add a unique constraint on the filename column as a safety net—this enforces duplicates at the database level, even if code-level checks miss edge cases:

CREATE TABLE IF NOT EXISTS file_names (
    id INT AUTO_INCREMENT PRIMARY KEY,
    file_name VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Complete Node.js Code #

Here's the full, working code that combines your existing MySQL connection with directory scanning and duplicate validation:

const path = require('path');
const fs = require('fs').promises; // Use promise-based fs for cleaner async code
const mysql = require('mysql');

// MySQL Connection Configuration
const con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "",
    database: "sms"
});

// Connect to Database & Start Workflow
con.connect(function(err) {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL database!');
    // Replace with your target directory path
    scanTxtFilesAndStore('./your-target-folder'); 
});

// Scan directory for .txt files
async function scanTxtFilesAndStore(targetDir) {
    try {
        const files = await fs.readdir(targetDir);
        // Filter only .txt files (case-insensitive)
        const txtFiles = files.filter(file => path.extname(file).toLowerCase() === '.txt');
        
        for (const fileName of txtFiles) {
            await checkAndInsertFileName(fileName);
        }
        
        console.log('All valid .txt filenames processed!');
        con.end(); // Clean up connection after work is done
    } catch (err) {
        console.error('Error scanning directory:', err);
        con.end();
    }
}

// Check for duplicates & insert new filenames
function checkAndInsertFileName(fileName) {
    return new Promise((resolve, reject) => {
        // First, check if the filename already exists
        const checkQuery = 'SELECT id FROM file_names WHERE file_name = ?';
        con.query(checkQuery, [fileName], (err, results) => {
            if (err) return reject(err);
            
            if (results.length > 0) {
                console.log(`Filename "${fileName}" already exists—skipping.`);
                resolve();
            } else {
                // Insert new filename
                const insertQuery = 'INSERT INTO file_names (file_name) VALUES (?)';
                con.query(insertQuery, [fileName], (err, result) => {
                    if (err) {
                        // Handle race condition duplicates (if two processes insert at the same time)
                        if (err.code === 'ER_DUP_ENTRY') {
                            console.log(`Filename "${fileName}" was added by another process—skipping.`);
                            resolve();
                        } else {
                            reject(err);
                        }
                    } else {
                        console.log(`Successfully added filename: "${fileName}"`);
                        resolve();
                    }
                });
            }
        });
    });
}

Key Details Explained #

• Async/Await for File Scanning: Using fs.promises and async/await makes the directory scanning code easier to read and maintain compared to nested callbacks.
• Dual Duplicate Protection:
  1. Code-level check: We query the database first to skip existing filenames before attempting insertion.
  2. Database-level constraint: The UNIQUE index on file_name blocks duplicates even if race conditions slip past code checks. We handle the ER_DUP_ENTRY error gracefully.
• Error Handling: Every critical step includes error logging to help debug connection issues or directory access problems.
• Connection Cleanup: We close the MySQL connection after processing all files to avoid hanging connections.

Quick Notes #

• Replace ./your-target-folder with the actual absolute or relative path to your directory.
• Ensure you have the mysql package installed: run npm install mysql in your project folder if not.
• For large directories, you could add batch processing to optimize speed, but this code works smoothly for most use cases.

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