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

柔光的暖阳◎ 2022-09-15 12:43 141阅读 0赞

*  结构体定义(所有代码到在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
        }