Démonstration de l'approche de table virtuelle/temporaire SQL : extraction de données à partir d'un tableau associatif

Question

J'ai besoin d'une simple requête SQL pour afficher une table virtuelle/temporaire sans la créer dans la base de données.

J'utilise PHP pour créer une chaîne de requête avec des données.

Mon code PHP actuel est :

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

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

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

$sql = "AVEC cte AS
(
  {$sous-requête}
)
SELECT col1, col2 FROM cte;";

echo $sql;

Sa sortie est :

AVEC cte AS
(
  SELECT 1 AS col1, 'un' AS col2 UNION ALL SELECT 2, 'deux' UNION ALL SELECT 3, 'trois'
)
SELECT col1, col2 FROM cte;

// Table de sortie du SQL
col1 col2
1 un
2 deux
3 trois≪/pre>
J'ai eu l'idée de cette requête à partir d'ici. 
Mais le problème avec cette requête est que s'il y a 100 données dans $array, la partie UNION ALL sera incluse 100 fois dans le SQL. Je ne pense pas que ce soit un meilleur SQL car c'est comme rejoindre 100 tables en même temps. 
Je pourrais également créer une table temporaire (CREATE TEMPORARY TABLE table_name) à la place de cette clause WITH Une autre requête est nécessaire pour insérer des enregistrements dans la table temporaire. 
Quelqu'un peut-il m'aider à mieux simplifier cette requête ? 

Answer 1

Lorsque vous utilisez MySQL 8, vous pouvez utiliser json_table des expressions comme celle-ci :

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

Éditeur PHP en ligne

Dans MySQL 8.0, les résultats de la requête ci-dessus sont les suivants :

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