Snippets: Golang: Query MySQL System Variables

2nd December 2021 at 3:53pm

在 Go 中查询 MySQL 的 system variables。跟 SELECT 语句类似,SHOW 请求也返回一个多行结构:

func GetMySQLSystemVariables(db *gorm.DB) error {
    sql := "SHOW VARIABLES WHERE Variable_name IN ('system_time_zone', 'time_zone')"
    var variables []struct {
        VariableName string `gorm:"column:Variable_name"`
        Value        string `gorm:"column:Value"`
    }
    if err := db.Raw(sql).Find(&variables).Error; err != nil {
        return fmt.Errorf("query time zone variables failed: %w", err)
    }
    if len(variables) != 2 {
        return fmt.Errorf("query time zone variables should return 2 rows, got %d", len(variables))
    }

    var systemTZ, tz string
    for _, v := range variables {
        if v.VariableName == "system_time_zone" {
            systemTZ = v.Value
        }
        if v.VariableName == "time_zone" {
            tz = v.Value
        }
    }
    if systemTZ == "" || tz == "" {
        return fmt.Errorf("value of system_time_zone (%s) or time_zone(%s) is empty", systemTZ, tz)
    }
    return nil
}