Convert NULL data method through mysql-Mysql Tutorial-php.cn

Convert NULL data method through mysql

jacklove

Release： 2018-06-08 17:39:57

Original

1453 people have browsed it

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)

Copy after login

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

Example:
userTable structure and data

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

Copy after login

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

Copy after login

user_lastloginTable structure and data

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

Copy after login

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 |
+----+-----------+---------------+

Copy after login

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)

Copy after login

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 |
+----+-----------+---------------+

Copy after login

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:

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!