[原文链接：https://www.dubby.cn/detail.html?id=9113](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]
```
有疑问加站长微信联系（非本文作者））
