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

This article is about SQL do not use count to find the existence of the example analysis, the editor feels very practical, so share with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.

There are only two states for querying "yes" and "no" from the database table according to a certain condition, so why do you have to SELECT count (*) when writing SQL?

Whether it is a new programmer who has just entered the profession, or a programmer who has been superb battlefield for many years, it is the same count.

The way most people write it at present

After several times of REVIEW code, such phenomena are found as follows:

In the business code, you need to query whether there are records based on one or more conditions, regardless of how many records there are. The common SQL and code writing methods are as follows

# SQL:

SELECT count (*) FROM table WHERE a = 1 AND b = 2

# Java:

Int nums = xxDao.countXxxxByXxx (params)

If (nums > 0) {

/ / execute the code here when it exists

} else {

/ / execute the code here when it does not exist

}

Does it feel very OK? there is no problem.

Remain skeptical about the way SQL is written

Optimization scheme

It is recommended to write as follows:

# SQL:

SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

# Java:

Integer exist = xxDao.existXxxxByXxx (params)

If (exist! = NULL) {

/ / execute the code here when it exists

} else {

/ / execute the code here when it does not exist

}

SQL no longer uses count, but uses LIMIT 1 instead, so that when a database query comes across, it will be returned, and do not continue to find how many entries are left.

Directly judge whether it is not empty in the business code.

SQL query speed is greatly improved

The more entries are found according to the query conditions, the more significant the performance improvement is, and in some cases, the creation of federated indexes can be reduced.

The above is the example analysis of SQL not using count to find out whether it exists. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.