前言
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() }
