go Sql 初探

zhaoyihuaer發表於2020-12-27
GoSQL
系列文章：
Go Modules 命令 go Sql 初探

Go 的 sql 包裡只包含介面，各個資料庫系統的驅動可以在官方維護的 Wiki 裡找到 github.com/golang/go/wiki/SQLDrive 。
知名資料庫各自都有幾個驅動可供選擇，推薦以下驅動：

MySQL/MariaDB —— github.com/go-sql-driver/mysql/Postgres SQL —— github.com/lib/pqSQLite3 —— github.com/mattn/go-sqlite3Oracle —— github.com/mattn/go-oci8SQL Server —— github.com/denisenkom/go-mssqldb

sql.DB 結構是 database/sql 包封裝的一個資料庫操作物件，包含了運算元據庫的基本方法。大部分時間我們運算元據庫都使用它。
可以把 sql.DB 當做是連線池，它內部會自動維護 SQL 連線的關閉和建立

// 設定最大連線數
db.SetMaxOpenConns(100)

// 設定最大空閒連線數
db.SetMaxIdleConns(25)

// 設定每個連結的過期時間
db.SetConnMaxLifetime(5 * time.Minute)

全稱為 Data Source Name，表示資料庫連線源，用於定義資料庫的連線資訊，不同資料庫的 DSN 格式不同，MySQL 的 DSN 格式如下：

// [使用者名稱[:密碼]@][協議(資料庫伺服器地址)]]/資料庫名稱?引數列表
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

為了更加直觀，我們可以使用 mysql.Config 來建立連線資訊：

// 設定資料庫連線資訊
config := mysql.Config{
    User:                 "homestead",
    Passwd:               "secret",
    Addr:                 "127.0.0.1:33060",
    Net:                  "tcp",
    DBName:               "goblog",
    AllowNativePasswords: true,
}

// fmt.Println("conn: ", config.FormatDSN())

一般而言，我們使用 Open() 方法便可初始化並返回一個 *sql.DB 例項，如下：

func Open(driverName, dataSourceName string) (*DB, error)

使用 Open() 方法只要傳入驅動名稱及對應的 DSN 便可，使用很簡單，也很通用，當需要連線不同資料庫時，只需要修改驅動名與 DSN 即可。配合 mysql.Config 使用：

db, err = sql.Open("mysql", config.FormatDSN())

使用 Open() 時需要知道的是 —— 我們只是做好連線的準備，並未真是連線到資料庫上。因未發生連線，所以即使配置資訊有誤，也不會報錯。所以一般我們在使用的時候，會搭配 Ping() 進行測試;

err = db.Ping()
checkError(err)

Ping() 會與資料庫伺服器發生連線，如果連線資訊有錯誤，err 就會有值，否為 nil。
DB 的連線都是被設計來當作長連線使用的，所以不該頻繁的 Open、Close。Open 取得的 db 例項，要重複利用，不應該去重複生成。

sql.DB.Prepare() 方法會返回一個 sql.Stmt 物件，與 Stmt 相關的方法如下：

stmt.Exec()
stmt.Query()
stmt.QueryRow()
stmt.Close()

請注意與 sql.DB 下的方法區分。

做單獨的語句查詢時，謹記呼叫 defer stmt.Close() 來關閉 SQL 連線。
使用 Prepare 語句會傳送兩次請求到資料庫伺服器上，第一次是呼叫 Prepare() 語句時，第二次是呼叫以上提到的四個 Stmt 方法時：

cdn.learnku.com/uploads/images/202...

Prepare 語句可有效防範 SQL 注入攻擊。
以下常見的 DB 查詢方法中，呼叫在傳參一個以上時，底層皆會使用 Prepare 來傳送請求;

func (db *DB) Exec(query string, args ...interface{}) (Result, error)
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryRow(query string, args ...interface{}) *Row

另外，還有 翻譯：Go 資料庫技巧：重複利用 Prepare 後的 stmt 來提高 MySQL 的執行效

一般增加、刪除、更新，或者修改表結構，都使用 sql.DB 中的 Exec() 方法來處理。
語法如下：

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

單引數為純文字模式，不使用 Prepare，只傳送一條 SQL 查詢：

db.Exec("DELETE FROM articles WHERE id = " + strconv.FormatInt(a.ID, 10))

多引數為 Prepare 模式，底層使用 Prepare 語句，會傳送兩條 SQL 查詢：

query := "UPDATE articles SET title = ?, body = ? WHERE id = ?"
rs, err := db.Exec(query, title, body, id)

第二個及以上的引數為 SQL 佔位符對應的資料。
Exec() 方法會返回一個 sql.Result 型別的例項。

Result 的定義如下，包含兩個方法：

type Result interface {
    LastInsertId() (int64, error)
    RowsAffected() (int64, error)
}

LastInsertId() 方法只用在 INSERT 語句且資料表有自增 ID 時才有返回自增 ID 值，否則返回 0。
RowsAffected() 表示影響的資料錶行數，我們以此來判斷 SQL 語句是否執行成功。
SQL 語法正確的情況下 RowsAffected() 為 0 ，則表示 SQL 執行成功了，但是資料庫裡的資料沒有任何變更。例如說我們的資料庫中並沒有 ID 為 6 的資料，這時候執行以下語句：

DELETE FROM articles WHERE id=6

就會出現 SQL 執行成功了，但是資料未更改的情況。

一般使用 sql.DB 中的 Query() 來查詢得到多條資料。語法如下：

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

如下獲取所有文章的例子：：

rows, err := db.Query("SELECT * from articles")

Query() 方法返回一個 sql.Rows 結構體，代表一個查詢結果集。
你可能發現了，Query 和 Exec 都可以執行 SQL 語句，那他們的區別是什麼呢？
Exec 只會返回最後插入 ID 和影響行數，而 Query 會返回資料表裡的內容（結果集）。
或者可以這麼記：
Query 中文譯為 查詢，而 Exec 譯為 執行。想查詢資料，使用 Query。想執行命令，使用 Exec。

sql.Rows 所包含的方法如下：

func (rs *Rows) Close() error                            //關閉結果集
func (rs *Rows) ColumnTypes() ([]*ColumnType, error)    //返回資料表的列型別
func (rs *Rows) Columns() ([]string, error)             //返回資料表列的名稱
func (rs *Rows) Err() error                      // 錯誤集
func (rs *Rows) Next() bool                      // 遊標，下一行
func (rs *Rows) Scan(dest ...interface{}) error  // 掃描結構體
func (rs *Rows) NextResultSet() bool

結果集在檢出完 err 以後，遍歷資料之前，應呼叫 defer rows.Close() 來關閉 SQL 連線。
一般我們會使用 rows.Next() 來遍歷資料，如：

var articles []Article
//2. 迴圈讀取結果
for rows.Next() {
    var article Article
    // 2.1 掃碼每一行的結果並賦值到一個 article 物件中
    err := rows.Scan(&article.ID, &article.Title, &article.Body)
    checkError(err)
    // 2.2 將 article 追加到 articles 的這個陣列中
    articles = append(articles, article)
}
// 2.3 檢測迴圈時是否發生錯誤
err = rows.Err()
checkError(err)

迴圈完畢需檢測是否發生錯誤。
rows.Scan() 引數的順序很重要，需要和查詢的結果的 column 對應。

SELECT * from articles

而我們的 articles 的表結構為：

CREATE TABLE `articles` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

查詢到的每一行的 column 順序是 id, title, body，因此 rows.Scan 也需要按照此順序不然會造成資料讀取的錯位。

如果是讀取一行資料，可以使用 QueryRow()，語法定義如下：

func (db *DB) QueryRow(query string, args ...interface{}) *Row

返回的是一個 sql.Row 物件，與其相關的呼叫有：

func (r *Row) Scan(dest ...interface{}) error

sql.Row 沒有 Close 方法，當我們呼叫 Scan() 時就會自動關閉 SQL 連線。所以為了防止忘記關閉而浪費資源，一般需要養成連著呼叫 Scan() 習慣：

article := Article{}
query := "SELECT * FROM articles WHERE id = ?"
err := db.QueryRow(query, id).Scan(&article.ID, &article.Title, &article.Body)

以上我們從資料庫中讀取對應 ID 的一條資料，並立刻呼叫 Scan() 讀取資料到 article 變數裡。
當出現請求結果不止一條資料的情況，QueryRow() 會只使用第一條資料。

三個常用的 SQL 請求方法都有其支援上下文的版本，如下：

func (db *DB) Exec(query string, args ...interface{}) (Result, error)
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryRow(query string, args ...interface{}) *Row
func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

支援 Context 上下文的方法傳參標準庫 context 裡的 context.Context 物件例項。
在一些特殊場景裡，我們需要 SQL 請求在執行還未完成時，我們可以取消他們（cancel），或者為請求設定最長執行時間（timeout），就會用到這些方法。
在這裡你只需要記住有這些方法即可，手動管理上下文 SQL 請求使用場景較少，篇幅考慮這裡不做贅述。
另外需要知道的是，所有的請求方法底層都是用其上下文版本的方法呼叫，且傳入預設的上下文，例如 Exec() 的原始碼：

func (db *DB) Exec(query string, args ...interface{}) (Result, error) {
    return db.ExecContext(context.Background(), query, args...)
}

底層呼叫的是 ExecContext() 方法。context.Background() 是預設的上下文，這是一個空的 context ，我們無法對其進行取消、賦值、設定 deadline 等操作。

如果沒有開啟事務，當其中某個語句執行錯誤，則前面已經執行的 SQL 語句無法回滾。
對於一些要求比較嚴格的業務邏輯來說，如付款、轉賬等，應該在同一個事務中提交多條 SQL 語句，避免發生執行出錯無法回滾事務的情況。
使用以下可以開啟事務：

func (db *DB) Begin() (*Tx, error)
func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)

Begin() 和 BeginTxt() 方法返回一個 sql.Tx 結構體，他支援以上我們提到過的幾種查詢方法：

func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
func (tx *Tx) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryRow(query string, args ...interface{}) *Row
func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

// 預編譯 Prepare
func (tx *Tx) Stmt(stmt *Stmt) *Stmt
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt
func (tx *Tx) Prepare(query string) (*Stmt, error)
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error)

使用這同一個 sql.Tx 對資料庫進行操作，就會在同一個事務中提交。
當使用 sql.Tx 的操作方式運算元據後，需要使用 sql.Tx 的 Commit() 方法提交事務，如果出錯，則可以使用 sql.Tx 中的 Rollback() 方法回滾事務，保持資料的一致性，下面是這兩個方法的定義：

func (tx *Tx) Commit() error
func (tx *Tx) Rollback() error

下面是個簡單的示例：

func (s Service) DoSomething() (err error) {
    // 1. 建立事務
    tx, err := s.db.Begin()
    if err != nil {
        return
    }
    // 2. 如果請求失敗，就回滾所有 SQL 操作，否則提交
    //    defer 會在當前方法的最後執行
    defer func() {
        if err != nil {
            tx.Rollback()
            return err
        }
        err = tx.Commit()
    }()

    // 3. 執行各種請求
    if _, err = tx.Exec(...); err != nil {
        return err
    }
    if _, err = tx.Exec(...); err != nil {
        return err
    }
    // ...
    return nil
}

需要注意的是，所有 SQL 操作都必須使用 tx 來操作，才能支援事務，如果中間使用 db.Exec() 那這條語句是無法回滾的。

這些是 Go 開發者需要掌握的基礎知識，是以後使用 ORM 或者其他高階 SQL 工具的基石

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章