【go笔记】使用sqlx操作MySQL

前言

go在操作MySQL时，可以使用ORM（比如gorm、xorm），也可以使用原生sql。本文以使用sqlx为例，简单记录步骤。

• go version： 1.16
• 安装相关库

# mysql驱动
go get github.com/go-sql-driver/mysql
# 基于MySQL驱动的封装
go get github.com/jmoiron/sqlx

• 连接MySQL

database, err := sqlx.Open("mysql", "username:password@tcp(127.0.0.1:3306)/dbName")

• 测试数据表定义：

CREATE TABLE `person` (
	`user_id` INT(11) NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(260) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`sex` VARCHAR(260) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`email` VARCHAR(260) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	PRIMARY KEY (`user_id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

初始化MySQL连接

// 数据表person的结构体
type Person struct {
	UserId   int    `db:"user_id"`
	Username string `db:"username"`
	Sex      string `db:"sex"`
	Email    string `db:"email"`
}
func init() {
	// 初始化建立MySQL连接
	database,err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/gotest")
	if err != nil {
		fmt.Println("open mysql failed. ",err)
		return
	}
	// database.SetMaxOpenConns(200)
	// database.SetMaxIdleConns(10)
	db = database
	// defer db.Close()
}

插入数据

func InsertData() {
	// 插入数据方式1
	result,err := db.Exec("insert into person(username,sex,email) values(?,?,?)", "stu002", "woman", "stu002@email.com")
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		fmt.Println("insert data failed2, ",err)
		return
	}
	fmt.Println("insert data success, ",id)
}

func InsertData2() {
	// 插入数据方式2
	sqlStr := "insert into person(username,sex,email) values(:username,:sex,:email)"
	result,err := db.NamedExec(sqlStr,map[string]interface{}{
		"username": "stu003",
		"sex": "man",
		"email": "stu003@email.com",
	})
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		fmt.Println("insert data failed2, ",err)
		return
	}
	fmt.Println("insert data success, ",id)
}

删除数据

func DeleteData() {
	// delete 删除数据
	result,err := db.Exec("delete from person where username=? and sex=?","stu002","woman")
	if err != nil {
		fmt.Println("delete data failed, ",err)
		return
	}
	row,err := result.RowsAffected()
	if err != nil {
		fmt.Println("affected row unknown, ",err)
	}
	fmt.Println("delete data success: ",row)
}

修改数据

func UpdateData() {
	// update修改数据
	result,err := db.Exec("update person set sex=? where username=?","woman","stu001")
	if err != nil {
		fmt.Println("update data failed, ",err)
		return
	}
	row,err := result.RowsAffected()
	if err != nil {
		fmt.Println("affected row unknown, ",err)
	}
	fmt.Println("update data success: ",row)
}

查询数据

func SelectData() {
	// 查询数据
	var person Person
	err := db.Get(&person, "select user_id,username,sex,email from person where user_id=?", 2)
	if err != nil {
		fmt.Println("select data failed, ",err)
		return
	}
	fmt.Println(person.Email)
}

func SelectData2() {
	// 查询数据方式2
	sqlStr := "select user_id,username,sex,email from person where user_id=:user_id"
	rows,err := db.NamedQuery(sqlStr,map[string]interface{}{
		"user_id": "2",
	})
	if err != nil {
		fmt.Println("query data error, ",err)
		return
	}
	for rows.Next() {
		var person Person
		if err := rows.StructScan(&person);err != nil {
			fmt.Printf("struct scan failed, %v\n",err)
			continue
		}
		fmt.Println(person)
	}
}

事务操作

• 开启事务：

func (db *DB) Begin() (*Tx, error) 

• 回滚事务：

func (tx *Tx) Rollback() error

• 提交事务

func (tx *Tx) Commit() error

• 示例：

func InsertDataTransaction() {
	tx,err := db.Begin()
	if err != nil {
		fmt.Println("transaction begin failed, ",err)
		return
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		} else if err != nil {
			fmt.Printf("transaction rollback")
			_ = tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Printf("transaction commit")
			return
		}
	}()

	sqlStr := "insert into person(username,sex,email) values(?,?,?)"
	result,err := tx.Exec(sqlStr,"stu004", "woman", "stu004@email.com")
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	_,err = result.RowsAffected()
	if err != nil {
		fmt.Println("affected rows num is 0")
		return
	}
}

完整示例代码

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

// 数据表person的结构体
type Person struct {
	UserId   int    `db:"user_id"`
	Username string `db:"username"`
	Sex      string `db:"sex"`
	Email    string `db:"email"`
}

// 数据表place的结构体
type Place struct {
	Country string `db:"country"`
	City    string `db:"city"`
	Telcode int    `db:"telcode"`
}

var db *sqlx.DB

func init() {
	// 初始化建立MySQL连接
	database,err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/gotest")
	if err != nil {
		fmt.Println("open mysql failed. ",err)
		return
	}
	// database.SetMaxOpenConns(200)
	// database.SetMaxIdleConns(10)
	db = database
	// defer db.Close()
}

func InsertData() {
	// 插入数据
	result,err := db.Exec("insert into person(username,sex,email) values(?,?,?)", "stu002", "woman", "stu002@email.com")
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		fmt.Println("insert data failed2, ",err)
		return
	}
	fmt.Println("insert data success, ",id)
}

func InsertData2() {
	// 插入数据
	sqlStr := "insert into person(username,sex,email) values(:username,:sex,:email)"
	result,err := db.NamedExec(sqlStr,map[string]interface{}{
		"username": "stu003",
		"sex": "man",
		"email": "stu003@email.com",
	})
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	id, err := result.LastInsertId()
	if err != nil {
		fmt.Println("insert data failed2, ",err)
		return
	}
	fmt.Println("insert data success, ",id)
}

func SelectData() {
	// 查询数据
	var person Person
	err := db.Get(&person, "select user_id,username,sex,email from person where user_id=?", 2)
	if err != nil {
		fmt.Println("select data failed, ",err)
		return
	}
	fmt.Println(person.Email)
}

func SelectData2() {
	// 查询数据方式2
	sqlStr := "select user_id,username,sex,email from person where user_id=:user_id"
	rows,err := db.NamedQuery(sqlStr,map[string]interface{}{
		"user_id": "2",
	})
	if err != nil {
		fmt.Println("query data error, ",err)
		return
	}
	for rows.Next() {
		var person Person
		if err := rows.StructScan(&person);err != nil {
			fmt.Printf("struct scan failed, %v\n",err)
			continue
		}
		fmt.Println(person)
	}
}

func UpdateData() {
	// update修改数据
	result,err := db.Exec("update person set sex=? where username=?","woman","stu001")
	if err != nil {
		fmt.Println("update data failed, ",err)
		return
	}
	row,err := result.RowsAffected()
	if err != nil {
		fmt.Println("affected row unknown, ",err)
	}
	fmt.Println("update data success: ",row)
}

func DeleteData() {
	// delete 删除数据
	result,err := db.Exec("delete from person where username=? and sex=?","stu002","woman")
	if err != nil {
		fmt.Println("delete data failed, ",err)
		return
	}
	row,err := result.RowsAffected()
	if err != nil {
		fmt.Println("affected row unknown, ",err)
	}
	fmt.Println("delete data success: ",row)
}

func InsertDataTransaction() {
	tx,err := db.Begin()
	if err != nil {
		fmt.Println("transaction begin failed, ",err)
		return
	}
	defer func() {
		if p := recover(); p != nil {
			_ = tx.Rollback()
			panic(p)
		} else if err != nil {
			fmt.Printf("transaction rollback")
			_ = tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Printf("transaction commit")
			return
		}
	}()

	sqlStr := "insert into person(username,sex,email) values(?,?,?)"
	result,err := tx.Exec(sqlStr,"stu004", "woman", "stu004@email.com")
	if err != nil {
		fmt.Println("insert data failed, ",err)
		return
	}
	_,err = result.RowsAffected()
	if err != nil {
		fmt.Println("affected rows num is 0")
		return
	}
}

func main() {
	// InsertData()
	// InsertData2()
	// SelectData()
	// SelectData2()
	// UpdateData()
	// DeleteData()
	InsertDataTransaction()
	defer db.Close()
}

参考

• go操作MySQL · Go语言中文文档
• sqlx · github项目地址
• sql库使用指南|李文周的博客