为MySQL中的枚举值设置自定义消息
使用ifelse设置枚举的自定义消息。让我们首先创建一个表-
mysql> create table DemoTable1992 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), isActive ENUM('Y','N') );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1992(ClientName,isActive) values('Chris','N'); mysql> insert into DemoTable1992(ClientName,isActive) values('Bob','N'); mysql> insert into DemoTable1992(ClientName,isActive) values('David','Y'); mysql> insert into DemoTable1992(ClientName,isActive) values('Carol','N');
使用select语句显示表中的所有记录-
mysql> select * from DemoTable1992;
这将产生以下输出-
+----------+------------+----------+ | ClientId | ClientName | isActive | +----------+------------+----------+ | 1 | Chris | N | | 2 | Bob | N | | 3 | David | Y | | 4 | Carol | N | +----------+------------+----------+ 4 rows in set (0.00 sec)
这是使用if设置自定义消息的查询
mysql> select ClientName,if(isActive='Y','Active Member','Not Active Member') as Result from DemoTable1992;
这将产生以下输出-
+------------+-------------------+ | ClientName | Result | +------------+-------------------+ | Chris | Not Active Member | | Bob | Not Active Member | | David | Active Member | | Carol | Not Active Member | +------------+-------------------+ 4 rows in set (0.00 sec)