咨询：SQL查询50+无关联含Name字段表的优化方案及JS存储

Hey there! Your current approach of looping through a table name array and running individual SELECT queries works, but we can make this more efficient, maintainable, and secure. Let's break down better approaches:

1. Automate Table Name Discovery (Ditch Manual Arrays) #

Hardcoding a table name array gets outdated fast when tables are added or removed. Instead, use your database's system catalog to dynamically fetch all tables that have a Name column. This eliminates manual maintenance entirely.

Example (MySQL): #

First, query the information schema to get valid tables:

SELECT table_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE column_name = 'Name' 
  AND table_schema = DATABASE(); -- Target your current database

In your JavaScript code, run this query first to get the table list, then use that list for your data queries. Any new table with a Name column will automatically be included.

2. Parallelize Queries for Faster Results #

Running queries one after another (serial loop) can drag with 50+ tables. Use Promise.all() to execute queries in parallel—just make sure your database connection pool can handle the concurrency (adjust pool size if you hit connection limits).

Example (Node.js with mysql2): #

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

async function fetchJohnData() {
  const connection = await mysql.createConnection({ /* your database config */ });
  
  // Step 1: Fetch all tables with a Name column
  const [tables] = await connection.execute(`
    SELECT table_name 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE column_name = 'Name' 
      AND table_schema = DATABASE()
  `);
  const tableNames = tables.map(row => row.table_name);

  // Step 2: Run all queries in parallel
  const queryPromises = tableNames.map(async (tableName) => {
    // Sanitize table name to block SQL injection (critical!)
    if (!/^[a-zA-Z0-9_]+$/.test(tableName)) {
      throw new Error(`Invalid table name: ${tableName}`);
    }
    const [rows] = await connection.execute(`
      SELECT * FROM \`${tableName}\` WHERE Name = ?
    `, ['John']);
    return { [tableName]: rows };
  });

  // Step 3: Combine results into a single object
  const results = await Promise.all(queryPromises);
  const finalArr = Object.assign({}, ...results);

  await connection.end();
  return finalArr;
}

// Usage
fetchJohnData().then(arr => console.log(arr)).catch(err => console.error(err));

3. Standardize Your Result Format #

Instead of storing results as strings, use arrays: empty arrays for tables with no matches, arrays of row objects for tables with hits. This makes downstream code way more consistent—you won't have to handle string vs array checks later.

4. Critical Security Note: Block SQL Injection #

Never directly concatenate untrusted table names into queries! When using dynamic table names:

• Validate against a trusted whitelist (like the tables fetched from INFORMATION_SCHEMA)
• Use regex to sanitize names (as shown in the example)
• Avoid accepting arbitrary table names from untrusted sources

When to Stick with Your Original Approach? #

If your table list is fixed and never changes, hardcoding the array is totally fine—just remember to update it if tables are added/removed. The dynamic approach is better for environments where tables evolve regularly.

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