background

If you are new to SQL, you may be confused when you see where 1 = 1 in SQL statements. If you have been using SQL statements like this for a long time, you may be used to it.

Do you remember why you wrote it in the first place? Are there performance issues? Is there a better way to write it?

In today’s article, we will take you through the use and improvement of where 1 = 1 from beginning to end, and maybe you can get more unexpected gains from it.

Where 1=1

The purpose of adding 1=1 to a SQL statement’s WHERE condition is simple: to make the WHERE condition statement always true. It’s essentially where condition, but it’s always true, so there’s no constraint on it.

This statement is used in the following scenarios: dynamically building SQL statements.

String sql = "select * from t_user where 1=1 "; if(! b.equals("")){ sql += "and b='"+b+"'"; }Copy the code

Use where 1=1 when spelling the above statement. If b is not null, you can concatenate the “and” statement without causing syntax errors. If there is no where 1=1, the original SQL statement becomes (assuming b passes “ABC”) :

"select * from t_user  where and  b= 'abc' "; 
Copy the code

Obviously, the above SQL statement will have syntax errors. Therefore, the reason for adding 1=1 is to facilitate SQL concatenation.

On the other hand, not only 1=1 can be used this way, but expressions like: 1<>2, 2>1, ‘a’=’a’ can be used this way, as long as the result is true.

where 1<>1

Where 1=1 where 1<>1 where 1<>1

Where 1<>1 can be used in some scenarios, such as fetching table structure without fetching data.

create table t_temp as select * from t_user  where 1<>1
Copy the code

The above statement creates a new table T_TEMP with the same structure as the T_USER table but without any data.

Of course, other structures than table structures can be used in the same way.

Where 1=1 performance issues

Some people say that using where 1=1 May cause performance problems, so let’s experiment directly.

Mysql 8.0.18 alter table t_user; select * from id_no;

explain select * from t_user where id_no = 'Tom25';
explain select * from t_user where 1=1 and id_no = 'Tom25';
Copy the code

Execute the above two SQL statements, explain results are as follows:

In other words, the condition 1=1 does not affect the index and performance, and there is no essential difference between the two from explain results.

Different SQL statements render the same result because they are optimized by Mysql. Mysql optimizes instructions for invalid conditions such as 1=1. This is somewhat like the Java compiler, which optimizes many invalid judgments or statements when they are encoded into bytecode.

Where 1=1

Although 1=1 will be optimized by the optimizer, the optimization itself will still consume MySQL performance, and it would be nice if this situation could be avoided at all.

For example, Mybatis, where 1=1 is usually written as follows:

<select id="queryUser" parameterType="com.choupangxia.entity.User" resultType="java.lang.Integer"> select count(id) from  t_user u where 1=1 <if test="username ! =null and username ! ='' "> AND u.username = #{username} </if> <if test="userNo ! =null and userNo ! ='' "> AND u.user_no = #{userNo} </if> </select>Copy the code

Where 1=1 does the same thing.

instead of where 1=1:

<select id="queryUser" parameterType="com.choupangxia.entity.User" resultType="java.lang.Integer"> select count(id) from  t_user u <where> <if test="username ! =null and username ! ='' "> u.username = #{username} </if> <if test="userNo ! =null and userNo ! ='' "> AND u.user_no = #{userNo} </if> </where> </select>Copy the code

In this way, MySQL can reduce the performance loss caused by optimizing conditions such as 1=1 in the case of large query data.

summary

In this article, we start with the common where 1=1 usage, discuss the usage scenarios, MySQL optimization, and extended use of WHERE 1<>1, and how to further improve based on the common Mybatis framework.

In fact, writing this article to convey an idea is: no matter how small, no matter how accustomed things are, if you think and study, you will learn a lot of relevant knowledge points, and you can further optimize them.

Once there was a technology guru said: no matter how simple a piece of code is, if the page view is expanded 100 times, 1000 times, there will be problems. Don’t stop thinking and learning!

About the blogger: Author of the technology book SpringBoot Inside Technology, loves to delve into technology and writes technical articles.

Public account: “program new vision”, the blogger’s public account, welcome to follow ~

Technical exchange: Please contact the weibo user at Zhuan2quan