桌子太多了; MySQL在连接中只能使用61个表

What is the best way to export data from multiple tables in MySQL. I'm basically working with product details. Say a product has 150 attributes of data. How can I export that in a single row and then export it to a flat file in CSV or tabdelimited format.

从MySQL中的多个表导出数据的最佳方法是什么？我基本上都在处理产品细节。假设一个产品有150个数据属性。如何将其导出到一行中，然后将其导出为CSV或tabdelimited格式的平面文件。

Getting error Too many tables; MySQL can only use 61 tables in a join

获取错误表太多; 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"];

3 个解决方案

#1

You're using an EAV design, and trying to re-construct a single row from a variable number of attributes. This points out one of the many landmines you'll encounter using the EAV design: there's a practical limit on the number of joins you can do in a single SQL query.

您正在使用EAV设计，并尝试从可变数量的属性重新构造单行。这指出了使用EAV设计会遇到的众多地雷中的一个：在单个SQL查询中可以执行的连接数量存在实际限制。

Especially in MySQL -- there's a hard limit, as you've found. But even in other RDBMS brands, there's an effective limit because the cost of joins is geometric with respect to the number of tables.

尤其是在MySQL中 - 正如您所发现的那样，存在一个严格的限制。但即使在其他RDBMS品牌中，也存在一个有效的限制，因为连接的成本相对于表的数量是几何的。

If you use EAV, don't try to re-construct a row in SQL as if you had a conventional database design. Instead, fetch the attributes as rows, sorted by the entity id. Then post-process them in your application code. This does mean that you can't dump the data in one step -- you have to write code to loop over the attribute rows, and reform each row of data before you can output it.

如果使用EAV，请不要尝试在SQL中重新构造一行，就像使用传统的数据库设计一样。相反，将属性作为行获取，按实体ID排序。然后在应用程序代码中对它们进行后处理。这意味着您不能一步转储数据 - 您必须编写代码来循环遍历属性行，并在输出之前重新构造每一行数据。

EAV is not a convenient database design. There are many expensive drawbacks to using it, and you've just hit one of them.

EAV不是一种方便的数据库设计。使用它有许多昂贵的缺点，你只需要打其中一个。

---

See http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/ for a great story about how using EAV doomed one business.

请参阅http://www.simple-talk.com/opinion/opinion-pieces/bad-carma/，了解有关如何使用EAV注定一项业务的精彩故事。

And also see http://en.wikipedia.org/wiki/Inner-platform_effect because EAV is an example of this Anti-pattern.

另请参阅http://en.wikipedia.org/wiki/Inner-platform_effect，因为EAV就是这种反模式的一个例子。

---

I understand the need to support a dynamic set of attributes per product in a catalog. But EAV is going to kill your application. Here's what I do to support dynamic attributes:

我理解需要在目录中支持每个产品的动态属性集。但是EAV会杀死你的应用程序。这是我为支持动态属性所做的工作：

• Define a real column in the base table for each attribute that's common to all product types. Product name, price, quantity in stock, etc. Work hard to imagine the canonical product entity so you can include as many attributes as possible in this set.

  在基表中为所有产品类型共有的每个属性定义一个实际列。产品名称，价格，库存数量等。努力想象规范产品实体，以便您可以在此集合中包含尽可能多的属性。

• Define one more column of type TEXT for all additional attributes of each given product type. Store in this column as Serialized LOB of the attributes, in whatever format suits you: XML, JSON, YAML, your own homemade DSL, etc.

  为每种给定产品类型的所有其他属性定义另一个TEXT类型的列。将此列作为属性的序列化LOB存储，以适合您的任何格式存储：XML，JSON，YAML，您自己的自制DSL等。

  Treat this as a single column in your SQL queries. Any searching, sorting, or display you need to do based on these attributes requires you to fetch the whole TEXT blob into your application deserialize it, and analyze the attributes using application code.

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

#2

If you have this many attributes, I expect that it is a sparse database, so you have a great deal of wasted space.

如果你有这么多属性，我希望它是一个稀疏的数据库，所以你有很多浪费的空间。

You may want to look at using an Entity-Attribute-Value database instead, if possible.

如果可能，您可能希望查看使用Entity-Attribute-Value数据库。

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

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

What this buys you is a way to refactor the database, but have it be more extensible, and reduce how many tables you need. You should be able to come down to 4-6 tables (2-3 entity tables with their attributes). It is a bit more difficult to create the queries as all the queries will be dynamic, but it will simplify your export, and the database maintenance should be simpler.

这给你带来的是一种重构数据库的方法，但它具有更大的可扩展性，并减少了你需要的表数量。你应该能够达到4-6个表（2-3个实体表及其属性）。创建查询有点困难，因为所有查询都是动态的，但它会简化导出，数据库维护应该更简单。

If you must use this schema you may want to create several triggers and then you can call the trigger, which is joining several tables, and then make your query, but you will take a huge performance hit.

如果你必须使用这个模式，你可能想要创建几个触发器，然后你可以调用触发器，它连接几个表，然后进行查询，但是你会受到巨大的性能影响。

UPDATE:

更新：

Since an EAV table is being used, and MySQL doesn't do a pivot function you may want to read the answer to this question: How to pivot a MySQL entity-attribute-value schema How to pivot a MySQL entity-attribute-value schema

由于正在使用EAV表，并且MySQL不执行数据透视功能，您可能需要阅读以下问题的答案：如何透视MySQL实体 - 属性 - 值模式如何透视MySQL实体 - 属性 - 值模式

#3

If you're using EAV and you want to export a large number of attributes at once, the best way is actually to use multiple temporary tables.

如果您正在使用EAV并且想要一次导出大量属性，那么最好的方法是使用多个临时表。

Each temporary table will have the same primary key column. Then join all of them and export into csv.

每个临时表都具有相同的主键列。然后加入所有这些并导出到csv。

I don't know if I want to do a fully fleshed out example, but I will try to do an outline that will hopefully make things clearer.

我不知道我是否想要做一个完全充实的例子，但我会尝试做一个有希望让事情变得清晰的大纲。

1.) Get your list of attributes you want to export. You will use their attribute_ids in the join to your EAV attribute_values table.

1.）获取要导出的属性列表。您将在连接中将其attribute_ids用于EAV attribute_values表。

2.) Split up the attributes so that you will not exceed the join limit. You need the original table, and 1 table per join, so you can have 60 attributes per table in this scheme.

2.）拆分属性，以便不会超过连接限制。您需要原始表，每个连接需要1个表，因此在此方案中每个表可以有60个属性。

3.) Create "flat" temporary tables for each group of attributes. It would go something like this.

3.）为每组属性创建“平面”临时表。它会像这样。

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.) Now you have temporary tables temp1, temp2, temp3, etc. They all share the same primary key (product_id and/or product_sku for example). Assuming you have less than 60 temporary tables (which would be absurd), you can now join all of those and create a single table.

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

In my system, I don't think I've exceeded 3 temporary tables and that is quite a lot.

在我的系统中，我认为我没有超过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.) Export. Use MySQL's file export feature to create a CSV. Send it to the user with PHP.

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

I hope that helps.

我希望有所帮助。

Also note that the above process executes fairly quickly for me. The reason to use temporary tables is because they will be automatically dropped after use, and because multiple users can all run the same type of process without interfering with each other since temporary tables only exist for the user who created them.

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