Go sqlx的增删改查+事务

秋叶随风起

已于 2022-08-31 10:30:02 修改

阅读量2.3k

点赞数

文章标签： golang 数据库开发语言

于 2022-08-31 09:52:58 首次发布

本文链接：https://blog.csdn.net/qq_34252060/article/details/126616754

版权

sqlx的增删改查

数据库设计

在这里插入图片描述
表test1

表test2

插入操作（Scrypt算法给用户密码加密）

Scrypt算法主要用于区块链，虽然难以破解，但是计算时间长，影响性能

package main

import (
	"database/sql"
	"encoding/base64"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	"golang.org/x/crypto/scrypt"
	"log"
	"math/rand"
	"time"
)

type Person struct {
	UserId   int            `db:"user_id" json:"user_id"`
	Username string         `db:"username" json:"username"`
	Password sql.NullString `db:"password" json:"password"`
	Email    sql.NullString `db:"email" json:"email"`
	Age      sql.NullInt64  `db:"age" json:"age"`
}

type Place struct {
	Country string         `db:"country"`
	City    sql.NullString `db:"city"`
	Code    int            `db:"code"`
}

var Db *sqlx.DB

// int类型转化为sql.NullInt64
func IntToNullInt64(a int) sql.NullInt64 {
	return sql.NullInt64{Int64: int64(a), Valid: true}
}
// string类型转化为sql.NullString
func StringToNullString(a string) sql.NullString {
	return sql.NullString{String: a, Valid: true}
}

// 将密码加密，即使用户输入的密码相同，最后数据库中的密码依旧不同

func ScryptPwd(password string) string {
	const KeyLen = 10
	rand.Seed(time.Now().UnixNano())
	salt := make([]byte, 8)
	// 随机数使盐也不同，这样即便某些用户的密码相同，他们编码的密码也不同
	salt = []byte{byte(rand.Intn(2000)), byte(rand.Intn(3003)), byte(rand.Intn(3000)), byte(rand.Intn(5000)), byte(rand.Intn(5000)), byte(rand.Intn(200))}
	HashPwd, err := scrypt.Key([]byte(password), salt, 16384, 8, 1, KeyLen)
	if err != nil {
		log.Fatal(err)
	}
	fpwd := base64.StdEncoding.EncodeToString(HashPwd)
	return fpwd
}

func init() {
	db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
	if err != nil {
		//fmt.Println("open mysql failed,", err)
		//continue
		panic(err)
	}
	Db = db
}

func main() {
	defer Db.Close()

	// 往表test1中插入数据
	// 法一：Exec插入数据
	r1, err := Db.Exec("insert into test1(username,password,email) values (?,?,?)", "tom", ScryptPwd("154"), "222@qq.com")
	if err != nil {
		fmt.Println("exec failds,", err)
		return
	}
	id_1, err := r1.LastInsertId()
	if err != nil {
		fmt.Println("exec faild", err)
		return
	}
	fmt.Println("insert success", id_1)

	// 法二：NameExec插入数据
	person := Person{
		Username: "jerry",
		Password: StringToNullString(ScryptPwd("154")),
		Email:    StringToNullString("455@163.com"),
		Age:      IntToNullInt64(20),
	}
	r2, err := Db.NamedExec("insert into test1(username,password,email,age)values (:username,:password,:email,:age)", person)
	if err != nil {
		fmt.Println("name exec err:", err)
		return
	}
	id_2, err := r2.LastInsertId()
	if err != nil {
		fmt.Println("exec faild", err)
		return
	}
	fmt.Println("insert success", id_2)

	// 往表test2中插入数据

	// 法一：Exec插入数据
	row1, err := Db.Exec("insert into test2(country,city,code) values (?,?,?)", "china", "xian", 2)
	if err != nil {
		fmt.Println("exec failds,", err)
		return
	}
	id_3, err := row1.LastInsertId()
	if err != nil {
		fmt.Println("exec faild", err)
		return
	}
	fmt.Println("id_1 success:", id_3)

	// 法二：NameExec插入数据
	place1 := Place{
		Country: "usa",
		City:    StringToNullString("newyork"),
		Code:    21,
	}
	row2, err2 := Db.NamedExec("insert into test2(country,city,code)values (:country,:city,:code)", place1)
	if err2 != nil {
		fmt.Println("nameexec err:", err2)
		return
	}
	id_4, err := row2.LastInsertId()
	if err != nil {
		fmt.Println("NameExec faild:", err)
		return
	}
	fmt.Println("insert success:", id_4)
}

在这里插入图片描述

删除操作

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)


var Db *sqlx.DB

func init() {
	db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
	if err != nil {
		fmt.Println("database open err:", err)
		return
	}
	Db = db
}

func main() {
	defer Db.Close()
	res, err := Db.Exec("delete from test1 where user_id = ?", 168)
	if err != nil {
		fmt.Println("delete faild:", err)
		return
	}
	row, err := res.RowsAffected()
	if err != nil {
		fmt.Println("rows faild:", err)
	}
	fmt.Println("delete success:", row)
}

更新操作

package main

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var Db *sqlx.DB

func init() {
	db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
	if err != nil {
		fmt.Println("database open err:", err)
		return
	}
	Db = db
}

func main() {
	defer Db.Close()
	r, err := Db.Exec("update test1 set username = ? where user_id = ?", "李四", 163)
	if err != nil {
		fmt.Println("update err :", err)
		return
	}
	row, err := r.RowsAffected()
	if err != nil {
		fmt.Println("rows faild:", err)
	}
	fmt.Println("update success:", row)
}

查询操作

Select和Query用于查询多条记录
Get和QueryRow用于查询单条记录

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type Person struct {
	UserId   int            `db:"user_id" json:"user_id"`
	Username string         `db:"username" json:"username"`
	Password sql.NullString `db:"password" json:"password"`
	Email    sql.NullString `db:"email" json:"email"`
	Age      sql.NullInt32  `db:"age" json:"age"`
}

var Db *sqlx.DB

func init() {
	db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
	if err != nil {
		fmt.Println("open mysql failed,", err)
		return
		//panic(err)
	}
	Db = db
}

func main() {
	defer Db.Close()
	var p []Person
	// Select用于多行查询 Select的第一个值是一个切片
	err := Db.Select(&p, "select username,password,email,age from test1 where username = ?", "jerry")
	if err != nil {
		fmt.Println("select failds,", err)
		return
	}
	fmt.Println("select success", p)

	// Get用于单行查询
	var p1 Person
	err1 := Db.Get(&p1, "select username,password,age from test1 where user_id = ?", 164)
	if err != nil {
		fmt.Println("get err:", err1)
		return
	}
	fmt.Println("get success", p1)

	// Query方法用于查询多条记录
	rows, err := Db.Query("select username,password,email,age from test1 where user_id > 2")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer rows.Close()
	for rows.Next() {
		var (
			username string
			password sql.NullString
			email    sql.NullString
			age      sql.NullInt32
		)
		if err := rows.Scan(&username, &password, &email, &age); err != nil {
			fmt.Println(err)
			return
		}
		fmt.Println(username, password, email, age)
	}
	rows.Close()
	// Err返回迭代期间遇到的错误（如果有）。Err可在显式或隐式关闭后调用
	if err = rows.Err(); err != nil {
		fmt.Println("err...", err)
		return
	}
	fmt.Println("----------")
	//QueryRow方法用于查询单条记录
	var city sql.NullString
	//var country string
	row := Db.QueryRow("select city from test2 where code = 1")
	err3 := row.Scan(&city)
	if err3 != nil {
		fmt.Println("scan err:", err3)
		return
	}
	fmt.Println(city)

	// 含有空值的查询
	var email sql.NullString
	var username string
	var password sql.NullString
	var age sql.NullInt32
	newrow := Db.QueryRow("select email,username,password,age from test1 where user_id = 167")
	err4 := newrow.Scan(&email, &username, &password, &age)
	if err4 != nil {
		fmt.Println("scan err...:", err4)
		return
	}
	fmt.Println(email, username, password, age)
}

事务

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

type Person struct {
	UserId   int            `db:"user_id" json:"user_id"`
	Username string         `db:"username" json:"username"`
	Password sql.NullString `db:"password" json:"password"`
	Email    sql.NullString `db:"email" json:"email"`
	Age      sql.NullInt64  `db:"age" json:"age"`
}

type GRPCPersonOption func(*Person)

func StringToNullString(a string) sql.NullString {
	return sql.NullString{String: a, Valid: true}
}

func NewPerson(opts ...GRPCPersonOption) *Person {
	entry := &Person{
		Email: StringToNullString("111@163.com"),
	}
	for i := range opts {
		opts[i](entry)
	}
	return entry
}

func withEmail(email sql.NullString) GRPCPersonOption {
	return func(entry *Person) {
		entry.Email = email
	}
}

var Db *sqlx.DB

func init() {
	db, err := sqlx.Open("mysql", "root:061118@tcp(127.0.0.1:3306)/person")
	if err != nil {
		fmt.Println("database open err:", err)
		return
	}
	Db = db
}

func main() {
	conn, err := Db.Beginx()
	if err != nil {
		fmt.Println("begin err:", err)
		return
	}
	// NamedExec插入
	p := NewPerson(withEmail(StringToNullString("7889@qq.com")))
	p.Username = "KANSH2"
	p.Password = StringToNullString("123987")
	result1, err1 := conn.NamedExec("insert into test1(username,password,email) values (:username,:password,:email)", p)
	if err1 != nil {
		fmt.Println("exec faild..", err1)
		conn.Rollback()
		return
	}
	id_1, err := result1.LastInsertId()
	if err != nil {
		fmt.Println("exec failed, ", err)
		conn.Rollback()
		return
	}
	fmt.Println("insert success:", id_1)

	// Exec插入
	result2, err2 := conn.Exec("insert into test1(username,password,email) values (?,?,?)", "西门吹雪", "1234", "455@163.com")
	if err2 != nil {
		fmt.Println("insert err:", err)
		conn.Rollback()
		return
	}
	id_2, err := result2.LastInsertId()
	if err != nil {
		fmt.Println("exec failed, ", err)
		conn.Rollback()
		return
	}
	fmt.Println("insert success:", id_2)

	conn.Commit()
}