Go语言MySQL操作

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `userid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `type` int NULL DEFAULT NULL,
  `createtime` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`userid`)
);

INSERT INTO `t_user` VALUES (1, '张剑', '123456', '码客说', 1, '2024-02-18 17:29:07');

package model

type Tuser struct {
	Userid     int    `db:"userid"`
	UserName   string `db:"username"`
	Password   string `db:"password"`
	Nickname   string `db:"nickname"`
	Type       int    `db:"type"`
	Createtime string `db:"createtime"`
}

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

package mydb

import (
	"fmt"

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

var Db *sqlx.DB

var (
	userName  string = "zhangjian"
	password  string = "Test_123456"
	ipAddrees string = "dbtest.xhkjedu.com"
	port      int    = 3306
	dbName    string = "z-wiki"
	charset   string = "utf8"
)

// OpenDB 打开DB
func OpenDB() *sqlx.DB {
	var err error
	//"用户名:密码@[连接方式](主机名:端口号)/数据库名"
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
	Db, err = sqlx.Open("mysql", dsn) // 设置连接数据库的参数
	if err != nil {
		fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
	}
	Db.SetMaxOpenConns(100)
	Db.SetMaxIdleConns(10)
	return Db
}

// CloseDb 关闭数据库
func CloseDb() {
	err := Db.Close()
	if err != nil {
		fmt.Println("数据库关闭失败")
	}
	Db = nil
}

func PingDbHasErr() bool {
	if Db == nil {
		OpenDB()
	}

	err := Db.Ping() //连接数据库
	if err != nil {
		fmt.Println("数据库连接失败")
		return true
	}
	return false
}

package dao

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"z-wiki/db"
	"z-wiki/model"
)

// UserLogin 查询单个用户
func UserLogin(username string, pwd string) *model.UserVo {
	if mydb.PingDbHasErr() {
		return nil
	}
	var err error
	var user = new(model.UserVo)
	err = mydb.Db.Get(user, "select * from t_user where username=?&&password=?", username, pwd)
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return nil
	}

	fmt.Println("user", ":", user)
	return user
}

// UserList 查询用户列表
func UserList() []model.UserVo {
	if mydb.PingDbHasErr() {
		return nil
	}
	var err error
	var userList []model.UserVo
	err = mydb.Db.Select(&userList, "select * from t_user")
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return nil
	}

	fmt.Println("userList", ":", userList)
	return userList
}

// UserAdd 添加 返回自增长字段的值
func UserAdd(username string, password string, nickname string, mtype int, createtime int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("insert into t_user (username, password,nickname,type,createtime) values(?,?,?,?,?)", username, password, nickname, mtype, createtime)
	if err != nil {
		fmt.Printf("data insert faied, error:%v\n", err.Error())
		return 0
	}
	id, _ := result.LastInsertId()
	fmt.Printf("insert success, last id:%d\n", id)
	return id
}

// UserDelete 删除 返回删除的数量
func UserDelete(userid int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("delete from t_user where userid = ?", userid)
	if err != nil {
		fmt.Printf("delete faied, error:%v\n", err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	fmt.Printf("delete success, affected rows:%d\n", num)
	return num
}

// UserUpdate 更新 返回更新的数量
func UserUpdate(username string, userid int) int64 {
	if mydb.PingDbHasErr() {
		return 0
	}
	result, err := mydb.Db.Exec("update t_user set username = ? where userid = ?", username, userid)
	if err != nil {
		fmt.Printf("update faied, error:%v\n", err.Error())
		return 0
	}
	num, _ := result.RowsAffected()
	fmt.Printf("update success, affected rows:%d\n", num)
	return num
}


// UserListCount 获取总条数
func UserListCount() int {
	if mydb.PingDbHasErr() {
		return 0
	}
	var err error
	var count int
	err = mydb.Db.QueryRow("select count(*) from t_user").Scan(&count)
	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return 0
	}
	return count
}

// ArticleListByManager 查询列表
func ArticleListByManager(search string, categoryid int, status int, currPage int, pageSize int) ([]model.ArticleVo, int) {
	var offset = (currPage - 1) * pageSize
	if mydb.PingDbHasErr() {
		return []model.ArticleVo{}, 0
	}
	var err error
	var mList []model.ArticleVo
	var count int

	var selectSql string
	var selectParas []interface{}

	var countSql string
	var countParas []interface{}

	selectSql = "select articleid,title,description,wordnum,tags,visitnum,createtime,updatetime,status from t_article where 1=1 "
	countSql = "select count(*) from t_article where 1=1 "
	if search != "" {
		var searchStr = "%" + search + "%"
		selectSql += " and title like ? or outline like ?"
		selectParas = append(selectParas, searchStr, searchStr)
		countSql += " and title like ? or outline like ?"
		countParas = append(countParas, searchStr, searchStr)
	}

	if categoryid != 0 {
		selectSql += " and categoryid=?"
		selectParas = append(selectParas, categoryid)
		countSql += " and categoryid=?"
		countParas = append(countParas, categoryid)
	}

	if status != 0 {
		selectSql += " and status=?"
		selectParas = append(selectParas, status)
		countSql += " and status=?"
		countParas = append(countParas, status)
	}

	selectSql += " LIMIT ? OFFSET ?"
	selectParas = append(selectParas, pageSize, offset)
	err = mydb.Db.Select(&mList, selectSql, selectParas...)
	err = mydb.Db.QueryRow(countSql, countParas...).Scan(&count)

	if err != nil {
		fmt.Printf("query faied, error:%v\n", err.Error())
		return []model.ArticleVo{}, 0
	}
	if mList == nil {
		mList = []model.ArticleVo{}
	}
	return mList, count
}

mydb.OpenDB()
defer mydb.CloseDb()
userList := dao.UserList()
fmt.Println("userList:", userList)