Use mysql to query the database. When executingleft join, some associated field contents areNULL. 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 providesIFNULLfunction
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!