如何根据给定产品ID查询其所在MySQL表名并在PHP中复用

1. 构造查询语句定位表名 #

假设你的产品表（比如products_electronics、products_clothing）都包含唯一的product_id字段，可以用UNION ALL遍历所有产品表，返回包含目标ID的表名：

SELECT 'products_electronics' AS table_name FROM products_electronics WHERE product_id = :product_id
UNION ALL
SELECT 'products_clothing' AS table_name FROM products_clothing WHERE product_id = :product_id
UNION ALL
SELECT 'products_furniture' AS table_name FROM products_furniture WHERE product_id = :product_id

只有包含目标ID的表会返回对应表名，无匹配的表不会产生结果行。

2. 用PDO获取表名并存入PHP变量 #

基于已初始化的PDO连接，执行上述查询并提取表名：

$targetProductId = 123; // 替换为你的目标产品ID

// 构造查询SQL
$sql = "
    SELECT 'products_electronics' AS table_name FROM products_electronics WHERE product_id = :product_id
    UNION ALL
    SELECT 'products_clothing' AS table_name FROM products_clothing WHERE product_id = :product_id
    UNION ALL
    SELECT 'products_furniture' AS table_name FROM products_furniture WHERE product_id = :product_id
";

$stmt = $pdo->prepare($sql);
$stmt->execute([':product_id' => $targetProductId]);
$tableName = $stmt->fetchColumn(); // 获取返回的表名字段

if (!$tableName) {
    die("未找到该产品ID对应的表");
}

3. 安全复用表名执行后续查询 #

PDO占位符无法用于表名，所以必须通过白名单验证避免SQL注入，再构造查询：

// 定义允许的产品表白名单
$allowedTables = ['products_electronics', 'products_clothing', 'products_furniture'];

if (!in_array($tableName, $allowedTables)) {
    die("非法的表名");
}

// 执行后续查询
$selectSql = "SELECT * FROM `$tableName` WHERE product_id = :product_id";
$selectStmt = $pdo->prepare($selectSql);
$selectStmt->execute([':product_id' => $targetProductId]);
$productDetails = $selectStmt->fetch(PDO::FETCH_ASSOC);

print_r($productDetails);

补充说明 #

如果产品表数量较多，可以先查询INFORMATION_SCHEMA.TABLES获取所有产品表列表，再动态生成UNION语句，但上述方案是最直接简易的实现方式。

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