GoLang使用Mysql

1、安装mysql开源库

go get github.com/go-sql-driver/mysql

2、go实现mysql的查询、插入、更新、删除

package main

import (
    "database/sql"
    "fmt"
    "time"

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

type User struct {
    id            int64          `db:"id"`
    groupname     sql.NullString `db:"groupname"`
    groupfatherid int            `db:"groupfatherid"`
    remark        sql.NullString `db:"remark"`
}

const (
    USERNAME = "root"
    PASSWORD = "fgsgsgwr2356346"
    NETWORK  = "tcp"
    SERVER   = "127.0.0.1"
    PORT     = 3306
    DATABASE = "gotest"
)

func main() {
    dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
    DB, err := sql.Open("mysql", dsn)
    if err != nil {
        fmt.Printf("Open mysql failed,err:%v\n", err)
        return
    }
       //最大连接周期，超过时间的连接就close
    DB.SetConnMaxLifetime(100 * time.Second)
       //设置最大连接数
    DB.SetMaxOpenConns(100)
       //设置闲置连接数
    DB.SetMaxIdleConns(16)
    queryOne(DB)
    queryMulti(DB)
    insertData(DB)
    updateData(DB)
    deleteData(DB)
}

//查询单行
func queryOne(DB *sql.DB) {
    user := new(User)
    row := DB.QueryRow("select * from groupinfo where id=?", 2)
    err := row.Scan(&user.id, &user.groupname, &user.groupfatherid, &user.remark)
    if err != nil {
        fmt.Printf("queryOne scan failed, err:%v\n", err)
        return
    }
    fmt.Println(*user)
}

//查询多行
func queryMulti(DB *sql.DB) {
    user := new(User)
    rows, err := DB.Query("select * from groupinfo where id > ?", 10)
    defer func() {
        if rows != nil {
            rows.Close()
        }
    }()
    if err != nil {
        fmt.Printf("queryMulti Query failed,err:%v\n", err)
        return
    }
    for rows.Next() {
        err = rows.Scan(&user.id, &user.groupname, &user.groupfatherid, &user.remark)
        if err != nil {
            fmt.Printf("queryMulti Scan failed,err:%v\n", err)
            return
        }
        fmt.Print(*user)
    }
    fmt.Printf("\n")
}

//插入数据
func insertData(DB *sql.DB) {
    result, err := DB.Exec("insert INTO groupinfo(id,groupname,groupfatherid,remark) values(?,?,?,?)", 30, "YDZ", 23, "remark")
    if err != nil {
        fmt.Printf("Insert failed,err:%v\n", err)
        return
    }
    lastInsertID, err := result.LastInsertId()
    if err != nil {
        fmt.Printf("insertData Get lastInsertID failed,err:%v\n", err)
        return
    }
    fmt.Println("insertData LastInsertID:", lastInsertID)
    rowsaffected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("insertData Get RowsAffected failed,err:%v\n", err)
        return
    }
    fmt.Println("insertData RowsAffected:", rowsaffected)
}

//更新数据
func updateData(DB *sql.DB) {
    result, err := DB.Exec("UPDATE groupinfo set groupfatherid=? where id=?", 40, 30)
    if err != nil {
        fmt.Printf("updateData failed,err:%v\n", err)
        return
    }
    rowsaffected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("updateData Get RowsAffected failed,err:%v\n", err)
        return
    }
    fmt.Println("updateData RowsAffected:", rowsaffected)
}

//删除数据
func deleteData(DB *sql.DB) {
    result, err := DB.Exec("delete from groupinfo where id=?", 30)
    if err != nil {
        fmt.Printf("delete failed,err:%v\n", err)
        return
    }
    rowsaffected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("delete Get RowsAffected failed,err:%v\n", err)
        return
    }
    fmt.Println("delete RowsAffected:", rowsaffected)
}

欢迎关注【技术型项目经理】公众号。可获取软件行业动态、技术积累和项目管理理念文章分享。选择「考试经验」菜单「PMP」、「高项」（信息系统项目管理师）、「CISSP」、「GoLang」可获取学习资料。