Detailed explanation of the correct use of the if function in MySQL

For what I am going to write today, the program ran for nearly 7 hours and stored 10 million pieces of data in the database. ——

What I want to talk about today is an example of the IF() function of the MySQL database.

The specific scenarios are as follows:

Let's take a look at the table structure first:

CREATE TABLE `message` (
 `id` varchar(30) NOT NULL,
 `title` varchar(30) DEFAULT NULL,
 `content` text,
 `send_time` datetime DEFAULT NULL,
 `type` int(1) DEFAULT NULL COMMENT '1: system notification, 2: complaint suggestion',
 `status` int(1) DEFAULT NULL COMMENT '0: waiting to be sent, 1: success, 2: failure',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The specific requirements are: to count the total number, successful number and failed number of system notifications and complaint suggestions respectively.

When encountering such a problem, our general idea is to use type grouping to query the total number of system notifications and complaints and suggestions respectively, and then use two subqueries to count the number of successful and failed items. The sql is as follows:

SELECT
 COUNT(1) total,
 m.type,
 (SELECT
   COUNT(1)
  FROM message ms
  WHERE ms.status = 1
    AND m.type = ms.type) successtotal,
 (SELECT
   COUNT(1)
  FROM message mf
  WHERE mf.status = 1
    AND m.type = mf.type) failtotal
FROM message m
GROUP BY m.type

Let's take a look at the running time. It takes about 6 minutes and 18 seconds to count 10 million pieces of data.

So is there a simpler and faster way to count? Of course there is, and that is the if() function we are mainly talking about today.

Basic syntax

IF(expr1, expr2, expr3) returns the value of expr2 if the value of expr1 is true, and returns the value of expr3 if the value of expr1 is false. It is a simple ternary expression.

How to do it

Let's talk about the idea. If we count the number of successful results, we can write if(status=1,1,0) like this. If status==1, it returns 1, otherwise it returns 0. Then we can add up the number of successful results using the SUM() function.

Implementation

The sql statement is as follows:

SELECT
 COUNT(1) total,
 m.type,
 SUM(IF(m.status = 1,1,0)) successtotal,
 SUM(IF(m.status != 1,1,0)) failtotal
FROM message m
GROUP BY m.type;

Doesn’t it look much simpler than the subquery above? Let’s take a look at the running time, which is only 1 minute and 30 seconds. Isn't it much faster?

So, have you learned it today?

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.