golang mysql demo

Go操作Mysql数据库

使用Go操作MySQL等数据库，一般有两种方式：一是使用database/sql接口，直接在代码里硬编码sql语句；二是使用gorm，即对象关系映射的方式在代码里抽象的操作数据库。一般推荐使用第二种方式。

使用database/sql接口

Go没有内置的驱动支持任何数据库，但是Go定义了database/sql接口，用户可以基于驱动接口开发相应数据库的驱动。但缺点是，直接用 github.com/go-sql-driver/mysql 访问数据库都是直接写 sql，取出结果然后自己拼成对象，使用上面不是很方便，可读性也不好。

下载包

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

源码如下：

package main

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

//数据库连接信息
const (
    USERNAME = "root"
    PASSWORD = "123qwe"
    NETWORK  = "tcp"
    SERVER   = "127.0.0.1"
    PORT     = 3306
    DATABASE = "test"
)

//user表结构体定义
type User struct {
    Id         int    `json:"id" form:"id"`
    Username   string `json:"username" form:"username"`
    Password   string `json:"password" form:"password"`
    Status     int    `json:"status" form:"status"` // 0 正常状态， 1删除
    Createtime int64  `json:"createtime" form:"createtime"`
}

func CreateTable(DB *sql.DB) {
    sql := `CREATE TABLE IF NOT EXISTS users(
        id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL,
        username VARCHAR(64),
        password VARCHAR(64),
        status INT(4),
        createtime INT(10)
    ); `

    if _, err := DB.Exec(sql); err != nil {
        fmt.Println("create table failed:", err)
        return
    }
    fmt.Println("create table successd")
}

//插入数据
func InsertData(DB *sql.DB) {
    result, err := DB.Exec("insert INTO users(username,password) values(?,?)", "demo", "123qwe")
    if err != nil {
        fmt.Printf("Insert data failed,err:%v", err)
        return
    }
    lastInsertID, err := result.LastInsertId() //获取插入数据的自增ID
    if err != nil {
        fmt.Printf("Get insert id failed,err:%v", err)
        return
    }
    fmt.Println("Insert data id:", lastInsertID)

    rowsaffected, err := result.RowsAffected() //通过RowsAffected获取受影响的行数
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v", err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}

//查询单行
func QueryOne(DB *sql.DB) {
    user := new(User) //用new()函数初始化一个结构体对象
    row := DB.QueryRow("select id,username,password from users where id=?", 1)
    //row.scan中的字段必须是按照数据库存入字段的顺序，否则报错
    if err := row.Scan(&user.Id, &user.Username, &user.Password); err != nil {
        fmt.Printf("scan failed, err:%v\n", err)
        return
    }
    fmt.Println("Single row data:", *user)
}

//查询多行
func QueryMulti(DB *sql.DB) {
    user := new(User)
    rows, err := DB.Query("select id,username,password from users where id = ?", 2)

    defer func() {
        if rows != nil {
            rows.Close() //关闭掉未scan的sql连接
        }
    }()
    if err != nil {
        fmt.Printf("Query failed,err:%v\n", err)
        return
    }
    for rows.Next() {
        err = rows.Scan(&user.Id, &user.Username, &user.Password) //不scan会导致连接不释放
        if err != nil {
            fmt.Printf("Scan failed,err:%v\n", err)
            return
        }
        fmt.Println("scan successd:", *user)
    }
}

//更新数据
func UpdateData(DB *sql.DB) {
    result, err := DB.Exec("UPDATE users set password=? where id=?", "111111", 1)
    if err != nil {
        fmt.Printf("Insert failed,err:%v\n", err)
        return
    }
    fmt.Println("update data successd:", result)

    rowsaffected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v\n", err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}

//删除数据
func DeleteData(DB *sql.DB) {
    result, err := DB.Exec("delete from users where id=?", 1)
    if err != nil {
        fmt.Printf("Insert failed,err:%v\n", err)
        return
    }
    fmt.Println("delete data successd:", result)

    rowsaffected, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("Get RowsAffected failed,err:%v\n", err)
        return
    }
    fmt.Println("Affected rows:", rowsaffected)
}

func main() {
    conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)
    fmt.Println("conn: ", conn)
    DB, err := sql.Open("mysql", conn)
    if err != nil {
        fmt.Println("connection to mysql failed:", err)
        return
    }

    DB.SetConnMaxLifetime(100 * time.Second) //最大连接周期，超时的连接就close
    DB.SetMaxOpenConns(100)                  //设置最大连接数
    CreateTable(DB)
    InsertData(DB)
    QueryOne(DB)
    QueryMulti(DB)
    UpdateData(DB)
    DeleteData(DB)
}

运行结果如下：

[root@localhost mysql]# go run main.go 
conn:  root:123qwe@tcp(127.0.0.1:3306)/test
create table successd
Insert data id: 2
Affected rows: 1
scan failed, err:sql: no rows in result set
scan successd: {2 demo 123qwe 0 0}
update data successd: {0xc0000d8000 0xc0000d60b0}
Affected rows: 0
delete data successd: {0xc0000d8000 0xc000016330}
Affected rows: 0