如何通过关联数组生成更简洁的SQL虚拟/临时表查询语句？

问题背景 #

通过PHP拼接UNION ALL生成虚拟临时表的方式，在数据量较大时（如100条）会产生冗长的SQL语句，执行效率低下。需要一种单查询、无需创建实体/临时表的更简洁高效实现方式。

方案1：利用JSON_TABLE（MySQL 8.0+/MariaDB 10.6+） #

这是最高效简洁的方案，将PHP数组转为JSON字符串，通过JSON_TABLE函数直接解析为表结构，无需拼接大量UNION ALL。

PHP代码实现 #

$array = [
    ['id' => 1, 'name' => 'one'],
    ['id' => 2, 'name' => 'two'],
    ['id' => 3, 'name' => 'three']
];

// 转义JSON字符串，防止SQL注入（根据数据库扩展选择合适转义方式）
$json = json_encode($array);
// mysqli扩展示例
$escapedJson = mysqli_real_escape_string($yourDbConnection, $json);
// PDO扩展示例
// $escapedJson = $pdo->quote($json);

$sql = "SELECT jt.id AS col1, jt.name AS col2
FROM JSON_TABLE(
    '{$escapedJson}',
    '$[*]' COLUMNS(
        id INT PATH '$.id',
        name VARCHAR(255) PATH '$.name'
    )
) AS jt;";

echo $sql;

生成的SQL语句 #

SELECT jt.id AS col1, jt.name AS col2
FROM JSON_TABLE(
    '[{"id":1,"name":"one"},{"id":2,"name":"two"},{"id":3,"name":"three"}]',
    '$[*]' COLUMNS(
        id INT PATH '$.id',
        name VARCHAR(255) PATH '$.name'
    )
) AS jt;

方案2：使用VALUES行构造器（MySQL 8.0.19+/MariaDB 10.3+） #

利用数据库原生的行构造器，直接一次性构造所有数据行，语法简洁且执行效率优于多次UNION ALL。

PHP代码实现 #

$array = [
    ['id' => 1, 'name' => 'one'],
    ['id' => 2, 'name' => 'two'],
    ['id' => 3, 'name' => 'three']
];

$rows = [];
foreach ($array as $item) {
    // 强制转换id为整数，转义name字符串避免注入
    $id = (int)$item['id'];
    $name = mysqli_real_escape_string($yourDbConnection, $item['name']);
    $rows[] = "($id, '$name')";
}
$rowsStr = implode(', ', $rows);

$sql = "SELECT col1, col2 FROM (VALUES {$rowsStr}) AS t(col1, col2);";

echo $sql;

生成的SQL语句 #

SELECT col1, col2 FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);

方案3：兼容旧版本MySQL（5.x及以下） #

如果必须兼容无JSON_TABLE/VALUES支持的旧版本，优化UNION ALL拼接逻辑，同时做好SQL注入防护：

PHP代码实现 #

$array = [
    ['id' => 1, 'name' => 'one'],
    ['id' => 2, 'name' => 'two'],
    ['id' => 3, 'name' => 'three']
];

$selects = [];
foreach ($array as $item) {
    $id = (int)$item['id'];
    $name = mysqli_real_escape_string($yourDbConnection, $item['name']);
    $selects[] = "SELECT $id AS col1, '$name' AS col2 FROM DUAL";
}
$subQuery = implode(' UNION ALL ', $selects);

$sql = "SELECT col1, col2 FROM ($subQuery) AS t;";

echo $sql;

生成的SQL语句 #

SELECT col1, col2 FROM (
    SELECT 1 AS col1, 'one' AS col2 FROM DUAL
    UNION ALL SELECT 2 AS col1, 'two' AS col2 FROM DUAL
    UNION ALL SELECT 3 AS col1, 'three' AS col2 FROM DUAL
) AS t;

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