低效的“WHERE 1=1”

ゝ一纸荒年。 2023-09-30 05:14 4阅读 0赞

网上有不少人提出过类似的问题：“看到有人写了WHERE 1=1这样的SQL，到底是什么意 
    思？”。其实使用这种用法的开发人员一般都是在使用动态组装的SQL。

**让我们想像如下的场景：用户要求提供一个灵活的查询界面来根据各种复杂的条件来查询员工信息，界面如下图：**  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16]

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

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16 1]

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

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16 2]

这里的数据检索与前面的数据检索都不一样，因为前边例子中的数据检索的过滤条件都是 
    确定的，而这里的过滤条件则随着用户设置的不同而有变化，这时就要根据用户的设置来动态组 
    装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”的过滤条件以后数据库系统就无法使用索引等查询优化策略，数据库系统将会被迫 对每行数据进行扫描（也就是全表扫描）以比较此行是否满足过滤条件，当表中数据量比较大的时候查询速度会非常慢。因此如果数据检索对性能有比较高的要求就不要使用这种“简便”的方 式。**

xml参考实现

使用<where></where>标签
    替代直接使用的where

![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_17_color_FFFFFF_t_70_g_se_x_16]

java参考实现，伪代码如下：

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"); 
    }
    }

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/5f456213b23e40928e6547fd98ec1a7c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/0df711a2c6ab4bbbbcebc25001ecbe8b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_13_color_FFFFFF_t_70_g_se_x_16 2]: https://img-blog.csdnimg.cn/29522284cfe3426382b1633a28cbee42.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a,size_13,color_FFFFFF,t_70,g_se,x_16
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a_size_17_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ce705f8463244d6e967a80ff532c8646.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5LyP5Yqg54m56YGH5LiK6KW_5p-a,size_17,color_FFFFFF,t_70,g_se,x_16