Go操作mysql实现增删改查及连接池

系统管理员 2023-10-17 18:21 2阅读 0赞

[\[-\]][-]

1.  [下载驱动][Link 1]
2.  [创建测试表][Link 2]
3.  [数据库连接][Link 3]
4.  [插入操作][Link 4]
5.  [查询操作][Link 5]
6.  [修改操作][Link 6]
7.  [删除操作][Link 7]
8.  [完整代码][Link 8]
9.  [小结][Link 9]
10. [开启web服务][web]
11. [db对象初始化][db]
12. [请求方法][Link 10]
13. [小结][Link 11]

golang本身没有提供连接mysql的驱动，但是定义了标准接口供第三方开发驱动。这里连接mysql可以使用第三方库，第三方库推荐使用https://github.com/Go-SQL-Driver/MySQL这个驱动，更新维护都比较好。下面演示下具体的使用，完整代码示例可以参考最后。

### 下载驱动 ###

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

如果提示这样的失败信息：cannot download, $GOPATH not set. For more details see: go help gopath，可以使用如下命令解决

sudo env GOPATH=/Users/chenjiebin/golang go get github.com/go-sql-driver/mysql

GOPATH的值根据自行环境进行替换。

### 创建测试表 ###

在mysql test库中创建测试表

CREATE TABLE IF NOT EXISTS `test`.`user` (
     `user_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
     `user_name` VARCHAR(45) NOT NULL COMMENT '用户名称',
     `user_age` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年龄',
     `user_sex` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户性别',
     PRIMARY KEY (`user_id`))
     ENGINE = InnoDB
     AUTO_INCREMENT = 1
     DEFAULT CHARACTER SET = utf8
     COLLATE = utf8_general_ci
     COMMENT = '用户表'

### 数据库连接 ###

数据库连接使用datebase/sql Open函数进行连接

db, err := sql.Open("mysql", "user:password@tcp(localhost:5555)/dbname?charset=utf8")

其中连接参数可以有如下几种形式：

> user@unix(/path/to/socket)/dbname?charset=utf8  
> user:password@tcp(localhost:5555)/dbname?charset=utf8  
> user:password@/dbname  
> user:password@tcp(\[de:ad:be:ef::ca:fe\]:80)/dbname

通常我们都用第二种。

### 插入操作 ###

stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
    checkErr(err)
    res, err := stmt.Exec("tony", 20, 1)
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)

这里使用结构化操作，不推荐使用直接拼接sql语句的方法。

### 查询操作 ###

rows, err := db.Query("SELECT * FROM user")
    checkErr(err)
    
    for rows.Next() {
    	var userId int
    	var userName string
    	var userAge int
    	var userSex int
    	rows.Columns()
    	err = rows.Scan(&userId, &userName, &userAge, &userSex)
    	checkErr(err)
    	fmt.Println(userId)
    	fmt.Println(userName)
    	fmt.Println(userAge)
    	fmt.Println(userSex)
    }

这里查询的方式使用声明4个独立变量userId、userName、userAge、userSex来保存查询出来的每一行的值。在实际开发中通常会封装数据库的操作，对这样的查询通常会考虑返回字典类型。

//构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址
    columns, _ := rows.Columns()
    scanArgs := make([]interface{}, len(columns))
    values := make([]interface{}, len(columns))
    for i := range values {
    	scanArgs[i] = &values[i]
    }
    
    for rows.Next() {
    	//将行数据保存到record字典
    	err = rows.Scan(scanArgs...)
    	record := make(map[string]string)
    	for i, col := range values {
    		if col != nil {
    			record[columns[i]] = string(col.([]byte))
    		}
    	}
    	fmt.Println(record)
    }

### 修改操作 ###

stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
    checkErr(err)
    res, err := stmt.Exec(21, 2, 1)
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)

### 删除操作 ###

stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
    checkErr(err)
    res, err := stmt.Exec(1)
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)

修改和删除操作都比较简单，同插入数据类似，只是使用RowsAffected来获取影响的数据行数。

### 完整代码 ###

package main
    
    import (
    	"database/sql"
    	"fmt"
    	_ "github.com/go-sql-driver/mysql"
    )
    
    func main() {
    	insert()
    }
    
    //插入demo
    func insert() {
    	db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    	checkErr(err)
    
    	stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
    	checkErr(err)
    	res, err := stmt.Exec("tony", 20, 1)
    	checkErr(err)
    	id, err := res.LastInsertId()
    	checkErr(err)
    	fmt.Println(id)
    }
    
    //查询demo
    func query() {
    	db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    	checkErr(err)
    
    	rows, err := db.Query("SELECT * FROM user")
    	checkErr(err)
    
    	//普通demo
    	//for rows.Next() {
    	//	var userId int
    	//	var userName string
    	//	var userAge int
    	//	var userSex int
    
    	//	rows.Columns()
    	//	err = rows.Scan(&userId, &userName, &userAge, &userSex)
    	//	checkErr(err)
    
    	//	fmt.Println(userId)
    	//	fmt.Println(userName)
    	//	fmt.Println(userAge)
    	//	fmt.Println(userSex)
    	//}
    
    	//字典类型
    	//构造scanArgs、values两个数组，scanArgs的每个值指向values相应值的地址
    	columns, _ := rows.Columns()
    	scanArgs := make([]interface{}, len(columns))
    	values := make([]interface{}, len(columns))
    	for i := range values {
    		scanArgs[i] = &values[i]
    	}
    
    	for rows.Next() {
    		//将行数据保存到record字典
    		err = rows.Scan(scanArgs...)
    		record := make(map[string]string)
    		for i, col := range values {
    			if col != nil {
    				record[columns[i]] = string(col.([]byte))
    			}
    		}
    		fmt.Println(record)
    	}
    }
    
    //更新数据
    func update() {
    	db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    	checkErr(err)
    
    	stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
    	checkErr(err)
    	res, err := stmt.Exec(21, 2, 1)
    	checkErr(err)
    	num, err := res.RowsAffected()
    	checkErr(err)
    	fmt.Println(num)
    }
    
    //删除数据
    func remove() {
    	db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    	checkErr(err)
    
    	stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
    	checkErr(err)
    	res, err := stmt.Exec(1)
    	checkErr(err)
    	num, err := res.RowsAffected()
    	checkErr(err)
    	fmt.Println(num)
    }
    
    func checkErr(err error) {
    	if err != nil {
    		panic(err)
    	}
    }

### 小结 ###

整体上来说都比较简单，就是查询那边使用字典来存储返回数据比较复杂一些。既然说到数据库连接，通常应用中都会使用连接池来减少连接开销，关于连接池下次整理一下再放上来。

参考资料：《go web编程》，go web编程中对数据库连接做了比较详细的解说，值得一看。

转载请注明：[快乐编程][Link 12] » [golang连接mysql操作示例增删改查][golang_mysql]

golang内部自带了连接池功能，刚开始接触golang的时候不了解这个，还自己搞了一个 sql.Open的对象管理池，真的非常囧啊。

sql.Open函数实际上是返回一个连接池对象，不是单个连接。在open的时候并没有去连接数据库，只有在执行query、exce方法的时候才会去实际连接数据库。在一个应用中同样的库连接只需要保存一个sql.Open之后的db对象就可以了，不需要多次open。

golang中关于mysql的增删改查我在前面的一篇文章中有说明了，不了解的小伙们可以先去了解一下：[golang连接mysql操作示例增删改查][golang_mysql]

因为普通程序执行完毕之后资源就会被释放掉，所以这里尝试使用web服务进行演示。

### 开启web服务 ###

首页先启动一个web服务监听9090端口，比较简单不多做说明。

func startHttpServer() {
    	http.HandleFunc("/pool", pool)
    	err := http.ListenAndServe(":9090", nil)
    	if err != nil {
    		log.Fatal("ListenAndServe: ", err)
    	}
    }

### db对象初始化 ###

声明一个全局的db对象，并进行初始化。

var db *sql.DB
    
    func init() {
    	db, _ = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?charset=utf8")
    	db.SetMaxOpenConns(2000)
    	db.SetMaxIdleConns(1000)
    	db.Ping()
    }

连接池的实现关键在于SetMaxOpenConns和SetMaxIdleConns，其中：

> SetMaxOpenConns用于设置最大打开的连接数，默认值为0表示不限制。  
> SetMaxIdleConns用于设置闲置的连接数。

设置最大的连接数，可以避免并发太高导致连接mysql出现too many connections的错误。设置闲置的连接数则当开启的一个连接使用完成后可以放在池里等候下一次使用。

### 请求方法 ###

上面开启http请求设置了请求/pool地址的执行方法

func pool(w http.ResponseWriter, r *http.Request) {
    	rows, err := db.Query("SELECT * FROM user limit 1")
    	defer rows.Close()
    	checkErr(err)
    
    	columns, _ := rows.Columns()
    	scanArgs := make([]interface{}, len(columns))
    	values := make([]interface{}, len(columns))
    	for j := range values {
    		scanArgs[j] = &values[j]
    	}
    
    	record := make(map[string]string)
    	for rows.Next() {
    		//将行数据保存到record字典
    		err = rows.Scan(scanArgs...)
    		for i, col := range values {
    			if col != nil {
    				record[columns[i]] = string(col.([]byte))
    			}
    		}
    	}
    
    	fmt.Println(record)
    	fmt.Fprintln(w, "finish")
    }
    
    func checkErr(err error) {
    	if err != nil {
    		fmt.Println(err)
    		panic(err)
    	}
    }

pool方法就是从user表中查出一条记录然后存放到map中，最后输出finish。代码到这里就算完了非常简单，下面来测试一下。首先启动http服务，然后使用ab进行并发测试访问：

$ ab -c 100 -n 1000 'http://localhost:9090/pool'

在数据库中通过show processlist查看连接进程：

![golang数据库连接池][golang]

golang数据库连接池

可以看到有100来个进程。

因为避免了重复创建连接，所以使用连接池可以很明显的提高性能。有兴趣的童靴可以去掉连接池代码自己测试一下。完整代码如下：

//数据库连接池测试
    package main
    
    import (
    	"database/sql"
    	"fmt"
    	_ "github.com/go-sql-driver/mysql"
    	"log"
    	"net/http"
    )
    
    var db *sql.DB
    
    func init() {
    	db, _ = sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?charset=utf8")
    	db.SetMaxOpenConns(2000)
    	db.SetMaxIdleConns(1000)
    	db.Ping()
    }
    
    func main() {
    	startHttpServer()
    }
    
    func startHttpServer() {
    	http.HandleFunc("/pool", pool)
    	err := http.ListenAndServe(":9090", nil)
    	if err != nil {
    		log.Fatal("ListenAndServe: ", err)
    	}
    }
    
    func pool(w http.ResponseWriter, r *http.Request) {
    	rows, err := db.Query("SELECT * FROM user limit 1")
    	defer rows.Close()
    	checkErr(err)
    
    	columns, _ := rows.Columns()
    	scanArgs := make([]interface{}, len(columns))
    	values := make([]interface{}, len(columns))
    	for j := range values {
    		scanArgs[j] = &values[j]
    	}
    
    	record := make(map[string]string)
    	for rows.Next() {
    		//将行数据保存到record字典
    		err = rows.Scan(scanArgs...)
    		for i, col := range values {
    			if col != nil {
    				record[columns[i]] = string(col.([]byte))
    			}
    		}
    	}
    
    	fmt.Println(record)
    	fmt.Fprintln(w, "finish")
    }
    
    func checkErr(err error) {
    	if err != nil {
    		fmt.Println(err)
    		panic(err)
    	}
    }

### 小结 ###

golang这边实现的连接池只提供了SetMaxOpenConns和SetMaxIdleConns方法进行连接池方面的配置。在使用的过程中有一个问题就是数据库本身对连接有一个超时时间的设置，如果超时时间到了数据库会单方面断掉连接，此时再用连接池内的连接进行访问就会出错。

packets.go:32: unexpected EOF
    packets.go:118: write tcp 192.168.3.90:3306: broken pipe

上面都是错误都是go-sql-drive本身的输出，有的时候还会出现bad connection的错误。多请求几次后连接池会重新打开新连接这时候就没有问题了。关于这个问题自己有初步的解决方法，但是感觉不太完美，下次再放上来。

转载请注明：[快乐编程][Link 12] » [golang go-sql-drive mysql连接池的实现][golang go-sql-drive mysql]

来源:http://blog.csdn.net/kenkao/article/details/47857795

[-]: http://blog.csdn.net/kenkao/article/details/47857795#
[Link 1]: http://blog.csdn.net/kenkao/article/details/47857795#t0
[Link 2]: http://blog.csdn.net/kenkao/article/details/47857795#t1
[Link 3]: http://blog.csdn.net/kenkao/article/details/47857795#t2
[Link 4]: http://blog.csdn.net/kenkao/article/details/47857795#t3
[Link 5]: http://blog.csdn.net/kenkao/article/details/47857795#t4
[Link 6]: http://blog.csdn.net/kenkao/article/details/47857795#t5
[Link 7]: http://blog.csdn.net/kenkao/article/details/47857795#t6
[Link 8]: http://blog.csdn.net/kenkao/article/details/47857795#t7
[Link 9]: http://blog.csdn.net/kenkao/article/details/47857795#t8
[web]: http://blog.csdn.net/kenkao/article/details/47857795#t9
[db]: http://blog.csdn.net/kenkao/article/details/47857795#t10
[Link 10]: http://blog.csdn.net/kenkao/article/details/47857795#t11
[Link 11]: http://blog.csdn.net/kenkao/article/details/47857795#t12
[Link 12]: http://www.01happy.com/
[golang_mysql]: http://www.01happy.com/golang-mysql-demo/
[golang]: http://01happy-r.stor.sinaapp.com/uploads/2014/12/golang%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%9E%E6%8E%A5%E6%B1%A0.png
[golang go-sql-drive mysql]: http://www.01happy.com/golang-go-sql-drive-mysql-connection-pooling/