Demonstration des SQL-Ansatzes für virtuelle/temporäre Tabellen: Extrahieren von Daten aus einem assoziativen Array

Question

Ich benötige eine einfache SQL-Abfrage, um eine virtuelle/temporäre Tabelle anzuzeigen, ohne sie in der Datenbank zu erstellen.

Ich verwende PHP, um eine Abfragezeichenfolge mit Daten zu erstellen.

Mein aktueller PHP-Code ist:

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

$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;

Die Ausgabe ist:

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

// Ausgabetabelle aus dem SQL
Spalte1 Spalte2
1 Eins
2 zwei
3 drei

Die Idee zu dieser Abfrage kam mir hier.

Das Problem bei dieser Abfrage besteht jedoch darin, dass bei 100 Daten im $array der UNION ALL-Teil 100 Mal in die SQL aufgenommen wird. Ich glaube nicht, dass dies ein besseres SQL ist, weil es so ist, als würde man 100 Tabellen gleichzeitig verknüpfen.

Anstelle dieser WITH-Klausel könnte ich auch eine temporäre Tabelle erstellen (CREATE TEMPORARY TABLE table_name), aber das wäre keine einzelne Abfrage, da ich Um Datensätze in die temporäre Tabelle einzufügen, ist eine weitere Abfrage erforderlich.

Kann mir jemand helfen, diese Abfrage besser zu vereinfachen?

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 |
+----+-------+