原文链接：https://www.dubby.cn/detail.html?id=9113

依赖

先下载go-sql-driver/mysql：

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

数据库

建表：

CREATE TABLE `data` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`key` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
`value` varchar(1024) COLLATE utf8mb4_bin DEFAULT NULL,
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

实现代码：

实体对象

func (data Data) String() string {
    return fmt.Sprintf("id:%d key:%s value:%s\n[createTime:%d updateTime:%d]\n",
        data.Id, data.Key, data.Value, data.CreateTime.Unix(), data.UpdateTime.Unix())
}

type Data struct {
    Id         int64
    Key        string
    Value      string
    CreateTime time.Time //[]uint8
    UpdateTime time.Time //[]uint8
}

获得连接

db, err := sql.Open("mysql", "dubby:123456@tcp(127.0.0.1:3306)/go_test?parseTime=true")
if err != nil {
    panic(err.Error())
}

删除

func Delete(id int64, db *sql.DB) error {
    stmtOut, err := db.Prepare("DELETE FROM `data` WHERE id = ?")
    if err != nil {
        panic(err.Error())
    }
    defer stmtOut.Close()

    result, err := stmtOut.Exec(id)
    if err != nil {
        panic(err.Error())
    }

    if rowNum, err := result.RowsAffected(); err != nil || rowNum != int64(1) {
        panic("delete error")
    }
    return nil
}

新增

func Insert(key string, value string, db *sql.DB) *Data {
    stmtOut, err := db.Prepare("INSERT INTO `data` (`key`, `value`) values (?, ?)")
    if err != nil {
        panic(err.Error())
    }
    defer stmtOut.Close()

    result, err := stmtOut.Exec(key, value)
    if err != nil {
        panic(err.Error())
    }

    id, err := result.LastInsertId()
    if err != nil {
        panic(err.Error())
    }

    return QueryById(id, db)
}

更新

func UpdateById(id int64, key string, value string, db *sql.DB) *Data {
    stmtOut, err := db.Prepare("UPDATE `data` SET `key`=?, `value`=? WHERE `id`=?")
    if err != nil {
        panic(err.Error())
    }
    defer stmtOut.Close()

    _, err = stmtOut.Exec(key, value, id)
    if err != nil {
        panic(err.Error())
    }

    return QueryById(id, db)
}

根据ID查询

func QueryById(idRequest int64, db *sql.DB) *Data {
    stmtOut, err := db.Prepare("SELECT * FROM `data` WHERE id = ?")
    if err != nil {
        panic(err.Error())
    }
    defer stmtOut.Close()

    rows := stmtOut.QueryRow(idRequest)

    data := new(Data)
    err = rows.Scan(&data.Id, &data.Key, &data.Value, &data.CreateTime, &data.UpdateTime)
    if err != nil {
        panic(err.Error())
    }

    return data
}

根据ID范围查询

func QueryByIdRange(minId int64, maxId int64, db *sql.DB) []*Data {
    stmtOut, err := db.Prepare("SELECT * FROM `data` WHERE id >= ? AND id <= ?")
    if err != nil {
        panic(err.Error())
    }
    defer stmtOut.Close()

    rows, err := stmtOut.Query(minId, maxId)
    if err != nil {
        panic(err.Error())
    }

    var result []*Data

    for rows.Next() {
        data := new(Data)
        err = rows.Scan(&data.Id, &data.Key, &data.Value, &data.CreateTime, &data.UpdateTime)
        if err != nil {
            panic(err.Error())
        }
        result = append(result, data)
    }

    return result
}

测试

fmt.Println("========QueryById========")
data := QueryById(2, db)
fmt.Println(*data)

fmt.Println("========QueryByIdRange========")
resultList := QueryByIdRange(1, 10, db)
for i := 0; i < len(resultList); i++ {
    fmt.Println(*resultList[i])
}

fmt.Println("========Insert========")
data = Insert("go_key", "go_value", db)
fmt.Println(*data)

fmt.Println("========Delete========")
err = Delete(data.Id, db)
fmt.Println(err)

fmt.Println("========UpdateById========")
data = QueryById(2, db)
prefix := fmt.Sprintf("update-%d-", time.Now().UnixNano()/1000%10000)
data = UpdateById(data.Id, prefix+"dubby", prefix+"www.dubby.cn", db)
fmt.Println(*data)

测试结果

========QueryById========
id:2 key:update-5469-dubby value:update-5469-www.dubby.cn
[createTime:1539340810 updateTime:1539345474]

========QueryByIdRange========
id:2 key:update-5469-dubby value:update-5469-www.dubby.cn
[createTime:1539340810 updateTime:1539345474]

id:3 key:dubby value:dubby.cn
[createTime:1539340810 updateTime:1539340810]

id:4 key:test value:test
[createTime:1539343652 updateTime:1539343652]

id:5 key:test value:test
[createTime:1539343760 updateTime:1539343760]

id:6 key:go_key value:go_value
[createTime:1539343942 updateTime:1539343942]

id:7 key:go_key value:go_value
[createTime:1539343956 updateTime:1539343956]

id:8 key:go_key value:go_value
[createTime:1539343977 updateTime:1539343977]

id:9 key:go_key value:go_value
[createTime:1539343981 updateTime:1539343981]

id:10 key:go_key value:go_value
[createTime:1539343982 updateTime:1539343982]

========Insert========
id:35 key:go_key value:go_value
[createTime:1539355099 updateTime:1539355099]

========Delete========
<nil>
========UpdateById========
id:2 key:update-8738-dubby value:update-8738-www.dubby.cn
[createTime:1539340810 updateTime:1539355099]