Click on “SQL database development”, \

Set it as “top or star mark” to deliver dry goods in the first time

Programmers often use “where 1=1” in their code during programming. Why?

SQL injection

Where 1=1 where 1=1 where 1=1 where 1=1 Such as:

select* from customers; withselect * from customers where 1=1;
Copy the code

The results are exactly the same.

Yes, the above query results in no difference, but that’s not what we added it for. We know that 1=1 means true, that is, forever true, when SQL injection with or operator will get unexpected results.

For example, when we want to delete a record with the customer name “Zhang SAN”, we can write:

delete from customers where name='Joe'
Copy the code

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

That is:

delete from customers where name='Joe' or 1=1
Copy the code

If or 1=1, all entries in the table will be deleted.

Of course, we must not do this kind of thing, also do not allow others to take advantage of, here is just to express where 1=1 one of the functions.

Grammar specification

We use where 1=1 when we write code to make sure that the syntax is correct. \

Let’s start with the following Java code:

String sql="select * from table_name where 1=1";
if( condition 1) {
  sql=sql+" and var2=value2";
}
if(condition 2) {
  sql=sql+" and var3=value3";
}
Copy the code

If condition 1 is true, the following SQL code will be executed after concatenation:

select * from table_name where and var2=value2;
Copy the code

There is an obvious SQL syntax error: and must be conditional before and after.

Some people say I just write where in the if statement, so I don’t write where 1=1.

String sql="select * from table_name";
if( condition 1) {
  sql=sql+" where var2=value2 ";
}
if(condition 2) {
  sql=sql+" where var3=value3";
}
Copy the code

If condition 1 is true and condition 2 is false, the following SQL code is executed:

select * from table_name where var2=value2;
Copy the code

This is true, but what if condition 1 and condition 2 are both true? The SQL statement then looks like this:

select * from table_name 
where var2=value2 
where var3=value3;
Copy the code

This is clearly not SQL syntactic.

1=1 where 1=1 where 1=1 where 1=1 where 1=1 where 1=1 where 1=1

Copy the table

When backing up data, we often use where 1=1. Of course, we can not write where 1=1, but if we want to filter some data after writing it, it will be more convenient to add and conditions at the end. \

create table  table_name
as   
select * from  Source_table
where   1=1;
Copy the code

Replicate table structure

Select * from table where 1=1 where 1<>1 and 1=2 where 1<>1 and 1=2 \

create table  table_name
as   
select  * from   
Source_table where   1 <> 1;
Copy the code

The disadvantages of 1 = 1

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

select * from table 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.

-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Recommended reading domestic database modeling tools, see the interface at first glance, conscience! Kuaishou company toilet pit timer, net friend: no more paid shit! How to elegantly optimize a girl's computer? The most complete set of MySQL interview questions has this4The script tool, the boss is no longer afraid of me to write bad SQL this is a can learn the technology of the public number, welcome to pay attention to click "read the text" understand SQL training campCopy the code