在MySQL中将NULL类型转换为0
您可以借助IFNULL()函数将NULL强制转换为0。语法如下-
select ifnull(yourColumnName) as anyVariableName from yourTableName;
要了解上述概念，让我们首先创建一个表-
mysql> create table TypecastDemo
−> (
−> AccountNumber int
−> );让我们插入一些带有NULL值的记录。插入记录的查询如下-
mysql> insert into TypecastDemo values(NULL); mysql> insert into TypecastDemo values(1234); mysql> insert into TypecastDemo values(9876); mysql> insert into TypecastDemo values(6666); mysql> insert into TypecastDemo values(NULL); mysql> insert into TypecastDemo values(NULL); mysql> insert into TypecastDemo values(3214);
现在，您可以在select语句的帮助下显示所有记录。查询如下-
mysql> select *from TypecastDemo;
以下是输出-
+---------------+ | AccountNumber | +---------------+ | NULL | | 1234 | | 9876 | | 6666 | | NULL | | NULL | | 3214 | +---------------+ 7 rows in set (0.00 sec)
应用我们上面看到的语法将NULL类型转换为0。查询如下-
mysql> select ifnull(AccountNumber,0) as TypeCastNullToZero from TypecastDemo;
以下是输出-
+--------------------+ | TypeCastNullToZero | +--------------------+ | 0 | | 1234 | | 9876 | | 6666 | | 0 | | 0 | | 3214 | +--------------------+ 7 rows in set (0.00 sec)
假设您需要多个列，则可以使用COALESCE。