在MySQL中如何检查字段是否为空或null?
MySQL中的NULL和空字符串”是不同的。为了检查字段是否为空,如‘’或为null,您需要使用IS NULL属性或其他方法。您可以使用CASE语句检查所有条件。 语法如下:
SELECT *, CASE
WHEN yourColumnName =‘’ THEN ‘yourMessage1’
WHEN yourColumnName IS NULL THEN ‘yourMessage2’
ELSE CONCAT(yourColumnName,'是一个名称')
END AS anyVariableName
FROM yourTableName;
为了理解上述语法,让我们创建一个表。创建表的查询如下所示:
mysql> create table checkFieldDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name varchar(10),
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.64 sec)
使用INSERT命令在表中插入一些记录。查询如下所示:
mysql> insert into checkFieldDemo(Name) values(NULL);
Query OK, 1 row affected (0.17 sec)
mysql> insert into checkFieldDemo(Name) values('John');
Query OK, 1 row affected (0.15 sec)
mysql> insert into checkFieldDemo(Name) values(NULL);
Query OK, 1 row affected (0.10 sec)
mysql> insert into checkFieldDemo(Name) values('');
Query OK, 1 row affected (0.51 sec)
mysql> insert into checkFieldDemo(Name) values('Carol');
Query OK, 1 row affected (0.14 sec)
mysql> insert into checkFieldDemo(Name) values('');
Query OK, 1 row affected (0.17 sec)
mysql> insert into checkFieldDemo(Name) values(NULL);
Query OK, 1 row affected (0.10 sec)
使用SELECT语句显示表中的所有记录。查询如下所示:
mysql> select *from checkFieldDemo;
以下是输出结果:
+----+-------+
| Id | Name |
+----+-------+
| 1 | NULL |
| 2 | John |
| 3 | NULL |
| 4 | |
| 5 | Carol |
| 6 | |
| 7 | NULL |
+----+-------+
7 rows in set (0.00 sec)
以下是使用case语句检查字段是否为空或null或其他内容的查询。查询如下所示:
mysql> select *,case
-> when Name='' then 'It is an empty string'
-> when Name is null then 'It is a NULL value'
-> else concat(Name,'是一个名称')
-> END as ListOfValues
-> from checkFieldDemo;
以下是输出结果:
+----+-------+-----------------------+
| Id | Name | ListOfValues |
+----+-------+-----------------------+
| 1 | NULL | It is a NULL value |
| 2 | John | John is a Name |
| 3 | NULL | It is a NULL value |
| 4 | | It is an empty string |
| 5 | Carol | Carol is a Name |
| 6 | | It is an empty string |
| 7 | NULL | It is a NULL value |
+----+-------+-----------------------+
7 rows in set (0.00 sec)
阅读更多:MySQL 教程