展示SQL虛擬/臨時表的方法：從關聯數組提取數據

Question

我需要一個簡單的SQL查詢來顯示一個虛擬/臨時表，而不需要在資料庫中建立它。

我使用PHP來建立帶有資料的查詢字串。

我的目前PHP程式碼是：

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

$subQuery = "SELECT {$array[0]['id']} AS col1, '{$array[0]['name']}' AS col2";

for ($i=1; $i < count($array); $i ) {
    $subQuery .= " UNION ALL SELECT {$array[$i]['id']}, '{$array[$i]['name']}'";
}

$sql = "WITH cte AS
(
  {$subQuery}
)
SELECT col1, col2 FROM cte;";

echo $sql;

它的輸出是：

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

// Output table from the SQL
col1 col2
1 one
2 two
3 three

我從這裡得到了這個查詢的想法。

但這個查詢的問題是，如果$array中有100個數據，UNION ALL部分會在SQL中包含100次。我覺得這不是一個更好的SQL，因為它就像同時聯合了100個表。

我也可以建立一個臨時表（CREATE TEMPORARY TABLE table_name）來取代這個WITH子句，但這不是單一的查詢，因為我需要另一個查詢來將記錄插入到臨時表中。

有人可以幫我用更好的方式簡化這個查詢嗎？

Answer 1

當您使用MySQL 8時，可以使用json_table表達式，如下所示：

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

$data = json_encode($array);

$sql = "SELECT tbl.* 
FROM JSON_TABLE(
        '{\"data\":$data}',
        '$.data[*]' COLUMNS (
                id VARCHAR(40)  PATH '$.id',
                name VARCHAR(100) PATH '$.name')
     ) tbl";

echo $sql;

PHP線上編輯器

#在MySQL 8.0中，上述查詢的結果如下：

+====+=======+
| id | name  |
+====+=======+
| 1  | one   |
+----+-------+
| 2  | two   |
+----+-------+
| 3  | three |
+----+-------+