MySQL NULL data conversion method (must read)

When using MySQL to query the database and executing left join , some of the associated fields have NULL contents. Therefore, after obtaining the record set, the NULL data needs to be converted.

This article will provide a method to perform the conversion processing directly at query time. The acquired record set does not need to be converted again.

mysql provides the IFNULL function

IFNULL(expr1, expr2)

IFNULL() returns expr1 if expr1 is not NULL, otherwise it returns expr2

Examples:

User table structure and data

+----+-----------+
| id | name |
+----+-----------+
| 1 | Abby |
| 2 | Daisy |
| 3 | Christine |
+----+-----------+

user_lastlogin table structure and data

+-----+---------------+
| uid | lastlogintime |
+-----+---------------+
| 1 | 1488188120 |
| 3 | 1488188131 |
+-----+---------------+

Query the user's name and last login time

mysql> select a.id,a.name,b.lastlogintime from user as a left join user_lastlogin as b on a.id=b.uid;

+----+-----------+---------------+
| id | name | lastlogintime |
+----+-----------+---------------+
| 1 | Abby | 1488188120 |
| 2 | Daisy | NULL |
| 3 | Christine | 1488188131 |
+----+-----------+---------------+

Because the user with id=2 has never logged in, there is no record in the user_lastlogin table. Therefore lastlogintime is NULL.

Use IFNULL to convert NULL to 0

IFNULL(lastlogintime, 0)
mysql> select a.id,a.name,IFNULL(b.lastlogintime,0) as lastlogintime from user as a left join user_lastlogin as b on a.id=b.uid;
+----+-----------+---------------+
| id | name | lastlogintime |
+----+-----------+---------------+
| 1 | Abby | 1488188120 |
| 2 | Daisy | 0 |
| 3 | Christine | 1488188131 |
+----+-----------+---------------+

The above article on MySQL conversion of NULL data (must read) is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.