SELECT * FROM table where 1=1

Transferred from: http://www.dzwebs.net/2418.html

Let's take a look at the result of this statement: SELECT * from table where 1=1, where the where 1=1, because 1=1 is always true, return to real, the condition is true; So, this statement is equivalent to the SELECT * from table, Returns all data in the query table.

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+ "an D 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.

Do I have to use where 1=1 to get a long term query?

Non-also, you love to use it, you do not like to use it, for you, do not lose anything, there will not be much;

So, how do I construct a dynamic query statement if I don't use the where 1=1 to do a long-term query? It's very simple and gives you a sense of thinking:

The following statement:

String mysqlstr= "SELECT * from table";  if(age.text.lenght>0) {querysqlstr=querysqlst r+ "age=" + "' Age.text '";}  if(address.text.lenght>0) {querysqlstr=querysqlstr+ "and ADD ress= "+" ' Address.text '";}  if(querysqlstr.lenght>0) {mysqlstr=mysqlstr+" where "+ Querysqlstr;}

Whether you use a long conditional query without the where 1=1, it is foolproof if you can guarantee that the query statement you construct is correct.

Iv. Summary of where 1=1

In order to facilitate the construction of dynamic multi-condition uncertain factors, a complex and correct query statement is adopted as a "lake-state means".

Such methods, generally, are not common in books, but in practical applications, people have to consider from a realistic point of view, that is, to ensure that the multi-conditional query can be met, but also to deal with the flexibility of uncertainty, and finally to ensure that the statement does not appear any grammatical errors.

This method is a good method, but because of this where 1=1 do not know how many novice, pondering how many times, always to no avail, at the same time, but also may mislead the novice astray.

SELECT * FROM table where 1=1