sqlx操作MySQL實戰及其ORM原理

2022 年 5 月 6 日
筆記
Go, MySQL, sqlx

sqlx是Golang中的一個知名三方庫，其為Go標準庫database/sql提供了一組擴展支援。使用它可以方便的在數據行與Golang的結構體、映射和切片之間進行轉換，從這個角度可以說它是一個ORM框架；它還封裝了一系列地常用SQL操作方法，讓我們用起來更爽。

sqlx實戰

這裡以操作MySQL的增刪改查為例。

準備工作

先要準備一個MySQL，這裡通過docker快速啟動一個MySQL 5.7。

docker run -d --name mysql1 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

在MySQL中創建一個名為test的資料庫：

CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

資料庫中創建一個名為Person的資料庫表：

CREATE TABLE test.Person (
	Id integer auto_increment NOT NULL,
	Name VARCHAR(30) NULL,
	City VARCHAR(50) NULL,
	AddTime DATETIME NOT NULL,
	UpdateTime DATETIME NOT NULL,
	CONSTRAINT Person_PK PRIMARY KEY (Id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

然後創建一個Go項目，安裝sqlx：

go get github.com/jmoiron/sqlx

因為操作的是MySQL，還需要安裝MySQL的驅動：

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

編寫程式碼

添加引用

添加sqlx和mysql驅動的引用：

import (
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

MySQL的驅動是隱式註冊的，並不會在接下來的程式中直接調用，所以這裡加了下劃線。

創建連接

操作資料庫前需要先創建一個連接:

	db, err := sqlx.Connect("mysql", "root:123456@tcp(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=true&loc=Local")
	if err != nil {
		log.Println("資料庫連接失敗")
	}

這個連接中指定了程式要用MySQL驅動，以及MySQL的連接地址、用戶名和密碼、資料庫名稱、字元編碼方式；這裡還有兩個參數parseTime和loc，parseTime的作用是讓MySQL中時間類型的值可以映射到Golang中的time.Time類型，loc的作用是設置time.Time的值的時區為當前系統時區，不使用這個參數的話保存到的資料庫的就是UTC時間，會和北京時間差8個小時。

增刪改查

sqlx擴展了DB和Tx，繼承了它們原有的方法，並擴展了一些方法，這裡主要看下這些擴展的方法。

增加

通用佔位符的方式：

insertResult := db.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Zhang San", "Beijing", time.Now(), time.Now())
lastInsertId, _ := insertResult.LastInsertId()
log.Println("Insert Id is ", lastInsertId)

這個表的主鍵使用了自增的方式，可以通過返回值的LastInsertId方法獲取。

命名參數的方式：

insertPerson := &Person{
		Name:       "Li Si",
		City:       "Shanghai",
		AddTime:    time.Now(),
		UpdateTime: time.Now(),
	}
	insertPersonResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPerson)

命名參數的方式是sqlx擴展的，這個方式就是常說的ORM。這裡需要注意給struct欄位添加上db標籤：

type Person struct {
	Id         int       `db:"Id"`
	Name       string    `db:"Name"`
	City       string    `db:"City"`
	AddTime    time.Time `db:"AddTime"`
	UpdateTime time.Time `db:"UpdateTime"`
}

struct中的欄位名稱不必和資料庫欄位相同，只需要通過db標籤映射正確就行。注意SQL語句中使用的命名參數需要是db標籤中的名字。

除了可以映射struct，sqlx還支援map，請看下面這個示例：

insertMap := map[string]interface{}{
		"n": "Wang Wu",
		"c": "HongKong",
		"a": time.Now(),
		"u": time.Now(),
	}
	insertMapResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:n, :c, :a, :u)", insertMap)

再來看看批增加的方式：

insertPersonArray := []Person{
		{Name: "BOSIMA", City: "Wu Han", AddTime: time.Now(), UpdateTime: time.Now()},
		{Name: "BOSSMA", City: "Xi An", AddTime: time.Now(), UpdateTime: time.Now()},
		{Name: "BOMA", City: "Cheng Du", AddTime: time.Now(), UpdateTime: time.Now()},
	}
	insertPersonArrayResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPersonArray)
	if err != nil {
		log.Println(err)
		return
	}
	insertPersonArrayId, _ := insertPersonArrayResult.LastInsertId()
	log.Println("InsertPersonArray Id is ", insertPersonArrayId)

這裡還是採用命名參數的方式，參數傳遞一個struct數組或者切片就可以了。這個執行結果中也可以獲取到最後插入數據的自增Id，不過實測返回的是本次插入的第一條的Id，這個有點彆扭，但是考慮到增加多條只獲取一個Id的場景似乎沒有，所以也不用多慮。

除了使用struct數組或切片，也可以使用map數組或切片，這裡就不貼出來了，有興趣的可以去看文末給出的Demo鏈接。

刪除

刪除也可以使用通用佔位符和命名參數的方式，並且會返回本次執行受影響的行數，某些情況下可以使用這個數字判斷SQL實際有沒有執行成功。

deleteResult := db.MustExec("Delete from Person where Id=?", 1)
log.Println(deleteResult.RowsAffected())

deleteMapResult, err := db.NamedExec("Delete from Person where Id=:Id",
                                     map[string]interface{}{"Id": 1})
if err != nil {
  log.Println(err)
  return
}
log.Println(deleteMapResult.RowsAffected())

修改

Sqlx對修改的支援和刪除差不多：

updateResult := db.MustExec("Update Person set City=?, UpdateTime=? where Id=?", "Shanghai", time.Now(), 1)
log.Println(updateResult.RowsAffected())

updateMapResult, err := db.NamedExec("Update Person set City=:City, UpdateTime=:UpdateTime where Id=:Id",
                                     map[string]interface{}{"City": "Chong Qing", "UpdateTime": time.Now(), "Id": 1})
if err != nil {
  log.Println(err)
}
log.Println(updateMapResult.RowsAffected())

查詢

Sqlx對查詢的支援比較多。

使用Get方法查詢一條：

getPerson := &Person{}
db.Get(getPerson, "select * from Person where Name=?", "Zhang San")

使用Select方法查詢多條：

selectPersons := []Person{}
db.Select(&selectPersons, "select * from Person where Name=?", "Zhang San")

只查詢部分欄位：

getId := new(int64)
db.Get(getId, "select Id from Person where Name=?", "Zhang San")

selectTowFieldSlice := []Person{}
db.Select(&selectTowFieldSlice, "select Id,Name from Person where Name=?", "Zhang San")

selectNameSlice := []string{}
db.Select(&selectNameSlice, "select Name from Person where Name=?", "Zhang San")

從上可以看出如果只查詢部分欄位，還可以繼續使用struct；特別的只查詢一個欄位時，使用基本數據類型就可以了。

除了這些高層次的抽象方法，Sqlx也對更低層次的查詢方法進行了擴展：

查詢單行：

row = db.QueryRowx("select * from Person where Name=?", "Zhang San")
	if row.Err() == sql.ErrNoRows {
		log.Println("Not found Zhang San")
	} else {
		queryPerson := &Person{}
		err = row.StructScan(queryPerson)
		if err != nil {
			log.Println(err)
			return
		}
		log.Println("QueryRowx-StructScan:", queryPerson.City)
	}

查詢多行：

	rows, err := db.Queryx("select * from Person where Name=?", "Zhang San")
	if err != nil {
		log.Println(err)
		return
	}
	for rows.Next() {
		rowSlice, err := rows.SliceScan()
		if err != nil {
			log.Println(err)
			return
		}
		log.Println("Queryx-SliceScan:", string(rowSlice[2].([]byte)))
	}

命名參數Query：

rows, err = db.NamedQuery("select * from Person where Name=:n", map[string]interface{}{"n": "Zhang San"})

查詢出數據行後，這裡有多種映射方法：StructScan、SliceScan和MapScan，分別對應映射後的不同數據結構。

預處理語句

對於重複使用的SQL語句，可以採用預處理的方式，減少SQL解析的次數，減少網路通訊量，從而提高SQL操作的吞吐量。

下面的程式碼展示了sqlx中如何使用stmt查詢數據，分別採用了命名參數和通用佔位符兩種傳參方式。

bosima := Person{}
bossma := Person{}

nstmt, err := db.PrepareNamed("SELECT * FROM Person WHERE Name = :n")
if err != nil {
  log.Println(err)
  return
}
err = nstmt.Get(&bossma, map[string]interface{}{"n": "BOSSMA"})
if err != nil {
  log.Println(err)
  return
}
log.Println("NamedStmt-Get1:", bossma.City)
err = nstmt.Get(&bosima, map[string]interface{}{"n": "BOSIMA"})
if err != nil {
  log.Println(err)
  return
}
log.Println("NamedStmt-Get2:", bosima.City)

stmt, err := db.Preparex("SELECT * FROM Person WHERE Name=?")
if err != nil {
  log.Println(err)
  return
}
err = stmt.Get(&bosima, "BOSIMA")
if err != nil {
  log.Println(err)
  return
}
log.Println("Stmt-Get1:", bosima.City)
err = stmt.Get(&bossma, "BOSSMA")
if err != nil {
  log.Println(err)
  return
}
log.Println("Stmt-Get2:", bossma.City)

對於上文增刪改查的方法，sqlx都有相應的擴展方法。與上文不同的是，需要先使用SQL模版創建一個stmt實例，然後執行相關SQL操作時，不再需要傳遞SQL語句。

資料庫事務

為了在事務中執行sqlx擴展的增刪改查方法，sqlx必然也對資料庫事務做一些必要的擴展支援。

tx, err = db.Beginx()
	if err != nil {
		log.Println(err)
		return
	}
	tx.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Zhang San", "Beijing", time.Now(), time.Now())
	tx.MustExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES (?, ?, ?, ?)", "Li Si Hai", "Dong Bei", time.Now(), time.Now())
	err = tx.Commit()
	if err != nil {
		log.Println(err)
		return
	}
	log.Println("tx-Beginx is successful")

上面這段程式碼就是一個簡單的sqlx資料庫事務示例，先通過db.Beginx開啟事務，然後執行SQL語句，最後提交事務。

如果想要更改默認的資料庫隔離級別，可以使用另一個擴展方法：

tx, err = db.BeginTxx(context.Background(), &sql.TxOptions{Isolation: sql.LevelRepeatableRead})

sqlx幹了什麼

通過上邊的實戰，基本上就可以使用sqlx進行開發了。為了更好的使用sqlx，我們可以再了解下sqlx是怎麼做到上邊這些擴展的。

Go的標準庫中沒有提供任何具體資料庫的驅動，只是通過database/sql庫定義了操作資料庫的通用介面。sqlx中也沒有包含具體資料庫的驅動，它只是封裝了常用SQL的操作方法，讓我們的SQL寫起來更爽。

MustXXX

sqlx提供兩個幾個MustXXX方法。

Must方法是為了簡化錯誤處理而出現的，當開發者確定SQL操作不會返回錯誤的時候就可以使用Must方法，但是如果真的出現了未知錯誤的時候，這個方法內部會觸發panic，開發者需要有一個兜底的方案來處理這個panic，比如使用recover。

這裡是MustExec的源碼：

func MustExec(e Execer, query string, args ...interface{}) sql.Result {
	res, err := e.Exec(query, args...)
	if err != nil {
		panic(err)
	}
	return res
}

NamedXXX

對於需要傳遞SQL參數的方法， sqlx都擴展了命名參數的傳參方式。這讓我們可以在更高的抽象層次處理資料庫操作，而不必關心資料庫操作的細節。

這種方法的內部會解析我們的SQL語句，然後從傳遞的struct、map或者slice中提取命名參數對應的值，然後形成新的SQL語句和參數集合，再交給底層database/sql的方法去執行。

這裡摘抄一些程式碼：

func NamedExec(e Ext, query string, arg interface{}) (sql.Result, error) {
	q, args, err := bindNamedMapper(BindType(e.DriverName()), query, arg, mapperFor(e))
	if err != nil {
		return nil, err
	}
	return e.Exec(q, args...)
}

NamedExec 內部調用了 bindNamedMapper，這個方法就是用於提取參數值的。其內部分別對Map、Slice和Struct有不同的處理。

func bindNamedMapper(bindType int, query string, arg interface{}, m *reflectx.Mapper) (string, []interface{}, error) {
	...
	switch {
	case k == reflect.Map && t.Key().Kind() == reflect.String:
		...
		return bindMap(bindType, query, m)
	case k == reflect.Array || k == reflect.Slice:
		return bindArray(bindType, query, arg, m)
	default:
		return bindStruct(bindType, query, arg, m)
	}
}

以批量插入為例，我們的程式碼是這樣寫的：

insertPersonArray := []Person{
		{Name: "BOSIMA", City: "Wu Han", AddTime: time.Now(), UpdateTime: time.Now()},
		{Name: "BOSSMA", City: "Xi An", AddTime: time.Now(), UpdateTime: time.Now()},
		{Name: "BOMA", City: "Cheng Du", AddTime: time.Now(), UpdateTime: time.Now()},
	}
	insertPersonArrayResult, err := db.NamedExec("INSERT INTO Person (Name, City, AddTime, UpdateTime) VALUES(:Name, :City, :AddTime, :UpdateTime)", insertPersonArray)

經過bindNamedMapper處理後SQL語句和參數是這樣的：

這裡使用了反射，有些人可能會擔心性能的問題，對於這個問題的常見處理方式就是快取起來，sqlx也是這樣做的。

XXXScan

這些Scan方法讓數據行到對象的映射更為方便，sqlx提供了StructScan、SliceScan和MapScan，看名字就可以知道它們映射的數據結構。而且在這些映射能力的基礎上，sqlx提供了更為抽象的Get和Select方法。

這些Scan內部還是調用了database/sql的Row.Scan方法。

以StructScan為例，其使用方法為：

queryPerson := &Person{}
err = row.StructScan(queryPerson)

經過sqlx處理後，調用Row.Scan的參數是：

以上就是本文的主要內容，如有錯漏，歡迎指正。

老規矩，Demo程式已經上傳到Github，歡迎訪問：//github.com/bosima/go-demo/tree/main/sqlx-mysql

收穫更多架構知識，請關注微信公眾號螢火架構。原創內容，轉載請註明出處。

Tags: Go MySQL sqlx

sqlx操作MySQL實戰及其ORM原理

sqlx實戰

準備工作

編寫程式碼

添加引用

創建連接

增刪改查

預處理語句

資料庫事務

sqlx幹了什麼

MustXXX

NamedXXX

XXXScan

VirMach 便宜 VPS

QNews

sqlx操作MySQL實戰及其ORM原理

sqlx實戰

準備工作

編寫程式碼

添加引用

創建連接

增刪改查

預處理語句

資料庫事務

sqlx幹了什麼

MustXXX

NamedXXX

XXXScan

分享此文：

Related Posts

一文掌握GitHub Actions基本概念與配置

PHP設計模式——門面模式

深圳女子考過鹵鵝證領1700元補貼上熱搜：官方神回應

網約車掉頭撞勞斯萊斯維修百萬 前者回應全責：竟毫無壓力

VirMach 便宜 VPS

QNews

熱門搜尋

網約車掉頭撞勞斯萊斯維修百萬前者回應全責：竟毫無壓力