SQL where 1=1 effect

The role of SQL where 1=1 and 0=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.   A, do not use where  1=1   distress in the multi-conditional query   For example, if you do a query page, and, can query the options have more than one, but also let the user choose and input query keywords, then, according to the usual query statement dynamic construction, The code is roughly 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   If the above two if judgment statements are true, that is, the user has entered a query term, then the final mysqlstr dynamic construction statement becomes:  mysqlstr= "select * from table where age= ' 18 ' & Nbsp;and address= ' Yunnan Wenshan Prefecture Guangnan County wavelet Village ' "   can be seen, this is a complete correct SQL query statement, can be executed correctly, and based on the existence of the database records, return data.  ② 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 clause, because the WHERE keyword after the use of conditions, but this statement does not exist at all, so the statement is an error statement, it must not be executed, not only error, but also do not query any data.   The two assumptions above represent the practical application, indicating that there is a problem in the construction of the statement, which is not enough to deal with the flexible query conditions.   Second, use where  1=1   benefits   If we change the above statement to:  string mysqlstr= "select * FROM table where &NBsp;1=1 ";    if (age.text.lenght>0) {mysqlstr=mysqlstr+" and age= "+" ' age.text ' ";}  if (Addre Ss. text.lenght>0) {mysqlstr=mysqlstr+ "and address=" + "' address.text '";}  now there are two assumptions  ① kind of hypothesis   if the two if are Established, 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 records, will certainly be queried.    ② hypothesis   If none of the two if is true, then the statement becomes:  mysqlstr= "select * from table where 1=1", now we 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 meaning 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 the criteria to query.   Here, don't know if you understand, in fact, where 1=1 application, is not a high-level application, is not so-called intelligent construction, It is just a way to construct a dynamic SQL statement that can be run correctly in order to satisfy the uncertainties in the multi-criteria query page.  where 1=0; This condition is always false, and the result does not return any data, only the table structure, which 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.  

SQL where 1=1 effect