Facebook加入
LINE加入
2021/12/25
生命不止，繼續 go go go !!!
繼續與大家分享，go語言的實戰，今天介紹的是如何操作sqlite資料庫。
何為sqlite3？
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine.
最主要的是，sqlite是一款輕型的資料庫
官網：
database/sql包
go中有一個database/sql package，我們看看是怎樣描述的：
Package sql provides a generic interface around SQL (or SQL-like) databases.
The sql package must be used in conjunction with a database driver
很清晰吧，需要我們自己提供一個database driver。當然，我們可以在github上找到相關的sqlite3的driver，稍後介紹。
Open
func Open(driverName, dataSourceName string) (*DB, error)需要提供兩個參數，一個driverName，一個資料庫的名。
Prepare
func (db *DB) Prepare(query string) (*Stmt, error)Prepare creates a prepared statement for later queries or executions.
返回的 *Stmt是什麼鬼？
Stmt
Stmt is a prepared statement. A Stmt is safe for concurrent use by multiple goroutines.
func (*Stmt) Exec
準備完成後，就要執行了。
func (s *Stmt) Exec(args ...interface{}) (Result, error)Exec executes a prepared statement with the given arguments and returns a Result summarizing the effect of the statement.
返回的Resault是什麼鬼？
Result
type Result interface { // LastInsertId returns the integer generated by the database// in response to a command. Typically this will be from an// "auto increment" column when inserting a new row. Not all// databases support this feature, and the syntax of such// statements varies. LastInsertId (int64, error) // RowsAffected returns the number of rows affected by an// update, insert, or delete. Not every database or database// driver may support this. RowsAffected (int64, error) }Query
func (s *Stmt) Query(args ...interface{}) (*Rows, error)查詢，返回的Rows是什麼鬼？
Rows
Rows is the result of a query. Its cursor starts before the first row of the result set.
func (rs *Rows) Next boolNext prepares the next result row for reading with the Scan method
func (rs *Rows) Scan(dest ...interface{}) errorScan copies the columns in the current row into the values pointed at by dest.
介紹少不多了，下面介紹一個sqlite3的Driver：
mattn/go-sqlite3
sqlite3 driver for go that using database/sql
github地址：
執行：
go get -u github.com/mattn/go-sqlite3下面要開始我們的實戰了！！！！
創建資料庫，創建表
//打開資料庫，如果不存在，則創建 db, err := sql.Open("sqlite3", "./test.db") checkErr(err) //創建表 sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(64) NULL, departname VARCHAR(64) NULL, created DATE NULL ); ` db.Exec(sql_table)新建一個資料庫叫test.db，並在這個資料庫中建一個表，叫做userinfo。
userinfo中包含了四個欄位，uid username departname created.
把uid設置為主鍵，並AUTOINCREMENT，自增。
插入數據
stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)") checkErr(err) res, err := stmt.Exec("wangshubo", "國務院", "2017-04-21") checkErr(err)顯示Prepare，然後Exec.
接下來，就不再贅述了，我們需要一個基本的sql知識。
補充：import中_的作用
官方解釋：
To import a package solely for its side-effects (initialization), use the blank identifier as explicit package name: import _ "lib/math"當導入一個包時，該包下的文件里所有init函數都會被執行。
然而，有些時候我們並不需要把整個包都導入進來，僅僅是是希望它執行init函數而已。這個時候就可以使用 import _ 引用該包。
最後獻上全部代碼：
package main import ( "database/sql""fmt""time" _ "github.com/mattn/go-sqlite3" ) func main { //打開資料庫，如果不存在，則創建 db, err := sql.Open("sqlite3", "./foo.db") checkErr(err) //創建表 sql_table := ` CREATE TABLE IF NOT EXISTS userinfo( uid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(64) NULL, departname VARCHAR(64) NULL, created DATE NULL ); ` db.Exec(sql_table) // insert stmt, err := db.Prepare("INSERT INTO userinfo(username, departname, created) values(?,?,?)") checkErr(err) res, err := stmt.Exec("wangshubo", "國務院", "2017-04-21") checkErr(err) id, err := res.LastInsertId checkErr(err) fmt.Println(id) // update stmt, err = db.Prepare("update userinfo set username=? where uid=?") checkErr(err) res, err = stmt.Exec("wangshubo_new", id) checkErr(err) affect, err := res.RowsAffected checkErr(err) fmt.Println(affect) // query rows, err := db.Query("SELECT * FROM userinfo") checkErr(err) var uid intvar username stringvar department stringvar created time.Time for rows.Next { err = rows.Scan(&uid, &username, &department, &created) checkErr(err) fmt.Println(uid) fmt.Println(username) fmt.Println(department) fmt.Println(created) } rows.Close // delete"delete from userinfo where uid=?") checkErr(err) res, err = stmt.Exec(id) checkErr(err) affect, err = res.RowsAffected checkErr(err) fmt.Println(affect) db.Close } func checkErr(err error) { if err != nil { panic(err) } } 
熱門推薦
留言回覆
