Go--连接mysql，增删改查

下载驱动库，下为官方推荐的，还有其他ORM库，暂时没涉及，故本文不做阐述

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

 

一、连接

1.1 直接连接，查询单行

package main

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

func OneLineQuery() {
    //拼接mysql数据库信息
    dataSourceName := user + ":" + password + "@tcp(" + host + ":" + port + ")/" + database + "?charset=utf8&parseTime=True"
    //连接mysql
    conn, err := sql.Open("mysql", dataSourceName)
    if err != nil {
        fmt.Println("Failed to connect to database", err)
        return
    }
    //记得结束后关闭连接
    defer conn.Close()

    //单行查询，不管语句查询多少数据，只返回第一行数据
    row := conn.QueryRow("select psa_id,name from table_name;")
    //提取数据
    var psaId int //根据表字段类型定义变量类型，须一致
    var name string
    err = row.Scan(&psaId, &name) //将查询结果映射到相应的变量或结构体字段，注意顺序
    if err != nil {
        fmt.Println("Failure to interpolate data", err)
    }
    fmt.Println(psaId, name) //输出查询数据
}

func main() {
    OneLineQuery()
}

 

1.2 使用连接池

1.2.1 连接池

通过内存中维护一组数据库连接，以便在需要时快速分配和释放连接。

连接池可以有效地管理和复用数据库连接，避免频繁的打开和关闭数据库连接，提高性能并减少资源浪费，故它是以长链接的形式存在内存中。

 

1.2.2 创建调用

用sql.Open函数创建连接池，可是此时只是初始化了连接池，并没有创建任何连接。连接创建都是惰性的，只有当你真正使用到连接的时候，连接池才会创建连接。

函数调用：

• db.Ping() ：调用完毕后会马上把连接返回给连接池。通常使用db.Ping()方法初始化，调用了Ping之后，连接池一定会初始化一个数据库连接
• db.Exec() ：调用完毕后会马上把连接返回给连接池，但是它返回的Result对象还保留着连接的引用，当后面的代码需要处理结果集的时候连接将会被重用。
• db.Query() ：调用完毕后会将连接传递给sql.Rows类型，当然后者迭代完毕或者显示的调用.Clonse()方法后，连接将会被释放回到连接池。
• db.QueryRow()：调用完毕后会将连接传递给sql.Row类型，当.Scan()方法调用之后把连接释放回到连接池。
• db.Begin() ：调用完毕后将连接传递给sql.Tx类型对象，当.Commit()或.Rollback()方法调用后释放连接。

 

1.2.3 连接失败

若连接失败，database/sql会自动尝试重连10次，仍然无法重连的情况下会自动从连接池再获取一个或者新建另外一个。

 

1.2.4 相关配置

先了解下mysql的相关配置

字段	简述	说明
max_connections	同时处理的最大连接数	通常默认是200，如果服务器达到了这个限制，新的连接请求将被拒绝，直到当前连接数小于阈值为此，可根据业务需求、服务性能进行修改
extra_max_connections	超过最大连接数后最多可接受的连接数	指定了服务器可同时处理的最大连接数。如果服务去繁忙，且连接数已达到了max_connections的限制，这个配置允许服务器接收更多的请求，但可能影响性能，非必要不使用
wait_timeout	非交互式连接(程序之间调用，如一边是tomcat web服务器，一边是数据库服务器)最大空闲时间	如果一段时间内，连接没有发送任何请求，则mysql会断开这个连接，通常默认是8小时（28800秒）
interactive_timeout	交互式连接(本地打开mysql的客户端，在黑窗口下进行各种sql操作)最大空闲时间	默认也是8小时（28800秒）

查看及修改

#查询
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'interactive_timeout';

#修改，对已经存在的连接不生效，重新建立连接后生效
SET GLOBAL max_connections = 200;
SET GLOBAL wait_timeout=120;
SET GLOBAL interactive_timeout=120;

#永久修改
#修改mysql配置文件(通常是my.cnf或my.ini)，重启MySQL服务生效
[mysqld]
max_connections = 200
wait_timeout=28800
interactive_timeout=28800

go连接池相关配置：

• SetMaxOpenConns：最大打开连接数，默认为0，不限制，应小于数据库max_connections的值
• SetMaxIdleConns：最大空闲连接数，应小于SetMaxOpenConns的值
• SetConnMaxLifetime：连接的最大生命周期，默认为0不限制。指定了从建立连接开始到连接关闭之间的总时间(活动+空闲时间)，应小于数据库本身的连接超时时间
• SetConnMaxIdleTime：连接的最大空闲时间，指定了连接在没有任何数据传输活动时的最长闲置时间。如果超过这个时间，连接将被关闭

示例：

// DB 设置全局变量
var DB *sql.DB

// ConnectionPool 连接池配置
func ConnectionPool() {
    //拼接mysql数据库信息
    dataSourceName := user + ":" + password + "@tcp(" + host + ":" + port + ")/" + database + "?charset=utf8&parseTime=True"
    //连接mysql
    db, err := sql.Open("mysql", dataSourceName)
    if err != nil {
        fmt.Println("Failed to connect to database", err)
        return
    }
    //连接池配置
    db.SetMaxOpenConns(100)                 //最大打开连接数，0为不限制
    db.SetMaxIdleConns(10)                  //最大闲置连接数
    db.SetConnMaxIdleTime(time.Second * 10) //最大空闲时间
    //db.SetConnMaxLifetime(time.Minute * 30) //连接超时时间，应小于数据库本身的连接超时时间
    //defer db.Close()    //后面代码引用，这里暂不关闭
    //赋值
    DB = db
}

 

二、增删改查

以下的操作都使用1.2中的DB变量

2.1 查询

单行查询如1.1，下为多行查询

在遍历时一行行的输出查询数据，在函数中引用

func MultilineQuery() {
    rows, err := DB.Query("select psa_id,name from table_name limit 5;")
    if err != nil {
        fmt.Println("Inquiry Failure", err)
    }
　　 //关闭查询结果集，避免造成资源泄漏
    defer rows.Close()
    //遍历查询结果
    for rows.Next() {
        var psaId int
        var name string
        err = rows.Scan(&psaId, &name) //将查询结果映射到变量中
        if err != nil {
            fmt.Println("Scan data mapping failure", err)
        }
        //输出数据
        fmt.Printf("ID: %d, Name: %s\n", psaId, name)
    }

    //程序调用完毕后关闭连接池
    defer DB.Close()
}

先定义一个结构体，将查询结果映射进结构体内，作为函数返回值，这样查询结构可在查询函数外引用

// 定义结构体，结构类型跟查询字段类型一致，注意顺序
type str struct {
    psaId int
    name  string
}

func MultilineQuery() []str {
    rows, err := DB.Query("select psa_id,name from table_name limit 5;")
    if err != nil {
        fmt.Println("Inquiry Failure", err)
    }
    defer rows.Close()

    var str1 []str //定义结构体切片，存储结果数据
    //遍历返回数据，将数据循环赋值给变量
    for rows.Next() {
        var str2 str                             //定义结构体变量，用于接收数据
        err = rows.Scan(&str2.psaId, &str2.name) //将数据赋值到对应变量中，下一次赋值将覆盖前一次的数据；注意变量顺序需一致
        if err != nil {
            fmt.Println("Scan data mapping failure", err)
        }
        str1 = append(str1, str2) //将每次循环的值添加到切片中，切片存储最终结果数据
    }

    //程序调用完毕后关闭连接池
    defer DB.Close()

    //返回值
    return str1
}

// ExportData 引用str1
func ExportData(str1 []str) {
    //遍历
    for _, v := range str1 {
        //输出数据
        fmt.Println(v.psaId, v.name)
        fmt.Println(v)
    }
}

func main() {
    //OneLineQuery()
    ConnectionPool()
    str1 := MultilineQuery()
    ExportData(str1)
}

 

2.2 增删改

插入数据

func InsertData() {
    // 插入数据的SQL语句
    //与表结构一致，不然可能会报错，自增主键字段可忽略不写，将自动生成
    //但clickhouse的自增主键不可忽略，可用"INSERT INTO table_name (id, column2) VALUES (generateUUIDv4(), ?)"表示
    sqlStatement := "INSERT INTO table_name (column1, column2) VALUES (?, ?)"

    // 创建事务
    tx, err := DB.Begin()
    if err != nil {
        fmt.Println("事务创建失败：", err)
        return
    }

    // 执行插入语句
    _, err = tx.Exec(sqlStatement, "value1", "value2") //value输入具体的值
    if err != nil {
        fmt.Println("插入数据失败：", err)
        tx.Rollback() // 回滚事务
        return
    }

    // 提交事务
    err = tx.Commit()
    if err != nil {
        fmt.Println("提交事务失败：", err)
        return
    }

    fmt.Println("数据插入成功！")
}

删除数据

// 执行删除操作
    _, err = DB.Exec("DELETE FROM table_name WHERE condition")
    if err != nil {
        // 处理删除失败的情况
    }

更改数据

    // 执行更新语句
    err = DB.QueryRow("UPDATE table_name SET column1 = ? WHERE id = ?", "new_value", 1).Scan()
    if err != nil {
        fmt.Println("更新数据失败:", err)
        return
    }

 

三、补充

3.1 查询时接受传参

1.使用字符串拼接：可以将传递的参数直接拼接到 SQL 查询语句中。这种方式简单直接，但存在 SQL 注入的安全风险，因此需要谨慎处理。

import "database/sql"

func queryData(db *sql.DB, param string) {
    query := "SELECT * FROM table WHERE column = '" + param + "'"
    rows, err := db.Query(query)
    // 处理查询结果...
}

 

2.使用参数占位符：可以使用预定义的参数占位符，然后将参数作为参数值传递给查询函数。这种方式可以避免 SQL 注入的风险。

import "database/sql"

func queryData(db *sql.DB, param string) {
    query := "SELECT * FROM table WHERE column = ?"
    rows, err := db.Query(query, param)
    // 处理查询结果...
}

或者直接在sql后面加上参数，注意顺序

import "database/sql"

func queryData(db *sql.DB) {
    var column1 = "test"
    var column2 = "test2"
    rows, err := db.Query("SELECT * FROM table WHERE column = ? and name = ?",column1,column2)
    // 处理查询结果...
}

 

3.使用命名参数：可以使用命名参数，将参数和对应的名称一起传递给查询函数。这种方式更加直观和可读性好。

import "database/sql"

func queryData(db *sql.DB, param string) {
    query := "SELECT * FROM table WHERE column = :param"
    rows, err := db.NamedQuery(query, map[string]interface{}{"param": param})
    // 处理查询结果...
}