Go 語(yǔ)言之 SQLX 高級(jí)操作 sqlx.In

這篇具有很好參考價(jià)值的文章主要介紹了Go 語(yǔ)言之 SQLX 高級(jí)操作 sqlx.In。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問(wèn)。

Go 語(yǔ)言之 SQLX 高級(jí)操作 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.

普通批量插入數(shù)據(jù)，不使用 sqlx.In

package main

import (
	"database/sql"
	"fmt"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql" // 匿名導(dǎo)入 自動(dòng)執(zhí)行 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 對(duì)象，不要新聲明一個(gè) db 變量
	db, err = sql.Open("mysql", dsn) // 只對(duì)格式進(jìn)行校驗(yàn)，并不會(huì)真正連接數(shù)據(jù)庫(kù)
	if err != nil {
		return err
	}

	// Ping 驗(yàn)證與數(shù)據(jù)庫(kù)的連接是否仍處于活動(dòng)狀態(tài)，并在必要時(shí)建立連接。
	err = db.Ping()
	if err != nil {
		fmt.Printf("connect to db failed, err: %v\n", err)
		return err
	}
	// 數(shù)值需要根據(jù)業(yè)務(wù)具體情況來(lái)確定
	db.SetConnMaxLifetime(time.Second * 10) // 設(shè)置可以重用連接的最長(zhǎng)時(shí)間
	db.SetConnMaxIdleTime(time.Second * 5)  // 設(shè)置連接可能處于空閑狀態(tài)的最長(zhǎng)時(shí)間
	db.SetMaxOpenConns(200)                 // 設(shè)置與數(shù)據(jù)庫(kù)的最大打開(kāi)連接數(shù)
	db.SetMaxIdleConns(10)                  //  設(shè)置空閑連接池中的最大連接數(shù)
	return nil
}

type User struct {
	Name string `db:"name"`
	Age  int    `db:"age"`
}

// BatchInsertUsers 批量插入數(shù)據(jù)
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) // 占位符與插入值 一一對(duì)應(yīng)
	}
	// 拼接完整的SQL語(yǔ)句
	// Sprintf根據(jù)格式說(shuō)明符進(jìn)行格式化，并返回結(jié)果字符串。
	// Join將其第一個(gè)參數(shù)的元素連接起來(lái)以創(chuàng)建單個(gè)字符串。分隔字符串sep放置在結(jié)果字符串的元素之間。
	stmt := fmt.Sprintf("INSERT INTO user (name, age) VALUES %s", strings.Join(valueStrings, ","))
	// Exec執(zhí)行查詢而不返回任何行。參數(shù)用于查詢中的任何占位符參數(shù)。
	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() // 返回受更新、插入或刪除影響的行數(shù)。并非每個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序都支持此功能。
	if err != nil {
		fmt.Printf("返回受更新、插入或刪除影響的行數(shù) failed, err: %v\n", err)
		return err
	}
	fmt.Println("受更新、插入或刪除影響的行數(shù): ", rows_affected)
	return nil
}

func main() {
	if err := initMySQL(); err != nil {
		fmt.Printf("connect to db failed, err: %v\n", err)
	}
	// 檢查完錯(cuò)誤之后執(zhí)行，確保 db 不為 nil
	// Close() 用來(lái)釋放數(shù)據(jù)庫(kù)連接相關(guān)的資源
	// Close 將關(guān)閉數(shù)據(jù)庫(kù)并阻止啟動(dòng)新查詢。關(guān)閉，然后等待服務(wù)器上已開(kāi)始處理的所有查詢完成。
	defer db.Close()

	fmt.Println("connect to database success")
	// db.xx() 去使用數(shù)據(jù)庫(kù)操作...

	// 批量插入數(shù)據(jù)
	users := []*User{
		{Name: "劉備", Age: 25},
		{Name: "關(guān)羽", Age: 30},
		{Name: "張飛", Age: 28},
	}
	err := BatchInsertUsers(users)
	if err != nil {
		fmt.Printf("Failed to batch insert users: %v", err)
	}
}

運(yùn)行

Code/go/mysql_demo via ?? v1.20.3 via ?? base 
? go run  main.go
connect to database success
受更新、插入或刪除影響的行數(shù):  3

Code/go/mysql_demo via ?? v1.20.3 via ?? base 
?

SQL 查詢結(jié)果

mysql> select * from user;  # 插入之前
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
+----+--------+------+
11 rows in set (0.00 sec)

mysql> select * from user;  # 插入之后
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
+----+--------+------+
14 rows in set (0.01 sec)

使用 sqlx.In 批量插入

package main

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

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 連接到數(shù)據(jù)庫(kù)并使用ping進(jìn)行驗(yàn)證。
	// 也可以使用 MustConnect MustConnect連接到數(shù)據(jù)庫(kù)，并在出現(xiàn)錯(cuò)誤時(shí)恐慌 panic。
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20) // 設(shè)置數(shù)據(jù)庫(kù)的最大打開(kāi)連接數(shù)。
	db.SetMaxIdleConns(10) // 設(shè)置空閑連接池中的最大連接數(shù)。
	return
}

type user struct {
	ID   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}

// BatchInsertUsersSqlxIn 使用sqlx.In幫我們拼接語(yǔ)句和參數(shù), 注意傳入的參數(shù)是[]interface{}
func BatchInsertUsersSqlxIn(users []interface{}) error {
	// In展開(kāi)args中的切片值，返回修改后的查詢字符串和一個(gè)可以由數(shù)據(jù)庫(kù)執(zhí)行的新的arg列表。
	// “查詢”應(yīng)該使用“?”“bindVar。返回值使用' ?“bindVar。
	query, args, _ := sqlx.In(
		"INSERT INTO user (name, age) VALUES (?), (?), (?)",
		users..., // 如果arg實(shí)現(xiàn)了 driver.Valuer, sqlx.In 會(huì)通過(guò)調(diào)用 Value()來(lái)展開(kāi)它
	)
	fmt.Println("query sql string: ", query) // 查看生成的querystring
	fmt.Println("args: ", args)              // 查看生成的args
	// Exec執(zhí)行查詢而不返回任何行。參數(shù)用于查詢中的任何占位符參數(shù)。
	result, err := db.Exec(query, args...)
	var rows_affected int64
	rows_affected, err = result.RowsAffected() // 返回受更新、插入或刪除影響的行數(shù)。并非每個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序都支持此功能。
	if err != nil {
		fmt.Printf("返回受更新、插入或刪除影響的行數(shù) failed, err: %v\n", err)
		return err
	}
	fmt.Println("受更新、插入或刪除影響的行數(shù): ", rows_affected)
	return nil
}


func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	fmt.Println("init DB succeeded")
	
	// 批量插入
	u1 := user{Name: "李白", Age: 16}
	u2 := user{Name: "杜甫", Age: 42}
	u3 := user{Name: "王維", Age: 29}
	users := []interface{}{u1, u2, u3}
	_ = BatchInsertUsersSqlxIn(users)
}

運(yùn)行

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
? go run main.go
init DB succeeded
query sql string:  INSERT INTO user (name, age) VALUES (?, ?), (?, ?), (?, ?)
args:  [李白 16 杜甫 42 王維 29]
受更新、插入或刪除影響的行數(shù):  3

Code/go/sqlx_demo via ?? v1.20.3 via ?? base

SQL 查詢結(jié)果

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
+----+--------+------+
14 rows in set (0.01 sec)

mysql> select * from user;  # 插入之后
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
| 18 | 李白   |   16 |
| 19 | 杜甫   |   42 |
| 20 | 王維   |   29 |
+----+--------+------+
17 rows in set (0.00 sec)

mysql>

使用 NamedExec 批量插入

package main

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

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 連接到數(shù)據(jù)庫(kù)并使用ping進(jìn)行驗(yàn)證。
	// 也可以使用 MustConnect MustConnect連接到數(shù)據(jù)庫(kù)，并在出現(xiàn)錯(cuò)誤時(shí)恐慌 panic。
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20) // 設(shè)置數(shù)據(jù)庫(kù)的最大打開(kāi)連接數(shù)。
	db.SetMaxIdleConns(10) // 設(shè)置空閑連接池中的最大連接數(shù)。
	return
}

type user struct {
	ID   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

func (u user) Value() (driver.Value, error) {
	return []interface{}{u.Name, u.Age}, nil
}


// BatchInsertUsersNamedExec NamedExec 批量插入
func BatchInsertUsersNamedExec(users []*user) error {
	// 任何命名的占位符參數(shù)都將被arg中的字段替換。
	result, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
	var rows_affected int64
	rows_affected, err = result.RowsAffected() // 返回受更新、插入或刪除影響的行數(shù)。并非每個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序都支持此功能。
	if err != nil {
		fmt.Printf("返回受更新、插入或刪除影響的行數(shù) failed, err: %v\n", err)
		return err
	}
	fmt.Println("BatchInsertUsersNamedExec 受插入影響的行數(shù): ", rows_affected)
	return nil
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	fmt.Println("init DB succeeded")
	
	// 批量插入
	u1 := user{Name: "褒姒", Age: 16}
	u2 := user{Name: "貂蟬", Age: 42}
	u3 := user{Name: "飛燕", Age: 29}
	
	// NamedExec
	users := []*user{&u1, &u2, &u3}
	_ = BatchInsertUsersNamedExec(users)
}

運(yùn)行

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
? go run main.go
init DB succeeded
BatchInsertUsersNamedExec 受插入影響的行數(shù):  3

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
?

SQL 查詢結(jié)果

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
| 18 | 李白   |   16 |
| 19 | 杜甫   |   42 |
| 20 | 王維   |   29 |
+----+--------+------+
17 rows in set (0.00 sec)

mysql> select * from user;  # 插入之后
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
| 18 | 李白   |   16 |
| 19 | 杜甫   |   42 |
| 20 | 王維   |   29 |
| 21 | 褒姒   |   16 |
| 22 | 貂蟬   |   42 |
| 23 | 飛燕   |   29 |
+----+--------+------+
20 rows in set (0.00 sec)

mysql>

sqlx 高級(jí)操作之 IN 查詢

查詢 ID 在指定集合中的數(shù)據(jù)

package main

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

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 連接到數(shù)據(jù)庫(kù)并使用ping進(jìn)行驗(yàn)證。
	// 也可以使用 MustConnect MustConnect連接到數(shù)據(jù)庫(kù)，并在出現(xiàn)錯(cuò)誤時(shí)恐慌 panic。
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20) // 設(shè)置數(shù)據(jù)庫(kù)的最大打開(kāi)連接數(shù)。
	db.SetMaxIdleConns(10) // 設(shè)置空閑連接池中的最大連接數(shù)。
	return
}

type user struct {
	ID   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

// QueryByIDs 查詢 ID 在指定集合中的數(shù)據(jù)
func QueryByIDs(ids []int) (users []user, err error) {
	// In 展開(kāi)args中的切片值，返回修改后的查詢字符串和一個(gè)可以由數(shù)據(jù)庫(kù)執(zhí)行的新的arg列表。
	// “查詢”應(yīng)該使用“?”“bindVar。返回值使用' ?“bindVar
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return nil, err
	}
	// Rebind 將查詢從 QUESTION 轉(zhuǎn)換為DB驅(qū)動(dòng)程序的 bindvar 類型。
	query = db.Rebind(query)
	// Select 使用此數(shù)據(jù)庫(kù)。任何占位符參數(shù)都將被提供的參數(shù)替換。
	err = db.Select(&users, query, args...)
	if err != nil {
		return nil, err
	}
	return users, nil
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	fmt.Println("init DB succeeded")
	
	// IN 查詢
	users, err := QueryByIDs([]int{1, 15, 21, 2})  // 默認(rèn)按照主鍵順序排列
	if err != nil {
		fmt.Printf("query error: %v\n", err)
		return
	}
	fmt.Printf("query successful result users %v\n", users)
	for _, user := range users {
		fmt.Printf("user: %#v\n", user)
	}
}

運(yùn)行

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
? go run main.go
init DB succeeded
query successful result users [{0 12 小喬} {0 22 小喬} {0 25 劉備} {0 16 褒姒}]
user: main.user{ID:0, Age:12, Name:"小喬"}
user: main.user{ID:0, Age:22, Name:"小喬"}
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
?

SQL 查詢結(jié)果

mysql> select * from user;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小喬   |   12 |
|  2 | 小喬   |   22 |
|  5 | 昭君   |   18 |
|  6 | 黛玉   |   16 |
|  8 | 李煜   |   26 |
|  9 | Alice  |   25 |
| 10 | Bob    |   30 |
| 11 | Carol  |   28 |
| 12 | Alice1 |   25 |
| 13 | Bob1   |   30 |
| 14 | Carol1 |   28 |
| 15 | 劉備   |   25 |
| 16 | 關(guān)羽   |   30 |
| 17 | 張飛   |   28 |
| 18 | 李白   |   16 |
| 19 | 杜甫   |   42 |
| 20 | 王維   |   29 |
| 21 | 褒姒   |   16 |
| 22 | 貂蟬   |   42 |
| 23 | 飛燕   |   29 |
+----+--------+------+
20 rows in set (0.00 sec)

mysql>

查詢結(jié)果默認(rèn)按照主鍵順序排列
自定義查詢結(jié)果順序
- 使用代碼排序
- 使用 MySQL 排序 FIND_IN_SET

sqlx 高級(jí)操作之 FIND_IN_SET

package main

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

var db *sqlx.DB

func initDB() (err error) {
	dsn := "root:12345678@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 連接到數(shù)據(jù)庫(kù)并使用ping進(jìn)行驗(yàn)證。
	// 也可以使用 MustConnect MustConnect連接到數(shù)據(jù)庫(kù)，并在出現(xiàn)錯(cuò)誤時(shí)恐慌 panic。
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB failed, err:%v\n", err)
		return
	}
	db.SetMaxOpenConns(20) // 設(shè)置數(shù)據(jù)庫(kù)的最大打開(kāi)連接數(shù)。
	db.SetMaxIdleConns(10) // 設(shè)置空閑連接池中的最大連接數(shù)。
	return
}

type user struct {
	ID   int    `db:"id"`
	Age  int    `db:"age"`
	Name string `db:"name"`
}

// QueryByIDs  查詢 ID 在指定集合中的數(shù)據(jù)
func QueryByIDs(ids []int) (users []user, err error) {
	// In 展開(kāi)args中的切片值，返回修改后的查詢字符串和一個(gè)可以由數(shù)據(jù)庫(kù)執(zhí)行的新的arg列表。
	// “查詢”應(yīng)該使用“?”“bindVar。返回值使用' ?“bindVar
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
	if err != nil {
		return nil, err
	}
	// Rebind 將查詢從 QUESTION 轉(zhuǎn)換為DB驅(qū)動(dòng)程序的 bindvar 類型。
	query = db.Rebind(query)
	// Select 使用此數(shù)據(jù)庫(kù)。任何占位符參數(shù)都將被提供的參數(shù)替換。
	err = db.Select(&users, query, args...)
	if err != nil {
		return nil, err
	}
	return users, nil
}

// QueryAndOrderByIDs 根據(jù) ID 在指定集合中和指定順序查詢
func QueryAndOrderByIDs(ids []int) (users []user, err error) {
	// 創(chuàng)建一個(gè)字符串切片，大小為ids的長(zhǎng)度
	strIDs := make([]string, 0, len(ids))
	// 將ids轉(zhuǎn)換為字符串類型
	for _, id := range ids {
		// Sprintf根據(jù)格式說(shuō)明符進(jìn)行格式化，并返回結(jié)果字符串。
		strIDs = append(strIDs, fmt.Sprintf("%d", id))
	}
	// In展開(kāi)args中的切片值，返回修改后的查詢字符串和一個(gè)可以由數(shù)據(jù)庫(kù)執(zhí)行的新的arg列表?！安樵儭睉?yīng)該使用“?”“bindVar。返回值使用' ?“bindVar。
	query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
	if err != nil {
		return
	}

	// Rebind 將查詢從QUESTION轉(zhuǎn)換為DB驅(qū)動(dòng)程序的bindvar類型。
	query = db.Rebind(query)
	// 執(zhí)行查詢 Select 使用此數(shù)據(jù)庫(kù)。任何占位符參數(shù)都將被提供的參數(shù)替換。
	err = db.Select(&users, query, args...)
	return
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init DB failed, err:%v\n", err)
		return
	}
	fmt.Println("init DB succeeded")
	
	// IN 查詢
	users, err := QueryByIDs([]int{1, 15, 21, 2})
	if err != nil {
		fmt.Printf("query error: %v\n", err)
		return
	}
	fmt.Printf("query successful result users %v\n", users)
	for _, user := range users {
		fmt.Printf("user: %#v\n", user)
	}

	fmt.Println("**************")
	// FIND_IN_SET
	users, err = QueryAndOrderByIDs([]int{1, 15, 21, 2})
	if err != nil {
		fmt.Printf("query error: %v\n", err)
		return
	}
	fmt.Printf("query successful result users %v\n", users)
	for _, user := range users {
		fmt.Printf("user: %#v\n", user)
	}
}

運(yùn)行

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
? go run main.go
init DB succeeded
query successful result users [{0 12 小喬} {0 22 小喬} {0 25 劉備} {0 16 褒姒}]
user: main.user{ID:0, Age:12, Name:"小喬"}
user: main.user{ID:0, Age:22, Name:"小喬"}
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}
**************
query successful result users [{0 12 小喬} {0 25 劉備} {0 16 褒姒} {0 22 小喬}]
user: main.user{ID:0, Age:12, Name:"小喬"}  # FIND_IN_SET 按照指定順序查詢
user: main.user{ID:0, Age:25, Name:"劉備"}
user: main.user{ID:0, Age:16, Name:"褒姒"}
user: main.user{ID:0, Age:22, Name:"小喬"}

Code/go/sqlx_demo via ?? v1.20.3 via ?? base 
?

注意：開(kāi)發(fā)中，使用代碼排序還是使用 SQL FIND_IN_SET 查詢排序，需要根據(jù)開(kāi)發(fā)實(shí)際情況來(lái)使用。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-482399.html

官方示例

package main

import (
    "database/sql"
    "fmt"
    "log"
    
    _ "github.com/lib/pq"
    "github.com/jmoiron/sqlx"
)

var schema = `
CREATE TABLE person (
    first_name text,
    last_name text,
    email text
);

CREATE TABLE place (
    country text,
    city text NULL,
    telcode integer
)`

type Person struct {
    FirstName string `db:"first_name"`
    LastName  string `db:"last_name"`
    Email     string
}

type Place struct {
    Country string
    City    sql.NullString
    TelCode int
}

func main() {
    // this Pings the database trying to connect
    // use sqlx.Open() for sql.Open() semantics
    db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")
    if err != nil {
        log.Fatalln(err)
    }

    // exec the schema or fail; multi-statement Exec behavior varies between
    // database drivers;  pq will exec them all, sqlite3 won't, ymmv
    db.MustExec(schema)
    
    tx := db.MustBegin()
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")
    tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")
    tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")
    tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")
    // Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person
    tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})
    tx.Commit()

    // Query the database, storing results in a []Person (wrapped in []interface{})
    people := []Person{}
    db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")
    jason, john := people[0], people[1]

    fmt.Printf("%#v\n%#v", jason, john)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}
    // Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}

    // You can also get a single result, a la QueryRow
    jason = Person{}
    err = db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")
    fmt.Printf("%#v\n", jason)
    // Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}

    // if you have null fields and use SELECT *, you must use sql.Null* in your struct
    places := []Place{}
    err = db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")
    if err != nil {
        fmt.Println(err)
        return
    }
    usa, singsing, honkers := places[0], places[1], places[2]
    
    fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}

    // Loop through rows using only one struct
    place := Place{}
    rows, err := db.Queryx("SELECT * FROM place")
    for rows.Next() {
        err := rows.StructScan(&place)
        if err != nil {
            log.Fatalln(err)
        } 
        fmt.Printf("%#v\n", place)
    }
    // Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}
    // Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}
    // Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}

    // Named queries, using `:name` as the bindvar.  Automatic bindvar support
    // which takes into account the dbtype based on the driverName on sqlx.Open/Connect
    _, err = db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`, 
        map[string]interface{}{
            "first": "Bin",
            "last": "Smuth",
            "email": "bensmith@allblacks.nz",
    })

    // Selects Mr. Smith from the database
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})

    // Named queries can also use structs.  Their bind names follow the same rules
    // as the name -> db mapping, so struct fields are lowercased and the `db` tag
    // is taken into consideration.
    rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)
    
    
    // batch insert
    
    // batch insert with structs
    personStructs := []Person{
        {FirstName: "Ardie", LastName: "Savea", Email: "asavea@ab.co.nz"},
        {FirstName: "Sonny Bill", LastName: "Williams", Email: "sbw@ab.co.nz"},
        {FirstName: "Ngani", LastName: "Laumape", Email: "nlaumape@ab.co.nz"},
    }

    _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personStructs)

    // batch insert with maps
    personMaps := []map[string]interface{}{
        {"first_name": "Ardie", "last_name": "Savea", "email": "asavea@ab.co.nz"},
        {"first_name": "Sonny Bill", "last_name": "Williams", "email": "sbw@ab.co.nz"},
        {"first_name": "Ngani", "last_name": "Laumape", "email": "nlaumape@ab.co.nz"},
    }

    _, err = db.NamedExec(`INSERT INTO person (first_name, last_name, email)
        VALUES (:first_name, :last_name, :email)`, personMaps)
}

更多示例請(qǐng)參考官方文檔：https://github.com/jmoiron/sqlx/blob/master/sqlx_test.go

到了這里，關(guān)于Go 語(yǔ)言之 SQLX 高級(jí)操作 sqlx.In的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！

国产无码综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

Toy模板網(wǎng)

Go 語(yǔ)言之 SQLX 高級(jí)操作 sqlx.In