SQL Case条件判断语句
问题描述：在表中取到一些值做出判断，配合监控监测一些表中的数据。使用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 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-01-29 12:10:01 | 2023-01-29 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_Test-MySQL-DB03-2023-01-29_12-10.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | full | 2023-01-30 00:20:01 | 2023-01-30 00:20:03 | success | 41M | /localbackup/backup_mariadb/Full_Test-MySQL-DB03-2023-01-30_00-20.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-01-30 12:10:01 | 2023-01-30 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_Test-MySQL-DB03-2023-01-30_12-10.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | full | 2023-01-31 00:20:01 | 2023-01-31 00:20:05 | success | 41M | /localbackup/backup_mariadb/Full_Test-MySQL-DB03-2023-01-31_00-20.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-01-31 12:10:01 | 2023-01-31 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_Test-MySQL-DB03-2023-01-31_12-10.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | full | 2023-02-01 00:20:01 | 2023-02-01 00:20:03 | success | 41M | /localbackup/backup_mariadb/Full_Test-MySQL-DB03-2023-02-01_00-20.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | success | 21M | /localbackup/backup_mariadb/Inc_Test-MySQL-DB03-2023-02-01_12-10.zip | | 54 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed | 0M | | | 54 | Test-MySQL-DB03 | 192.168.163.21 | inc | 2023-02-01 12:10:01 | 2023-02-01 12:10:04 | failed | 0M | | | 54 | Test-MySQL-DB03 | 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)
