How to convert null data in mysql

Mysql method of converting null data: use the IFNULL function. If expr1 is not NULL, [IFNULL()] returns expr1, otherwise it returns expr2, and the code is [IFNULL(expr1, expr2)].

The operating environment of this tutorial: Windows 7 system, mysql version 8.0.22, DELL G3 computer. This method is suitable for all brands of computers.

Related learning recommendations: mysql database

mysql method of converting null data:

Mysql provides the IFNULL function

IFNULL(expr1, expr2)

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

Example:

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 user’s 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)

The above is the detailed content of How to convert null data in mysql. For more information, please follow other related articles on the PHP Chinese website!