php - 改进 CSV 产品导入 -> Array Category Query by name

Question

最近我们做了一个很大的改变，比如为我们的网站建立一个新的类别树，并将超过 200 万个产品移动到正确的类别中。

我们没有性能问题，一切运行顺利。

我唯一的问题是，现在类别树更加复杂和深入，并且在通过 CSV 导入添加新文章时会受到影响（超时脚本，非常慢，脚本崩溃）基本上是通过 CSV 添加新产品时类别字段始终为文本（由“#”分隔）示例：Engine#Crankshaft#Audi#Audi A5

那么问题出在我的获取categoryID的函数中，以防数据库中已经存在或不存在。我该如何改进？我认为主要问题在于创建的非常大的数组

protected function _getCategoriesID($field_value)
        {
            global $Database;
            $products_categories = array();
            $separator           = $this->_getSeparator($field_value);
            $category_array      = array();
            if ($separator != '') {
                $category_array = explode($separator, $field_value);
                $category_array = array_reverse($category_array);
            } else {
                $category_array[0] = $field_value;
            }
            unset($separator);
            $Qcategory = $Database->query('select c0.categories_id from :table_categories as c0 inner join :table_categories_description as cd on c0.categories_id=cd.categories_id where cd.categories_name = :categories_name and cd.language_id=:language_id');
            $Qcategory->bindTable(':table_categories', TABLE_CATEGORIES);
            $Qcategory->bindTable(':table_categories_description', TABLE_CATEGORIES_DESCRIPTION);
            $Qcategory->bindValue(':categories_name', $category_array[0]);
            $Qcategory->bindInt(':language_id', $this->language);
            $count = 1;
            if (count($category_array) > 1) {
                while ($count < count($category_array)) {
                    $Qcategory->appendQuery(' and c' . ($count - 1) . '.parent_id in (select c' . $count . '.categories_id from :table_categories' . $count . ' as c' . $count . ' inner join :table_categories_description' . $count . ' as cd' . $count . ' on c' . $count . '.categories_id=cd' . $count . '.categories_id where cd' . $count . '.categories_name = :categories_name' . $count . ' and cd' . $count . '.language_id=:language_id' . $count . ' ');
                    $Qcategory->bindTable(':table_categories' . $count, TABLE_CATEGORIES);
                    $Qcategory->bindTable(':table_categories_description' . $count, TABLE_CATEGORIES_DESCRIPTION);
                    $Qcategory->bindValue(':categories_name' . $count, $category_array[$count]);
                    $Qcategory->bindInt(':language_id' . $count, $this->language);
                    $count++;
                }
                for ($i = 0; $i < $count - 1; $i++) {
                    $Qcategory->appendQuery(')');
                }
            }
            $Qcategory->execute();
            while ($Qcategory->next()) {
                $products_categories[] = $Qcategory->value('categories_id');
            }
            unset($Qcategories);
            return $products_categories;
        }

标签： phpmysqlarrayscsvquery-performance