Chuyển NULL thành 0 trong MySQL

Dùng hàm IFNULL() để chuyển NULL thành 0.

Cú pháp:

select ifnull(yourColumnName, 0) as anyVariableName from yourTableName;

Tạo bảng:

mysql> create table TypecastDemo
-> (
-> AccountNumber int
-> );
Query OK, 0 rows affected (0.84 sec)

Chèn dữ liệu có NULL:

mysql> insert into TypecastDemo values(NULL);
Query OK, 1 row affected (0.13 sec)
mysql> insert into TypecastDemo values(1234);
Query OK, 1 row affected (0.14 sec)
mysql> insert into TypecastDemo values(9876);
Query OK, 1 row affected (0.14 sec)
mysql> insert into TypecastDemo values(6666);
Query OK, 1 row affected (0.16 sec)
mysql> insert into TypecastDemo values(NULL);
Query OK, 1 row affected (0.27 sec)
mysql> insert into TypecastDemo values(NULL);
Query OK, 1 row affected (0.36 sec)
mysql> insert into TypecastDemo values(3214);
Query OK, 1 row affected (0.34 sec)

Hiển thị:

mysql> select *from TypecastDemo;

Kết quả:

+---------------+
| AccountNumber |
+---------------+
| NULL          |
| 1234          |
| 9876          |
| 6666          |
| NULL          |
| NULL          |
| 3214          |
+---------------+
7 rows in set (0.00 sec)

Áp dụng IFNULL:

mysql> select ifnull(AccountNumber,0) as TypeCastNullToZero from TypecastDemo;

Kết quả:

+--------------------+
| TypeCastNullToZero |
+--------------------+
| 0                  |
| 1234               |
| 9876               |
| 6666               |
| 0                  |
| 0                  |
| 3214               |
+--------------------+
7 rows in set (0.00 sec)

Nếu nhiều cột, dùng COALESCE.