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/2/21 07:08:42