這篇文章運(yùn)用簡(jiǎn)單易懂的例子給大家介紹如何解決Magento 2.2.5和2.2.6的問(wèn)題產(chǎn)品設(shè)置特價(jià)又刪除問(wèn)題，代碼非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來(lái)自于我們對(duì)這個(gè)行業(yè)的熱愛。我們立志把好的技術(shù)通過(guò)有效、簡(jiǎn)單的方式提供給客戶，將通過(guò)不懈努力成為客戶在信息化領(lǐng)域值得信任、有價(jià)值的長(zhǎng)期合作伙伴，公司提供的服務(wù)項(xiàng)目有：國(guó)際域名空間、網(wǎng)頁(yè)空間、營(yíng)銷軟件、網(wǎng)站建設(shè)、新華網(wǎng)站維護(hù)、網(wǎng)站推廣。

一、問(wèn)題描述：版本2.2.5和2.2.6均有此問(wèn)題，為Magento2的系統(tǒng)bug。為產(chǎn)品設(shè)置special price，比如0.5元，這個(gè)產(chǎn)品按價(jià)格由低到高排序時(shí)，排在首位；然后刪去special price，保存，重建索引后，此產(chǎn)品顯示的價(jià)格是正確的，但是即使有顯示的價(jià)格比它還低的，按價(jià)格排序這個(gè)產(chǎn)品依然排在首位。

二、問(wèn)題定位：
1、價(jià)格排序有問(wèn)題，肯定是數(shù)據(jù)保存有問(wèn)題。先在數(shù)據(jù)庫(kù)里找與價(jià)格有關(guān)的數(shù)據(jù)表，catalog_product_index_price 和 catalog_category_entity_decimal 放在一起看，發(fā)現(xiàn)有問(wèn)題的產(chǎn)品中，final_price max_price min_price的值都為0，改為和price的值一樣時(shí)，價(jià)格排序正確。有此確定有問(wèn)題的表出在 catalog_product_index_price。

2、確定產(chǎn)品保存時(shí) catalog_product_index_price 這張表的final price的值為什么會(huì)被保存為0。產(chǎn)品保存與 vendor/magento/module-catalog/Controller/Adminhtml/Product/Save.php 這個(gè)文件有關(guān)，斷點(diǎn)調(diào)試未能發(fā)現(xiàn)保存 catalog_product_index_price 的操作。后經(jīng)同事提醒，產(chǎn)品保存后會(huì)進(jìn)行 reindex 的操作，簡(jiǎn)單測(cè)試發(fā)現(xiàn) catalog_product_index_price 表確實(shí)是 reindex 時(shí)保存。

3、reindex時(shí)，斷點(diǎn)調(diào)試獲取最終插入數(shù)據(jù)表的 SQL語(yǔ)句。只要分析SQL語(yǔ)句，就能確定問(wèn)題的來(lái)源。reindex的起始點(diǎn)在文件 vendor/magento/module-indexer/Console/Command/IndexerReindexCommand.php，但是indexer有很多，要準(zhǔn)確找到價(jià)格的reindex操作，需要花費(fèi)很大的努力和耐心。最終找到文件 vendor/magento/module-catalog/Model/ResourceModel/Product/Indexer/Price/SimpleProductPrice.php ，從中可以獲取插入臨時(shí)表的SQL語(yǔ)句 $query 變量，復(fù)制SQL語(yǔ)句，放到Navicat中執(zhí)行，可以發(fā)現(xiàn)要插入的數(shù)據(jù)中，final_price為0，下面主要分析這個(gè)SQL語(yǔ)句。

4、如下面展示的SQL語(yǔ)句所示，這個(gè)語(yǔ)句很長(zhǎng)而且很復(fù)雜，要分析清楚它內(nèi)部的邏輯結(jié)構(gòu)，一是沒(méi)有相應(yīng)的分析復(fù)雜SQL語(yǔ)句的經(jīng)驗(yàn)而很是犯難，二是非常耗費(fèi)時(shí)間。后經(jīng)同事演示和提醒，發(fā)現(xiàn)分析這個(gè)SQL語(yǔ)句也沒(méi)有想象中那么難，因?yàn)樵購(gòu)?fù)雜的語(yǔ)句都是由基本語(yǔ)句組合而成，不過(guò)其中加上了多層嵌套，if語(yǔ)句的判斷讓它看起來(lái)非常復(fù)雜罷了，基本的分析方法還是：“分而治之，各個(gè)擊破”，這句中國(guó)的成語(yǔ)包含了豐富的哲理智慧，我發(fā)現(xiàn)現(xiàn)實(shí)生活中的問(wèn)題都可以用這套理論去解決。下面詳細(xì)說(shuō)明如何“分而治之”，又如何“各個(gè)擊破”。
分而治之，就是把這段SQL語(yǔ)句中無(wú)關(guān)的東西撇去，只關(guān)注核心的數(shù)據(jù)。final_price有問(wèn)題，我們就看它的final_price是怎么查出來(lái)的，看黃色背景的部分。它最外面是一層IFNULL判斷，它的意思是第一個(gè)參數(shù)如果為真，那么返回它本身，否則返回第二個(gè)參數(shù)，final_price現(xiàn)在為0，那么就可以判斷，第一個(gè)參數(shù)一定為FALSE。但是第一個(gè)參數(shù)是很長(zhǎng)一大段，我們又來(lái)仔細(xì)分析它，因?yàn)榍短缀芏啵蝗菀卓辞宄?，我們這時(shí)要引入外面的工具，把它格式化，層次結(jié)構(gòu)分明一點(diǎn)，并且可以看到括弧的開頭和結(jié)束。PHPStorm是一個(gè)很好用的工具，把這段代碼都復(fù)制進(jìn)去，并且格式化后再來(lái)分析。
各個(gè)擊破，因?yàn)榇a中涉及到值的對(duì)比和運(yùn)算，所以我們要學(xué)會(huì)將這些不同的值打印顯示出來(lái)，算出來(lái)后一個(gè)個(gè)拿來(lái)進(jìn)行比較分析。打印（查詢）出來(lái)也不難，模仿它本身就好了，用IFNULL或IF語(yǔ)句，就可以查詢出來(lái)。

5、經(jīng)過(guò)上面的分析，最終確定，問(wèn)題出在一個(gè)叫 special_from_date 的產(chǎn)品屬性上。當(dāng)保存special_price 時(shí)，會(huì)將這個(gè)屬性的值也保存起來(lái)，但是刪除 special_price 時(shí)卻沒(méi)有刪除，遺留的數(shù)據(jù)會(huì)影響上面SQL語(yǔ)句的判斷，從而導(dǎo)致final_price的值變?yōu)?。

6、定位了問(wèn)題所在后，就是最終的解決。覆寫 vendor/magento/module-catalog/Observer/SetSpecialPriceStartDate.php 文件的 execute方法，改為如下。它的作用就是，當(dāng)有 special_price時(shí)，就保存special_from_date,沒(méi)有special_price時(shí)，就刪除speical_from_date。更新代碼后，問(wèn)題解決。

 /**
* Set the current date to Special Price From attribute if it empty
*
* If special price was deleted, Special Price From attribute will be deleted
*
* (Important! Otherwise indexer would be confused)
*
* @param \Magento\Framework\Event\Observer $observer
* @return $this
*/
public function execute(\Magento\Framework\Event\Observer $observer)
{
　　/** @var $product \Magento\Catalog\Model\Product */
　　$product = $observer->getEvent()->getProduct();
　　if ($product->getSpecialPrice() && !$product->getSpecialFromDate()) {
　　　　$product->setData('special_from_date', $this->localeDate->date());
　　} elseif (!$product->getSpecialPrice() && $product->getSpecialFromDate()) {
　　　　$product->unsetData('special_from_date');
　　}

　　return $this;
}

php視頻教程

三、總結(jié)：經(jīng)此，定位問(wèn)題，解決問(wèn)題的能力又獲得一丁點(diǎn)的提升。主要是學(xué)會(huì)了對(duì)復(fù)雜SQL語(yǔ)句的初步分析，知道了IFNULL、IF、LEAST函數(shù)的使用，AND比OR的優(yōu)先級(jí)要高的事實(shí)。

SQL語(yǔ)句：

 INSERT INTO `catalog_product_index_price_temp` SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `pw`.`website_id`, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS `tax_class_id`, IFNULL((ta_price.value), 0) AS `price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `final_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `min_price`, IFNULL((LEAST(ta_price.value, IF(ta_special_price.value IS NOT NULL AND IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= cwd.website_date AND IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL OR DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= cwd.website_date, ta_special_price.value, ~0), IFNULL((IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0)))), ~0))), 0) AS `max_price`, IF(tier_price_1.value_id is NULL AND tier_price_2.value_id is NULL AND tier_price_3.value_id is NULL AND tier_price_4.value_id is NULL, NULL, LEAST(IFNULL((IF(tier_price_1.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_1.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_1.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_2.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_2.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_2.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_3.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_3.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_3.value * cwd.rate, 4))), ~0), IFNULL((IF(tier_price_4.value = 0, ROUND(ta_price.value * (1 - ROUND(tier_price_4.percentage_value * cwd.rate, 4) / 100), 4), ROUND(tier_price_4.value * cwd.rate, 4))), ~0))) AS `tier_price` FROM `catalog_product_entity` AS `e`
CROSS JOIN `customer_group` AS `cg`
INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = e.entity_id
INNER JOIN `catalog_product_index_website` AS `cwd` ON pw.website_id = cwd.website_id
LEFT JOIN `catalog_product_index_tier_price` AS `tp` ON tp.entity_id = e.entity_id AND tp.customer_group_id = cg.customer_group_id AND tp.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_1` ON tier_price_1.row_id = e.row_id AND tier_price_1.all_groups = 0 AND tier_price_1.customer_group_id = cg.customer_group_id AND tier_price_1.qty = 1 AND tier_price_1.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_2` ON tier_price_2.row_id = e.row_id AND tier_price_2.all_groups = 0 AND tier_price_2.customer_group_id = cg.customer_group_id AND tier_price_2.qty = 1 AND tier_price_2.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_3` ON tier_price_3.row_id = e.row_id AND tier_price_3.all_groups = 1 AND tier_price_3.customer_group_id = 0 AND tier_price_3.qty = 1 AND tier_price_3.website_id = 0
LEFT JOIN `catalog_product_entity_tier_price` AS `tier_price_4` ON tier_price_4.row_id = e.row_id AND tier_price_4.all_groups = 1 AND tier_price_4.customer_group_id = 0 AND tier_price_4.qty = 1 AND tier_price_4.website_id = pw.website_id
LEFT JOIN `catalog_product_entity_int` AS `tad_tax_class_id` ON tad_tax_class_id.row_id = e.row_id AND tad_tax_class_id.attribute_id = 149 AND tad_tax_class_id.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_tax_class_id` ON tas_tax_class_id.row_id = e.row_id AND tas_tax_class_id.attribute_id = 149 AND tas_tax_class_id.store_id = cwd.default_store_id
INNER JOIN `catalog_product_entity_int` AS `tad_status` ON tad_status.row_id = e.row_id AND tad_status.attribute_id = 97 AND tad_status.store_id = 0
LEFT JOIN `catalog_product_entity_int` AS `tas_status` ON tas_status.row_id = e.row_id AND tas_status.attribute_id = 97 AND tas_status.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity_decimal` AS `ta_price` ON ta_price.row_id = e.row_id AND ta_price.attribute_id = 77 AND ta_price.store_id = 0
LEFT JOIN `catalog_product_entity_decimal` AS `ta_special_price` ON ta_special_price.row_id = e.row_id AND ta_special_price.attribute_id = 78 AND ta_special_price.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_from_date` ON tad_special_from_date.row_id = e.row_id AND tad_special_from_date.attribute_id = 79 AND tad_special_from_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_from_date` ON tas_special_from_date.row_id = e.row_id AND tas_special_from_date.attribute_id = 79 AND tas_special_from_date.store_id = cwd.default_store_id
LEFT JOIN `catalog_product_entity_datetime` AS `tad_special_to_date` ON tad_special_to_date.row_id = e.row_id AND tad_special_to_date.attribute_id = 80 AND tad_special_to_date.store_id = 0
LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_to_date` ON tas_special_to_date.row_id = e.row_id AND tas_special_to_date.attribute_id = 80 AND tas_special_to_date.store_id = cwd.default_store_id WHERE ((IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value) = 1) AND (e.type_id = 'simple') AND (e.entity_id BETWEEN 2 AND 21)) AND (e.created_in <= '1546224120') AND (e.updated_in > '1546224120')

關(guān)于如何解決Magento 2.2.5和2.2.6的問(wèn)題產(chǎn)品設(shè)置特價(jià)又刪除問(wèn)題就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。