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

This article mainly introduces "what is the meaning of where1=1 in mysql". In daily operation, I believe that many people have doubts about the meaning of where1=1 in mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "what is the meaning of where1=1 in mysql?" Next, please follow the editor to study!

Where 1 # 1

Let's take a look at a piece of code.

Select count (id) from t_book t where 1 AND title = # {title} AND author = # {author}

The above code is familiar, that is, the total number of queries that meet the criteria. In mybatis, the if tag is often used to judge the condition after the where clause, in order to prevent the sql from reporting an error because the first field is empty. Yes, when faced with multiple query conditions, using where 1 query 1 can easily solve the problem that our conditions are empty, so what's the problem with writing this way?

Many people on the Internet say that this will cause performance problems and may invalidate the index, so let's test it today to see if we will not leave the index.

Actual measurement

The title field has been indexed, so let's take a look at it through EXPLAIN

EXPLAIN SELECT * FROM t_book WHERE title = 'and on earth'

EXPLAIN SELECT * FROM t_book WHERE 1, 1 AND title = 'and in the world'

Comparing the above two, we can see that both possible_keys (the index that may be used) and key (the index actually used) use the index for retrieval.

Conclusion

Where 1 will also go to the index, which does not affect the query efficiency. The sql instructions we write will be parsed and optimized by mysql into their own processing instructions, and meaningless conditions such as 1: 1 will be optimized in the process. Using explain EXTENDED sql for proofreading, it is found that conditions such as where1=1 are optimized by mysql's optimizer.

Well, we can change the writing method in mybatis, because after all, the mysql optimizer also takes time. Although it is indexed, it will have an impact when the amount of data is large, so we recommend that the code be written as follows:

Select count (*) from t_book t title = # {title} AND author = # {author}

We use the where tag instead.

Where tag

MyBatis has a simple solution that is suitable for most scenarios. In other scenarios, it can be customized to meet your needs. This requires only one simple change:

SELECT * FROM BLOG state = # {state} AND title like # {title} AND author_name like # {author.name}

The where element inserts a "WHERE" clause only if the child element returns anything. Also, if the clause begins with "AND" or "OR", the where element removes them.

Or use where 1 to 1

At this point, the study of "what is the meaning of where1=1 in mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.