Shulou(Shulou.com)06/01 Report--

The article is reproduced from the official Wechat account of "developer Round Table", which is about the introduction, advancement and trampling of developers.

What is the use of where 1 / 1? In SQL language, writing such a sentence is the same as not writing it, so why do you want to be thankless?

There is no difference between select * from table1 where 1 and select * from table1 at all, and there are even many other variants, such as 12thecontrol. The where condition is forever true, and the result is unconstrained.

This is used in SQL injection, for example, select * from table1 where name='test' forcibly adds select * from table1 where name='test' or 1 to 1, which becomes an unconstrained query again.

However, the beauty of where 1 is not only that, these methods of use may not be seen in books, most of them come from actual projects.

Multi-condition query

In the case of variable number of query conditions, where 1 query 1 can be very convenient to standardize the statement. For example, a query may or may not have name,age,phone query conditions, so what should be done?

Notice that an extra where 1 is written here.

String sql= "select * from table1 where 1 # 1"

StringBuffer buffer = new StringBuffer ()

Buffer.append (sql)

If (null! = name & &! ".equals (name)) {

Buffer.append ("and name =" + name)

}

If (null! = age & &! ".equals (age)) {

Buffer.append ("and age =" + age)

}

If (null! = phone & &! ".equals (phone)) {

Buffer.append ("and phone like'%" + phone + "%'")

}

If you don't write 1x1, you must determine whether there is a where clause in front of every query condition that is not empty, and then add where in the first place.

Of course, in general, it is a multi-table association query, so if the where clause has already appeared in SQL, then it is not necessary to add where 1 query, for example: String sql=select * from table1,table2 where table1.id=table2.id

If you are using ORM frameworks such as Hibernate or Mybatis, there is no need to worry about this. These ORM frameworks have been handled well. Here is a sample configuration of Mybatis:

Select * from table1

And cusId = # {cusId}

Table copy

The so-called copy of a table is to take a table as the source table and copy out a table with the same data and structure. Take Oracle as an example, the syntax of other databases is self-referenced, which is basically the same.

Copy table (where 1 is always true, copy table structure and data)

Create table_name as select * from source_table where 1

Copy table structure (where 11 is always false, do not copy data, only copy table structure)

Create table_name as select * from source_table where 11

There are many technical books that use rather complex judgment logic instead of where 1. From this point, we can see the difference between book knowledge and practice. In practice, developers will continue to use labor-saving methods instead of sticking to books.

There may be other applications of where 1, so you might as well leave a message and select it, so that more people can learn to use it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.