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. 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. Bài viết này sẽ hướng dẫn bạn cách map giá trị NULL vào giá trị có ý nghĩa khác thay vì chỉ hiển thị là NULL. Dr.E.F.Codd, là người tạo ra mô hình quan hệ trong database và giới thiệu
khái niệm NULL có nghĩa là giá tị unknown hoặc không có thông tin. MySQL cũng hỗ trợ NULL với ý nghĩa là thiếu thông tin hoặc thông tin không khả dụng. Trong bảng dữ liệu, nếu như lưu giá trị NULL và khi truy vấn để hiển thị ra màn hình hoặc bất kỳ báo cáo thì giá trị đó sẽ không hiển thị gì lên. Để tăng tính đọc được và hiểu được thì bạn phải hiển thị giá trị NULL bằng một giá trị unknown, missing hoặc không khả dụng (N/A). Để làm điều này thì bạn có thể sử dụng hàm
IF. IF(exp,exp_result1,exp_result2);
Nếu như mà exp là TRUE (exp <> 0 và exp <> NULL ), thì hàm IF sẽ trả về giá trị của exp_result1 nếu không thì trả về giá trị củaexp_result2. Giá trị trả về phụ thuộc vào biểu thức exp_result1 và exp_result2 và có thể là string hoặc number. Để hiểu hơn thì chúng ta sẽ làm một số ví dụ. Chúng ta sẽ sử dụng bảng customers trong database mẫu Lấy một số thông tin trong bảng customers: SELECT
customername, state, country
FROM
customers
ORDER BY country;
Ở kết quả truy vấn trên thì một số giá trị của cột state không có. Chúng ta sẽ sử dụng hàm IF để hiển thị giá trị N/A thay vì NULL: SELECT
customername, IF(state IS NULL, 'N/A', state) state, country
FROM
customers
ORDER BY country;
Ngoài hàm IF, chúng ta có thể sử dụng hàm IFNULL để xử lý trực tiếp giá trị NULL. Cú pháp như sau: Hàm IFNULL sẽ trả về giá trị biểu thức exp_result nếu exp là NULL nếu không thì trả về giá trị của exp. Xem ví dụ dưới đây: SELECT customername,
IFNULL(state,'N/A') state,
country
FROM customers
ORDER BY country;
Vậy là với 2 hàm IF và IFNULL thì bạn có thể tùy ý hiển thị giá trị thay thế NULL một cách hợp lý. Giúp cho việc đọc dữ liệu dễ hơn và hiểu hơn.
| 
