Go 语言之 SQLX 高级操作 sqlx.In
sqlx.In 介绍
sqlx is a package for Go which provides a set of extensions on top of the excellent built-in database/sql package.
Illustrated guide to SQLX：http://jmoiron.github.io/sqlx/
sqlx：https://github.com/jmoiron/sqlx
"In" Queries
Because database/sql does not inspect your query and it passes your arguments directly to the driver, it makes dealing with queries with IN clauses difficult:
SELECT * FROM users WHERE level IN (?);
When this gets prepared as a statement on the backend, the bindvar ? will only correspond to a single argument, but what is often desired is for that to be a variable number of arguments depending on the length of some slice, eg:
var levels = []int{4, 6, 7}rows, err := db.Query("SELECT * FROM users WHERE level IN (?);", levels)
This pattern is possible by first processing the query with sqlx.In:
var levels = []int{4, 6, 7}query, args, err := sqlx.In("SELECT * FROM users WHERE level IN (?);", levels) // sqlx.In returns queries with the `?` bindvar, we can rebind it for our backendquery = db.Rebind(query)rows, err := db.Query(query, args...)
What sqlx.In does is expand any bindvars in the query passed to it that correspond to a slice in the arguments to the length of that slice, and then append those slice elements to a new arglist. It does this with the ? bindvar only; you can use db.Rebind to get a query suitable for your backend.
普通批量插入数据，不使用 sqlx.In
package main
import (
"database/sql"
"fmt"
"strings"
"time"
_ "github.com/go-sql-driver/mysql" // 匿名导入 自动执行 init()
)
var db *sql.DB
func initMySQL() (err error) {
//DSN (Data Source Name)
dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test"
// 注意：要初始化全局的 db 对象，不要新声明一个 db 变量
db, err = sql.Open("mysql", dsn) // 只对格式进行校验，并不会真正连接数据库
if err != nil {
return err
}
// Ping 验证与数据库的连接是否仍处于活动状态，并在必要时建立连接。
err = db.Ping()
if err != nil {
fmt.Printf("connect to db failed, err: %vn", err)
return err
}
// 数值需要根据业务具体情况来确定
db.SetConnMaxLifetime(time.Second * 10) // 设置可以重用连接的最长时间
db.SetConnMaxIdleTime(time.Second * 5) // 设置连接可能处于空闲状态的最长时间
db.SetMaxOpenConns(200) // 设置与数据库的最大打开连接数
db.SetMaxIdleConns(10) // 设置空闲连接池中的最大连接数
return nil
}
type User struct {
Name string `db:"name"`
Age int `db:"age"`
}
// BatchInsertUsers 批量插入数据
func BatchInsertUsers(users []*User) error {
valueStrings := make([]string, 0, len(users)) // 占位符 slice
valueArgs := make([]interface{}, 0, len(users)*2) // 插入值 slice
for _, u := range users {
valueStrings = append(valueStrings, "(?, ?)")
valueArgs = append(valueArgs, u.Name, u.Age) // 占位符与插入值 一一对应
}
// 拼接完整的SQL语句
// Sprintf根据格式说明符进行格式化，并返回结果字符串。
// Join将其第一个参数的元素连接起来以创建单个字符串。分隔字符串sep放置在结果字符串的元素之间。
stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s", strings.Join(valueStrings, ","))
// Exec执行查询而不返回任何行。参数用于查询中的任何占位符参数。
result, err := db.Exec(stmt, valueArgs...)
if err != nil {
fmt.Printf("Error inserting user into database: %v n", err)
return err
}
var rows_affected int64
rows_affected, err = result.RowsAffected() // 返回受更新、插入或删除影响的行数。并非每个数据库或数据库驱动程序都支持此功能。
if err != nil {
fmt.Printf("返回受更新、插入或删除影响的行数 failed, err: %vn", err)
return err
}
fmt.Println("受更新、插入或删除影响的行数: ", rows_affected)
return nil
}
func main() {
if err := initMySQL(); err != nil {
fmt.Printf("connect to db failed, err: %vn", err)
}
// 检查完错误之后执行，确保 db 不为 nil
// Close() 用来释放数据库连接相关的资源
// Close 将关闭数据库并阻止启动新查询。关闭，然后等待服务器上已开始处理的所有查询完成。
defer db.Close()
fmt.Println("connect to database success")
// db.xx() 去使用数据库操作...
// 批量插入数据
users := []*User{
{Name: "刘备", Age: 25},
{Name: "关羽", Age: 30},
{Name: "张飞", Age: 28},
}
err := BatchInsertUsers(users)
if err != nil {
fmt.Printf("Failed to batch insert users: %v", err)
}
}
运行
Code/go/mysql_demo via
内容来源于网络如有侵权请私信删除
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
