golang使用database/mysql连接mysql，并进行操作-蒲公英云

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
}