Go 操作 Mysql（一）

吕学英 發表於 2019-7-5 20:15:00

<h2>关于 Go 的标准库 database/sql 和 sqlx</h2>
database/sql 是 Go 操作数据库的标准库之一，它提供了一系列接口方法，用于访问数据库（mysql，sqllite，oralce，postgresql），它并不会提供数据库特有的方法，那些特有的方法交给数据库驱动去实现
而通常在工作中，我们更多的是用  https://github.com/jmoiron/sqlx 包来操作数据库，sqlx 是基于标准库 sql 的扩展，并且我们可以通过 sqlx 操作各种类型的数据，如将查询的数据转为结构体等
github 地址：
<ul>
<li>https://github.com/go-sql-driver/mysql</li>
<li>https://github.com/jmoiron/sqlx</li>
</ul>
安装：
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">go get "github.com/go-sql-driver/mysql"
go get "github.com/jmoiron/sqlx"</pre>
</div>
 
sqlx 库提供了一些类型，掌握这些类型的用法非常的重要
1）DB（数据库对象）
sql.DB 类型代表了数据库，其它语言操作数据库的时候，需要创建一个连接，对于 Go 而言则是需要创建一个数据库类型，它不是数据库连接，Go 中的连接来自内部实现的连接池，连接的建立是惰性的，连接将会在操作的时候，由连接池创建并维护
使用 sql.Open 函数创建数据库类型，第一个是数据库驱动名，第二个是连接信息的字符串
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db
</pre>
</div>
 
2）Results 和 Result（结果集）
新增、更新、删除；和查询所用的方法不一样，所有返回的类型也不同
<ul>
<li>Result 是新增、更新、删除时返回的结果集</li>
<li>Results 是查询数据库时的结果集，sql.Rows 类型表示查询返回多行数据的结果集，sql.Row 则表示单行查询的结果集</li>
</ul>
 
3）Statements（语句）
sql.Stmt 类型表示 sql 语句，例如 DDL,DML 等类似的 sql 语句，可以当成 prepare 语句构造查询，也可以直接使用 sql.DB 的函数对其操作
 
<h2>实践部分（数据库CURD）</h2>
数据库建表
以下所有 demo 都以下表结构作为基础
<div class="cnblogs_code">
<pre>CREATE TABLE `userinfo` (
`uid` INT(10) NOT NULL AUTO_INCREMENT,
`create_time` datetime DEFAULT NULL,
`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;</pre>
</div>
 
Exec() 方法使用（新增、修改、删除）
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">func (db *DB) Exec(query string, args ...interface{}) (Result, error)</pre>
</div>
Exec 和 MustExec 从连接池中获取一个连接然后指向对应的 query 操作，对于不支持 ad-hoc query execution 的驱动，在操作执行的背后会创建一个 prepared statement，在结果返回前，这个 connection 会返回到连接池中
需要注意的是，不同的数据库，使用的占位符不同，mysql 采用？作为占位符
<ul>
<li>Mysql 使用 ?</li>
<li>PostgreSQL 使用 1,1,2 等等</li>
<li>SQLLite 使用 ? 或 $1</li>
<li>Oracle 使用 :name        （注意有冒号）</li>
</ul>
 
demo：定义了 4 个函数，分别是连接数据库，插入数据，更新数据，删除数据
关于下面数据库操作的几个小知识点
<ol>
<li>插入数据后可以通过 LastInsertId() 方法获取插入数据的主键 id</li>
<li>通过 RowsAffected 可以获取受影响的行数</li>
<li>通过 Exec() 方法插入数据，返回的结果是 sql.Result 类型</li>
</ol>
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">package main

import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)

var (
userNamestring = "chenkai"
passwordstring = "chenkai"
ipAddrees string = "192.168.0.115"
port int = 3306
dbName string = "test"
charset string = "utf8"
)

func connectMysql() (*sqlx.DB) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
Db, err := sqlx.Open("mysql", dsn)
if err != nil {
fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
}
return Db
}

func addRecord(Db *sqlx.DB) {
for i:=0; i<2; i++ {
result, err := Db.Exec("insert into userinfovalues(?,?,?,?,?,?)",0, "2019-07-06 11:45:20", "johny", "123456", "技术部", "123456@163.com")
if err != nil {
fmt.Printf("data insert faied, error:[%v]", err.Error())
return
}
id, _ := result.LastInsertId()
fmt.Printf("insert success, last id:[%d]\n", id)
}
}

func updateRecord(Db *sqlx.DB){
//更新uid=1的username
result, err := Db.Exec("update userinfo set username = 'anson' where uid = 1")
if err != nil {
fmt.Printf("update faied, error:[%v]", err.Error())
return
}
num, _ := result.RowsAffected()
fmt.Printf("update success, affected rows:[%d]\n", num)
}

func deleteRecord(Db *sqlx.DB){
//删除uid=2的数据
result, err := Db.Exec("delete from userinfo where uid = 2")
if err != nil {
fmt.Printf("delete faied, error:[%v]", err.Error())
return
}
num, _ := result.RowsAffected()
fmt.Printf("delete success, affected rows:[%d]\n", num)
}

func main() {
var Db *sqlx.DB = connectMysql()
defer Db.Close()

addRecord(Db)
updateRecord(Db)
deleteRecord(Db)
}

运行结果：
API server listening at: 127.0.0.1:59899
insert success, last id:
insert success, last id:
update success, affected rows:
delete success, affected rows:
</pre>
</div>
　　
Query() 方法使用（查询单个字段数据）
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">func (db *DB) Query(query string, args ...interface{}) (*Rows, error)</pre>
</div>
Query() 方法返回的是一个 sql.Rows 类型的结果集
也可以用来查询多个字段的数据，不过需要定义多个字段的变量进行接收
迭代后者的 Next() 方法，然后使用 Scan() 方法给对应类型变量赋值，以便取出结果，最后再把结果集关闭（释放连接）
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)

var (
userNamestring = "chenkai"
passwordstring = "chenkai"
ipAddrees string = "192.168.0.115"
port int = 3306
dbName string = "test"
charset string = "utf8"
)

func connectMysql() (*sqlx.DB) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
Db, err := sqlx.Open("mysql", dsn)
if err != nil {
fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
}
return Db
}

func queryData(Db *sqlx.DB) {
rows, err := Db.Query("select * from userinfo")
if err != nil {
fmt.Printf("query faied, error:[%v]", err.Error())
return
}
for rows.Next() {
//定义变量接收查询数据
var uid int
var create_time, username, password, department, email string

err := rows.Scan(&uid, &create_time, &username, &password, &department, &email)
if err != nil {
fmt.Println("get data failed, error:[%v]", err.Error())
}
fmt.Println(uid, create_time, username, password, department, email)
}

//关闭结果集（释放连接）
rows.Close()
}

func main() {
var Db *sqlx.DB = connectMysql()
defer Db.Close()

queryData(Db)
}

运行结果：
1 2019-07-06 11:45:20 anson 123456 技术部 123456@163.com
3 2019-07-06 11:45:20 johny 123456 技术部 123456@163.com
4 2019-07-06 11:45:20 johny 123456 技术部 123456@163.com
</pre>
</div>
 
Get() 方法使用
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">func (db *DB) Get(dest interface{}, query string, args ...interface{}) error</pre>
</div>
是将查询到的一条记录，保存到结构体
结构体的字段名首字母必须大写，不然无法寻址
<div class="cnblogs_code"><img id="code_img_closed_8934b56f-f0d8-4ea3-812d-35b27fb680f9" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_8934b56f-f0d8-4ea3-812d-35b27fb680f9" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_8934b56f-f0d8-4ea3-812d-35b27fb680f9" class="cnblogs_code_hide">
<pre>package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)

var (
userNamestring = "chenkai"
passwordstring = "chenkai"
ipAddrees string = "192.168.0.115"
port int = 3306
dbName string = "test"
charset string = "utf8"
)

func connectMysql() (*sqlx.DB) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
Db, err := sqlx.Open("mysql", dsn)
if err != nil {
 fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
}
return Db
}

func getData(Db *sqlx.DB) {
type userInfo struct {
 Uid int `db:"uid"`
 UserName string `db:"username"`
 CreateTime string `db:"create_time"`
 Password string `db:"password"`
 Department string `db:"department"`
 Email string `db:"email"`
}

//初始化定义结构体，用来存放查询数据
var userData *userInfo = new(userInfo)
err := Db.Get(userData,"select *from userinfo where uid = 1")
if err != nil {
 fmt.Printf("query faied, error:[%v]", err.Error())
 return
}

//打印结构体内容
fmt.Println(userData.Uid, userData.CreateTime, userData.UserName,
 userData.Password, userData.Department, userData.Email)
}

func main() {
var Db *sqlx.DB = connectMysql()
defer Db.Close()

getData(Db)
}

运行结果：
1 2019-07-06 11:45:20 anson 123456 技术部 123456@163.com</pre>
</div>
View Code</div>
 
Select() 方法使用
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">func (db *DB) Select(dest interface{}, query string, args ...interface{}) error</pre>
</div>
将查询的多条记录，保存到结构体的切片中
结构体的字段名首字母必须大写，不然无法寻址
<div class="cnblogs_code"><img id="code_img_closed_09b9b451-5a65-4f13-abb7-64e0b64a182a" class="code_img_closed" src="https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif" alt=""><img id="code_img_opened_09b9b451-5a65-4f13-abb7-64e0b64a182a" class="code_img_opened" style="display: none" src="https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif" alt="">
<div id="cnblogs_code_open_09b9b451-5a65-4f13-abb7-64e0b64a182a" class="cnblogs_code_hide">
<pre>package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)

var (
userNamestring = "chenkai"
passwordstring = "chenkai"
ipAddrees string = "192.168.0.115"
port int = 3306
dbName string = "test"
charset string = "utf8"
)

func connectMysql() (*sqlx.DB) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
Db, err := sqlx.Open("mysql", dsn)
if err != nil {
 fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
}
return Db
}

func selectData(Db *sqlx.DB) {
type userInfo struct {
 Uid int `db:"uid"`
 UserName string `db:"username"`
 CreateTime string `db:"create_time"`
 Password string `db:"password"`
 Department string `db:"department"`
 Email string `db:"email"`
}

//定义结构体切片，用来存放多条查询记录
var userInfoSlice []userInfo
err := Db.Select(&userInfoSlice,"select * from userinfo")
if err != nil {
 fmt.Printf("query faied, error:[%v]", err.Error())
 return
}

//遍历结构体切片
for _, userData := range userInfoSlice {
 fmt.Println(userData.Uid, userData.CreateTime, userData.UserName,
 userData.Password, userData.Department, userData.Email)
}

}

func main() {
var Db *sqlx.DB = connectMysql()
defer Db.Close()

selectData(Db)
}

运行结果：
1 2019-07-06 11:45:20 anson 123456 技术部 123456@163.com
3 2019-07-06 11:45:20 johny 123456 技术部 123456@163.com
4 2019-07-06 11:45:20 johny 123456 技术部 123456@163.com</pre>
</div>
View Code</div>
 
<h2>重点内容回顾</h2>
sql.DB
<ol>
<li>当我们调用 sqlx.Open() 可以获取一个 sql.DB 类型对象，sqlx.DB 是数据库的抽象，切记它不是数据库连接，sqlx.Open() 只是验证数据库参数，并没有创建数据库连接</li>
<li>sqlx.DB 拥有一系列与数据库交互的方法（Exec，Query，Get，Select ...），同时也管理维护着一个数据库连接池，并且对于多个 goroutine 也是安全的</li>
<li>sqlx.DB 表示是数据库的抽象，因此有几个数据库就要创建几个 sqlx.DB 类型对象，因为它要维护一个连接池，因此不需要频繁的创建和销毁</li>
</ol>
 
连接池
只用 sqlx.Open() 函数创建连接池，此时只是初始化了连接池，并没有连接数据库，连接都是惰性的，只有调用 sqlx.DB 的方法时，此时才真正用到了连接，连接池才会去创建连接，连接池很重要，它直接影响着你的程序行为
 
连接池的工作原理也非常简单，当调用 sqlx.DB 的方法时，会首先去向连接池请求要一个数据库连接，如果连接池有空闲的连接，则返回给方法中使用，否则连接池将创建一个新的连接给到方法中使用；一旦将数据库连接给到了方法中，连接就属于方法了。方法执行完毕后，要不把连接所属权还给连接池，要不传递给下一个需要数据库连接的方法中，最后都使用完将连接释放回到连接池中
请求数据库连接的方法有几个，执行完毕处理连接的方式也不同：
<ol>
<li>DB.Ping() 使用完毕后会马上把连接返回给连接池</li>
<li>DB.Exec() 使用完毕后会马上把连接返回给连接池，但是它返回的 Result 对象还保留着连接的引用，当后面的代码需要处理结果集的时候，连接将会被重新启用</li>
<li>DB.Query() 调用完毕后将连接传递给 sql.Rows 类型，当后者迭代完毕或者显示的调用 Close() 方法后，连接将会被释放到连接池</li>
<li>DB.QueryRow() 调用完毕后将连接传递给 sql.Row 类型，当 Scan() 方法调用完成后，连接将会被释放到连接池</li>
<li>DB.Begin() 调用完毕后将连接传递给 sql.Tx 类型对象，当 Commit() 或 Rollback() 方法调用后释放连接</li>
</ol>
 
每个连接都是惰性的，如果验证 sqlx.Open() 调用之后，sqlx.DB 类型对象可用呢？通过 DB.Ping() 方法来初始化
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">func (db *DB) Ping() error</pre>
</div>
demo：需要知道，当调用了 Ping() 方法后，连接池一定会初始化一个数据库连接
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)

var (
userNamestring = "chenkai"
passwordstring = "chenkai"
ipAddrees string = "192.168.0.115"
port int = 3306
dbName string = "test"
charset string = "utf8"
)

func connectMysql() (*sqlx.DB) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", userName, password, ipAddrees, port, dbName, charset)
Db, err := sqlx.Open("mysql", dsn)
if err != nil {
fmt.Printf("mysql connect failed, detail is [%v]", err.Error())
}
return Db
}

func ping(Db *sqlx.DB) {
err := Db.Ping()
if err != nil {
fmt.Println("ping failed")
} else {
fmt.Println("ping success")
}
}

func main() {
var Db *sqlx.DB = connectMysql()
defer Db.Close()

ping(Db)
}

运行结果：
ping success
</pre>
</div>
 
连接池配置
DB.SetMaxIdleConns(n int) 设置连接池中的保持连接的最大连接数。默认也是0，表示连接池不会保持数据库连接的状态：即当连接释放回到连接池的时候，连接将会被关闭。这会导致连接再连接池中频繁的关闭和创建，我们可以设置一个合理的值。
 
DB.SetMaxOpenConns(n int) 设置打开数据库的最大连接数。包含正在使用的连接和连接池的连接。如果你的方法调用需要用到一个连接，并且连接池已经没有了连接或者连接数达到了最大连接数。此时的方法调用将会被 block，直到有可用的连接才会返回。设置这个值可以避免并发太高导致连接 mysql 出现 too many connections 的错误。该函数的默认设置是0，表示无限制
 
DB.SetConnMaxLifetime(d time.Duration) 设置连接可以被使用的最长有效时间，如果过期，连接将被拒绝
 
数据库连接重试次数
sqlx 中的方法帮我们做了很多事情，我们不用考虑连接失败的情况，当调用方法进行数据库操作的时候，如果连接失败，sqlx 中的方法会帮我们处理，它会自动连接2次，这个如果查看源码中我们可以看到如下的代码：
其它的方法中也有这种处理，代码中变量maxBadConnRetries小时如果连接失败尝试的次数，默认是 2
<div class="cnblogs_Highlighter">
<pre class="brush:go;gutter:true;">// ExecContext executes a query without returning any rows.
// The args are for any placeholder parameters in the query.
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error) {
var res Result
var err error
for i := 0; i < maxBadConnRetries; i++ {
 res, err = db.exec(ctx, query, args, cachedOrNewConn)
 if err != driver.ErrBadConn {
 break
 }
}
if err == driver.ErrBadConn {
 return db.exec(ctx, query, args, alwaysNewConn)
}
return res, err
}
</pre>
</div>
　　
 
参考链接：https://www.cnblogs.com/zhaof/p/8509164.html
ending ~
 

</div>
<div id="MySignature" role="contentinfo">
每天都要遇到更好的自己. 
来源：https://www.cnblogs.com/kaichenkai/p/11140555.html

頁: [1]

琼殿技术论坛's Archiver

Go 操作 Mysql（一）