1. 安装mysql驱动库和sqlx基于官方sql库的扩展库
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx
2. 链接mysql
package mainimport (
"fmt"
_ "github.com/go-sql-driver/mysql" // 初始化mysql驱动
"github.com/jmoiron/sqlx" // sqlx是一个库，为go标准库database/sql提供了一组扩展
)var db *sqlx.DBfunc 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)}
