Explain the correct use posture of if function in mysql in detail

For today's writing, the program ran for nearly 7 hours and stored 10 million pieces of data in the database. --

Today we are going to talk about an instance of the IF () function of the mysql database.

The specific scenarios are as follows,

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 : To be sent, 1 Success ,2 : Failure ',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Specific requirements are: Statistics system notification and complaint recommendations of the total number of messages, the number of success and failure of the number of messages.

In case of such a problem, our general idea is to use type grouping to query the total number of system notices and complaints and suggestions respectively, and then use two sub-queries to count the number of successful and failed items. 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 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 make statistics? Of course there is, that is, the if () function that we are mainly talking about today.

Basic grammar

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. Is a simple 3-order expression.

How to do it

Let's talk about it. If we count the number of successful entries, we can write if (status=1, 1, 0), so that if status==1, it will return 1, otherwise it will return 0. Then we can add the successful numbers through SUM () function.

Implementation mode

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;

To see if it is much simpler than the subquery above, let's look at the running time, which is only 1 minute and 30 seconds. Is it much faster?

So, did you learn today?