The role of SQL where 1=1 and 0=1

　

The role of SQL where 1=1 and 0=1

Excerpt from: http://www.cnblogs.com/junyuz/archive/2011/03/10/1979646.html

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

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 * from table where";

if (age.text.lenght>0)
{
mysqlstr=mysqlstr+ "age=" + "' age.text '";
}

if (address.text.lenght>0)
{
mysqlstr=mysqlstr+ "and address=" + "' address.text '";
}

Hypothesis of ①

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= ' and address= ' Yunnan province Wenshan Prefecture Guangnan County wavelet Village '"

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.

Hypothesis of ②

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 * from table where 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

Hypothesis of ①

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

Mysqlstr= "SELECT * from table where 1=1 and age= ' and address= ' Yunnan Wenshan Prefecture Guangnan County wavelet Village '", it is clear that the statement is a correct statement, can be executed correctly, if the database has a record, will certainly be checked To be consulted.

Hypothesis of ②

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, it functions equivalent to: mysqlstr= " SELECT * FROM table, which returns all 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 SQL where 1=1 and 0=1