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: %v\n", 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: %v\n", err)
		return err
	}
	fmt.Println("受更新、插入或刪除影響的行數: ", rows_affected)
	return nil
}

func main() {
	if err := initMySQL(); err != nil {
		fmt.Printf("connect to db failed, err: %v\n", 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