在MySQL中将0替换为null？
您可以NULLIF()在MySQL中使用NULL替换0。语法如下-
SELECT *,NULLIF(yourColumnName,0) as anyVariableName from yourTableName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table Replace0WithNULLDemo -> ( -> Id int NOT NULL auto_increment, -> Name varchar(20), -> Marks int, -> PRIMARY KEY(Id) -> );
现在，您可以使用insert命令在表中插入一些记录。查询如下-
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('John',76);
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('Carol',86);
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('Sam',0);
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('Mike',0);
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('Larry',98);
mysql> insert into Replace0WithNULLDemo(Name,Marks) values('Bob',0);使用select语句显示表中的记录。查询如下-
mysql> select *from Replace0WithNULLDemo;
以下是输出-
+----+-------+-------+ | Id | Name | Marks | +----+-------+-------+ | 1 | John | 76 | | 2 | Carol | 86 | | 3 | Sam | 0 | | 4 | Mike | 0 | | 5 | Larry | 98 | | 6 | Bob | 0 | +----+-------+-------+ 6 rows in set (0.00 sec)
现在让我们用NULL替换0。查询如下-
mysql> select *,NULLIF(Marks,0) as ReplaceZeroWithNULL from Replace0WithNULLDemo;
以下是显示新列的输出，其中new已用NULL替换了0-
+----+-------+-------+---------------------+ | Id | Name | Marks | ReplaceZeroWithNULL | +----+-------+-------+---------------------+ | 1 | John | 76 | 76 | | 2 | Carol | 86 | 86 | | 3 | Sam | 0 | NULL | | 4 | Mike | 0 | NULL | | 5 | Larry | 98 | 98 | | 6 | Bob | 0 | NULL | +----+-------+-------+---------------------+ 6 rows in set (0.00 sec)