太多的表格；MySQL在连接中最多只能使用61个表格。

Question

什么是从多个MySQL表中导出数据的最佳方法。我基本上在处理产品细节。比如，一个产品有150条数据属性。如何将其导出到单行中，并将其以CSV或制表符分隔的格式导出到平面文件中。

出现错误：太多表格；MySQL只能在联接中使用61个表

/**** Get Resultset *****/
$rs = mysql_query($sql);
/**** End of Get Resultset *****/

$objProfileHistory->addHistory($this->profile_id, "Loaded ". mysql_num_rows($rs)." records");


$this->runQuery($sql);

$this->exportToCSV();

/**
  * getAttributeDetails
  */
function getAttributeDetails(){
    global $dbObj, $profile;

    $base_table = "catalog_product_entity";
    $select  = array();
    $tables  = array();
    $i   = 0;

    $profile->showLog("Start fields mapping", "success");

   if( is_array($this->attributes_in_db) && sizeof($this->attributes_in_db) > 0 ){
    $arr = implode("','", $this->attributes_in_db);
    $sql = "select attribute_id, attribute_code, backend_type, frontend_input
        from eav_attribute 
        where attribute_code in ('".$arr."') 
        and entity_type_id = 
         (select entity_type_id 
          from eav_entity_type 
          where entity_type_code = 'catalog_product')";
    $rs = $dbObj->customqry($sql);

    if( $rs ){
     while( $row = mysql_fetch_assoc( $rs ) ){
      $backend_type  = $row["backend_type"];
      $attribut_code = $row["attribute_code"];
      $attribute_id = $row["attribute_id"];
      $frontend_input = $row["frontend_input"];
      switch( $backend_type ){
       case "text":
        $where[]  = $base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id;
        $and[]  = $base_table.".entity_id=".$base_table."_".$backend_type."".$i.".entity_id";
        $select[]  = $base_table."_".$backend_type."".$i.".value as ".$attribut_code;
        $tables[]  = $base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i;
       break;

       case "decimal":
        $where[]  = $base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id;
        $and[]  = $base_table.".entity_id=".$base_table."_".$backend_type."".$i.".entity_id";
        $select[]  = $base_table."_".$backend_type."".$i.".value as ".$attribut_code;
        $tables[]  = $base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i;
       break;

       case "static":
        $where[]  = $base_table."".$i.".entity_id=".$base_table.".entity_id";
        $and[]  = $base_table.".entity_id=".$base_table."".$i.".entity_id";
        $select[]  = $base_table."".$i.".".$attribut_code." as ".$attribut_code;
        $tables[]  = $base_table." as ".$base_table."".$i;
       break;

       case "int":
        if( $attribut_code == "tax_class_id" && $frontend_input == "select" ){
         $where[]  = "tax_class{$i}.class_id=(select ".$base_table."_".$backend_type."".$i.".value from ".$base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i." where  ".$base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id." and ".$base_table."_".$backend_type."".$i.".entity_id=".$base_table.".entity_id limit 1))";
         $and[]  = "";
         $select[]  = "tax_class{$i}.class_name as {$attribut_code}";
         $tables[]  = "tax_class as tax_class{$i}";
         } else if( $frontend_input == "select" ){
         $where[]  = "eav_attribute_option_value{$i}.option_id=(select ".$base_table."_".$backend_type."".$i.".value from ".$base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i." where  ".$base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id." and ".$base_table."_".$backend_type."".$i.".entity_id=".$base_table.".entity_id limit 1))";
         $and[]  = "";
         $select[] = "eav_attribute_option_value{$i}.value as {$attribut_code}";
         $tables[]  = "eav_attribute_option_value as eav_attribute_option_value{$i}";
        } else {
         $where[]  = $base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id;
         $and[]  = $base_table.".entity_id=".$base_table."_".$backend_type."".$i.".entity_id";
         $select[]  = $base_table."_".$backend_type."".$i.".value as ".$attribut_code;
         $tables[]  = $base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i;
        }
       break;

       case "varchar":
        $where[]  = $base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id;
        $and[]  = $base_table.".entity_id=".$base_table."_".$backend_type."".$i.".entity_id";
        $select[]  = $base_table."_".$backend_type."".$i.".value as ".$attribut_code;
        $tables[]  = $base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i;
       break;

       case "datetime":
        $where[]  = $base_table."_".$backend_type."".$i.".attribute_id=".$attribute_id;
        $and[]  = $base_table.".entity_id=".$base_table."_".$backend_type."".$i.".entity_id";
        $select[]  = $base_table."_".$backend_type."".$i.".value as ".$attribut_code;
        $tables[]  = $base_table."_".$backend_type." as ".$base_table."_".$backend_type."".$i;
       break;
      }//switch
      $i++;
     }//while


     $sql = "select ".implode(",", $select)." from ".$base_table;
     for($i=0; $i < sizeof($select); $i++){
      $sql .= " left join ". $tables[$i] . " on (".$where[$i];//." and ".$and[$i].")";
      if( strlen($and[$i]) > 0 ){
       $sql .= " and ".$and[$i].")";
      }
     }//for
     $sql .= " group by {$base_table}.entity_id ";
    }//if
    //echo $sql; exit;
    return $sql;
   }
   //echo $sql;
   //echo "<pre>";print_r($tables);print_r($select);print_r($where);print_r($and);
  }//end function

  /**
  * runQuery
  */
  function runQuery( $sql ){
   global $dbObj, $profile;
   if( $sql != "" ){
    $rs = $dbObj->customqry( $sql );
    $profile->showLog("Loaded ". mysql_num_rows($rs) ." records", "success");
    if( $rs ){
     $i = 0;
     while( $row = mysql_fetch_assoc( $rs ) ){
      $cnt = sizeof($this->attributes_in_db);
      for($j=0; $j < $cnt; $j++){
       $db_key  = $this->attributes_in_db[$j];
       $file_key = $this->attributes_in_file[$j];
       $this->export_data[$i][$db_key] = $row[$db_key];
      }
      $i++;
     }//while
    }
   }//if
  }//end function


  /**
  * exportToCSV
  */
  function exportToCSV(){
   global $smarty, $objProfileHistory, $profile;
   //$newFileName = $smarty->root_dir."/export/".$this->filename; //file name that you want to create
   $cnt = sizeof($this->var_array);
   for($i=0; $i < $cnt; $i++){
    extract($this->var_array[$i]);
   }//for


   if( $delimiter = "\t" ){
    $delimiter = "\t";//$delimiter;
   }

   if( strlen($filename) < 1 ){
    $filename = time().".csv";
   }

//    echo "<pre>";
//    print_r($this->action_array);
//    print_r($this->var_array);
//    print_r($this->map_array);
//    exit;
   # add amazon headers
   if( $this->action_array[0]['type'] == 'header' ){
//     $template_type  = $this->var_array[0]['template_type'];
//     $version   = $this->var_array[0]['version'];
//     $status_message = $this->var_array[0]['status_message'];
    $sStr = "TemplateType=".$template_type."{$delimiter}{$delimiter}Version=".$version."{$delimiter}{$delimiter}{$status_message}";
    $sStr .= "� ��\n"; //to seprate every record
   }





   $export_path = $path;
   $x_path = $profile->createDir( $export_path );

   $newFileName = $x_path ."/". $filename;

   $fpWrite = fopen($newFileName, "w"); // open file as writable

   # create header
   $cnt_header = sizeof($this->attributes_in_file);
   for( $i=0; $i < $cnt_header; $i++){
    $sStr .= $deli . $this->attributes_in_file[$i];
    $deli = $delimiter;
   }//for
   $sStr .= "� ��\n"; //to seprate every record

   # attach data
   $cnt_row = sizeof($this->export_data);
   for( $i=0; $i < $cnt_row; $i++ ){
    $sStr .= $saperator;
    $newdeli = "";
    for($j=0; $j < $cnt_header; $j++){
     $key  = $this->attributes_in_db[$j];
     $sku = $this->export_data[$i]["sku"];

- user204245

---

什么是从多个MySQL表中导出数据的最佳方法？我主要处理产品细节。假设一个产品有150个数据属性，如何将其导出到单行中，然后以CSV或制表符分隔格式导出到平面文件中。 - user204245

是时候重新考虑这个查询了。在超过7个表上进行连接对于任何数据库来说都是性能杀手。你是否曾经在任何地方运行过这个查询？ - duffymo

Answer 1

您正在使用 EAV 设计，并尝试从可变数量的属性中重新构造单行数据。这指出了使用 EAV 设计时会遇到的许多雷区之一：在单个 SQL 查询中，您可以进行的联接数量有实际上限。

特别是在 MySQL 中，存在硬性限制，正如您所发现的那样。但即使在其他 RDBMS 品牌中，由于联接成本随表数量呈几何级数增长，也存在有效限制。

如果使用 EAV，请不要像使用传统数据库设计一样，在 SQL 中尝试重新构建行。而是将属性作为行按实体 ID 排序获取。然后在应用程序代码中对其进行后处理。这意味着您不能一步转储数据，必须编写代码循环处理每个属性行，并在输出之前重新格式化每行数据。

EAV 不是方便的数据库设计。使用它有许多昂贵的缺点，你刚刚碰到了其中一个。

---

请参阅http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/，了解使用 EAV 使一个企业失败的真实故事。

还请参见http://en.wikipedia.org/wiki/Inner-platform_effect，因为 EAV 是该反模式的一个例子。

---

我理解需要在目录中支持每个产品的动态属性集。但是使用 EAV 将危及您的应用程序。以下是我用来支持动态属性的方法：

• 为所有产品类型共同具有的每个属性在基本表中定义真正的列。例如，产品名称、价格、库存数量等。努力设想规范化的产品实体，以便在此集合中包含尽可能多的属性。

• 为每个给定产品类型定义一个类型为TEXT的附加属性列。将这些属性作为序列化大型对象存储在该列中，可以使用XML、JSON、YAML或自己制作的DSL等任何格式。

  在SQL查询中，将其视为单个列。基于这些属性进行的任何搜索、排序或显示都需要获取整个TEXT blob到您的应用程序中进行反序列化，并使用应用程序代码分析属性。

- Bill Karwin

Answer 2

如果您有这么多属性，我预期它是一个稀疏的数据库，因此您浪费了大量空间。 如果可能的话，您可能想考虑使用实体-属性-值数据库。

http://en.wikipedia.org/wiki/Entity-attribute-value_model

这种方法可以让你重构数据库，使其更具可扩展性，并减少所需的表格数量。你应该能够将表格数量降至4-6个（2-3个实体表和它们的属性）。创建查询会稍微困难一些，因为所有查询都将是动态的，但它将简化你的导出，并且数据库维护应该更加简单。

如果你必须使用此架构，你可能需要创建多个触发器，然后调用触发器，将多个表连接起来，然后进行查询，但这会对性能产生巨大影响。

更新：

由于使用了EAV表格，而MySQL没有执行透视功能，你可能需要阅读这个问题的答案： 如何透视MySQL实体-属性-值模式 如何透视MySQL实体-属性-值模式

- James Black

Answer 3

如果您正在使用EAV，且需要一次性导出大量属性，则最好的方法是使用多个临时表。每个临时表将具有相同的主键列。然后连接所有表并导出到csv文件中。获取要导出的属性列表，使用它们的 attribute_id 加入到 EAV 属性值表中。分割属性以避免超过关联限制，每个表可以包含60个属性。为每组属性创建“平面”临时表。

CREATE TEMPORARY TABLE temp1
[(create_definition,...)]
SELECT t1.product_id, t1.sku, t2.color, GROUP_CONCAT(t3.sizes SEPARATOR ',') as sizes,
...

#( suppose the product has multiple sizes and you want them shown comma-separated in your export)

FROM products t1
LEFT JOIN eav_attribute_values t2 ON t1.product_id = t2.product_id AND t2.attribute_id = 55
LEFT JOIN eav_attribute_values t3 ON t1.product_id = t2.product_id AND t2.attribute_id = 76
... etc for up to 60 attributes

CREATE TEMPORARY TABLE temp2 ... # repeat for next 60 attributes

4.) 现在您有临时表temp1、temp2、temp3等。它们都共享相同的主键（例如product_id和/或product_sku）。 假设您有少于60个临时表（这是荒谬的），您现在可以将所有这些表连接并创建一个单独的表。

在我的系统中，我认为我没有超过3个临时表，那已经很多了。

CREATE TEMPORARY TABLE export_data
[(create_definition,...)]
SELECT t1.*, t2.*, t3.* FROM # though I would not actually use * here b/c it would cause repeated key fields. I would list out all the columns
temp1 t1 LEFT JOIN temp2 t2 ON t1.product_id = t2.product_id
LEFT JOIN temp3 t3 ON t1.product_id = t3.product_id # etc for more joins

5.) 导出。使用MySQL的文件导出功能创建CSV文件。使用PHP将其发送给用户。

希望这有所帮助。

还要注意，上述过程对我来说执行得相当快。使用临时表的原因是它们将在使用后自动删除，并且因为多个用户可以运行相同类型的进程而不会相互干扰，因为临时表仅存在于创建它们的用户中。

- Buttle Butkus

---

@TomKim 我更新了我的答案，附上我会做的概要。如果有任何疑惑，请告诉我。另外请注意，我实际上正在使用这个想法，并且它对我很有效。 - Buttle Butkus

Answer 4

在Spring Boot中，如果您没有使用LAZY作为获取类型，就会出现这种情况。在这种情况下，您的一个表格有超过61个子关系，例如表格名称A、B、C和D：

• A与B相关
• B与C相关
• C与D相关

在这种情况下，A有3个子关系。如果您从A获取数据，则可以基于关系访问D中的数据。

因此，请使用Fetch类型作为LAZY。

- Chameera W. Ashan