Go SQL

y_f_6

已于 2025-04-09 21:45:41 修改

阅读量553

点赞数 19

文章标签： golang sql 开发语言

于 2025-03-09 11:17:35 首次发布

本文链接：https://blog.youkuaiyun.com/y_f_6/article/details/146032699

版权

配置SQL驱动

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

加载失败可以尝试

go env -w GOPROXY=https://goproxy.io,direct
go get github.com/go-sql-driver/mysql

在 Go 中，database/sql 包默认使用连接池来管理数据库连接，这意味着每次您执行数据库操作时，可能会使用不同的连接。
这种行为在大多数情况下是高效的，但在某些情况下可能会导致问题，尤其是在需要将多个操作绑定到单个连接时

连接数据库

不直接使用驱动包，使代码和驱动实现松耦合(或完全解耦)，这样直接更换底层驱动就可以切换到不同数据库，或只需很少的代码改动
_ 匿名加载驱动包
驱动名称，用来在database/sql中进行自身注册的字符串
sql.Open 不会建立与数据库的任何连接，不会验证驱动程序的连接参数，只准备数据库抽象后备用，在第一次需要时建立实际连接
db.Ping 立即检查数据库是否可用和可访问

import (
	// 数据库操作包
	"database/sql"
	"fmt"
	// 导入驱动
	_ "github.com/go-sql-driver/mysql"
	"log"
)

func main() {
	//驱动名称
	//用户名:密码@(IP:端口)/数据库名
	db, err := sql.Open("mysql",
		"root:123456@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	//检测数据库连接
	err = db.Ping()
	if err != nil {
		fmt.Println("错误：", err)
	}
}

SQL注入

通过输入恶意SQL代码，进行非预期的数据库操作

$username = $_GET['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
// 输入 admin' OR 1=1 --
SELECT * FROM users WHERE username = 'admin' OR 1=1 --';

预处理SQL语句

一种提前编译的SQL语句，使用占位符代替实际值，在编译时会检查语法错误，执行时将输入值传入
占位符的使用不能用于表名，列名等
在数据库级别，一条预处理语句绑定到单一数据库连接

发送预处理语句：
客户端将带有占位符的 SQL 语句发送到数据库服务器进行预处理
服务器预处理语句：
数据库服务器解析 SQL 语句，生成执行计划，并分配一个唯一的语句 ID
服务器将这个语句 ID 返回给客户端
执行预处理语句：
客户端通过发送语句 ID 和参数来执行预处理语句
EXECUTE <statement_id> (1)
关闭预处理语句：
客户端可以显式关闭预处理语句，释放服务器上的资源
DEALLOCATE <statement_id>

在 Go 中，连接不会直接向 database/sql 包的用户公开，不需要针对连接做语句预处理
工作原理：

预处理阶段：
调用 DB.Prepare 或 Tx.Prepare 方法时，database/sql 包会在连接池中获取一个可用的连接，并在该连接上发送预处理语句的请求。数据库会解析这条语句，并生成一个执行计划，然后返回一个预处理语句对象（Stmt）
执行阶段：
使用 Stmt 对象执行查询（如 Stmt.Query 或 Stmt.Exec）时，database/sql 包会尝试使用创建该 Stmt 对象时所使用的连接。如果该连接不可用（例如，连接已被关闭或正忙于执行其他操作），则会从连接池中获取另一个可用的连接，并在新的连接上重新预处理该语句
连接池管理：
database/sql 包管理着一个连接池，当连接空闲时会将其返回到池中供其他操作使用。当连接池中的连接被耗尽时，包会根据配置自动创建新的连接，直到达到最大打开连接数的限制

问题：

重新预处理：
由于 database/sql 包在执行预处理语句时会尝试使用原始连接，但如果原始连接不可用，则需要在新的连接上重新预处理语句。这可能导致在高并发场景下频繁地重新预处理语句，增加了数据库的负担
语句泄漏：
如果预处理语句没有被正确关闭（例如，没有调用 Stmt.Close 方法），则可能导致预处理语句在数据库服务器上累积，占用资源，甚至可能达到服务器端预处理语句数量的限制，从而引发错误

当使用 db.Query(sql, param1, param2)时，database/sql包会自动执行：

预处理语句：
在连接池中获取一个连接，并在该连接上预处理 SQL 语句，生成一个 Stmt 对象
执行语句：
使用该 Stmt 对象和提供的参数执行查询
关闭语句：
在执行完成后，自动关闭 Stmt 对象，释放相关的资源

当无法使用预处理语句时，可以通过手动组装SQL语句，绕开预处理机制，直接执行组装好的SQL语句

	db, err := sql.Open("mysql",
		"root:123456@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	
	rows, err := queryWithoutPrepare(db, "2", "李四")
	if err != nil {
		fmt.Println("查询失败")
	}
	// 下一行有数据返回true,否则返回false
	for rows.Next() {
		var id int
		var username, balance string
		// 将行内值赋给变量
		rows.Scan(&id, &username, &balance)
		fmt.Println(id, " ", username, " ", balance)
	}
	if err := rows.Err(); err != nil {
		fmt.Println("循环过程中错误", err)
	}
	defer rows.Close()

func queryWithoutPrepare(db *sql.DB, param1, param2 string) (*sql.Rows, error) {
	// 组装 SQL 语句
	query := fmt.Sprintf("SELECT * FROM account WHERE id = '%s' AND username = '%s'", param1, param2)

	// 执行查询
	return db.Query(query)
}

在这里插入图片描述
事务中的预处理语句会独占式地绑定到事务所使用连接：

事务中的操作直接映射到单一连接：
与db操作不同，tx操作不涉及连接池中的多个连接，所有命令(包括预处理语句的创建和执行)，都在同一连接上执行
预处理语句不能单独使用：
事务一旦结束，预处理语句就不再有效，尝试在事务外使用会报错
外部预处理语句不能在事务中使用：
db上创建预处理语句绑定到不同连接，不能直接在事务中使用

在事务中使用外部处理语句：

创建对于事务的新语句：
Tx.Stmt接收一个在事务外部已经预处理过的语句，并返回一个新的Stmt对象，该对象绑定到当前事务连接
重新预处理行为：
每次执行Tx.Stmt创建语句时，都会在事务的连接上重新处理该语句，意味每次执行都会产生一个新的预处理语句

	db, err := sql.Open("mysql",
		"root:123456@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	externalStmt, err := db.Prepare("SELECT * FROM account WHERE id = ? AND username = ?")
	if err != nil {
		fmt.Println("预处理失败")
	}
	performTransactionWithExternalStmt(db, externalStmt, 4, "周六")

func performTransactionWithExternalStmt(db *sql.DB, externalStmt *sql.Stmt, param1 int, param2 string) error {
	// 开始一个事务
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	// 使用 Tx.Stmt() 将外部预处理语句绑定到事务
	txStmt := tx.Stmt(externalStmt)
	// 使用 txStmt 执行查询
	rows, err := txStmt.Query(param1, param2)
	if err != nil {
		tx.Rollback()
		return err
	}
	defer rows.Close()
	for rows.Next() {
		var id int
		var username, balance string
		// 将行内值赋给变量
		rows.Scan(&id, &username, &balance)
		fmt.Println(id, " ", username, " ", balance)
	}
	if err := rows.Err(); err != nil {
		fmt.Println("循环过程中错误", err)
	}
	// 提交事务
	return tx.Commit()
}

在这里插入图片描述

操作数据库

Exec 一般处理没有返回行的语句
可以忽略执行结果
Query 不可以忽略执行结果
返回的sql.Rows，将保留数据库连接，直到关闭，连接无法释放可能导致连接池耗尽
Query 执行非查询语句，不会返回结果集，但 sql.Rows 对象仍然会占用数据库连接。由于没有结果集可以读取，sql.Rows 对象不会被自动关闭，连接将被占用，直到垃圾回收器介入

增加

	// 预处理
	// default 默认值
	// ? 占位符
	stmt, err := db.Prepare("insert into account values (default,?,?)")
	defer stmt.Close()
	if err != nil {
		fmt.Println("处理失败")
	}
	// 参数和占位符对应
	r, err := stmt.Exec("周六", 99.99)
	if err != nil {
		fmt.Println("插入失败")
	}
	// 事务影响行数，返回修改的行数
	count, err := r.RowsAffected()
	if err != nil {
		fmt.Println("结果获取失败")
	}
	fmt.Println(count)
	// 获取新增时主键的值
	id, err := r.LastInsertId()
	if err != nil {
		fmt.Println("主键值获取失败")
	}
	fmt.Println(id)

在这里插入图片描述

修改

在golang中认为修改前后的值相同，RowsAffected()返回0

	stmt, err := db.Prepare("update account set username=?,balance=? where id =?")
	defer stmt.Close()
	if err != nil {
		fmt.Println("处理失败")
	}
	// 参数和占位符对应
	r, err := stmt.Exec("宋七", 66.66, 3)
	if err != nil {
		fmt.Println("修改失败")
	}
	count, err := r.RowsAffected()
	if err != nil {
		fmt.Println("结果获取失败")
	}
	fmt.Println(count)

在这里插入图片描述
sql.Result 提供对语句元数据的访问

删除

在golang中删除的数据不存在，RowsAffected()返回0

	stmt, err := db.Prepare("delete from account where id =?")
	defer stmt.Close()
	if err != nil {
		fmt.Println("处理失败")
	}
	// 参数和占位符对应
	r, err := stmt.Exec(3)
	if err != nil {
		fmt.Println("删除失败")
	}
	count, err := r.RowsAffected()
	if err != nil {
		fmt.Println("结果获取失败")
	}
	fmt.Println(count)

在这里插入图片描述

查询

函数名字包含 Query, 向数据库发出查询问题，并且即使它为空，也将返回一组行
所有内容都是强类型的,，需要创建正确类型的变量，并将指针传递给对应值

注意检查循环过程中可能会出现的错误
打开结果集(rows)时，底层连接就会进入繁忙，不能用于任何其他查询，它在连接池中不可使用
当使用rows.Next迭代所有行，当读取到最后一行时会遇到EOF的内部错误，并自动调用rows.Close
当特定要求提前退出循环，需要手动调用rows.Close
rows.Close可以多次调用，但需要先检查是否有错误，否则会有runtime panic
在循环中重复查询和使用结果集，需要显式调用rows.Close，不使用defer

Scan 会根据目标变量的类型自动尝试进行数据类型转换

在数据库中的列是字符串类型，但实际上存储的是数字，可以直接将结果扫描到整数变量中，Scan 会自动调用strconv.ParseInt 或类似函数进行转换
确保数据库中的数据类型与目标变量类型兼容，否则会导致运行时错误
数据库中的NULL列，需要使用指针或aql.NULL类型来处理

	stmt, err := db.Prepare("select * from account")
	defer stmt.Close()
	if err != nil {
		fmt.Println("处理失败")
	}
	// 获得结果集
	rows, err := stmt.Query()
	if err != nil {
		fmt.Println("查询失败")
	}
	// 下一行有数据返回true,否则返回false
	for rows.Next() {
		var id int
		var username, balance string
		// 将行内值赋给变量
		rows.Scan(&id, &username, &balance)
		fmt.Println(id, " ", username, " ", balance)
	}
	if err := rows.Err(); err != nil {
		fmt.Println("循环过程中错误",err)
	}
	defer rows.Close()

在这里插入图片描述
单条记录查询

	var id int
	var username, balance string
	err = db.QueryRow("select * from account where id = ?", 2).Scan(&id, &username, &balance)
	if err != nil {
		fmt.Println("查询失败")
	}
	fmt.Println(id, " ", username, " ", balance)

在这里插入图片描述

	var id int
	var username, balance string
	stmt, err := db.Prepare("select * from account where id = ?")
	defer stmt.Close()
	if err != nil {
		fmt.Println("处理失败")
	}
	// 获得结果集
	err = stmt.QueryRow(4).Scan(&id, &username, &balance)
	if err != nil {
		fmt.Println("查询失败")
	}
	fmt.Println(id, " ", username, " ", balance)

在这里插入图片描述

事务

事务具有ACID特性
一个保留与数据存储区连接的对象
连接和会话是同一个概念
执行到目前为止所看到的所有操作，但可以保证在同一连接上执行
tx 从连接池中获得连接，并保留，仅用于当前事务
db.Begin 开启事务
tx.Rollback 回滚
tx.Commit 提交

	tx, err := db.Begin()
	if err != nil {
		fmt.Println("事务开启失败")
		return
	}
	result, err := tx.Exec("update account set balance = balance - 33.33 where id = ?", 2)
	if err != nil {
		tx.Rollback()
	}
	err = tx.Commit()
	if err != nil {
		fmt.Println("提交失败")
	}
	fmt.Println(result.LastInsertId())
	fmt.Println(result.RowsAffected())

在这里插入图片描述

	tx, err := db.Begin()
	if err != nil {
		fmt.Println("事务开启失败")
		return
	}
	// 创建预处理语句绑定到指定事务
	stmt, err := tx.Prepare("insert into account values (default,?,?)")
	defer stmt.Close()
	if err != nil {
		fmt.Println("预处理失败")
	}
	result, err := stmt.Exec("宋七", 99.99)
	if err != nil {
		tx.Rollback()
		fmt.Println("执行失败")
	}
	tx.Commit()
	fmt.Println(result.LastInsertId())
	fmt.Println(result.RowsAffected())

在这里插入图片描述
避免混淆使用事务函数和SQL关键字
在go打开的事务中使用BEGIN和COMMIT关键字，会导致

事务未正确结束：Go的tx对象无法正确跟踪事务状态，导致事务未正确提交或回滚，数据库的状态可能与代表它的 Go 变量的状态不同步
连接未及时释放：事务对象可能保持打开状态，导致连接池中的连接无法及时返回到池中
事务范围混乱：此时在事务内部不是单个连接上执行查询， Go 已经创建了多个不可见的连接，并且某些语句不是该事务的一部分

	tx, _ := db.Begin()
	// 错误示例：在事务中使用 SQL 的 BEGIN
	_, err = tx.Exec("BEGIN")
	if err != nil {
		tx.Rollback()
		fmt.Println("回滚1")
	}
	// 执行操作
	res, err := tx.Exec("UPDATE accounts SET balance = balance - 33.33 WHERE id = 1")
	if err != nil {
		tx.Rollback()
		fmt.Println("回滚2")
	}
	// 提交事务
	err = tx.Commit()
	if err != nil {
		fmt.Println("提交失败")
	}
	// panic: runtime error: invalid memory
	// address or nil pointer dereference
	fmt.Println(res.RowsAffected())
	fmt.Println(res.LastInsertId())
	//回滚2
	//提交失败

在这里插入图片描述
需要绑定到单个连接，即使不需要事务，也要使用tx

创建临时表（仅对一个连接可见）

	// 临时表在sql中是会话级别的对象，只创建在绑定的会话中可见，在会话结束的时候自动销毁
	// 在事务之外创建临时表，Go的默认行为可能会在不同的连接上执行，导致临时表无法被访问
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("事务打开失败")
	}
	// 创建临时表
	_, err = tx.Exec("CREATE TEMPORARY TABLE temp_table (id INT)")
	if err != nil {
		tx.Rollback()
		fmt.Println("创建失败")
	}
	// 使用临时表
	_, err = tx.Exec("INSERT INTO temp_table (id) VALUES (1)")
	if err != nil {
		tx.Rollback()
		fmt.Println("插入失败")
	}
	// 查询临时表
	rows, err := tx.Query("SELECT id FROM temp_table")
	if err != nil {
		tx.Rollback()
		fmt.Println("查询失败")
	}
	defer rows.Close()
	// 处理查询结果
	for rows.Next() {
		var id int
		if err := rows.Scan(&id); err != nil {
			tx.Rollback()
			fmt.Println("结果获取失败")
		}
		fmt.Println(id)
	}
	err = tx.Commit()
	if err != nil {
		fmt.Println("提交失败")
	}

在这里插入图片描述

设置变量

	tx, err := db.Begin()
	if err != nil {
		fmt.Println("事务打开失败")
	}
	// 设置会话变量
	_, err = tx.Exec("SET @var := '钱五'")
	if err != nil {
		tx.Rollback()
		fmt.Println("变量设置失败")
	}
	// 使用会话变量
	_, err = tx.Exec("INSERT INTO account (username) VALUES (@var)")
	if err != nil {
		tx.Rollback()
		fmt.Println("无法使用变量")
	}
	err = tx.Commit()
	if err != nil {
		fmt.Println("事务提交失败")
	}

在这里插入图片描述

更改连接选项

	// 某些数据库连接选项(字符集，超时时间等)是会话级别的
	// 对于字符集，数据库连接的字符集不正确，可能会导致字符编码问题
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("事务打开失败")
	}
	// 更改连接选项，设置字符集
	_, err = tx.Exec("SET NAMES 'gbk'")
	if err != nil {
		tx.Rollback()
		fmt.Println("更改失败")
	}
	// 执行需要特定字符集的操作
	_, err = tx.Exec("INSERT INTO account (username) VALUES ('二娃')")
	if err != nil {
		tx.Rollback()
		fmt.Println("操作失败")
	}
	err = tx.Commit()
	if err != nil {
		fmt.Println("提交失败")
	}

在这里插入图片描述
在连接池中设置字符集，可以在创建时设置

db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:3306)/%s?charset=utf8mb4", dbUser, dbPass, dbHost, dbName))

处理NULL

错误的假设某列不会为NULL，但数据中出现NULL值，程序可能会崩溃

sql.NullBool：布尔类型空值
sql.NullFloat64：浮点类型空值
sql.NullInt64：整型类型空值
sql.NullString：字符串类型空值

func main() {
	db, err := sql.Open("mysql",
		"root:123456@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	queryWithNullableColumns(db)
}

func queryWithNullableColumns(db *sql.DB) {
	rows, err := db.Query("SELECT username, balance FROM account")
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var username sql.NullString
		var balance sql.NullFloat64
		err := rows.Scan(&username, &balance)
		if err != nil {
			panic(err)
		}
		// 判断是否为空值
		if username.Valid {
			fmt.Printf("username: %s\n", username.String)
		} else {
			fmt.Println("username: NULL")
		}
		if balance.Valid {
			fmt.Printf("balance: %f\n", balance.Float64)
		} else {
			fmt.Println("balance: NULL")
		}
	}
}

在这里插入图片描述

database/sql 包只提供了几种常见的可空类型，需要处理其他类型的空值，需要自定义类型
coalesce可以将NULL值转换为默认值
在预处理语句中同样使用

rows, err := db.Prepare(`
        SELECT
            COALESCE(username, '') as username,
            COALESCE(balance, 0) as balance
        FROM account
        where id = ?
    `)

func queryWithCoalesce(db *sql.DB) {
	rows, err := db.Query(`
        SELECT
            COALESCE(username, '') as username,
            COALESCE(balance, 0) as balance
        FROM account
    `)
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var username sql.NullString
		var balance sql.NullFloat64
		err := rows.Scan(&username, &balance)
		if err != nil {
			panic(err)
		}

		fmt.Printf("username: %s, balance: %f\n", username.String, balance.Float64)
	}
}

在这里插入图片描述
自定义类型
需要同时实现sql.Scanner和driver.Value接口

type NullUint64 struct {
	Uint64 uint64
	Valid  bool
}

// 实现 driver.Value接口，以便能够将 NullUint64 用于 SQL 查询
func (n *NullUint64) Value() (driver.Value, error) {
	if !n.Valid {
		return nil, nil
	}
	return n.Uint64, nil
}

// 实现sql.Scanner接口，能够从数据库中扫描数据
func (n *NullUint64) Scan(value interface{}) error {
	if value == nil {
		n.Valid = false
		return nil
	}
	// 假设数据库返回的是 uint64 类型
	uintValue, ok := value.(uint64)
	if !ok {
		return fmt.Errorf("invalid type for NullUint64: %T", value)
	}
	n.Uint64 = uintValue
	n.Valid = true
	return nil
}

// 在扫描时使用 NullUint64
func queryWithCustomNullableType(db *sql.DB) {
	rows, err := db.Query("SELECT count, username FROM account")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var username sql.NullString
		// 在数据库中对应BIGINT UNSIGNED
		var count NullUint64
		err := rows.Scan(&count, &username)
		if err != nil {
			panic(err)
		}
		if username.Valid {
			fmt.Printf("username: %s\n", username.String)
		} else {
			fmt.Println("username: NULL")
		}
		if count.Valid {
			fmt.Printf("count: %d\n", count.Uint64)
		} else {
			fmt.Println("count: NULL")
		}
	}
}

在这里插入图片描述

未知列

在Scan 函数要求传递正确数量的目标变量
当不明确查询返回的列数，可以动态处理使用Columns查询列名列表
使用正确的数值将一个切片传递到Scan中

rows.Columns：
获取查询结果的列名列表，返回一个字符串切片，其中包含每个列的名称
创建interface{}类型切片，用于存储扫描结果，初始化为sql.RawBytes类型指针，处理任意类型数据，并且可以处理NULL值
将结果扫描到vals切片中

func main() {
	// 连接到数据库
	db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/test")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()
	// 执行查询
	// SHOW PROCESSLIST 显示当前服务器中运行的所有线程信息
	rows, err := db.Query("SHOW PROCESSLIST")
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	// 获取列名列表
	cols, err := rows.Columns()
	if err != nil {
		log.Fatal(err)
	}
	// 创建一个切片来存储每个列的值
	vals := make([]interface{}, len(cols))
	for i := range cols {
		// sql.RawBytes 是一个 []byte 的别名，用于存储原始的二进制数据
		// new(sql.RawBytes) 会分配一个新的 sql.RawBytes 指针，并将其存储到 vals 切片中
		vals[i] = new(sql.RawBytes)
	}
	// 遍历查询结果
	for rows.Next() {
		// 扫描结果到 vals 切片
		// 将获取到的结果通过每个对应的指针进行存储
		// 将 vals 切片展开为多个参数传递给 Scan 方法
		err := rows.Scan(vals...)
		if err != nil {
			log.Fatal(err)
		}
		// 处理每个列的值
		for i, col := range cols {
			rawVal := vals[i].(*sql.RawBytes)
			if rawVal == nil {
				fmt.Printf("%s: NULL\n", col)
			} else {
				fmt.Printf("%s: %s\n", col, *rawVal)
			}
		}
		fmt.Println("-------------------")
	}
	// 检查错误
	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}
}