学习go操作mysql，crud测试

• 代码

package main

/**
 * 使用go-sql-driver/mysql连接mysql
 *安装方式：go get -u github.com/go-sql-driver/mysql
 */
import (
    _ "github.com/go-sql-driver/mysql"
    "database/sql"
    "fmt"
    "mypath"
    "crypto/md5"
    "encoding/hex"
)

/**
 *声明数据库信息
 */
const (
    DB_TYPE                   = "mysql"
    DB_SOURCE_URL_GOLANG_TEST = "root:password@tcp(localhost:3306)/golang_test?charset=utf8"
    DB_SOURCE_URL_TEST        = "root:password@tcp(localhost:3306)/test?charset=utf8"
)

/**
 * 程序目标：连接mysql，crud， 再完成一个简单的注册功能
 * 操作的表信息
 * CREATE TABLE `baiyi_user` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(255) NOT NULL COMMENT '用户名字',
  `user_password` varchar(255) NOT NULL COMMENT '用户密码',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8mb4;
 */
func main() {

    /**
     *获取数据源的一个连接
     */
    db, err := sql.Open(DB_TYPE, DB_SOURCE_URL_GOLANG_TEST)
    mypath.Check(err)
    defer db.Close()

    //testInsertData(db)
    //testQueryData(db)
    //testUpdateData(db)
    //testDeleteData(db)

    //flag := checkIsExistUser("username1", db)
    //fmt.Println(flag)
    //flag = checkIsExistUser("username2", db)
    //fmt.Println(flag)

    //fmt.Println(register("username1", "liangdaoyuan", db))
    //fmt.Println(register("DoubleJun", "liangdaoyuan", db))

    fmt.Println(login("DoubleJun", "liangdaoyuan2", db))
    fmt.Println(login("DoubleJun", "liangdaoyuan", db))
    fmt.Println(login("DoubleJun2", "liangdaoyuan", db))
}

/**
 * 测试插入
 */
func testInsertData(db *sql.DB)  {
    /**
     *插入就绪
     */
    stmtIns, err := db.Prepare("INSERT INTO baiyi_user(user_name, user_password) VALUES(?,?)")
    mypath.Check(err)
    defer stmtIns.Close()

    var password string
    var username string
    var effectedCount int = 0
    for i := 1; i < 25; i++ {
        password = fmt.Sprintf("520520somuch%d",i)
        password = md5Value(password)
        username = fmt.Sprintf("username%d", i)
        rs, err := stmtIns.Exec(username, password)
        if eff, _ := rs.RowsAffected(); eff>0 {
            effectedCount++
        }
        mypath.Check(err)
    }

}

/**
 *查询操作
 */
func testQueryData(db *sql.DB)  {
    /**
         *执行查询操作
         */
    rows, err := db.Query("SELECT user_name FROM baiyi_user")
    mypath.Check(err)

    columns, err := rows.Columns()
    mypath.Check(err)

    values := make([]sql.RawBytes, len(columns)) //存放数据
    scanArgs := make([]interface{}, len(values)) //存放地址
    for i := range values {
        scanArgs[i] = &values[i]
    }

    // Fetch rows
    for rows.Next() {
        // get RawBytes from data
        err = rows.Scan(scanArgs...) //
        mypath.Check(err)

        // Now do something with the data.
        // Here we just print each column as a string.
        var value string
        for i, col := range values {
            // Here we can check if the value is nil (NULL value)
            if col == nil {
                value = "NULL"
            } else {
                value = string(col)
            }
            fmt.Println(columns[i], ": ", value)
        }
    }


    if err = rows.Err(); err != nil {
        panic(err.Error())
    }

}

/*
 *更新操作
 */
func testUpdateData(db *sql.DB) {
    var updateSql string = "UPDATE baiyi_user SET user_name=? WHERE user_id=?"
    stmt, err := db.Prepare(updateSql)
    mypath.Check(err)
    rs, err := stmt.Exec("Jack", 2)
    defer stmt.Close()
    fmt.Println(rs.RowsAffected())
}

/**
 *
 */
func testDeleteData(db *sql.DB)  {
    var deleteSql string = "DELETE FROM baiyi_user WHERE user_id=?"
    stmt, err := db.Prepare(deleteSql)
    mypath.Check(err)
    rs, err := stmt.Exec(2)
    defer stmt.Close()
    fmt.Println(rs.RowsAffected())
}

/**
 *返回字符串的MD5值
 */
func md5Value(source string) string {
    md5Instnace := md5.New()
    md5Data := md5Instnace.Sum([]byte(source))
    return hex.EncodeToString(md5Data)
}

/**
 *返回结果包装
 */
type ResponseData struct {
    StatusCode int
    Info string
}



/**
 *注册操作
 */
func register(username string, password string, db *sql.DB) ResponseData {
    if checkIsExistUser(username, db) {
        resp := ResponseData{}
        resp.StatusCode=400
        resp.Info = "用户存在"
        return resp
    }

    md5Pwd := md5Value(password)
    insertSql := "INSERT INTO baiyi_user(user_name, user_password) VALUES (?,?)"

    rs, err := db.Exec(insertSql, username, md5Pwd)
    mypath.Check(err)
    if effectedRow, err :=rs.RowsAffected();effectedRow>0 {
        mypath.Check(err)
        resp := ResponseData{}
        resp.StatusCode=200
        resp.Info = "注册成功"
        return resp
    }

    return makeResponse(500, "意料之外的错误")

}

/**
 *检查用户名是否存在
 */
func checkIsExistUser(username string, db *sql.DB) bool{
    querlSql := "SELECT user_id FROM baiyi_user WHERE user_name=?"
    rows, err := db.Query(querlSql, username)
    mypath.Check(err)
    defer rows.Close()
    return rows.Next()
}


/**
 *登录操作
 */
func login(username string, password string, db *sql.DB) ResponseData {
    if !checkIsExistUser(username, db) {
        return makeResponse(400, "该用户不存在，请注册")
    }

    sql := "SELECT user_id FROM baiyi_user WHERE user_name=? AND user_password=? LIMIT 1"
    rows, err := db.Query(sql, username,md5Value(password))
    mypath.Check(err)
    if rows.Next() {
        return makeResponse(200,  "登录成功")
    }

    return makeResponse(200, "密码错误")
}

/*
 *生成对象
 */
func makeResponse(code int, info string) ResponseData  {
    resp := ResponseData{}
    resp.StatusCode=code
    resp.Info = info
    return resp
}

总结

• go操作mysql

打卡时间: 21:04