mysql - MYSQL中写IF..ELSE

Question

我有一张看起来像这样的 table

mysql> select * from rc;

    +----+----------------------------+----------------------------+----------------------------+
    | id | business_date              | cutoff_dt                  | recon_dt                   |
    +----+----------------------------+----------------------------+----------------------------+
    |  1 | 2017-03-21 16:50:29.032000 | 2017-03-21 16:50:31.441000 | 2017-03-21 16:50:32.832000 |
    +----+----------------------------+----------------------------+----------------------------+
    1 row in set (0.00 sec)

我要运行查询

-Select * from rc where business_date = '2017-03-17'
- 如果cutoff_dt`为null或为空，则显示null，否则显示not null

我把它写进了shell脚本。

#! /bin/bash
mysql -u root -p <<rcard
use rcard;
SELECT * 
(CASE WHEN (cut_off_dt = "NULL")
    THEN
      echo "Null"
    ELSE
       echo "NOT NULL"
END)
from rc WHERE business_date = '2017-03-17';
rcard

但是我得到错误

ERROR 1064 (42000) at line 2: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(CASE WHEN (cut_off_dt = "NULL")
    THEN
      echo "Null"
    ELSE
   ' at line 2

在 MySQL 中写 IF ELSE 的方法正确吗？

Answer 1

这不是检查 NULL 值的正确方法。

试试这个:

SELECT *,
       IF(mepslog_cut_off_dt IS NULL, 'NULL', 'NOT NULL')  
from rc_mepslog 
WHERE mepslog_business_date = '2017-03-17';

您也可以使用 CASE 表达式:

SELECT *,
       CASE 
          WHEN mepslog_cut_off_dt IS NULL THEN 'NULL'
          ELSE 'NOT NULL'
       END
from rc_mepslog 
WHERE mepslog_business_date = '2017-03-17';