go数据操作-MySQL

1.快速入门

下载依赖

go get -u github.com/go-sql-driver/mysql

使用MySQL驱动

func Open(driverName, dataSourceName string) (*DB, error)

Open打开一个dirverName指定的数据库，dataSourceName指定数据源，一般至少包括数据库文件名和其它连接必要的信息。

import ("database/sql"_ "github.com/go-sql-driver/mysql"
)func main() {// DSN:Data Source Namedsn := "user:password@tcp(127.0.0.1:3306)/dbname"db, err := sql.Open("mysql", dsn)if err != nil {panic(err)}defer db.Close()  // 注意这行代码要写在上面err判断的下面
}

SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns设置与数据库建立连接的最大数目。 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。 如果n<=0，不会限制最大开启连接数，默认为0（无限制）。

SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns设置连接池中的最大闲置连接数。 如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。 如果n<=0，不会保留闲置连接。

2.CRUD操作

2.1. Insert操作

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)type Person struct {UserId   int    `db:"user_id"`Username string `db:"username"`Sex      string `db:"sex"`Email    string `db:"email"`
}type Place struct {Country string `db:"country"`City    string `db:"city"`TelCode int    `db:"telcode"`
}var Db *sqlx.DBfunc init() {database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")if err != nil {fmt.Println("open mysql failed,", err)return}Db = database
}func main() {r, err := Db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "[e-mail]")if err != nil {fmt.Println("exec failed, ", err)return}id, err := r.LastInsertId()if err != nil {fmt.Println("exec failed, ", err)return}fmt.Println("insert succ:", id)
}

2.2. Select操作

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)type Person struct {UserId   int    `db:"user_id"`Username string `db:"username"`Sex      string `db:"sex"`Email    string `db:"email"`
}type Place struct {Country string `db:"country"`City    string `db:"city"`TelCode int    `db:"telcode"`
}var Db *sqlx.DBfunc init() {database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")if err != nil {fmt.Println("open mysql failed,", err)return}Db = database
}func main() {var person []Personerr := Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)if err != nil {fmt.Println("exec failed, ", err)return}fmt.Println("select succ:", person)
}

2.3. Update操作

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)type Person struct {UserId   int    `db:"user_id"`Username string `db:"username"`Sex      string `db:"sex"`Email    string `db:"email"`
}type Place struct {Country string `db:"country"`City    string `db:"city"`TelCode int    `db:"telcode"`
}var Db *sqlx.DBfunc init() {database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")if err != nil {fmt.Println("open mysql failed,", err)return}Db = database
}func main() {res, err := Db.Exec("update person set username=? where user_id=?", "stu0003", 1)if err != nil {fmt.Println("exec failed, ", err)return}row, err := res.RowsAffected()if err != nil {fmt.Println("rows failed, ",err)}fmt.Println("update succ:",row)}

 2.4. Delete操作

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx"
)type Person struct {UserId   int    `db:"user_id"`Username string `db:"username"`Sex      string `db:"sex"`Email    string `db:"email"`
}type Place struct {Country string `db:"country"`City    string `db:"city"`TelCode int    `db:"telcode"`
}var Db *sqlx.DBfunc init() {database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")if err != nil {fmt.Println("open mysql failed,", err)return}Db = database
}func main() {/*_, err := Db.Exec("delete from person where user_id=?", 1)if err != nil {fmt.Println("exec failed, ", err)return}*/res, err := Db.Exec("delete from person where user_id=?", 1)if err != nil {fmt.Println("exec failed, ", err)return}row,err := res.RowsAffected()if err != nil {fmt.Println("rows failed, ",err)}fmt.Println("delete succ: ",row)
}

3. MySQL事务

mysql事务特性：

    1) 原子性2) 一致性3) 隔离性4) 持久性

golang MySQL事务应用：

    1） import (“github.com/jmoiron/sqlx")2)  Db.Begin()        开始事务3)  Db.Commit()        提交事务4)  Db.Rollback()     回滚事务

package mainimport ("fmt"_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx")type Person struct {UserId   int    `db:"user_id"`Username string `db:"username"`Sex      string `db:"sex"`Email    string `db:"email"`}type Place struct {Country string `db:"country"`City    string `db:"city"`TelCode int    `db:"telcode"`}var Db *sqlx.DBfunc init() {database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")if err != nil {fmt.Println("open mysql failed,", err)return}Db = database}func main() {conn, err := Db.Begin()if err != nil {fmt.Println("begin failed :", err)return}r, err := conn.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "[e-mail]")if err != nil {fmt.Println("exec failed, ", err)conn.Rollback()return}id, err := r.LastInsertId()if err != nil {fmt.Println("exec failed, ", err)conn.Rollback()return}fmt.Println("insert succ:", id)r, err = conn.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "[e-mail]")if err != nil {fmt.Println("exec failed, ", err)conn.Rollback()return}id, err = r.LastInsertId()if err != nil {fmt.Println("exec failed, ", err)conn.Rollback()return}fmt.Println("insert succ:", id)conn.Commit()}