学会对mysql的增删改查_Go实现对MySQL的增删改查

依赖

先下载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========

========UpdateById========

id:2 key:update-8738-dubby value:update-8738-www.dubby.cn

[createTime:1539340810 updateTime:1539355099]