@Data
public class SlowQuery {
    private double queryTime;
    private double lockTime;
    private String sqlQuery;
    private String tableName;
    private Date executionDate;
}

public class MySQLSlowQueryLogParser {
    //  正則表達式匹配 慢日志內(nèi)容格式抓取
    private static final Pattern QUERY_TIME_PATTERN = Pattern.compile("# Query_time: (\\d+\\.\\d+)");
    private static final Pattern LOCK_TIME_PATTERN = Pattern.compile("  Lock_time: (\\d+\\.\\d+)");
    private static final Pattern TIMESTAMP_PATTERN = Pattern.compile("SET timestamp=(\\d+);");
    public static void main(String[] args) {
        MySQLSlowQueryLogParser parser = new MySQLSlowQueryLogParser();
        //  慢查詢?nèi)罩敬娣怕窂?
        String filePath = "D:\\日常\\2.OA\\OASERVERLANDB-slow.log";
        //  導(dǎo)出Excel路徑
        String excelPath = "D:\\日常\\2.OA\\slow_queries.xlsx";
        //  讀取慢查詢?nèi)罩?
        List<SlowQuery> slowQueries = parser.readSlowQueryLog(filePath);
        //  寫入本地Excel中
        parser.writeQueriesToExcel(slowQueries, excelPath);
    }
    /**
     * 讀取慢查詢?nèi)罩?返回List對象
     * @param filePath 慢查詢?nèi)罩疚募窂?
     * @return List<SlowQuery> 解析結(jié)果
     * */
    public List<SlowQuery> readSlowQueryLog(String filePath) {
        List<SlowQuery> slowQueries = new ArrayList<>();
        //  轉(zhuǎn)流
        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            StringBuilder queryBuilder = new StringBuilder();
            //  設(shè)定默認值
            double queryTime = 0;
            double lockTime = 0;
            boolean isSlowQuery = false;
            long timestamp = 0; // 用于存儲時間戳
            while ((line = br.readLine()) != null) {
                if (line.startsWith("# Query_time")) {
                    // 如果前一個查詢存在，添加到列表
                    if (isSlowQuery) {
                        addSlowQuery(slowQueries, queryTime, lockTime, queryBuilder.toString().trim(), timestamp);
                    }
                    // 解析查詢時間和鎖定時間
                    Matcher queryTimeMatcher = QUERY_TIME_PATTERN.matcher(line);
                    if (queryTimeMatcher.find()) {
                        queryTime = Double.parseDouble(queryTimeMatcher.group(1));
                    }
                    Matcher lockTimeMatcher = LOCK_TIME_PATTERN.matcher(line);
                    if (lockTimeMatcher.find()) {
                        lockTime = Double.parseDouble(lockTimeMatcher.group(1));
                    }
                    // 開始新的慢查詢
                    isSlowQuery = true;
                    // 清空緩存
                    queryBuilder.setLength(0);
                } else if (line.startsWith("SET timestamp")) {
                    // 提取時間戳
                    Matcher timestampMatcher = TIMESTAMP_PATTERN.matcher(line);
                    if (timestampMatcher.find()) {
                        timestamp = Long.parseLong(timestampMatcher.group(1)); // 獲取時間戳
                    }
                } else if (line.startsWith("#") || line.trim().isEmpty()) {
                    // 忽略注釋行和空行
                    continue;
                } else {
                    // 記錄當前慢查詢的內(nèi)容
                    if (isSlowQuery) {
                        queryBuilder.append(line).append("\n");
                    }
                }
            }
            // 處理最后一個慢查詢
            if (queryBuilder.length() > 0) {
                addSlowQuery(slowQueries, queryTime, lockTime, queryBuilder.toString().trim(), timestamp);
            }
        } catch (IOException e) {
            System.out.printf(e.toString());
        }
        return slowQueries;
    }
    /**
     * 添加慢查詢對象
     * @param slowQueries List<SlowQuery> 慢查詢對象集合
     * @param queryTime 查詢時間
     * @param lockTime 鎖定時間
     * @param sqlQuery Sql執(zhí)行時間
     * @param timestamp 時間戳
     * */
    private void addSlowQuery(List<SlowQuery> slowQueries, double queryTime, double lockTime, String sqlQuery, long timestamp) {
        SlowQuery slowQuery = new SlowQuery();
        slowQuery.setQueryTime(queryTime);
        slowQuery.setLockTime(lockTime);
        slowQuery.setSqlQuery(sqlQuery);
        // 提取表名
        slowQuery.setTableName(extractTableName(sqlQuery));
        // 設(shè)置執(zhí)行日期
        slowQuery.setExecutionDate(new Date(timestamp * 1000));
        slowQueries.add(slowQuery);
    }
    /**
     * 通過Sql語句中 提取出表名
     * @param sqlQuery 執(zhí)行的Sql語句
     * @return 表名
     * */
    private String extractTableName(String sqlQuery) {
        Pattern pattern = Pattern.compile("FROM\\s+([\\w.]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sqlQuery);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return "";
    }
    /**
     * 通過處理后的集合生成到指定路徑
     * @param slowQueries 數(shù)據(jù)集合
     * @param filePath 導(dǎo)出的Excel路徑
     * */
    public void writeQueriesToExcel(List<SlowQuery> slowQueries, String filePath) {
        final int MAX_CELL_LENGTH = 32767;
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 日期格式化
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Slow Queries");
            // 創(chuàng)建標題行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("Query Time (s)");
            headerRow.createCell(1).setCellValue("Lock Time (s)");
            headerRow.createCell(2).setCellValue("SQL Query");
            headerRow.createCell(3).setCellValue("Table Name");
            headerRow.createCell(4).setCellValue("Execution Date");
            // 填充數(shù)據(jù)行
            int rowNum = 1;
            for (SlowQuery slowQuery : slowQueries) {
                Row row = sheet.createRow(rowNum++);
                row.createCell(0).setCellValue(slowQuery.getQueryTime());
                row.createCell(1).setCellValue(slowQuery.getLockTime()); // 確保這里寫入的是原始 double 值
                String sqlQuery = slowQuery.getSqlQuery();
                if (sqlQuery.length() > MAX_CELL_LENGTH) {
                    sqlQuery = sqlQuery.substring(0, MAX_CELL_LENGTH);
                }
                row.createCell(2).setCellValue(sqlQuery);
                row.createCell(3).setCellValue(slowQuery.getTableName());
                row.createCell(4).setCellValue(dateFormat.format(slowQuery.getExecutionDate()));
            }
            // 寫入到文件
            try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
                workbook.write(fileOut);
            }
        } catch (IOException e) {
            System.out.printf(e.toString());
        }
    }