Trong bài này mình sẽ hướng dẫn cách sử dụng hàm ISNULL
trong MySQL
thông qua cú pháp và ví dụ thực tế.
Bài viết này được đăng tại freetuts.net, không được copy dưới mọi hình thức.
1. Mô tả
Hàm ISNULL
kiểm tra xem một
biểu thức có phải là NULL hay không.
2. Cú pháp
Cú pháp của hàm ISNULL
trong MySQL
là:
Trong đó:
Bài viết này được đăng tại [free tuts .net]
expression
: Biểu thức để kiểm tra null
Lưu ý:
- Nếu biểu thức là giá trị NULL, hàm
ISNULL
sẽ trả về 1. - Nếu biểu thức không phải là giá trị NULL, hàm
ISNULL
sẽ trả về 0.
3. Version
Hàm ISNULL
có thể được sử dụng trong các phiên bản sau của MySQL
:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
4. Ví dụ
mysql> SELECT ISNULL['freetuts.net']; Ket qua: 0 mysql> SELECT ISNULL['']; Ket qua: 0 mysql> SELECT ISNULL[NULL]; Ket qua: 1 mysql> SELECT ISNULL[15]; Ket qua: 0
❮ Previous ❮ MySQL Functions Next ❯
Example
Test whether an expression is NULL:
SELECT ISNULL[NULL];
Try it Yourself »
Definition and Usage
The ISNULL[] function returns 1 or 0 depending on whether an expression is NULL.
If expression is NULL, this function returns 1. Otherwise, it returns 0.
Syntax
ISNULL[expression]
Parameter Values
expression | Required. The value to test |
Technical Details
From MySQL 4.0 |
More Examples
Example
Test whether an expression is NULL:
SELECT ISNULL[""];
Try it Yourself »
Example
Test whether an expression is NULL:
SELECT ISNULL[350];
Try it Yourself »
Example
Test whether an expression is NULL:
SELECT ISNULL["Hello world!"];
Try it Yourself »
❮ Previous ❮ MySQL Functions Next ❯
B.3.4.3 Problems with NULL Values
The concept of the NULL
value is a common source of confusion for newcomers to SQL, who often think that NULL
is the same thing as an empty string ''
. This is not the case. For example, the following statements are completely different:
mysql> INSERT INTO my_table [phone] VALUES [NULL];
mysql> INSERT INTO my_table [phone] VALUES [''];
Both statements insert a value into the phone
column, but the first inserts a NULL
value and the second inserts
an empty string. The meaning of the first can be regarded as “phone number is not known” and the meaning of the second can be regarded as “the person is known to have no phone, and thus no phone number.”
To help with NULL
handling, you can use the IS NULL
and
IS
NOT NULL
operators and the IFNULL[]
function.
In SQL, the NULL
value is never true in comparison to any other value, even NULL
. An expression that contains NULL
always produces a NULL
value unless otherwise indicated in the documentation for the
operators and functions involved in the expression. All columns in the following example return NULL
:
mysql> SELECT NULL, 1+NULL, CONCAT['Invisible',NULL];
To search for column values that are NULL
, you cannot use an expr = NULL
test. The following statement returns no rows, because expr =
NULL
is never true for any expression:
mysql> SELECT * FROM my_table WHERE phone = NULL;
To look for NULL
values, you must use the IS NULL
test. The following
statements show how to find the NULL
phone number and the empty phone number:
mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';
See Section 3.3.4.6, “Working with NULL Values”, for additional information and examples.
You can add an index on a column that can have NULL
values if you are using the MyISAM
, InnoDB
, or MEMORY
storage engine. Otherwise, you must declare an
indexed column NOT NULL
, and you cannot insert NULL
into the column.
When reading data with LOAD
DATA
, empty or missing columns are updated with ''
. To load a NULL
value into a column, use \N
in the data file. The literal word NULL
may also be used under some circumstances. See
Section 13.2.7, “LOAD DATA Statement”.
When using DISTINCT
, GROUP
BY
, or ORDER BY
, all NULL
values are regarded as equal.
When using ORDER BY
, NULL
values are presented first, or last if you specify DESC
to sort in descending order.
Aggregate [group] functions such as
COUNT[]
, MIN[]
, and SUM[]
ignore NULL
values. The exception to this is
COUNT[*]
, which counts rows and not individual column values. For example, the following statement produces two counts. The first is a count of the number of rows in the table, and the second is a count of the number of non-NULL
values in the age
column:
mysql> SELECT COUNT[*], COUNT[age] FROM person;
For some data types, MySQL handles NULL
values in special ways. For example, if you insert NULL
into an
integer or floating-point column that has the AUTO_INCREMENT
attribute, the next number in the sequence is inserted. Under certain conditions, if you insert NULL
into a TIMESTAMP
column, the current date and time is inserted; this behavior depends in part on the server SQL mode [see Section 5.1.11, “Server SQL Modes”] as well as the value of the explicit_defaults_for_timestamp
system variable.