MySQL Ifnull and in operation

Mysql ifnull operation

Project, the value in the query results is set to the default value when the SQL query field is empty. The most stupid way of course is to process the query results, traversing the query results, when empty, set its value:

The code is as follows	Copy Code
$len =count ($result); for ($i =0; $i < $len; $i + +) { $var = $result [$i] [' name ']; if (! $var) { $result [$i] [' Name ']= ' Default_name '; } }

It is not only time consuming, but also. Long h curled? The QL statement directly solves how good ah.

There are isnull methods in SQL, described below:

ISNULL

Replaces NULL with the specified replacement value.

Grammar

ISNULL (Check_expression, Replacement_value)

Parameters

Check_expression

An expression that will be checked for null. Check_expression can be of any type.
Replacement_value

An expression to be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.

However, in MySQL, IsNull is only used to determine whether it is empty, can not implement the replacement function, as written above, will directly report the error (incorrect parameter count in the called to native function ' IsNull ' errornumber:1582).

So how does MySQL implement the IsNull method in SQL?

Ifnull (Check_expression, Replacement_value) implements the IsNull method in SQL.

See the SQL statement

The code is as follows	Copy Code
Mysql> SELECT ifnull (1,0); +-------------+ | Ifnull (1,0) | +-------------+ | 1 | +-------------+ 1 row in Set

Since EXPR1 is 1 and is not NULL, the function returns 1. We can try again, if let Expr1 be NULL, whether can return the second parameter? Let Expr1 = 1/0, because the divisor is 0, the result is NULL.

The code is as follows	Copy Code
Mysql> SELECT ifnull (1/0, ' nowamagic '); +-------------------------+ | Ifnull (1/0, ' nowamagic ') | +-------------------------+ | www.111cn.net | +-------------------------+ 1 row in Set

If the previous argument is NULL, the second argument is returned www.111cn.net.

This function is not difficult to understand, why do you want to introduce this function? This is to tell you the basics of advanced hacker technology under MySQL, so you can try to understand the hack SQL

The code is as follows	Copy Code
UPDATE table SET views = ' 1 ' WHERE id = -2441 OR (ORD (SELECT ifnull (CAST (FirstName as CHAR), 0x20) from Nowamagic ' ORDER by ID LIMIT 1,1, 2,1) ' >112) #

MySQL in Operation

See the following SQL statement

The code is as follows	Copy Code
Select Product From Productparameter where val = 19116 or val = 19127 or val = 19128 or val = 19119

Or a lot of it is not feel very cumbersome ah? In the solution:

code is as follows	copy code
Select Product from Productparameter where Val in (19116, 19127, 19128, 19119))