Golang SQL 操作初體驗

這是一個建立於 的文章，其中的資訊可能已經有所發展或是發生改變。

簡介

Golang 提供了 database/sql 包用於對 SQL 的資料庫的訪問, 在這個包中, 最重要的自然就是 sql.DB 了.
對於 sql.DB, 我們需要強調的是, 它並不代表一個資料庫連接, 它是一個已存在的資料庫的抽象提供者. sql.DB 為我們提供了兩個重要的功能:

• sql.DB 通過資料庫驅動為我們管理底層資料庫連接的開啟和關閉操作.

• sql.DB 為我們管理資料庫串連池

有一點需要注意的是, 正因為 sql.DB 是以串連池的方式管理資料庫串連, 我們每次進行資料庫操作時, 都需要從串連池中取出一個串連, 當操作任務完成時, 我們需要將此串連返回到串連池中, 因此如果我們沒有正確地將串連返回給串連池, 那麼會造成 db.SQL 開啟過多的資料庫連接, 使資料庫連接資源耗盡.

MySQL 資料庫的基本操作

資料庫驅動的匯入

有過資料庫開發經驗的朋友就知道了, 我們需要藉助於一個資料庫驅動才能和具體的資料庫進行串連. 這在 Golang 中也不例外. 例如以 MySQL 資料庫為例:

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

需要注意的是, 通常來說, 我們不應該直接使用驅動所提供的方法, 而是應該使用 sql.DB, 因此在匯入 mysql 驅動時, 我們使用了匿名匯入的方式(在包路徑前添加 _).
當匯入了一個資料庫驅動後, 此驅動會自行初始化並註冊自己到 Golang 的 database/sql 上下文中, 因此我們就可以通過 database/sql 包提供的方法訪問資料庫了.

資料庫的串連

當匯入了 MySQL 驅動後, 我們開啟資料庫連接:

func main() {    db, err := sql.Open("mysql",        "user:password@tcp(127.0.0.1:3306)/test")    if err != nil {        log.Fatal(err)    }    defer db.Close()}

通過 sql.Open 函數, 可以建立一個資料庫抽象操作介面, 如果開啟成功的話, 它會返回一個 sql.DB 指標.
sql.Open 函數的簽名如下:

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

它接收兩個參數:

• driverName, 使用的驅動名. 這個名字其實就是資料庫驅動註冊到 database/sql 時所使用的名字.

• dataSourceName, 第二個資料庫連接的連結. 這個連結包含了資料庫的使用者名稱, 密碼, 資料庫主機以及需要串連的資料庫名等資訊.

需要注意的是, golang 對資料庫的串連是延時初始化的(lazy init), 即 sql.Open 並不會立即建立一個資料庫的網路連接, 也不會對資料庫連結參數的合法性做檢驗, 它僅僅是初始化一個 sql.DB 對象. 當我們進行第一次資料庫查詢操作時, 此時才會真正建立網路連接.
如果我們想立即檢查資料庫連接是否可用, 那麼可以利用 sql.DB 的 Ping 方法, 例如:

err = db.Ping()if err != nil {    log.Fatal(err)}

sql.DB 的最佳實務:
sql.DB 對象是作為長期生存的對象來使用的, 我們應當避免頻繁地調用 Open() 和 Close(). 即一般來說, 我們要對一個資料庫進行操作時, 建立一個 sql.DB 並將其儲存起來, 每次操作此資料庫時, 傳遞此 sql.DB 對象即可, 最後在需要對此資料庫進行訪問時, 關閉對應的 sql.DB 對象.

資料庫的查詢

資料庫查詢的一般步驟如下:

• 調用 db.Query 執行 SQL 陳述式, 此方法會返回一個 Rows 作為查詢的結果

• 通過 rows.Next() 迭代查詢資料.

• 通過 rows.Scan() 讀取每一行的值

• 調用 db.Close() 關閉查詢

例如我們有如下一個資料庫表:

CREATE TABLE `user` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `name` varchar(20) DEFAULT '',  `age` int(11) DEFAULT '0',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4

我們向其中插入一條記錄:

func insertData(db *sql.DB) {    rows, err := db.Query(`INSERT INTO user (id, name, age) VALUES (1, "xys", 20)`)    defer rows.Close()    if err != nil {        log.Fatalf("insert data error: %v\n", err)    }    var result int    rows.Scan(&result)    log.Printf("insert result %v\n", result)}

通過調用 db.Query, 我們執行了一條 INSERT 語句插入了一條資料. 當執行完畢後, 首先需要做的是檢查語句是否執行成功, 當沒有錯誤時, 就通過 rows.Scan 擷取執行的結果. 因為 INSERT 返回的是插入的資料的行數, 因此我們列印的語句就是 "insert result 0".

接下來如法炮製, 我們從資料庫中將插入的資料取出:

func selectData(db *sql.DB) {    var id int    var name string    var age int    rows, err := db.Query(`SELECT * From user where id = 1`)    if err != nil {        log.Fatalf("insert data error: %v\n", err)        return    }    for rows.Next() {        rows.Scan(&id, &age, &name)        if err != nil {            log.Fatal(err)        }        log.Printf("get data, id: %d, name: %s, age: %d", id, name, age)    }    err = rows.Err()    if err != nil {        log.Fatal(err)    }}

上面的代碼的流程基本上沒有很大的差別, 不過我們需要注意一點的是 rows.Scan 參數的順序很重要, 需要和查詢的結果的 column 對應. 例如 "SELECT * From user where id = 1" 查詢的行的 column 順序是 "id, name, age", 因此 rows.Scan 也需要按照此順序 rows.Scan(&id, &name, &age), 不然會造成資料讀取的錯位.

注意:

1. 對於每個資料庫操作都需要檢查是否有錯誤返回

2. 每次 db.Query 操作後, 都需要調用 rows.Close(). 因為 db.Query() 會從資料庫連接池中擷取一個串連, 如果我們沒有調用 rows.Close(), 則此串連會一直被佔用. 因此通常我們使用 defer rows.Close() 來確保資料庫連接可以正確放回到串連池中.

3. 多次調用 rows.Close() 不會有副作用, 因此即使我們已經顯示地調用了 rows.Close(), 我們還是應該使用 defer rows.Close() 來關閉查詢.

完整的例子如下:

func insertData(db *sql.DB) {    rows, err := db.Query(`INSERT INTO user (id, name, age) VALUES (1, "xys", 20)`)    defer rows.Close()    if err != nil {        log.Fatalf("insert data error: %v\n", err)    }    var result int    rows.Scan(&result)    log.Printf("insert result %v\n", result)}func selectData(db *sql.DB) {    var id int    var name string    var age int    rows, err := db.Query(`SELECT id, name, age From user where id = 1`)    if err != nil {        log.Fatalf("insert data error: %v\n", err)        return    }    for rows.Next() {        err = rows.Scan(&id, &name, &age)        if err != nil {            log.Fatal(err)        }        log.Printf("get data, id: %d, name: %s, age: %d", id, name, age)    }    err = rows.Err()    if err != nil {        log.Fatal(err)    }}func main() {    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")    defer db.Close()    if err != nil {        fmt.Printf("connect to db 127.0.0.1:3306 error: %v\n", err)        return    }    insertData(db)    selectData(db)}

先行編譯語句(Prepared Statement)

先行編譯語句(PreparedStatement)提供了諸多好處, 因此我們在開發中盡量使用它. 下面列出了使用先行編譯語句所提供的功能:

• PreparedStatement 可以實現自訂參數的查詢

• PreparedStatement 通常來說, 比手動拼接字串 SQL 陳述式高效.

• PreparedStatement 可以防止SQL注入攻擊

下面我們將上一小節的例子使用 Prepared Statement 來改寫:

func deleteData(db *sql.DB) {    stmt, _ := db.Prepare(`DELETE FROM user WHERE id = ?`)    rows, err := stmt.Query(1)    defer stmt.Close()    rows.Close()    if err != nil {        log.Fatalf("delete data error: %v\n", err)    }    rows, err = stmt.Query(2)    rows.Close()    if err != nil {        log.Fatalf("delete data error: %v\n", err)    }}func insertData(db *sql.DB) {    stmt, _ := db.Prepare(`INSERT INTO user (id, name, age) VALUES (?, ?, ?)`)    rows, err := stmt.Query(1, "xys", 20)    defer stmt.Close()    rows.Close()    if err != nil {        log.Fatalf("insert data error: %v\n", err)    }    rows, err = stmt.Query(2, "test", 19)    var result int    rows.Scan(&result)    log.Printf("insert result %v\n", result)    rows.Close()}func selectData(db *sql.DB) {    var id int    var name string    var age int    stmt, _ := db.Prepare(`SELECT * From user where age > ?`)    rows, err := stmt.Query(10)    defer stmt.Close()    defer rows.Close()    if err != nil {        log.Fatalf("select data error: %v\n", err)        return    }    for rows.Next() {        err = rows.Scan(&id, &name, &age)        if err != nil {            log.Fatal(err)        }        log.Printf("get data, id: %d, name: %s, age: %d", id, name, age)    }    err = rows.Err()    if err != nil {        log.Fatal(err)    }}func main() {    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")    defer db.Close()    if err != nil {        fmt.Printf("connect to db 127.0.0.1:3306 error: %v\n", err)        return    }    deleteData(db)    insertData(db)    selectData(db)}