Convert NULL data method through mysql

Use mysql to query the database. When executing left join, some associated field contents are NULL. Therefore, after obtaining the record set, NULL data needs to be converted.

This article will provide a method to perform conversion processing directly at query time. The obtained record set does not need to be converted.
mysql provides IFNULL function

IFNULL(expr1, expr2)

If expr1 is not NULL, IFNULL() returns expr1, otherwise returns expr2

Example:
userTable structure and data

+-----+---------------+| uid | lastlogintime |

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

user_lastloginTable structure and data

+-----+---------------+|   1 |    1488188120 ||   3 |    1488188131 |
+-----+---------------+

Query user name and lastlogintime

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 not logged in, there is no record in the user_lastlogin table. So 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 |
+----+-----------+---------------+

This article explains the method of converting NULL data in mysql. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

Related content about PHP functions using a variable number of parameters

How to pass php calls Sina API to generate short links

Explain the method of sorting within the mysql group by group

The above is the detailed content of Convert NULL data method through mysql. For more information, please follow other related articles on the PHP Chinese website!