低效的“WHERE1=1”

http://www.haogongju.net/art/17046

网上有不少人提出过类似的问题：“看到有人写了WHERE 1=1这样的SQL，到底是什么意思？”。其实使用这种用法的开发人员一般都是在使用动态组装的SQL。让我们想像如下的场景：用户要求提供一个灵活的查询界面来根据各种复杂的条件来查询员工信息，界面如下图：

界面中列出了四个查询条件，包括按工号查询、按姓名查询、按年龄查询以及按工资查询，每个查询条件前都有一个复选框，如果复选框被选中，则表示将其做为一个过滤条件。比如上图就表示“检索工号介于DEV001和DEV008之间、姓名中含有J并且工资介于3000元到6000元的员工信息”。如果不选中姓名前的复选框，比如下图表示“检索工号介于DEV001和DEV008之间并且工资介于3000元到6000元的员工信息”：

 

如果将所有的复选框都不选中，则表示表示“检索所有员工信息”，比如下图：

这里的数据检索与前面的数据检索都不一样，因为前边例子中的数据检索的过滤条件都是确定的，而这里的过滤条件则随着用户设置的不同而有变化，这时就要根据用户的设置来动态组装SQL了。当不选中年龄前的复选框的时候要使用下面的SQL语句：

  
  

   
   SELECT
   
    
   
   *
   
    
   
   FROM
   
    T_Employee 

   
   WHERE
   
    FNumber 
   
   BETWEEN
   
    
   
   '
   
   DEV001
   
   '
   
    
   
   AND
   
    
   
   '
   
   DEV008
   
   '
   
    

   
   AND
   
    FName 
   
   LIKE
   
    
   
   '
   
   %J%
   
   '
   
    

   
   AND
   
    FSalary 
   
   BETWEEN
   
    
   
   3000
   
    
   
   AND
   
    
   
   6000
   
   
  
  

而如果不选中姓名和年龄前的复选框的时候就要使用下面的SQL语句：

  
  

   
   SELECT
   
    
   
   *
   
    
   
   FROM
   
    T_Employee 

   
   WHERE
   
    FNumber 
   
   BETWEEN
   
    
   
   '
   
   DEV001
   
   '
   
    
   
   AND
   
    
   
   '
   
   DEV008
   
   '
   
    

   
   AND
   
    FSalary 
   
   BETWEEN
   
    
   
   3000
   
    
   
   AND
   
    
   
   6000
   
   
  
  

而如果将所有的复选框都不选中的时候就要使用下面的SQL语句： 

  
  

   
   SELECT
   
    
   
   *
   
    
   
   FROM
   
    T_Employee
  
  

要实现这种动态的SQL语句拼装，我们可以在宿主语言中建立一个字符串，然后逐个判断各个复选框是否选中来向这个字符串中添加SQL语句片段。这里有一个问题就是当有复选框被选中的时候SQL语句是含有WHERE子句的， 而当所有的复选框都没有被选中的时候就没有WHERE子句了，因此在添加每一个过滤条件判断的时候都要判断是否已经存在WHERE语句了，如果没有WHERE语句则添加WHERE语句。 在判断每一个复选框的时候都要去判断， 这使得用起来非常麻烦，“聪明的程序员是会偷懒的程序员”，因此开发人员想到了一个捷径：为SQL语句指定一个永远为真的条件语句（比如“1=1”），这样就不用考虑WHERE语句是否存在的问题了。伪代码如下：

  
  

   
   String sql 
   
   =
   
    
   
   "
   
    SELECT * FROM T_Employee WHERE 1=1
   
   "
   
   ; 

   
   if
   
   (工号复选框选中) 
{ 
sql.appendLine(
   
   "
   
   AND FNumber BETWEEN '
   
   "
   
   +
   
   工号文本框1内容
   
   +
   
   "
   
   ' AND '
   
   "
   
   +
   
   工号文本框2内容
   
   +
   
   "
   
   '
   
   "
   
   ); 
} 

   
   if
   
   (姓名复选框选中) 
{ 
sql.appendLine(
   
   "
   
   AND FName LIKE '%
   
   "
   
   +
   
   姓名文本框内容
   
   +
   
   "
   
   %'
   
   "
   
   ); 
} 

   
   if
   
   (年龄复选框选中) 
{ 
sql.appendLine(
   
   "
   
   AND FAge BETWEEN 
   
   "
   
   +
   
   年龄文本框1内容
   
   +
   
   "
   
    AND 
   
   "
   
   +
   
   年龄文本框2内容); 
} 
executeSQL(sql);
  
  

这样如果不选中姓名和年龄前的复选框的时候就会 执行 下面的SQL语句：

  
  

   
   SELECT 
   
   *
   
    FROM T_Employee WHERE 
   
   1
   
   =
   
   1
   
    
AND FNumber BETWEEN 
   
   '
   
   DEV001
   
   '
   
    AND 
   
   '
   
   DEV008
   
   '
   
    
AND FSalary BETWEEN 
   
   3000
   
    AND 
   
   6000
   
   
  
  

而如果将所有的复选框都不选中的时候就会执行下面的SQL语句：

  
  

   
   SELECT 
   
   *
   
    FROM T_Employee WHERE 
   
   1
   
   =
   
   1
   
   
  
  

这看似非常优美的解决了问题，殊不知这样很可能会造成非常大的性能损失，因为使用添加了“1=1”的过滤条件以后数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫对每行数据进行扫描（也就是全表扫描）以比较此行是否满足过滤条件，当表中数据量比较大的时候查询速度会非常慢。因此如果数据检索对性能有比较高的要求就不要使用这种“简便”的方式。下面给出一种参考实现，伪代码如下：

  
  

   
   private
   
    
   
   void
   
    doQuery() 
{ 
Bool hasWhere 
   
   =
   
    
   
   false
   
   ; 
StringBuilder sql 
   
   =
   
    
   
   new
   
    StringBuilder(
   
   "
   
    SELECT * FROM T_Employee
   
   "
   
   ); 

   
   if
   
   (工号复选框选中) 
{ 
hasWhere 
   
   =
   
    appendWhereIfNeed(sql, hasWhere);
sql.appendLine(
   
   "
   
   FNumber BETWEEN '
   
   "
   
   +
   
   工号文本框1内容
   
   +
   
   "
   
   ' AND '
   
   "
   
   +
   
   工号文本框2内容
   
   +
   
   "
   
   '
   
   "
   
   ); 
} 

   
   if
   
   (姓名复选框选中) 
{ 
hasWhere 
   
   =
   
    appendWhereIfNeed(sql, hasWhere); 
sql.appendLine(
   
   "
   
   FName LIKE '%
   
   "
   
   +
   
   姓名文本框内容
   
   +
   
   "
   
   %'
   
   "
   
   ); 
} 

   
   if
   
   (年龄复选框选中) 
{ 
hasWhere 
   
   =
   
    appendWhereIfNeed(sql, hasWhere); 
sql.appendLine(
   
   "
   
   FAge BETWEEN 
   
   "
   
   +
   
   年龄文本框1内容
   
   +
   
   "
   
    AND 
   
   "
   
   +
   
   年龄文本框2内容); 
} 
executeSQL(sql); 
} 

   
   private
   
    Bool appendWhereIfNeed(StringBuilder sql,Bool hasWhere) 
{ 

   
   if
   
   (hasWhere
   
   ==
   
   false
   
   ) 
{ 
sql. appendLine(
   
   "
   
   WHERE
   
   "
   
   ); 
} 

   
   else
   
    
{ 
sql. appendLine(
   
   "
   
   AND
   
   "
   
   ); 
} 
}
  
  

以上内容由博主摘自《程序员的SQL金典》。