Mysql to convert NULL data (mandatory), mysqlnull

Mysql to convert NULL data (mandatory), mysqlnull

Use mysql to query the database.Left joinSome associated fields areNULLTherefore, after obtaining the record set, you need to convert NULL data.

This article provides a way to directly execute the Conversion Processing during the query. The obtained record set does not need to be converted.

Mysql provides IFNULL Functions

IFNULL (expr1, expr2)

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

Instance:

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 name and lastlogintime of a user

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. Therefore, lastlogintime is NULL.

Convert NULL to 0 using IFNULL

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 mysql-converted NULL data method (mandatory) is all the content shared by the editor. I hope you can give us a reference and support the house of helpers.