' ========== 模块顶部定义常量 ==========
Private Const DB_SERVER As String = "obmt6k4s711g1jc0-mi.aliyun-cn-hangzhou-internet.oceanbase.cloud"
Private Const DB_PORT As Long = 3306
Private Const DB_NAME As String = "skrxx"
Private Const DB_USER As String = "wangkun"
Private Const DB_PASSWORD As String = "{WANGkun@160830@}" ' 密码特殊字符需转义
Private Const DB_TABLE As String = "skr"
Private Const REFRESH_INTERVAL As Long = 5 ' 刷新间隔5分钟（单位：秒）

' ========== 全局变量 ==========
Private scheduledRefresh As Date
Private conn As Object

' ========== 主连接函数 ==========
Public Sub ConnectAndRefresh()
    On Error GoTo ErrorHandler
    
    ' 创建连接对象
    If conn Is Nothing Then Set conn = CreateObject("ADODB.Connection")
    
    ' 构建连接字符串（含SSL和超时）
    Dim connString As String
    connString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" & _
                "SERVER=" & DB_SERVER & ";" & _
                "PORT=" & DB_PORT & ";" & _
                "DATABASE=" & DB_NAME & ";" & _
                "UID=" & DB_USER & ";" & _
                "PWD=" & DB_PASSWORD & ";" & _
                "Option=3;" & _
                "UseSSL=True;" & _
                "ConnectTimeout=30;"
    
    ' 打开连接
    If conn.State <> 1 Then conn.Open connString
    
    ' 获取数据并写入表格
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT * FROM " & DB_TABLE, conn, 1, 1
    
    Dim ws As Worksheet
    Set ws = GetOrCreateWorksheet("数据")
    ws.Cells.Clear
    ws.Range("A1").CopyFromRecordset rs
    
    ' 格式化表头
    With ws
        .Rows(1).Font.Bold = True
        .Columns.AutoFit
    End With
    
    ' 安排下一次刷新
    scheduledRefresh = Now + TimeSerial(0, 0, REFRESH_INTERVAL)
    Application.OnTime scheduledRefresh, "ConnectAndRefresh"

Cleanup:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "错误代码：" & Err.Number & vbCrLf & _
           "错误描述：" & Err.Description & vbCrLf & _
           "解决方案：" & GetErrorSolution(Err.Number), vbCritical
    Resume Cleanup
End Sub

' ========== 辅助函数 ==========
Private Function GetOrCreateWorksheet(sheetName As String) As Worksheet
    On Error Resume Next
    Set GetOrCreateWorksheet = ThisWorkbook.Sheets(sheetName)
    If GetOrCreateWorksheet Is Nothing Then
        Set GetOrCreateWorksheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        GetOrCreateWorksheet.Name = sheetName
    End If
End Function

Private Function GetErrorSolution(errorNumber As Long) As String
    Select Case errorNumber
        Case 3706: Return "请检查MySQL ODBC驱动是否安装[3](@ref)"
        Case 80004005: Return "验证数据库权限：GRANT ALL ON skrxx.* TO 'wangkun'@'%'[2](@ref)"
        Case Else: Return "检查网络连接或服务器状态[1,5](@ref)"
    End Select
End Function