微信PK10平台開發Go實戰--go語言操作sqlite資料庫

生命不止，繼續 go go go !!!PK10平台開發

繼續與大家分享，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)
1
需要提供兩個參數，一個driverName，一個資料庫的名。

Prepare

func (db DB) Prepare(query string) (Stmt, error)
1
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)
1
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)

}
1
2
3
4
5
6
7
8
9
10
11
12
13
Query

func (s Stmt) Query(args ...interface{}) (Rows, error)
1
查詢，返回的Rows是什麼鬼？

Rows
Rows is the result of a query. Its cursor starts before the first row of the result set.

func (rs *Rows) Next() bool
1
Next prepares the next result row for reading with the Scan method

func (rs *Rows) Scan(dest ...interface{}) error
1
Scan 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地址：
https://github.com/mattn/go-sqlite3

執行：

go get -u github.com/mattn/go-sqlite3
1
下面要開始我們的實戰了！！！！

建立資料庫，建立表

//開啟資料庫，如果不存在，則建立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)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
建立一個資料庫叫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)

1
2
3
4
5
顯示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"
1
2
3
當匯入一個包時，該包下的檔案裡所有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)// insertstmt, 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)// updatestmt, 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)// queryrows, err := db.Query("SELECT * FROM userinfo")checkErr(err)var uid intvar username stringvar department stringvar created time.Timefor 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()// deletestmt, err = db.Prepare("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)
}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92