golang操作mysql使用總結

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

 

sql.DB 通過資料庫驅動為我們提供管理底層資料庫連接的開啟和關閉操作.
sql.DB 為我們管理資料庫串連池
需要注意的是，sql.DB表示操作資料庫的抽象提供者,而非一個資料庫連接對象;它可以根據driver開啟關閉資料庫連接，管理串連池。正在使用的串連被標記為繁忙，用完後回到串連池等待下次使用。所以，如果你沒有把串連釋放回串連池，會導致過多串連使系統資源耗盡。

操作mysql

1.匯入mysql資料庫驅動

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

 

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

2.串連資料庫

123456789101112131415161718

type DbWorker struct { //mysql data source name Dsn string }func main() { dbw := DbWorker{ Dsn: "user:password@tcp(127.0.0.1:3306)/test", } db, err := sql.Open("mysql", dbw.Dsn) if err != nil { panic(err) return } defer db.Close()}

 

通過調用sql.Open函數返回一個sql.DB指標; sql.Open函數原型如下:

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

 

• driverName: 使用的驅動名. 這個名字其實就是資料庫驅動註冊到 database/sql 時所使用的名字.
• dataSourceName: 資料庫連接資訊，這個串連包含了資料庫的使用者名稱, 密碼, 資料庫主機以及需要串連的資料庫名等資訊.

1. sql.Open並不會立即建立一個資料庫的網路連接, 也不會對資料庫連結參數的合法性做檢驗, 它僅僅是初始化一個sql.DB對象. 當真正進行第一次資料庫查詢操作時, 此時才會真正建立網路連接;
2. sql.DB表示操作資料庫的抽象介面的對象，但不是所謂的資料庫連接對象，sql.DB對象只有當需要使用時才會建立串連，如果想立即驗證串連，需要用Ping()方法;
3. sql.Open返回的sql.DB對象是協程並發安全的.
4. sql.DB的設計就是用來作為長串連使用的。不要頻繁Open, Close。比較好的做法是，為每個不同的datastore建一個DB對象，保持這些對象Open。如果需要短串連，那麼把DB作為參數傳入function，而不要在function中Open, Close。

3.資料庫基本操作

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

1. 調用 db.Query 執行 SQL 陳述式, 此方法會返回一個 Rows 作為查詢的結果
2. 通過 rows.Next() 迭代查詢資料.
3. 通過 rows.Scan() 讀取每一行的值
4. 調用 db.Close() 關閉查詢

現有user資料庫表如下:

123456

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

 

MySQL 5.5 之前， UTF8 編碼只支援1-3個位元組,從MYSQL5.5開始，可支援4個位元組UTF編碼utf8mb4，一個字元最多能有4位元組，utf8mb4相容utf8，所以能支援更多的字元集;關於emoji表情的話mysql的utf8是不支援，需要修改設定為utf8mb4，才能支援。

查詢資料

12345678910111213141516171819202122232425262728

func (dbw *DbWorker) QueryData() {dbw.QueryDataPre()rows, err := dbw.Db.Query(`SELECT * From user where age >= 20 AND age < 30`)defer rows.Close()if err != nil {fmt.Printf("insert data error: %v\n", err)return}for rows.Next() {rows.Scan(&dbw.UserInfo.Id, &dbw.UserInfo.Name, &dbw.UserInfo.Age)if err != nil {fmt.Printf(err.Error())continue}if !dbw.UserInfo.Name.Valid {dbw.UserInfo.Name.String = ""}if !dbw.UserInfo.Age.Valid {dbw.UserInfo.Age.Int64 = 0}fmt.Println("get data, id: ", dbw.UserInfo.Id, " name: ", dbw.UserInfo.Name.String, " age: ", int(dbw.UserInfo.Age.Int64))}err = rows.Err()if err != nil {fmt.Printf(err.Error())}}

 

1. rows.Scan 參數的順序很重要, 需要和查詢的結果的column對應. 例如 “SELECT * From user where age >=20 AND age < 30” 查詢的行的 column 順序是 “id, name, age” 和插入操作順序相同, 因此 rows.Scan 也需要按照此順序 rows.Scan(&id, &name, &age), 不然會造成資料讀取的錯位.
2. 因為golang是強型別語言，所以查詢資料時先定義資料類型，但是查詢資料庫中的資料存在三種可能:存在值，存在零值，未賦值NULL 三種狀態, 因為可以將待查詢的資料類型定義為sql.Nullxxx類型，可以通過判斷Valid值來判斷查詢到的值是否為賦值狀態還是未賦值NULL狀態.
3. 每次db.Query操作後, 都建議調用rows.Close(). 因為 db.Query() 會從資料庫連接池中擷取一個串連, 這個底層串連在結果集(rows)未關閉前會被標記為處於繁忙狀態。當遍曆讀到最後一條記錄時，會發生一個內部EOF錯誤，自動調用rows.Close(),但如果提前退出迴圈，rows不會關閉，串連不會回到串連池中，串連也不會關閉, 則此串連會一直被佔用. 因此通常我們使用 defer rows.Close() 來確保資料庫連接可以正確放回到串連池中; 不過閱讀源碼發現rows.Close()操作是等冪操作，即一個等冪操作的特點是其任意多次執行所產生的影響均與一次執行的影響相同, 所以即便對已關閉的rows再執行close()也沒關係.

單行查詢

123456

var name stringerr = db.QueryRow("select name from user where id = ?", 1).Scan(&name)if err != nil { log.Fatal(err)}fmt.Println(name)

 

1. err在Scan後才產生，上述鏈式寫法是對的
2. 需要注意Scan()中變數和順序要和前面Query語句中的順序一致，否則查出的資料會映射不一致.

插入資料

12345678910111213

func (dbw *DbWorker) insertData() {ret, err := dbw.Db.Exec(`INSERT INTO user (name, age) VALUES ("xys", 23)`)if err != nil {fmt.Printf("insert data error: %v\n", err)return}if LastInsertId, err := ret.LastInsertId(); nil == err {fmt.Println("LastInsertId:", LastInsertId)}if RowsAffected, err := ret.RowsAffected(); nil == err {fmt.Println("RowsAffected:", RowsAffected)}}

 

通過db.Exec()插入資料，通過返回的err可知插入失敗的原因，通過返回的ret可以進一步查詢本次插入資料影響的行數RowsAffected和最後插入的Id(如果資料庫支援查詢最後插入Id).

github完整程式碼範例

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

• PreparedStatement 可以實現自訂參數的查詢
• PreparedStatement 通常來說, 比手動拼接字串 SQL 陳述式高效.
• PreparedStatement 可以防止SQL注入攻擊

一般用Prepared Statements和Exec()完成INSERT, UPDATE, DELETE操作。

下面是將上述案例用Prepared Statement 修改之後的完整代碼

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql")type DbWorker struct {Dsn stringDb *sql.DBUserInfo userTB}type userTB struct {Id intName sql.NullStringAge sql.NullInt64}func main() {var err errordbw := DbWorker{Dsn: "root:123456@tcp(localhost:3306)/sqlx_db?charset=utf8mb4",}dbw.Db, err = sql.Open("mysql", dbw.Dsn)if err != nil {panic(err)return}defer dbw.Db.Close()dbw.insertData()dbw.queryData()}func (dbw *DbWorker) insertData() {stmt, _ := dbw.Db.Prepare(`INSERT INTO user (name, age) VALUES (?, ?)`)defer stmt.Close()ret, err := stmt.Exec("xys", 23)if err != nil {fmt.Printf("insert data error: %v\n", err)return}if LastInsertId, err := ret.LastInsertId(); nil == err {fmt.Println("LastInsertId:", LastInsertId)}if RowsAffected, err := ret.RowsAffected(); nil == err {fmt.Println("RowsAffected:", RowsAffected)}}func (dbw *DbWorker) QueryDataPre() {dbw.UserInfo = userTB{}}func (dbw *DbWorker) queryData() {stmt, _ := dbw.Db.Prepare(`SELECT * From user where age >= ? AND age < ?`)defer stmt.Close()dbw.QueryDataPre()rows, err := stmt.Query(20, 30)defer rows.Close()if err != nil {fmt.Printf("insert data error: %v\n", err)return}for rows.Next() {rows.Scan(&dbw.UserInfo.Id, &dbw.UserInfo.Name, &dbw.UserInfo.Age)if err != nil {fmt.Printf(err.Error())continue}if !dbw.UserInfo.Name.Valid {dbw.UserInfo.Name.String = ""}if !dbw.UserInfo.Age.Valid {dbw.UserInfo.Age.Int64 = 0}fmt.Println("get data, id: ", dbw.UserInfo.Id, " name: ", dbw.UserInfo.Name.String, " age: ", int(dbw.UserInfo.Age.Int64))}err = rows.Err()if err != nil {fmt.Printf(err.Error())}}

 

db.Prepare()返回的statement使用完之後需要手動關閉，即defer stmt.Close()