問題描述：在表中取到一些值做出判斷，配合監控監測一些表中的資料。使用select case when if 來做條件查詢判斷
CASE 表示式遍歷條件並在滿足第一個條件時返回一個值（類似於 if-then-else 語句）。 因此，一旦條件為真，它將停止讀取並返回結果。 如果沒有條件為真，則返回 ELSE 子句中的值。
如果沒有 ELSE 部分並且沒有條件為真，則返回 NULL。
case when語法結構
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
表結構：
MariaDB [test]> desc backup_job_details; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | server_id | int(11) | YES | | NULL | | | host_name | varchar(20) | YES | | NULL | | | host_ip | varchar(50) | YES | | NULL | | | backup_type | char(20) | YES | | NULL | | | start_time | datetime | YES | | NULL | | | end_time | datetime | YES | | NULL | | | backup_process | char(20) | YES | | NULL | | | backup_size | char(20) | YES | | NULL | | | backup_dir | varchar(200) | YES | | NULL | | +----------------+--------------+------+-----+---------+-------+ 9 rows in set (0.002 sec)
資料：
MariaDB [test]> select * from backup_job_details; +-----------+-----------------+---------------+-------------+---------------------+---------------------+----------------+-------------+-----------------------------------------------------------------------+ | server_id | host_name | host_ip | backup_type | start_time | end_time | backup_process | backup_size | backup_dir | +-----------+-----------------+---------------+-------------+---------------------+---------------------+----------------+-------------+-----------------------------------------------------------------------+ | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-01-29 12:10:01 | 2023-01-29 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-29_12-10.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | full | 2023-01-30 00:20:01 | 2023-01-30 00:20:03 | success | 41M | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-01-30_00-20.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-01-30 12:10:01 | 2023-01-30 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-30_12-10.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | full | 2023-01-31 00:20:01 | 2023-01-31 00:20:05 | success | 41M | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-01-31_00-20.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-01-31 12:10:01 | 2023-01-31 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-01-31_12-10.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | full | 2023-02-01 00:20:01 | 2023-02-01 00:20:03 | success | 41M | /localbackup/backup_mariadb/Full_HKTESTMYSQLDB03-2023-02-01_00-20.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_HKTESTMYSQLDB03-2023-02-01_12-10.zip | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed | 0M | | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed | 0M | | | 54 | HKTESTMYSQLDB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | success | 10M
1.備份表，通過判斷備份表中最後一次備份是否成功，或者失敗。成功返回success，失敗返回failed
select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1;
如果最後一條資料的backup_process 欄位資料為success，sql返回結果就是'success'
MariaDB [(none)]> select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1; +----------------+ | backup_process | +----------------+ | success | +----------------+ 1 row in set (0.001 sec)
如果最後一條資料的backup_process 欄位資料為其它，sql返回結果就是'failed'
MariaDB [(none)]> select case when backup_process = 'success' then 'success' when backup_process != 'success' then 'failed' end as backup_process from test.backup_job_details order by server_id DESC limit 1;
+----------------+
| backup_process |
+----------------+
| failed |
+----------------+
1 row in set (0.001 sec)
2.備份表，查詢最後一條備份記錄後的一段時間段內，有沒有新的備份記錄產生，如果沒有，返回failed；如果備份記錄正常產生，返回success。如果新的備份記錄沒有在規定時間內被記錄，此時備份指令碼狀態可能有異常。
SELECT (CASE WHEN TIMESTAMPDIFF(HOUR,end_time, now()) > 14 THEN "failed" ELSE "success" END )AS TIME_DIFF FROM test.backup_job_details order by end_time DESC limit 1;
通過判斷最後一條記錄的備份時間跟now()此時的時間比較，判斷下一次的記錄有沒有在規定的時間產生，以此來判斷備份指令碼的執行狀態
MariaDB [(none)]> SELECT (CASE WHEN TIMESTAMPDIFF(HOUR,end_time, now()) > 14 THEN "failed" ELSE "success" END )AS TIME_DIFF FROM test.backup_job_details order by end_time DESC limit 1; +-----------+ | TIME_DIFF | +-----------+ | success | +-----------+ 1 row in set (0.001 sec)