mysql sql

1、最近连续失败5次的sql

SELECT INTERFACE_NAME, COUNT(*) AS fail_count
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY INTERFACE_NAME ORDER BY REQUEST_TIME DESC) AS rn,
    ROW_NUMBER() OVER (PARTITION BY INTERFACE_NAME, INTERFACE_STATUS ORDER BY REQUEST_TIME DESC) AS rn_status
  FROM adg_request_record where REQUEST_TIME>='2023-03-17 00:00:00' and REQUEST_TIME<='2023-03-21 23:59:59'
) t
WHERE INTERFACE_STATUS = '0' 
-- AND rn = rn_status
GROUP BY INTERFACE_NAME,(rn-rn_status)
HAVING COUNT(*) >= 5

2、获取当前库所有表中所有varchar类型的字段，字符集改为utf8mb4，排序规则改成utf8mb4_general_ci的sql

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE,
'(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci',
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),' COMMENT \'', COLUMN_COMMENT , '\';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = (SELECT database())
AND DATA_TYPE = 'varchar';

3、获取当前库中所有表，修改默认字符集为utf8mb4，排序规则为utf8mb4_general_ci的sql

SELECT CONCAT('ALTER TABLE `', table_name, '` character SET utf8mb4 COLLATE utf8mb4_general_ci', ';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = (SELECT database());