-
结构体定义(所有代码到在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 }
03-21
1328
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
12-08
594
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)