golang mysql数据库基本操作笔记

mysql目前来说是使用最为流行的关系型数据库，在程序设计过程中得到广泛应用。golang操作mysql使用最多的包go-sql-driver/mysql。sqlx包是作为database/sql包的一个额外扩展包，在原有的database/sql加了很多扩展，如直接将查询的数据转为结构体，大大简化了代码书写，当然database/sql包中的方法同样起作用。

github地址：

https://github.com/go-sql-driver/mysql
https://github.com/jmoiron/sqlx

golang sql使用：

database/sql documentation 
go-database-sql tutorial

安装

go get "github.com/go-sql-driver/mysql"
go get "github.com/jmoiron/sqlx"

连接数据库

var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db

处理类型（Handle Types)

sqlx设计和database/sql使用方法是一样的。包含有4中主要的handle types：

sqlx.DB - 和sql.DB相似，表示数据库。 
sqlx.Tx - 和sql.Tx相似，表示事物。 
sqlx.Stmt - 和sql.Stmt相似，表示prepared statement。 
sqlx.NamedStmt - 表示prepared statement（支持named parameters）

所有的handler types都提供了对database/sql的兼容，意味着当你调用sqlx.DB.Query时，可以直接替换为sql.DB.Query.这就使得sqlx可以很容易的加入到已有的数据库项目中。

此外，sqlx还有两个cursor类型：

sqlx.Rows - 和sql.Rows类似，Queryx返回。 
sqlx.Row - 和sql.Row类似，QueryRowx返回。

相比database/sql方法还多了新语法，也就是实现将获取的数据直接转换结构体实现。

 Get(dest interface{}, …) errorSelect(dest interface{}, …) error 

建表

以下所有示例均已以下表结构作为操作基础。

CREATE TABLE `userinfo` (`uid` INT(10) NOT NULL AUTO_INCREMENT,`username` VARCHAR(64)  DEFAULT NULL,`password` VARCHAR(32)  DEFAULT NULL,`department` VARCHAR(64)  DEFAULT NULL,`email` varchar(64) DEFAULT NULL,PRIMARY KEY (`uid`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8

Exec使用

Exec和MustExec从连接池中获取一个连接然后只想对应的query操作。对于不支持ad-hoc query execution的驱动
，在操作执行的背后会创建一个prepared statement。在结果返回前这个connection会返回到连接池中。

需要注意的是不同的数据库类型使用的占位符不同，mysql采用？作为占位符号。

MySQL 使用？ 
PostgreSQL 使用1,1,2等等 
SQLite 使用？或$1 
Oracle 使用:name

Exec增删改示例

查询语法使用Query后续会提到

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBfunc init()  {db,err:=sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {result, err := Db.Exec("INSERT INTO userinfo (username, password,department,email) VALUES (?, ?,?,?)","TigerwolfC","666666","it","TigerwolfC@163.com")if err != nil{fmt.Println("insert failed,error： ", err)return}id,_ := result.LastInsertId()fmt.Println("insert id is :",id)_, err = Db.Exec("update userinfo set username = ? where uid = ?","chen_peggy",1)if err != nil{fmt.Println("update failed error:",err)} else {fmt.Println("update success!")}_, err = Db.Exec("delete from userinfo where uid = ? ", 1)if err != nil{fmt.Println("delete error:",err)}else{fmt.Println("delete success")}}
//insert id is : 1
//update success!
//delete success

sql预声明（Prepared Statements）

对于大部分的数据库来说，当一个query执行的时候，在sql语句数据库内部声明已经声明过了，其声明是在数据库中，我们可以提前进行声明，以便在其他地方重用。

stmt, err := db.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = stmt.QueryRow(65)tx, err := db.Begin()
txStmt, err := tx.Prepare(`SELECT * FROM place WHERE telcode=?`)
row = txStmt.QueryRow(852)

当然sqlx还提供了Preparex()进行扩展，可直接用于结构体转换

  stmt, err := db.Preparex(`SELECT * FROM place WHERE telcode=?`)var p Placeerr = stmt.Get(&p, 852)

Query

Query是database/sql中执行查询主要使用的方法，该方法返回row结果。Query返回一个sql.Rows对象和一个error对象。

在使用的时候应该吧Rows当成一个游标而不是一系列的结果。尽管数据库驱动缓存的方法不一样，通过Next()迭代每次获取一列结果，对于查询结果非常巨大的情况下，可以有效的限制内存的使用，Scan()利用reflect把sql每一列结果映射到go语言的数据类型如string，[]byte等。如果你没有遍历完全部的rows结果，一定要记得在把connection返回到连接池之前调用rows.Close()。

Query返回的error有可能是在server准备查询的时候发生的，也有可能是在执行查询语句的时候发生的。例如可能从连接池中获取一个坏的连级（尽管数据库会尝试10次去发现或创建一个工作连接）。一般来说，错误主要由错误的sql语句，错误的类似匹配，错误的域名或表名等。

在大部分情况下，Rows.Scan()会把从驱动获取的数据进行拷贝，无论驱动如何使用缓存。特殊类型sql.RawBytes可以用来从驱动返回的数据总获取一个zero-copy的slice byte。当下一次调用Next的时候，这个值就不在有效了，因为它指向的内存已经被驱动重写了别的数据。

Query使用的connection在所有的rows通过Next()遍历完后或者调用rows.Close()后释放。

示例：

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBfunc init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {rows, err := Db.Query("SELECT username,password,email FROM userinfo")if err != nil{fmt.Println("query failed,error： ", err)return}for rows.Next() {  //循环结果var username,password,email stringerr = rows.Scan(&username, &password, &email)println(username,password,email)}}
//	TigerwolfC 666666 TigerwolfC@163.com
//  chen_peggy 888888 chen_peggy@165.com

Queryx

Queryx和Query行为很相似，不过返回一个sqlx.Rows对象，支持扩展的scan行为,同时可将对数据进行结构体转换。

示例：

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBtype stu struct {Username string   `db:"username"`Password string      `db:"password"`Department string  `db:"department"`Email string        `db:"email"`
}func init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {rows, err := Db.Queryx("SELECT username,password,email FROM userinfo")if err != nil{fmt.Println("Qeryx failed,error： ", err)return}for rows.Next() {  //循环结果var stu1 stuerr = rows.StructScan(&stu1)// 转换为结构体fmt.Println("stuct data：",stu1.Username,stu1.Password)}
}
//stuct data： TigerwolfC 666666
//stuct data： chen_peggy 888888

QueryRow和QueryRowx

QueryRow和QueryRowx都是从数据库中获取一条数据，但是QueryRowx提供scan扩展，可直接将结果转换为结构体。

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBtype stu struct {Username string   `db:"username"`Password string      `db:"password"`Department string  `db:"department"`Email string        `db:"email"`
}func init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {row := Db.QueryRow("SELECT username,password,email FROM userinfo where uid = ?",1) // QueryRow返回错误，错误通过Scan返回var username,password,email stringerr :=row.Scan(&username,&password,&email)if err != nil{fmt.Println(err)}fmt.Printf("this is QueryRow res:[%s:%s:%s]\n",username,password,email)var s stuerr = Db.QueryRowx("SELECT username,password,email FROM userinfo where uid = ?",2).StructScan(&s)if err != nil{fmt.Println("QueryRowx error :",err)}else {fmt.Printf("this is QueryRowx res:%v",s)}
}
//this is QueryRow res:[TigerwolfC:666666:TigerwolfC@163.com]
//this is QueryRowx res:{chen_peggy 888888 chen_peggy@165.com}

Get 和Select（非常常用）

Get和Select是一个非常省时的扩展，可直接将结果赋值给结构体，其内部封装了StructScan进行转化。Get用于获取单个结果然后Scan，Select用来获取结果切片。

示例：

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBtype stu struct {Username string   `db:"username"`Password string      `db:"password"`Department string  `db:"department"`Email string        `db:"email"`
}func init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {var stus []stuerr := Db.Select(&stus,"SELECT username,password,email FROM userinfo")if err != nil{fmt.Println("Select error",err)}fmt.Printf("this is Select res:%v\n",stus)var s stuerr := Db.Get(&s,"SELECT username,password,email FROM userinfo where uid = ?",2)if err != nil{fmt.Println("GET error :",err)}else {fmt.Printf("this is GET res:%v",s)}
}
//this is Select res:[{TigerwolfC 666666  TigerwolfC@163.com} {chen_peggy 888888 chen_peggy@165.com}]
//this is GET res:{chen_peggy 888888 chen_peggy@165.com}

事务（Transactions）

事务操作是通过三个方法实现：

Begin()：开启事务

Commit()：提交事务（执行sql)

Rollback()：回滚

使用流程：

tx, err := db.Begin()
err = tx.Exec(...)
err = tx.Commit()

//或者使用sqlx扩展的事务

tx := db.MustBegin()
tx.MustExec(...)
err = tx.Commit()

由于事务是一个一直连接的状态，所以Tx对象必须绑定和控制单个连接。一个Tx会在整个生命周期中保存一个连接，然后在调用commit或Rollback()的时候释放掉。在调用这几个函数的时候必须十分小心，否则连接会一直被占用直到被垃圾回收。

使用示例：

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBfunc init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = db
}func main()  {tx, err := Db.Beginx()_, err = tx.Exec("insert into userinfo(username,password) values(?,?)", "Rose","2223")if err != nil {tx.Rollback()}_, err = tx.Exec("insert into userinfo(username,password) values(?,?)", "Mick",222)if err != nil {fmt.Println("exec sql error:",err)tx.Rollback()}err = tx.Commit()if err != nil {fmt.Println("commit error")}}

连接池设置

默认情况下，连接池增长无限制，并且只要连接池中没有可用的空闲连接，就会创建连接。我们可以使用DB.SetMaxOpenConns设置池的最大大小。未使用的连接标记为空闲，如果不需要则关闭。要避免建立和关闭大量连接，可以使用DB.SetMaxIdleConns设置最大空闲连接。

    DB.SetMaxIdleConns(n int)    设置最大空闲连接数DB.SetMaxOpenConns(n int)  设置最大打开的连接数

示例：

package mainimport (_ "github.com/go-sql-driver/mysql""github.com/jmoiron/sqlx""fmt"
)var Db *sqlx.DBfunc init()  {db, err := sqlx.Open("mysql", "user:password@tcp(localhost:3306)/dbname?charset=utf8")if err != nil {fmt.Println("open mysql failed,", err)return}Db = dbDb.SetMaxOpenConns(30)Db.SetMaxIdleConns(15)Db.Ping()}

如有不对欢迎指正，相互学习，共同进步。
参考连接