学习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
有疑问加站长微信联系(非本文作者)