读《程序员的SQL 金典》笔记 ------- 低效的“WHERE 1=1”

低效的“WHERE 1=1”

在工作中我们经常遇到根据用户输入不同而改变数据查询条件的需要，如：

根据用户输入的信息查询员工信息：

1、当工号项不为空时，根据输入的工号查询；

2、当年龄项不为空时，根据输入的年龄阶段查询；

3、当工资范围项不为空时，需对员工的工资项进行过滤等。

这里的过滤条件则随着用户设置的不同而有变化，这时就要根据用户的输入来动态组装SQL了。

 

要实现这种动态的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 FAge BETWEEN "+年龄文本框1内容+" AND "+年龄文本框2内容);
}
if(工资){
    sql.appendLine("AND FSalary BETWEEN "+工资文本框1内容+" AND "+工资文本框2内容);
}
executeSQL(sql);

 

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

 

private void doQuery(){
    boolean 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("FAge BETWEEN "+年龄文本框1内容+" AND "+年龄文本框2内容);
    }
    if(工资){
	hasWhere = appendWhereIfNeed(sql, hasWhere);
        sql.appendLine("FSalary BETWEEN "+工资文本框1内容+" AND "+工资文本框2内容);
    }
    executeSQL(sql);
}
private boolean appendWhereIfNeed(StringBuilder sql,boolean hasWhere){
    if(!hasWhere){
        sql.appendLine(" WHERE ");
    } else{
        sql.appendLine(" AND ");
    }
    return true;
}