基于JavaScript实现两张数据库表的列值与行数据对比验证需求

Alright, let's tackle this problem. The core challenge here is mapping Table1's ListType values to Table2's column names, then validating that ListValue matches the corresponding value in Table2. I'll walk through a concrete example with JavaScript, including sample data and edge case handling.

Step 1: Simulate Your Table Data #

First, let's define sample data that mirrors your table structure. This makes it easy to test the logic before connecting to a real database:

// Table1: Each row links a column name (ListType) to a value to validate (ListValue)
const table1Data = [
  { id: 1, ListType: "userName", ListValue: "john_doe" },
  { id: 2, ListType: "email", ListValue: "john@example.com" },
  { id: 3, ListType: "age", ListValue: "30" },
  { id: 4, ListType: "invalidColumn", ListValue: "test" } // Test for non-existent column
];

// Table2: The target row we want to validate against (adjust based on your use case)
const table2TargetRow = {
  userName: "john_doe",
  email: "john@example.com",
  age: 30, // Note: This is a number, while Table1's ListValue is a string
  address: "123 Main St"
};

Step 2: The Validation Function #

This function will loop through Table1's records, map ListType to Table2's columns, and check for matches. It handles data type mismatches and missing columns gracefully:

function validateTableValues(table1Records, table2Row) {
  const validationResults = [];

  table1Records.forEach(record => {
    const { id, ListType, ListValue } = record;

    // First, check if the column exists in Table2
    if (!(ListType in table2Row)) {
      validationResults.push({
        id,
        status: "ERROR",
        message: `Table2 has no column named '${ListType}'`
      });
      return;
    }

    // Normalize data types to avoid false mismatches (e.g., number vs string)
    const table2Value = String(table2Row[ListType]);
    const isMatch = table2Value === ListValue;

    validationResults.push({
      id,
      status: isMatch ? "PASS" : "FAIL",
      message: isMatch
        ? `Value matches: ${ListValue}`
        : `Mismatch: Table1 = ${ListValue}, Table2 = ${table2Row[ListType]}`
    });
  });

  return validationResults;
}

// Run the validation and log results
const results = validateTableValues(table1Data, table2TargetRow);
console.log("Validation Results:");
results.forEach(res => console.log(`[${res.status}] Record ${res.id}: ${res.message}`));

When you run this, you'll get output like:

Validation Results:
[PASS] Record 1: Value matches: john_doe
[PASS] Record 2: Value matches: john@example.com
[PASS] Record 3: Value matches: 30
[ERROR] Record 4: Table2 has no column named 'invalidColumn'

Step 3: Adapt to Real Databases (Node.js Example) #

If you're working with a real database (like MySQL or PostgreSQL), you'll first fetch the data, then run the validation. Here's a quick example using mysql2 (adjust for your DB driver):

const mysql = require('mysql2/promise');

async function runDatabaseValidation() {
  // Connect to your database
  const connection = await mysql.createConnection({
    host: 'your-db-host',
    user: 'your-db-user',
    password: 'your-db-password',
    database: 'your-db-name'
  });

  // Fetch data from Table1
  const [table1Rows] = await connection.execute('SELECT id, ListType, ListValue FROM Table1');
  
  // Fetch the target row from Table2 (adjust the WHERE clause to match your needs)
  const [table2Rows] = await connection.execute('SELECT * FROM Table2 WHERE id = ?', [1]);
  const targetRow = table2Rows[0];

  // Run validation
  const results = validateTableValues(table1Rows, targetRow);

  // Output or save results
  console.log("Database Validation Results:");
  results.forEach(res => console.log(`[${res.status}] Record ${res.id}: ${res.message}`));

  // Close the connection
  await connection.end();
}

// Execute the function (handle errors as needed)
runDatabaseValidation().catch(err => console.error("Validation failed:", err));

Key Edge Cases to Consider #

• Data Type Mismatches: Table1's ListValue might be a string while Table2's column is a number/date. Always normalize types (like converting to strings) before comparing.
• Missing Columns: Table1 might have a ListType that doesn't exist in Table2—make sure your function catches this and reports it as an error.
• Multiple Rows in Table2: If you need to validate against multiple rows in Table2, adjust the logic to loop through Table2 rows and match on a shared key (like a user ID) with Table1.

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