php - 将查询 LEFT JOIN 更改为 FULL OUTER JOIN(尊重空值) - 如何更改此设置(之后没有可怕的性能)？

Question

具有如下结构的实体属性值设置表(它来自第三方插件，我无法更改数据库设计):

现在我想创建一个表，其中 data_id 是 id，名称是列，值是它们的值。仍然每个 data_id 都没有每个名称的值，因此我希望结果中的值为 NULL(或空)，以防原始表中没有此名称的值。

现在我已经编写了一个 PHP 脚本，它正在为我生成所需的查询:

$ihash = function($len = 10){
        return substr(str_shuffle(str_repeat("abcdefghijklmnopqrstuvwxyz", 10)), 0, 10);
    };
$columns = $wpdb->get_col("SELECT DISTINCT name FROM ".$wpdb->prefix."cf7_vdata_entry");

$fromselects = [];
$left_joins = [];
$wheres = [];
$used=[];

foreach($columns as $column) {
    $m = $ihash();
    while(in_array($m,$used)) {
        $m = $ihash();
    }
    array_push($used,$m);
    array_push($fromselects,"$m.value as `$column`");
    $left_joins  .= " LEFT JOIN wp_cf7_vdata_entry AS $m ON a.data_id = $m.data_id ";
    array_push($wheres,"$m.name = '$column'");
}

$query = "SELECT a.data_id, ".implode(", ",$fromselects)."
  FROM (SELECT DISTINCT data_id FROM wp_cf7_vdata_entry) AS a JOIN
  ".$left_joins."
 WHERE ".implode(" AND ",$wheres);

这是生成的查询的样子:

SELECT a.data_id, 
       vtddnqrdjy.value AS `foerderung`, 
       fwfyxgczvn.value AS `company`, 
       jwlpmnbepe.value AS `firstname`,
       -- ... more fields
FROM   (SELECT DISTINCT data_id 
        FROM   wp_cf7_vdata_entry) AS a 
       JOIN wp_cf7_vdata_entry AS vtddnqrdjy 
         ON a.data_id = vtddnqrdjy.data_id 
       JOIN wp_cf7_vdata_entry AS fwfyxgczvn 
         ON a.data_id = fwfyxgczvn.data_id 
       JOIN wp_cf7_vdata_entry AS jwlpmnbepe 
         ON a.data_id = jwlpmnbepe.data_id 
       -- ... more joins
WHERE  vtddnqrdjy.name = 'foerderung' 
       AND fwfyxgczvn.name = 'company' 
       AND jwlpmnbepe.name = 'firstname' 
       AND mloxjygcqp.name = 'lastname' 
       -- ... more fields
LIMIT  10 

结果表生成正确，但结果为空:

原因是没有所有列值的结果被过滤掉，但它们应该有空值(对于表中存在的每个 data_id 恰好一个结果)。我正在考虑将所有 LEFT JOIN 替换为 FULL OUTER JOIN(必须在 MySQL 中伪造)，但这会使事情过于复杂，并且可能已经很糟糕的性能会非常糟糕。我该如何解决这个问题？

Answer 1

MySQL 不支持FULL OUTER JOIN。相反，使用聚合:

SELECT de.data_id,
       MAX(CASE WHEN de.name = 'foerderung' THEN de.value END) as foerderung, 
       MAX(CASE WHEN de.name = 'company' THEN de.value END) as company
           . . .   -- just repeat for each column
FROM wp_cf7_vdata_entry de
GROUP BY de.data_id;