GO+Mysql 增删改查

sql语句：

CREATE TABLE `userinfo` (
  `uid` int(10) NOT NULL AUTO_INCREMENT,
  `create_time` datetime DEFAULT NULL,
  `username` varchar(30) DEFAULT NULL,
  `pwd` varchar(40) DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  `email` varchar(99) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

main.go

package main

import (
	"fmt"

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

var (
	user    string = "root"      //mysql用户名
	pwd     string = "root"      //mysql密码
	ip      string = "127.0.0.1" //localhost
	port    int    = 3306        //port
	dbname  string = "blog"      //数据库名
	charset string = "utf8"      //数据库编码
)

/**
 * 创建数据库链接
 *
 */
func connect() *sqlx.DB {
	//dns 链接字符串
	dns := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", user, pwd, ip, port, dbname, charset)
	Db, err := sqlx.Open("mysql", dns)
	if err != nil {
		fmt.Println("数据库链接失败")
	}
	return Db

}

//用户信息表结构体(insert时用到)
type Users struct {
	create_time string
	username    string
	pwd         string
	department  string
	email       string
}

//数据库对象映射结构体（select时用到）
type UserData struct {
	Uid         int    `db:"uid"`
	Create_time string `db:"create_time"`
	Username    string `db:"username"`
	Pwd         string `db:"pwd"`
	Department  string `db:"department"`
	Email       string `db:"email"`
}

/**
 * 插入方法
 *
 */
func insert(Db *sqlx.DB, users Users) int64 {
	sql := fmt.Sprintf("insert into userinfo(create_time,username,pwd,department,email) values ('%s','%s','%s','%s','%s')", users.create_time, users.username, users.pwd, users.department, users.email)
	fmt.Println(sql)
	result, err := Db.Exec(sql)
	if err != nil {
		fmt.Println("插入失败" + err.Error())
		return 0
	}
	id, _ := result.LastInsertId()
	return id
}

/**
 * 修改方法
 *
 */
func update(Db *sqlx.DB, sql string) int64 {
	result, err := Db.Exec(sql)
	if err != nil {
		fmt.Println("修改失败" + err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	return num

}

/**
 * 删除方法
 *
 */
func delete(Db *sqlx.DB, id int) int64 {
	result, err := Db.Exec(fmt.Sprintf("delete from userinfo where uid=%d", id))
	if err != nil {
		fmt.Println("删除失败" + err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	return num

}

/**
 * 查询方法
 *
 */
func getAll(Db *sqlx.DB, sql string) (data []UserData) {

	var dt []UserData
	err := Db.Select(&dt, sql)
	if err != nil {
		return nil
	}

	return dt

}

func main() {
	var Dbo *sqlx.DB
	Dbo = connect() //数据库操作句柄
	/**********插入示例***************/
	var user Users
	user = Users{
		"2021-08-26",
		"测试",
		"123456",
		"技术部",
		"test@gmail.com"}
	in := insert(Dbo, user)
	fmt.Println(in)

	/**********修改示例***************/
	usql := "update userinfo set username='Java' where username='巴山';"
	i := update(Dbo, usql)
	fmt.Printf("受影响行数为：%d", i)

	/**********删除示例***************/
	id := 13
	it := delete(Dbo, id)
	fmt.Println(it)

	/**********查询示例***************/
	sql := "select * from userinfo where uid=15"
	var ret []UserData
	ret = getAll(Dbo, sql)
	for k, v := range ret {
		fmt.Println(k)                                   //取 key
		fmt.Println(v.Create_time + "====" + v.Username) //取key对应值
	}

}