GoWeb——访问MySQL

客官°小女子只卖身不卖艺 2024-03-17 11:13 48阅读 0赞

### Go访问MySQL ###

Go语言中的database/sql包提供了连接SQL数据库或类SQL数据库的泛用接口，但并不提供具体的数据库驱动程序。在使用database/sql包时，必须注入至少一个数据库驱动程序。

在Go语言中，常用的数据库基本都有完整的第三方包实现。在用Go语言访问M小ySQL之前，需要先创建数据库和数据表。

> 1.  创建数据库和数据表

create database chapter05;
    
    create table `user` (
    	`uid` bigint(20) not null auto_increment,
    	`name` varchar(20) default '',
    	`phone` varchar(20) default '',
    	primary key(`uid`)
    ) engine=InnoDB auto_increment=1 default charset=utf8mb4

> 1.  下载MySQL的驱动程序

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

> 1.  使用MySQL驱动程序

import (
    	"database/sql"
    	_ "github.com/go-sql-driver/mysql"
    )

在以上语句中，github.com/go-sql-driver/mysql就是依赖包。因为没有直接使用该包中的对象，所以在导入包前面被加上了下画线（\_)。

Go语言database/sql包中提供了Open()函数，用来连接数据库。Open()函数的定义如下：

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

其中，dirverName参数用于指定的数据。dataSourceName参数用于指定数据源，一般至少包括数据库文件名和（可能的）连接信息。

import (
    	"database/sql"
    	_ "github.com/go-sql-driver/mysql"
    	"log"
    )
    
    func main() {
        
    	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/chapter")
    	if err != nil {
        
    		log.Fatal(err)
    	}
    	defer db.Close()
    }

> 1.  初始化连接

在用Open()函数建立连接后，如果要检查数据源的名称是否合法，则可以调用Ping()方法。

返回的DB对象可以安全地被多个goroutine同时使用，并且它会维护自身的闲置连接池。这样Open()函数只需调用一次，因为一般启动后很少关闭DB对象。用Open()函数初始化连接的示例

import (
    	"database/sql"
    	"fmt"
    	_ "github.com/go-sql-driver/mysql"
    )
    
    var db *sql.DB
    
    //定义一个初始化数据库的函数
    func initDB() (err error) {
        
    	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/chapter")
    	if err != nil {
        
    		return err
    	}
    	//尝试与数据库建立连接
    	err = db.Ping()
    	if err != nil {
        
    		return err
    	}
    	return nil
    }
    
    func main() {
        
    	err := initDB()
    	if err != nil {
        
    		fmt.Printf("init db failed, err: %v\n", err)
    		return
    	}
    }

其中，sql.DB是一个数据库的操作句柄，代表一个具有零到多个底层连接的连接池。它可以安全地被多个goroutine同时使用。database/sql包会自动创建和释放连接，也会维护一个闲置连接的连接池。

> 1.  设置最大连接数

database/sql包中SetMaxOpenConns()方法用于设置与数据库建立连接的最大数目，其定义如下：

func (db *DB) SetMaxOpenConns(n int)

其中参数n为整数类型。如果n大于0且小于“最大闲置连接数”，则将“最大闲置连接数”减小到与“最大开启连接数的限制”匹配。如果≤0，则不会限制最大开启连接数，默认为0（无限制)。

> 1.  设置最大限制连接数

database/sql包中的SetMaxldleConns()方法用于设置连接池中的最大闲置连接数，其定义如下：

func (db *DB) SetMaxIdleConns(n int)

其中参数n为整数类型。如果n大于最大开启连接数，则新的最大闲置连接数会以最大开启连接数为准。如果n≤0，则将不会保留闲置连接。

> 1.  SQL查询

##### QueryRow()方法进行单行查询： #####

根据本节之前创建的uSer表，定义一个User结构体来存储数据库返回的数据：

type User struct {
        
    	Uid int 
    	Name string 
    	Phone string 
    }

database/sql包，中单行查询方法的定义如下：

func (db *DB)QueryRow(query string,args ...interface(}) *Row

QueryRow()方法执行一次查询，并返回最多一行（Row)结果。QueryRow()方法总是返回非nil的值，直到返回值的Scan()方法被调用时才会返回被延迟的错误。示例代码如下。

//单行测试
    func queryRow(uid int) (user User) {
        
    	//应确保在QueryRow()方法之后调用Scan方法，否则持有的数据库连接不会被释放
    	err := DB.QueryRow("select uid, name, phone from `user` where uid = ?", uid).Scan(&user.Uid, &user.Name, &user.Phone)
    	if err != nil {
        
    		log.Fatal(err)
    	}
    	fmt.Printf("uid:%d name:%s phone:%s\n", user.Uid, user.Name, user.Phone)
    	return
    }

##### 2. 用Query()方法进行多行查询 #####

Quey()方法执行一次查询，返回多行（Rows)结果，一般用于执行SELECT类型的SQL命令。Quey()方法的定义如下：

func (db *DB)Query(query string,args ...interface(})(*Rows, error)

其中，参数query表示SQL语句，参数args表示query查询语句中的占位参数。

//查询多条数据示例
    func queryMultiRow() {
        
    	rows, err := DB.Query("select uid, name, phone from `user` where uid > ?", 0)
    	if err != nil {
        
    		log.Fatal(err)
    	}
    	//关闭rows，释放持有的数据库连接
    	defer rows.Close()
    	//循环读取结果集中的数据
    	var user User
    	for rows.Next() {
        
    		err := rows.Scan(&user.Uid, &user.Name, &user.Phone)
    		if err != nil {
        
    			fmt.Printf("scan failed, err: %v\n", err)
    			return
    		}
    		fmt.Printf("uid:%d name:%s phone:%s\n", user.Uid, user.Name, user.Phone)
    	}
    }

##### 3. 用Exec()方法插入数据 #####

EXec()方法的定义如下：

func (db *DB)Exec(query string,args ...interface())(Result,error)

Exec()方法用于执行一次命令（包括查询、删除、更新、插入等），返回的Result是对已执行的SQL命令的执行结果。其中，参数query表示SQL语句，参数args表示query参数中的占位参数。

//插入数据
    func insertRow() {
        
    	ret, err := DB.Exec("insert into `user`(name, phone) values (?, ?)", "王五", 12345676789)
    	if err != nil {
        
    		fmt.Printf("insert failed, err: %v\n", err)
    		return
    	}
    	uid, err := ret.LastInsertId() //新插入数据的uid
    	if err != nil {
        
    		fmt.Printf("get lastinsert ID failed, err: %v\n", err)
    		return
    	}
    	fmt.Printf("insert success, the id is %d.\n", uid)
    }

##### 4. 更新数据 #####

//更新数据
    func updateRow() {
        
    	ret, err := DB.Exec("update `user` set name = ? where uid = ?", "张三", 3)
    	if err != nil {
        
    		fmt.Printf("update failed, err: %v\n", err)
    		return
    	}
    	n, err := ret.RowsAffected() //影响行数
    	if err != nil {
        
    		fmt.Printf("get RowsAffected failed, err: %v\n", err)
    		return
    	}
    	fmt.Printf("update success, affected rows: %d\n", n)
    }

##### 5. 删除数据 #####

//删除数据
    func deleteRow() {
        
    	ret, err := DB.Exec("delete from `user` where uid = ?", 3)
    	if err != nil {
        
    		fmt.Printf("delete failed, err: %v\n", err)
    		return
    	}
    	n, err := ret.RowsAffected()
    	if err != nil {
        
    		fmt.Printf("get RowsAffected failed, err: %v\n", err)
    		return
    	}
    	fmt.Printf("delete success, affected rows: %d\n", n)
    }

> 1.  MySQL预处理

##### 1. 什么是预处理。 #####

要了解预处理，需要首先了解普通SQL语句的执行过程：

1.  客户端对SQL语句进行占位符替换，得到完整的SQL语句；
2.  客户端发送完整的SQL语句到MySQL服务器端；
3.  MySQL服务器端执行完整的SQL语句，并将结果返给客户端。

##### 2. 预处理执行过程。 #####

1.  把SQL语句分成两部分——命令部分与数据部分：
2.  把命令部分发送给MySQL服务器端，MySQL服务器端进行SQL预处理；
3.  把数据部分发送给MySQL服务器端，MySQL服务器端对SQL语句进行占位符替换；
4.  MySQL服务器端执行完整的SQL语句，并将结果返回给客户端。

##### 3. 为什么要预处理。 #####

预处理用于优化小ySQL服务器重复执行SQL语句的问题，可以提升服务器性能。提前让服务器编译，一次编译多次执行，可以节省后续编译的成本，避免SQL注入问题。

##### 4. Go语言中的MySQL预处理。 #####

在Go语言中，Prepare()方法会将SQL语句发送给MySQL服务器端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。Prepare()方法的定义如下：

func (db *DB)Prepare(query string)(*Stmt,error)

用Prepare()方法进行预处理查询的示例代码如下。

//预处理查询示例
    func prepareQuery() {
        
    	stmt, err := DB.Prepare("select uid, name, phone from `user` where uid > ?")
    	if err != nil {
        
    		fmt.Printf("prepare failed, err: %v\n", err)
    		return
    	}
    	defer stmt.Close()
    	rows, err := stmt.Query(0)
    	if err != nil {
        
    		fmt.Printf("query failed, err: %v\n", err)
    		return
    	}
    	defer rows.Close()
    	var u User
    	for rows.Next() {
        
    		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
    		if err != nil {
        
    			fmt.Printf("scan failed, err: %v\n", err)
    			return
    		}
    		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
    	}
    }

插入、更新和删除操作的预处理语句十分类似，这里以插入操作的预处理为例：

//预处理插入示例
    func prepareInsert() {
        
    	stmt, err := DB.Prepare("insert into `user`(name, phone) values (?, ?)")
    	if err != nil {
        
    		fmt.Printf("prepare failed, err: %v\n", err)
    		return
    	}
    	defer stmt.Close()
    	_, err = stmt.Exec("barry", 12345678900)
    	if err != nil {
        
    		fmt.Printf("insert failed, err: %v\n", err)
    		return
    	}
    	_, err = stmt.Exec("jim", 23451234567)
    	if err != nil {
        
    		fmt.Printf("insert failed, err: %v\n", err)
    		return
    	}
    	fmt.Println("insert success.")
    }

> 1.  Go实现MySQL事务

##### 事务相关方法 #####

Go语言使用以下3个方法实现MySOL中的事务操作。

*  Begin()方法用于开始事务，定义如下：

func (db *DB)Begin()(*Tx,error)

Commit()方法用于提交事务，定义如下：

func (tx *Tx)Commit()error

Rollback()方法用于回滚事务，定义如下：

func (tx *Tx)Rollback()error

下面的代码演示了一个简单的事务操作，该事务操作能够确保两次更新操作要么同时成功要么同时失败，不会存在中间状态：

//事务操作示例
    func transaction() {
        
    	tx, err := DB.Begin() //开启事务
    	if err != nil {
        
    		if tx != nil {
        
    			tx.Rollback() //回滚
    		}
    		fmt.Printf("begin trans failed, err: %v\n", err)
    	}
    	_, err = tx.Exec("update user set name = 'james' where uid = ?", 1)
    	if err != nil {
        
    		tx.Rollback() //回滚
    		fmt.Printf("exec sql1 failed, err: %v\n", err)
    		return
    	}
    	_, err = tx.Exec("update user set name = 'james' where uid = ?", 3)
    	if err != nil {
        
    		tx.Rollback() //回滚
    		fmt.Printf("exec sql2 failed, err: %v\n", err)
    		return
    	}
    	tx.Commit() //提交事务
    	fmt.Println("exec transaction success!")
    }

> 1.  SQL注入与防御

SQL注入是一种攻击手段，通过执行恶意SQL语句，进而将任意SQL代码插入数据库查询中，从而使攻击者完全控制Web应用程序后台的数据库服务器。

攻击者可以使用SQL注入漏洞绕过应用程序验证，比如绕过登录验证登绿、Wb身份验证和授权页面；也可以绕过网页，直接检索数据库的所有内容；还可以恶意修改、删除和增加数据库内容。

//SQL注入示例
    func sqlInject(name string) {
        
    	sqlStr := fmt.Sprintf("select uid, name, phone from user where name = '%s'", name)
    	fmt.Printf("SQL:%s\n", sqlStr)
    	ret, err := DB.Exec(sqlStr)
    	if err != nil {
        
    		fmt.Printf("update failed, err: %v\n", err)
    		return
    	}
    	n, err := ret.RowsAffected()
    	if err != nil {
        
    		fmt.Printf("get RowsAffected failed, err: %v\n", err)
    		return
    	}
    	fmt.Printf("get success, affected rows: %d\n", n)
    }
    
    //此时用sqlInject()方法输入字符串可以引发SQL注入问题
    sqlInject("xxx' or 1=1#")
    sqlInject("xxx' union select * from user #")
    sqlInject("xxx' and (select count(*) from user) < 10 #")

针对SQL注入问题，常见的防御措施有：

1.  禁止将变量直接写入SQL语句。
2.  对用户进行分级管理，严格控制用户的权限。
3.  对用户输入进行检查，确保数据输入的安全性。在具体检查输入或提交的变量时，对单引号、双引号、冒号等字符进行转换或者过滤。
4.  对数据库信息进行加密。