golang操作mysql
1. 安装mysql驱动库和sqlx基于官方sql库的扩展库
go get github.com/go-sql-driver/mysql go get github.com/jmoiron/sqlx
2. 链接mysql
package main import ( "fmt" _ "github.com/go-sql-driver/mysql" // 初始化mysql驱动 "github.com/jmoiron/sqlx" // sqlx是一个库,为go标准库database/sql提供了一组扩展 ) var db *sqlx.DB func init() { // 初始化 database, err := sql.Open("mysql", "username:password@protocal(host:port)/db_name") if err != nil { fmt.Println("open mysql field", err) return } db = database }
3. insert
func main() { defer db.Close() result, err := db.Exec("insert into person(username, sex, email)values(?,?,?)", "abc", "男", "myn_cn@163.com") if err != nil { fmt.Println("Exec failed", err) return } lastId, err := result.LastInsertId() if err != nil { fmt.Println("exec failed", err) return } fmt.Println("insert success", lastId) }
4. select
type Person struct { Userid int `db:"user_id"` Username string `db:"username"` Sex string `db:"sex"` Email string `db:"email"` } func main() { defer db.Close() var person []Person if err := db.Select(&person, "select user_id, username, sex, email from person where user_id = ?;", 2); err != nil { fmt.Println("select failed", err) return } fmt.Println("select success", person) }
5. update
func main() { result, err := db.Exec("update person set username = ? where user_id = ?", "嘿嘿2", 2) if err != nil { fmt.Println("exec failed", err) return } rows, err := result.RowsAffected() if err != nil { fmt.Println("exec failed", err) } fmt.Println("update success", rows) }
6. delete
func main() { result, err := db.Exec("delete from person where user_id = ?", 2) if err != nil { fmt.Println("exec failed", err) return } rows, err := result.RowsAffected() if err != nil { fmt.Println("exec failed", err) } fmt.Println("delete success", rows) }
7. mysql事务
func main() { conn, err := db.Begin() // 开启事务 if err != nil { fmt.Println("begin err", err) return } result, err := conn.Exec("insert into person(username, sex, email)values(?,?,?)", "张三", "女", "1341935532@qq.com") if err != nil { fmt.Println("exec failed", err) conn.Rollback() // 回滚事务 return } rows, err := result.RowsAffected() if err != nil { fmt.Println("exec failed", err) conn.Rollback() // 回滚事务 return } conn.Commit() // 提交事务 fmt.Println("delete success", rows) }