Go 语言中的mysql实现细节与样例

虽然Go语言有自己的database/sql的包，但是它本身并没有实现database/sql/driver驱动包，仅仅提供了接口，如果要使用数据库，需要使用第三方的包，比如使用mysql，我们常常选择go-sql-driver包，它实现了mysql的驱动。

一、接口定义与实现
在database/sql/driver包中，我们可以看见几个重要的接口如下，这些接口Go语言本身都没有提供实现，由第三方包实现
type Driver interface {
    Open(name string) (Conn, error)
}

type Conn interface {
    Prepare(query string) (Stmt, error)

    Close() error

    Begin() (Tx, error)
}

type Queryer interface {
    Query(query string, args []Value) (Rows, error)
}

type Stmt interface {
    Close() error

    NumInput() int

    Exec(args []Value) (Result, error)

    Query(args []Value) (Rows, error)
}

来看看，go-sql-driver是如何实现上面的接口
// Driver接口实现，具体代码参考：https://github.com/go-sql-driver/mysql/blob/master/driver.go
type MySQLDriver struct{}
func (d MySQLDriver) Open(dsn string) (driver.Conn, error) {
...
}

// Conn与Queryer接口实现，具体代码参考：https://github.com/go-sql-driver/mysql/blob/master/connection.go
type mysqlConn struct {
....
}

func (mc *mysqlConn) Begin() (driver.Tx, error) {
...
}

func (mc *mysqlConn) Close() (err error) {
...
}

func (mc *mysqlConn) Prepare(query string) (driver.Stmt, error) {
...
}
// Queryer接口实现
func (mc *mysqlConn) Query(query string, args []driver.Value) (driver.Rows, error) {
    return mc.query(query, args)
}

// Stmt接口实现，具体代码参考：https://github.com/go-sql-driver/mysql/blob/master/statement.go
type mysqlStmt struct {
    mc *mysqlConn
    id uint32
    paramCount int
}

func (stmt *mysqlStmt) Query(args []driver.Value) (driver.Rows, error) {
    return stmt.query(args)
}

func (stmt *mysqlStmt) NumInput() int {
    return stmt.paramCount
}

func (stmt *mysqlStmt) Exec(args []driver.Value) (driver.Result, error) {
...
}

func (stmt *mysqlStmt) Close() error {
...
}

接口实现，代码是如何初始化呢，有两个地方要关注，下面代码告诉我们驱动是如何被使用进来的
// 第三方包初始化，代码位于：https://github.com/go-sql-driver/mysql/blob/master/driver.go
func init() {
    sql.Register("mysql", &MySQLDriver{})
}

// 再看看Register函数，位于本地的安装目录下：C:\Go\src\database\sql\sql.go
func Register(name string, driver driver.Driver) {
    driversMu.Lock()
    defer driversMu.Unlock()
    if driver == nil {
        panic("sql: Register driver is nil")
    }
    if _, dup := drivers[name]; dup {
        panic("sql: Register called twice for driver " + name)
    }
    drivers[name] = driver
}

二、Go语言使用mysql的样例
上面是针对mysql数据库，对数据库驱动接口的实现，如果用户对底层的实现不感兴趣，可以忽略，下面我们来写样例：
package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"
    "strings"
    "time"

    _ "github.com/go-sql-driver/mysql"
)

// Db 是mysql数据库连接
var Db *sql.DB

func init() {
    var err error
    Db, err = sql.Open("mysql", "root:password@tcp(192.168.111.137:3306)/hrefs?charset=utf8")
    if err != nil {
        log.Fatal(err)
    }
    return
}

func main() {
    mux := http.NewServeMux()
    mux.HandleFunc("/insert", insert)
    mux.HandleFunc("/delete", delete)
    mux.HandleFunc("/update", update)
    mux.HandleFunc("/query", query)
    mux.HandleFunc("/queryrow", queryrow)
    server := &http.Server{
        Addr: "0.0.0.0:8080",
        Handler: mux,
        ReadTimeout: time.Duration(10 * int64(time.Second)),
        WriteTimeout: time.Duration(600 * int64(time.Second)),
        MaxHeaderBytes: 1 << 20,
    }
    server.ListenAndServe()
}

// 用Go语言向mysql数据库插入一条记录
func insert(writer http.ResponseWriter, request *http.Request) {
    res, err := Db.Exec("insert account(userid,username,password,regdate)values(?,?,?,?)", "jack", "jack", "e10aae", time.Now())
    if err != nil {
        log.Fatal(err)
    }

    result, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Fprintf(writer, "%t", result > 0)
}

// 用Go语言向mysql数据库删除一条记录
func delete(writer http.ResponseWriter, request *http.Request) {
    res, err := Db.Exec("delete from account where userid = ?", "jack")
    if err != nil {
        log.Fatal(err)
    }

    result, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Fprintf(writer, "%t", result > 0)
}

// 用Go语言向mysql数据库更新记录
func update(writer http.ResponseWriter, request *http.Request) {
    res, err := Db.Exec("update account set username = ? where userid = ?", "jimmy", "jack")
    if err != nil {
        log.Fatal(err)
    }

    result, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Fprintf(writer, "%t", result > 0)
}

// 用Go语言向mysql数据库获取多条记录
func query(writer http.ResponseWriter, request *http.Request) {
    rows, err := Db.Query("select username FROM account WHERE id >= ?", 1)
    if err != nil {
        log.Fatal(err)
    }

    defer rows.Close()
    names := make([]string, 0)
    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            log.Fatal(err)
        }
        names = append(names, name)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }

    fmt.Fprintf(writer, "%s are id > %d", strings.Join(names, ", "), 1)
}

// 用Go语言向mysql数据库获取一条记录
func queryrow(writer http.ResponseWriter, request *http.Request) {
    stmt, err := Db.Prepare("select username FROM account WHERE id = ?")
    if err != nil {
        log.Fatal(err)
    }

    id := 1
    var username string
    defer stmt.Close()
    err = stmt.QueryRow(id).Scan(&username)

    fmt.Fprintf(writer, "%s", username)
}

总结：例子包含增、删、改、查，例子中使用了两种写法，都是可以的，因为DB与Stmt都包含了增删改查的方法。同时注意，这两种方法的参数都是使用了参数化过程，可以防止sql注入危险。
Posted by 何敏 on 2019/02/21 07:08:42