本文介绍了桌子太多;MySQL 在一个连接中只能使用 61 个表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

获取错误太多表;MySQL在一个join中只能使用61个表

/**** 获取结果集 *****/$rs = mysql_query($sql);/**** 获取结果集结束 *****/$objProfileHistory->addHistory($this->profile_id, "Loaded ".mysql_num_rows($rs)."records");$this->runQuery($sql);$this->exportToCSV();/*** 获取属性详细信息*/函数 getAttributeDetails(){全局 $dbObj, $profile;$base_table = "catalog_product_entity";$select = array();$tables = array();$i = 0;$profile->showLog("开始字段映射", "success");if( is_array($this->attributes_in_db) && sizeof($this->attributes_in_db) > 0 ){$arr = implode("','", $this->attributes_in_db);$sql = "选择attribute_id、attribute_code、backend_type、frontend_input来自 eav_attributewhere attribute_code in ('".$arr."')和 entity_type_id =(选择 entity_type_id来自 eav_entity_type其中 entity_type_code = 'catalog_product')";$rs = $dbObj->customqry($sql);如果($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"];开关($backend_type){案例文本":$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;休息;案例十进制":$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;休息;案例静态":$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;休息;案例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."和 ".$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."和 ".$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}";} 别的 {$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;}休息;案例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;休息;案例日期时间":$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;休息;}//转变$i++;}//尽管$sql = "select ".implode(",", $select)." from ".$base_table;for($i=0; $i < sizeof($select); $i++){$sql .=左连接".$tables[$i] ." on (".$where[$i];//." and ".$and[$i].")";if( strlen($and[$i]) > 0 ){$sql .= " 和 ".$and[$i].")";}}//为了$sql .= " group by {$base_table}.entity_id ";}//如果//echo $sql;出口;返回 $sql;}//echo $sql;//echo "<pre>";print_r($tables);print_r($select);print_r($where);print_r($and);}//结束函数/*** 运行查询*/函数运行查询( $sql ){全局 $dbObj, $profile;if( $sql != "" ){$rs = $dbObj->customqry( $sql );$profile->showLog("加载".mysql_num_rows($rs)."记录","成功");如果($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++;}//尽管}}//如果}//结束函数/*** 导出到CSV*/函数 exportToCSV(){全局 $smarty, $objProfileHistory, $profile;//$newFileName = $smarty->root_dir."/export/".$this->filename;//要创建的文件名$cnt = sizeof($this->var_array);for($i=0; $i < $cnt; $i++){提取($this->var_array[$i]);}//为了if( $delimiter = "	" ){$delimiter = "	";//$delimiter;}如果(strlen($文件名)<1){$filename = time().".csv";}//echo "

";//print_r($this->action_array);//print_r($this->var_array);//print_r($this->map_array);//   出口;# 添加亚马逊标题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 .= "
";//分离每条记录}$export_path = $path;$x_path = $profile->createDir( $export_path );$newFileName = $x_path ."/".$文件名;$fpWrite = fopen($newFileName, "w");//以可写方式打开文件# 创建标题$cnt_header = sizeof($this->attributes_in_file);for( $i=0; $i <$cnt_header; $i++){$sStr .= $deli .$this->attributes_in_file[$i];$deli = $delimiter;}//为了$sStr .= "
";//分离每条记录# 附加数据$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"];
解决方案

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

特别是在 MySQL 中——正如您所发现的,有一个硬性限制.但即使在其他 RDBMS 品牌中,也存在有效限制,因为连接的成本与表的数量成几何关系.

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

EAV 不是一种方便的数据库设计.使用它有许多代价高昂的缺点,而您只是遇到了其中之一.

参见 http://www.simple-talk.com/意见/意见片/bad-carma/ 关于使用 EAV 如何注定一项业务的精彩故事.

另见http://en.wikipedia.org/wiki/Inner-platform_effect 因为 EAV 是这种反模式的一个例子.

我了解需要支持目录中每个产品的动态属性集.但是 EAV 会杀死您的应用程序.这是我为支持动态属性所做的工作:

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

  • 为每个给定产品类型的所有附加属性再定义一列 TEXT 类型.在此列中存储为属性的序列化 LOB,以适合您的任何格式:XML、JSON、YAML、自己自制的DSL等

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

    莉>

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.

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

/**** 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 = "	" ){
    $delimiter = "	";//$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 .= "� ��
"; //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 .= "� ��
"; //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"];
解决方案

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.

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.

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 is not a convenient database design. There are many expensive drawbacks to using it, and you've just hit one of them.


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

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


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:

  • 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.

    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.

这篇关于桌子太多;MySQL 在一个连接中只能使用 61 个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 22:53
查看更多