go 操作 mysql

安装 mysql 驱动

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

连接 mysql

/**
params(使用的驱动名,数据库连接信息)
*/
sql.Open("mysql", "用户名:密码@tcp(ip:端口号)/数据库名称?charset=utf8")

示例代码

package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "strings"
)

// 定义数据库链接信息

type DbConn struct {
   Dsn string // 数据库驱动链接字符串
   Db  *sql.DB
}

// user_info 表映射对象

type UserTable struct {
   Uid        int
   Username   string
   Department string
   Created    string
}

func main() {
   var err error

   // 数据库信息
   const (
      userName = "root"
      passWord = "wwh123456"
      ip       = "127.0.0.1"
      port     = "3306"
      dbName   = "test_db"
   )
   dbConn := DbConn{}
   dbConn.Dsn = strings.Join([]string{userName, ":", passWord, "@tcp(", ip, ":", port, ")/", dbName, "?charset=utf8"}, "")
   dbConn.Db, err = sql.Open("mysql", dbConn.Dsn)
   if err != nil {
      fmt.Println("open fail:", err)
      return
   }
   defer dbConn.Db.Close()

   execData(&dbConn)
   //preExecData(&dbConn)
   // 查询最后一条数据的信息
   result := dbConn.QueryRowData("select * from user_info where uid=(select max(uid) from user_info)")
   fmt.Println("the last one:", result)
   // 查询多行数据
   result1 := dbConn.QueryData("select * from user_info where uid < 10")
   fmt.Println("data uid < 10:", result1)
      for k, v :=range result1 {
      fmt.Println("k:", k, "v:", v)
   }
   // 查询多行数据
   result2 := dbConn.PreQueryData("select * from user_info where uid < ? order by uid desc", 10)
   fmt.Println("data uid < 10:", result2)
   for k, v :=range result2 {
      fmt.Println("k:", k, "v:", v)
   }
}

// 测试封装的 ExecData 函数
func execData(dbConn *DbConn) {
   // count, id, err := dbConn.ExecData("insert user_info(username, departname, created) values ('cc', 'business', '2022-11-16')")
   // count, id, err := dbConn.ExecData("update user_info set departname='fired', created='2022-11-16' where uid=2")
   count, id, err:= dbConn.ExecData("delete from user_info where uid=3")
   if err != nil {
      fmt.Println(err.Error())
   } else {
      fmt.Println("受影响行数：", count)
      fmt.Println("新添加数据的uid：", id)
   }
}

// 测试封装的 preExecData 函数
func preExecData(dbConn *DbConn) {
   count, id, err := dbConn.PreExecData("insert user_info(username, departname, created) values (?, ?, ?)", "jack", "eduction", "2022-10-6")

   if err != nil {
      fmt.Println(err.Error())
   } else {
      fmt.Println("受影响行数：", count)
      fmt.Println("新添加数据的uid：", id)
   }
}

// 新增增删改数据的函数， 该函数直接使用DB的Exec()方法实现

func (dbConn *DbConn) ExecData(sqlString string) (count, id int64, err error) {
   result, err := dbConn.Db.Exec(sqlString)
   if err != nil {
      fmt.Println("exec fail:", err)
      return
   }
   if id, err = result.LastInsertId(); err != nil {
      fmt.Println("getLastInsertId fail:", err)
      return
   }
   if count, err = result.RowsAffected(); err != nil {
      fmt.Println("getRowsAffected fail:", err)
      return
   }
   return count, id, nil
}

// 新增增删改数据的函数， 该函数使用预编译语句加 Exec()方法实现增删改数据

func (dbConn *DbConn) PreExecData(sqlString string, args ...interface{}) (count, id int64, err error) {
   stmt, err := dbConn.Db.Prepare(sqlString)
   defer stmt.Close()
   if err != nil {
      fmt.Println("Prepare fail:", err)
      return
   }
   result, err := stmt.Exec(args...)
   if id, err = result.LastInsertId(); err != nil {
      fmt.Println("getLastInsertId fail:", err)
      return
   }
   if count, err = result.RowsAffected(); err != nil {
      fmt.Println("getRowsAffected fail:", err)
      return
   }
   return count, id, nil
}

// 查询当行数据

func (dbConn *DbConn) QueryRowData(sqlString string) (data UserTable) {
   user := new(UserTable)
   err := dbConn.Db.QueryRow(sqlString).Scan(&user.Uid, &user.Username, &user.Department, &user.Created)
   if err != nil {
      fmt.Println("QueryRow fail:", err)
      return
   }
   return *user
}

// 未使用预编译，直接查询多行数据

func (dbConn *DbConn) QueryData(sqlString string) (resultSet map[int]UserTable) {
   rows, err := dbConn.Db.Query(sqlString)
   defer rows.Close()
   if err != nil {
      fmt.Println("QueryRows fail:", err)
      return
   }
   resultSet = make(map[int]UserTable)
   user := new(UserTable)
   for rows.Next() {
      err := rows.Scan(&user.Uid, &user.Username, &user.Department, &user.Created)
      if err != nil {
         fmt.Println("QueryRow fail:", err)
         continue
      }
      resultSet[user.Uid] = *user
   }
   return resultSet
}

// 使用预编译，查询多行数据

func (dbConn *DbConn) PreQueryData(sqlString string, args ...interface{}) (resultSet map[int]UserTable) {
   stmt, err := dbConn.Db.Prepare(sqlString)
   defer stmt.Close()
   if err != nil {
      fmt.Println("Prepare fail:", err)
      return
   }
   rows, err := stmt.Query(args...)
   defer rows.Close()
   if err != nil {
      fmt.Println("Query fail:", err)
      return
   }
   resultSet = make(map[int]UserTable)
   user := new(UserTable)
   for rows.Next() {
      err := rows.Scan(&user.Uid, &user.Username, &user.Department, &user.Created)
      if err != nil {
         fmt.Println("QueryRow fail:", err)
         continue
      }
      resultSet[user.Uid] = *user
   }
   return resultSet
}