IFNULL and IN operations IN Mysql

Source: Internet
Author: User
In the MysqlIFNULL operation project, 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: Code $ lencount ($ result); for ($ i0; $ I $ len; $ I ++) {$ var $ result [$ I] [

Mysql IFNULL operation is used in the project. When a field in SQL query is null, 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: Code $ len = count ($ result ); for ($ I = 0; $ I $ len; $ I ++) {$ var = $ result [$ I] ['

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 ') |
+ ------------------------- +
|
+ ------------------------- +
1 row in set

If the preceding parameter is NULL, the second parameter 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 ))

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.