Golang - Sql Unknown Columns

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

db.Stats Key        Values
----------          -----
MaxLifetimeClosed   0
MaxOpenConnections  0
OpenConnections     1
InUse               0
Idle                1
WaitCount           0
WaitDuration        0s
MaxIdleClosed       0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

// 未知列信息输出
[0]: uid:5597, email:Hamy@Yob.com, passwd:22ebd0dc4ac40087808b8d84fa22e649
[1]: uid:5243, email:Have@Gmq.com, passwd:8b024a0f5abc6a2aad89a2f5f81acbaa
[2]: uid:6016, email:Hbay@Pjt.com, passwd:a83a77f3455f7658790f8568b493e62c
[3]: uid:5392, email:Hbgu@Nfo.com, passwd:7acedc9c037f9f864a5ec8b9b2e6aca4
[4]: uid:6484, email:Hbgx@Hot.com, passwd:ef10e71af017783a26fb27a3bdf12d30
// 用户信息输出
[0]: uid:5597, email:Hamy@Yob.com
[1]: uid:5243, email:Have@Gmq.com
[2]: uid:6016, email:Hbay@Pjt.com
[3]: uid:5392, email:Hbgu@Nfo.com
[4]: uid:6484, email:Hbgx@Hot.com

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/tkstorm/gearimg/mysql"
    "strconv"
)

// 用户信息
type User struct {
    uid    int
    email  string
    passwd string
}

func main() {
    db, _ := mysql.GetDb()

    for i, r := range unknownColumnsData(db) {
        fmt.Printf("[%d]: uid:%s, email:%s, passwd:%s\n", i, r["uid"], r["email"], r["passwd"])
    }

    // parse to user
    for i, u := range parseUser(unknownColumnsData(db)) {
        fmt.Printf("[%d]: uid:%d, email:%s\n", i, u.uid, u.email)
    }

}

// unknownColumnsData 获取未知列的相关信息
func unknownColumnsData(db *sql.DB) (records []map[string]string) {
    // prepare
    stmt, err := db.Prepare("SELECT * FROM users WHERE email LIKE ? LIMIT ?, ?; ")
    if err != nil {
        mysql.ErrHandle(err, "db.Prepare")
    }
    defer stmt.Close()

    // query
    rows, err := stmt.Query("H%", 2, 5)
    if err != nil {
        mysql.ErrHandle(err, "db.Query")
    }
    defer rows.Close()

    // columns field init
    cols, err := rows.Columns()
    if err != nil {
        mysql.ErrHandle(err, "rows.Columns")
    }

    return scanRecords(cols, rows)
}

// 解析记录集
func scanRecords(cols []string, rows *sql.Rows) (records []map[string]string) {
    // init cols
    vals := make([]sql.RawBytes, len(cols)) //sql.RawBytes存储每一单元的值，[]sql.RawBytes存储每行的值
    dest := make([]interface{}, len(cols))  //dest为一行值，每个元素对应数据库行项的slice的指针
    for i := 0; i < len(cols); i++ {
        dest[i] = &vals[i]
    }

    // loop
    for rows.Next() {
        // scan row to dest
        if err := rows.Scan(dest...); err != nil {
            mysql.ErrHandle(err, "rows.Scan fail.")
        }

        // row vals
        row := map[string]string{}
        for k, v := range vals {
            sv := fmt.Sprintf("%s", v)
            row[cols[k]] = sv
        }
        records = append(records, row)
    }

    return
}

// parseUser 基于已有的记录集解析用户信息
func parseUser(records []map[string]string) (users []*User) {

    for _, record := range records {
        user := new(User)

        // uid
        if v, ok := record["uid"]; ok {
            uid, err := strconv.Atoi(v)
            if err != nil {
                mysql.ErrHandle(err, "uid, strconv")
            }
            user.uid = uid
        }

        // email
        if v, ok := record["email"]; ok {
            user.email = v
        }

        // append user to slice
        users = append(users, user)
    }
    return
}

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101

package mysql

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "io"
    "log"
    "os"
    "text/tabwriter"
    "text/template"
)

// Mysql 连接配置
var (
    dbUser = "root"
    dbPass = "Secret123."
    dbName = "test_db"
)

// ErrHandle Mysql错误处理
func ErrHandle(err error, pos string, v ...interface{}) {
    format := "%s, %s";
    if v != nil {
        for range v {
            format = format + ", %s"
        }
        log.Panicf(format+"\n", pos, err, v)
    } else {
        log.Panicf(format+"\n", pos, err)
    }

}

// dsn string
func dsn() string {
    return fmt.Sprintf("%s:%s@/%s", dbUser, dbPass, dbName)
}

// GetDb 获取DB实例
func GetDb() (db *sql.DB, err error) {
    db, err = sql.Open("mysql", dsn())
    if err != nil {
        ErrHandle(err, "sql.open")
    }

    if err = db.Ping(); err != nil {
        ErrHandle(err, "sql.ping")
    }

    return
}

// ShowStatus 打印当前连接DB状态信息
func ShowStatus(db *sql.DB) {
    status := db.Stats()
    stData := map[string]interface{}{
        "MaxOpenConnections": status.MaxOpenConnections,
        "OpenConnections":    status.OpenConnections,
        "InUse":              status.InUse,
        "Idle":               status.Idle,
        "WaitCount":          status.WaitCount,
        "WaitDuration":       status.WaitDuration,
        "MaxIdleClosed":      status.MaxIdleClosed,
        "MaxLifetimeClosed":  status.MaxLifetimeClosed,
    }

    // text/tabwriter
    textTableShow(os.Stdout, stData)

    // text/template
    // textTmplShow(os.Stdout, stData)
}

// 表格输出
func textTableShow(w io.Writer, data map[string]interface{}) {
    const format = "%v\t%v\n"
    tw := new(tabwriter.Writer).Init(w, 0, 8, 2, ' ', 0)
    fmt.Fprintf(tw, format, "db.Stats Key", "Values")
    fmt.Fprintf(tw, format, "----------", "-----")
    for k, v := range data {
        fmt.Fprintf(tw, format, k, v)
    }
    _ = tw.Flush()
}

// 文本模板输出
func textTmplShow(w io.Writer, data map[string]interface{}) {
    // tmpl
    const tmplTxt = `DB STATUS
-----------------------{{range $k, $v := .}}
{{printf "%-18v" $k}} => {{printf "%v" .}}{{end}}
`
    tmpl, err := template.New("showStatus").Parse(tmplTxt)
    if err != nil {
        ErrHandle(err, "template.Parse")
    }
    if err := tmpl.Execute(w, data); err != nil {
        ErrHandle(err, "template.Execute")
    }
}