The role of where 1=1 and 0=1 are used in MySQL

When working with MySQL, you often use the WHERE statement to query. When the where statement does not exist, often adds a where 1=1

where 1=1; This condition is always true, and in the case of an indefinite number of query conditions, the 1=1 can be a convenient specification statement.

One, without the trouble of where 1=1 in the multi-condition query

For example, if you make a query page, and, there are many options to query, but also let the user choose and input query keywords, then, according to the usual query statement dynamic construction, the code is generally as follows:

String Mysqlstr=Select * FromTablewhere”； 　　 If(Age.Text.Lenght>0) 　　 { 　　　　 Mysqlstr=mysqlstr+ "age=" + " span class= "str" > ' age.text '  " } if (address. Text. Lenght>0)  { mysqlstr=mysqlstr+ "and address= "+"  Address.text ' }       

The first of these assumptions

If the above two if judgment statements are true, that is, the user has entered the query term, then the final mysqlstr dynamic construction statement becomes:

MySqlStr=”select * from table where Age=‘18‘ and Address=‘云南省文山州广南县小波吗村‘”

It can be seen that this is a complete and correct SQL query statement that is executed correctly and returns data based on the existence of a record of the database.

The second hypothesis

If the above two if judgment statements are not true, then the final mysqlstr dynamic construct statement becomes:

MySqlStr=”select * from table where“

Now, let's take a look at this statement, because the WHERE keyword needs to use the condition, but this statement does not exist at all, so the statement is an error statement, must not be executed, not only error, but also do not query any data.

The above two hypotheses represent the application of reality, indicating that there is a problem in the construction of the statement, which is not enough to deal with the flexible query conditions.

Ii. benefits of using where 1=1

If we change the above statement to:

String Mysqlstr=Select * FromTablewhere 1=1 ”； 　　 If(Age.Text.Lenght>0) 　　 { 　　　　 Mysqlstr=Mysqlstr+ "and age=" + "  ' age.text '  " } if (address. Text. Lenght>0)  { mysqlstr=mysqlstr+ "and address= "+"  Address.text ' }       

Now, there are two assumptions

The first kind of hypothesis

If all two if are true, then the statement becomes:

MySqlStr=”select * from table where 1=1 and Age=‘18‘ and Address=‘云南省文山州广南县小波吗村‘”

Obviously, the statement is a correct statement, can be executed correctly, if the database has records, will certainly be queried.

The second hypothesis

If none of the two if are true, then the statement becomes:

MySqlStr=”select * from table where 1=1”，

Now, let's take a look at this statement, because where 1=1 is a statement that is true, so that the syntax of the statement is correct and can be executed correctly, which is equivalent to: mysqlstr= "select * from table", which returns all the data in the table.

The implication is: if the user in the multi-criteria query page, do not select any field, do not enter any keywords, then will return all the data in the table, if the user in the page, select a section of the field and entered a part of the query keyword, then the user set conditions to query.

Speaking of which, I do not know whether you understand, in fact, where 1=1 application, is not a high-level application, is not the so-called intelligent construction, just to meet the multi-conditional query page of the uncertainties of a variety of factors used to construct a correctly can run dynamic SQL statement of a method.

where 1=0; This condition is always false, the result will not return any data, only the table structure, can be used to quickly build the table

"SELECT * FROM strName WHERE 1 = 0";

This SELECT statement is primarily used to read the structure of a table without regard to the data in the table, saving memory because you can save the result set.

create table newtable as select * from oldtable where 1=0;

Creates a new table, and the structure of the new table is the same as the structure of the queried table.

The role of where 1=1 and 0=1 are used in MySQL