IFNULL and IN operations IN Mysql _ MySQL

BitsCN.com operations for IFNULL and IN Mysql

Mysql IFNULL operation

When a field in the SQL query is empty, the default value is set in the query result. The most stupid method is to process the query results, traverse the query results, and set the value when it is null:

The code is as follows:
$ Len = count ($ result );
For ($ I = 0; $ I <$ len; $ I ++ ){
$ Var = $ result [$ I] ['name'];
If (! $ Var ){
$ Result [$ I] ['name'] = 'default _ name ';
}
}

The above method is not only time-consuming, but also ?? Long H? Why? How good is it to solve it directly in ql statements.

The ISNULL method in SQL is described as follows:

ISNULL

Replace NULL with the specified replacement value.

Syntax

ISNULL (check_expression, replacement_value)

Parameters

Check_expression

Whether the expression is NULL is checked. Check_expression can be of any type.
Replacement_value

The expression returned when check_expression is NULL. Replacement_value must be of the same type as check_expresssion.

However, in Mysql, isnull is only used to determine whether it is null and cannot be replaced, an error is reported directly (Incorrect parameter count in the call to native function 'isnull' Errornumber: 1582 ).

In Mysql, how does one implement the ISNULL method in SQL?

IFNULL (check_expression, replacement_value) implements the ISNULL method in SQL.

See SQL statements

The code is as follows:
Mysql> select ifnull (1, 0 );
+ ------------- +
| IFNULL (1, 0) |
+ ------------- +
| 1 |
+ ------------- +
1 row in set

Because expr1 is 1 and not NULL, the function returns 1. we can try again. if we make expr1 NULL, can we return the second parameter? Let's make expr1 = 1/0. because the divisor is 0, the result is NULL.

The code is as follows:
Mysql> select ifnull (1/0, 'nowamagic ');
+ ------------------------- +
| IFNULL (1/0, 'nowamagic ') |
+ ------------------------- +
| Www.111cn.net |
+ ------------------------- +
1 row in set

If the preceding parameter is NULL, the second parameter www.111cn.net is returned.

This function is not hard to understand. why should we introduce it? This is the basic knowledge preparation for the advanced hacker technology in MySQL. you can try to understand such an hack SQL statement first.

The code is as follows:
UPDATE table SET views = '1' WHERE id =-2441 OR (ORD (MID (select ifnull (CAST (FirstName as char), 0x20) FROM nowamagic. 'tb2' order by id LIMIT 112),)> )#

Mysql IN operations

See the following SQL statement

The code is as follows:
Select product
From ProductParameter
Where val = 19116 OR val = 19127 OR val = 19128 OR val = 19119

OR is it complicated? Use IN to solve the problem:

The code is as follows:
Select product
From ProductParameter
Where val in (19116,191 27, 19128,191 19 ))

For more details, see: http://www.111cn.net/database/mysql/57520.htm

BitsCN.com