golang使用database/mysql连接mysql，并进行操作

• 结构体定义(所有代码到在mysql包下)

  type User struct {
  	Id 			 int64
  	Name 	 	 string
  	Age  		 int
  	Address		 string
  	CreateTime   time.Time
  }
  

• 1、连接mysql数据库

  package mysql
  
  import (
  	"context"
  	"database/sql"
  	"fmt"
  	_ "github.com/go-sql-driver/mysql"
  	"github.com/prometheus/common/log"
  	"sync"
  )
  
  var (
  	Client *sql.DB
  	ctx    = context.Background()
  	once   sync.Once
  )
  
  func InitMysqlClient(){
  	// ${用户名}:${密码}@tcp${数据库地址}/${数据库}
  	dataSourceName :=fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8",
  		"", "", "", "")
  	log.Debugf("%s\n", dataSourceName)
  
  	once.Do(func(){
  		db, err := sql.Open("mysql", dataSourceName)
  		if err != nil {
  			log.Errorf("数据库打开出现了问题：%s", err)
  		}
  		// 最大空闲连接数
  		if flag.MysqlMaxIdle != 0 {
  			db.SetMaxIdleConns(flag.MysqlMaxIdle)
  		}
  		// 最大连接数
  		if flag.MysqlMaxConn != 0 {
  			db.SetMaxOpenConns(flag.MysqlMaxConn)
  		}
  		// 尝试与数据库建立连接（校验dsn是否正确）
  		err = db.Ping()
  		if err != nil {
  			log.Errorf("数据库连接出现了问题：%s", err)
  		}
  		Client = db
  	})
  	log.Info("mysql connection success !!!")
  }
  
  //注： 请在main方法中初始化mysql客户端,调用InitMysqlClient()函数
  

• 2、插入单条数据到mysql（事务）

  func Insert(user *User) (bool){
  	db := Client
  	// 开启事务
  	tx, err := db.Begin()
  	if err != nil{
  		log.Error("tx begin work fail")
  		return false
  	}
  	//准备sql语句
  	stmt, err := tx.Prepare("INSERT INTO user (`name`, `age`, `address`,`create_time`) " +
  		"VALUES (?, ?, ?, ?)")
  	if err != nil{
  		log.Error("Prepare fail")
  		return false
  	}
  	//将参数传递到sql语句中并且执行
  	res, err := stmt.Exec(user.Name,
  		user.Age,
  		user.Address,
  		time.Now().Format(time.RFC3339))
  	if err != nil{
  		tx.Rollback()
  		log.Errorf("Exec fail, err:%s", err)
  		return false
  	}
  	//将事务提交
  	tx.Commit()
  	//获得上一个插入自增的id
  	log.Info(res.LastInsertId())
  	if err != nil {
  		log.Errorf("user info insert mysql failed, err[%s]", err)
  	}
  	return true
  }
  

• 3、插入列表数据（仅供参考)

  func Inserts(users map[string]*User) (bool){
  	// 处理mysql语句，入库
  	sqlStr :="INSERT INTO user (`name`, `age`, `address`, `create_time`) VALUES "
  	var vals []interface{}
  	for _, row := range users {
  		sqlStr += "(?, ?, ?, ?),"
  		vals = append(vals,
  			row.name,
  			row.age,
  			row.address,
  			time.Now().Format(time.RFC3339))
  	}
  	db := Client
  	// 开启事务
  	tx, err := db.Begin()
  	if err != nil{
  		log.Error("tx begin work fail")
  		return false
  	}
  	//准备sql语句
  	stmt, err := tx.Prepare(sqlStr)
  	if err != nil{
  		log.Error("Prepare fail")
  		return false
  	}
  	//将参数传递到sql语句中并且执行
  	res, err := stmt.Exec(vals...)
  	if err != nil{
  		tx.Rollback()
  		log.Errorf("Exec fail, err:%s", err)
  		return false
  	}
  	//将事务提交
  	tx.Commit()
  	//获得上一个插入自增的id
  	log.Debug(res.LastInsertId())
  	if err != nil {
  		log.Errorf("user info inserts failed, err:%s", err)
  	}
  	return true
  }
  

• 4、查询语句，返回单条数据

  func SearchByName(name string) (User, error) {
  	var user User
  	db :=Client
  	err := db.QueryRow("SELECT id, name, age, address, create_time FROM user " +
  		"WHERE name = ?", name).Scan(&user.Id, &user.Name,
  			&user.Age, &user.Address, &user.CreateTime)
  	if err != nil{
  		log.Warnf("search user info failed, err:%s", err)
  	}
  	return user, err
  }
  

• 5、查询列表

  func SelectAllUser(address string) ([]User) {
  	db :=Client
      //执行查询语句
      rows, err := db.Query("SELECT * from user WHERE address = ?", address)
      if err != nil{
          fmt.Println("查询出错了")    
      }
      var users []User
      //循环读取结果
      for rows.Next(){
          var user bean.User
          //将每一行的结果都赋值到一个user对象中
          err := rows.Scan(&user.Id, &user.Name, &user.Age,&user.Address)
          if err != nil {
              fmt.Println("rows fail")
          }
          //将user追加到users的这个数组中
          users = append(users, user)
      }
      return users
  }
  

• 更新数据（删除数据同理，更换下sql语句即可）

  func Update(address string, name string) (bool) {
  	db :=Client
  	//开启事务
  	tx, err := db.Begin()
  	if err != nil{
  		log.Error("tx begin work fail")
  		return false
  	}
  	//准备sql语句
  	stmt, err := tx.Prepare("UPDATE user SET address = ? WHERE name = ?")
  	if err != nil{
  		log.Error("Prepare fail")
  		return false
  	}
  	//设置参数以及执行sql语句
  	_, err = stmt.Exec(address, name )
  	if err != nil{
  		tx.Rollback()
  		log.Errorf("Exec fail, err:%s", err)
  		return false
  	}
  	//提交事务
  	tx.Commit()
  	if err != nil {
  		log.Errorf("update user info failed, name:[%s]", name)
  	}
  	return true
  }