mySQL -如何正确编码SELECT语句

Question

我有一条mySQL SELECT语句，在WHERE条件中结合了全文搜索和普通搜索，还有一个类似下面这样的JOIN

SELECT 
 customer.*, 
 countries.name as country
 FROM customer  
 LEFT JOIN countries ON countries.ID = customer.country_id 
 WHERE customer.num   = :keyword 
    OR customer.city  = :keyword  
    OR customer.email = :keyword 
    OR MATCH (customer.company) AGAINST (:keyword)
    OR countries.name = :keyword

对(customer.company)列进行全文搜索的查询只要table.column countries.name是WHERE条件的一部分，就会返回no result。但是对countries.name本身的查询是成功的。

如何正确编码SELECT语句，以使用上述示例中的所有WHERE组件的组合返回成功的查询？

编辑：

在以前的版本中，我使用了以下语句

 SELECT 
  customer.* ,
  countries.name
  FROM customer, countries 
  WHERE customer.country_id=countries.ID 
  AND MATCH (customer.company) AGAINST (:keyword)

  UNION

  SELECT 
  customer.*, 
  countries.name
  FROM customer, countries 
  WHERE customer.country_id=countries.ID 
  AND countries.name=:keyword

效果很好。我只是不知道这是否是用不同的搜索(全文和普通)查询两个表的有效方法。另外，当我搜索超过2列时，代码很容易爆炸，这是我想要避免的。

欢迎更多的想法和帮助

Answer 1

您应该检查NULLs，因为您使用的是LEFT JOIN：

SELECT 
     customer.*, 
     countries.name as country
 FROM customer  
 LEFT JOIN countries ON countries.ID = customer.country_id 
 WHERE customer.num   = :keyword 
    OR customer.city  = :keyword  
    OR customer.email = :keyword 
    OR MATCH (customer.company) AGAINST (:keyword)
    OR (countries.name = :keyword OR countries.name IS NULL)

为了澄清起见，LEFT JOIN的右表上的条件应该只放在ON子句中，而不是WHERE子句中。这有一点不同，因为您希望将其与所有其他条件进行比较，因此- NULL比较。

Answer 2

我的解决方案是，如果默认值为''，则使用CASE WHEN将其设置为countries.name

(CASE WHEN countries.name is null then 'default value' else countries.name END)

新建查询

SELECT 
 customer.*, 
 countries.name as country
 FROM customer  
 LEFT JOIN countries ON countries.ID = customer.country_id 
 WHERE customer.num   = :keyword 
    OR customer.city  = :keyword  
    OR customer.email = :keyword 
    OR MATCH (customer.company) AGAINST (:keyword)
    OR (CASE WHEN countries.name is null then '' else countries.name END) = :keyword