我认为您希望模式与其他方式匹配.也就是说，产品名称应包含产品缩写:

I think that you want the pattern matching the other way around. That is, the product name should contain the product abbreviation:

LEFT JOIN `product_abbreviations` AS `pa` 
    ON `op`.`name` LIKE CONCAT('%', `pa`.`product_name` ,'%')