Using mysql to transform NULL data flow parsing

This article mainly introduces the use of mysql conversion NULL data flow parsing, the contents of the article are carefully selected and edited by the author, the use of mysql conversion NULL data flow parsing has a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the next topic content.

Using mysql to query the database, when performing left join, some of the associated field contents are NULL, so after obtaining the recordset, you need to transform the data of NULL.

Mysql provides the IFNULL function

IFNULL (expr1, expr2)

Returns expr1 if expr1 is not NULL,IFNULL (), otherwise returns expr2

Example:

User structure and data

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

User_ lastlogin table structure and data

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

Query name and lastlogintime of user

Mysql > select a. ID. A. Name. B. Lastlogintime from user as a left join user_lastlogin as b on a.id=b.uid +-1 | Abby | 1488188120 | 2 | Daisy | NULL | 3 | Christine | 1488188131 | +-+ -+

Because the user of id=2 has not logged in, it is not recorded in the user_lastlogin table. So lastlogintime is NULL.

Use IFNULL to convert NULL to 0

IFNULL (lastlogintime,0) mysql > select a.id as lastlogintime from user as a left join user_lastlogin as b on a.id=b.uid a.name as lastlogintime from user as a left join user_lastlogin as b on a.id=b.uid if null (b.lastlogintimepenol 0) +-1 | Abby | 1488188120 | 2 | Daisy | 0 | 3 | Christine | 1488188131 | +- -+

After reading the above about the use of mysql transformation NULL data process analysis, many readers must have some understanding, if you need to get more industry knowledge and information, you can continue to follow our industry information column.