In project writing, it is common to use “where 1=1” in code. Why is this?

directory

  • Where 1=1
  • Do not use where 1=1 in multi-condition query confusion
  • Benefits of using where 1=1
  • Disadvantages of using where 1=1

Where 1=1

For example, there is a scenario where the DB database has a blog table (blog), and you want to query all the records in the blog table, then you can operate in two ways. Where (1=1) where (1=1)

  • Where keyword nothing
select * from blog;
Copy the code
  • Where 1=1
select * from blog where 1 = 1;
Copy the code

There is no difference between the results of the two SQL statement queries. So why add “1=1” after the WHERE keyword?

We know that 1=1 means true, eternal truth. A T0 error can be created if used improperly. For example, when writing SQL statements to perform a WHERE query with the OR operator will get unexpected results, the results will make you shiver. Don’t believe me, read on:

For example, when we want to delete the record with the blog ID called “202102111501”, we can write:

delete from blog where blogId = "202102111501"
Copy the code

If you add or 1=1 after the where statement, what will happen?

delete from blog where blogId = "202102111501" or 1 = 1
Copy the code

Or 1=1 will delete all entries in the table if the blog ID is 202102111501. Then you’re in trouble.

Do not bother where 1=1 in multi-condition query

For example, if you want to view a comment record in the current blog, then according to the usual dynamic query statement structure, the code is as follows:

String sql="select * from blog where";
if ( condition 1) {
  sql = sql + " blogID = 202102111501";
}
if (condition 2) {
  sql = sql + " and commentID = 150101";
}
Copy the code

If both if statements above are true, then the final dynamic SQL statement is:

select * from table_name where blogID = 202102111501 and commentID = 150101;
Copy the code

You can see that this is a complete and correct SQL query statement that executes correctly.

If both of the above if statements are false, then the final dynamic SQL statement is:

select * from table_name where;
Copy the code

The SQL statement is an error and cannot be executed because it does not have a condition after the WHERE keyword.

Benefits of using where 1=1

If we add 1=1 to the where condition, let’s see what it looks like:

String sql="select * from blog where 1=1";
if ( condition 1) {
  sql = sql + " and blogID = 202102111501";
}
if (condition 2) {
  sql = sql + " and commentID = 150101";
}
Copy the code

When both condition 1 and condition 2 are true, the SQL code executed above is as follows:

select * from blog where 1=1 and blogID = "202102111501" and commentID = 150101;
Copy the code

You can see that this is a complete and correct SQL query statement that executes correctly.

If both of the above if statements are false, then the final dynamic SQL statement is:

select * from table_name where 1=1;
Copy the code

SQL =”select * from table” SQL =”select * from table”

When 1=1 is added after the WHERE keyword, all data in the table will be returned if no fields are selected. If a single query is made based on a field, then the condition is queried at that time.

In fact, the application of WHERE 1=1 is not an advanced application, nor is it a so-called intelligent construction. It is simply a way to construct a correctly running dynamic SQL statement in order to satisfy the various factors of uncertainty in a multi-condition query page.

Disadvantages of using where 1=1

When we write SQL, add 1=1 after although can guarantee the syntax will not error!

select * from table_name where 1=1;
Copy the code

However, since there is no field named 1 in the table, this SQL statement is equivalent to select * from table. This SQL statement is obviously a full table scan, requiring a large amount of IO operations, and the larger the data volume, the slower it is.

So when querying where1=1 you need to add other conditions after it and index them appropriately, you can be much more efficient.

The article will be updated continuously. You can search “Maimo Coding” on wechat to read it for the first time. Every day to share quality articles, big factory experience, big factory face, help interview, is worth paying attention to every programmer platform.