在這篇教程中，我們將在 MySQL 中創(chuàng)建一個?chitchat?數(shù)據(jù)庫作為論壇項(xiàng)目的數(shù)據(jù)庫，然后在 Go 項(xiàng)目中編寫模型類與之進(jìn)行交互。你可以本地安裝 MySQL 數(shù)據(jù)庫，也可以基于 Docker 容器運(yùn)行(后續(xù)會介紹容器化啟動方法)。

1、項(xiàng)目初始化

開始之前，我們先來初始化項(xiàng)目目錄，我們將項(xiàng)目名設(shè)置為?chitchat，所以在?~/Development/go/src/github.com/xueyuanjun?目錄下創(chuàng)建這個項(xiàng)目目錄，然后初始化目錄結(jié)構(gòu)如下：

重點(diǎn)看下紅框內(nèi)，各個子目錄/文件的作用介紹如下：

• main.go：應(yīng)用入口文件

• config.json：全局配置文件

• handlers：用于存放處理器代碼(可類比為 MVC 模式中的控制器目錄)

• logs：用于存放日志文件

• models：用于存放與數(shù)據(jù)庫交互的模型類

• public：用于存放前端資源文件，比如圖片、CSS、JavaScript 等

• routes：用于存放路由文件和路由器實(shí)現(xiàn)代碼

• views：用于存放視圖模板文件

接下來，我們在?chitchat?目錄下運(yùn)行如下命令初始化?go.mod，因?yàn)槲覀兒罄m(xù)通過 Go Module 來管理依賴：

go mod init github.com/xueyuanjun/chitchat

2、創(chuàng)建數(shù)據(jù)表

開始正式編碼之前，現(xiàn)在?chitchat?數(shù)據(jù)庫中創(chuàng)建數(shù)據(jù)表，對應(yīng)的 SQL 語句如下：

create table users ( id serial primary key, uuid varchar(64) not null unique, name varchar(255), email varchar(255) not null unique, password varchar(255) not null, created_at timestamp not null);create table sessions ( id serial primary key, uuid varchar(64) not null unique, email varchar(255), user_id integer references users(id), created_at timestamp not null);create table threads ( id serial primary key, uuid varchar(64) not null unique, topic text, user_id integer references users(id), created_at timestamp not null);create table posts ( id serial primary key, uuid varchar(64) not null unique, body text, user_id integer references users(id), thread_id integer references threads(id), created_at timestamp not null);

在 MySQL 客戶端連接到?chitchat?數(shù)據(jù)庫，運(yùn)行上述 SQL 語句創(chuàng)建所有數(shù)據(jù)表：

3、與數(shù)據(jù)庫交互

1)數(shù)據(jù)庫驅(qū)動

數(shù)據(jù)表創(chuàng)建完成后，接下來，如何在 Go 應(yīng)用代碼中與數(shù)據(jù)庫交互呢？Go 語言開發(fā)組并沒有為此提供官方的數(shù)據(jù)庫驅(qū)動實(shí)現(xiàn)，只是提供了數(shù)據(jù)庫交互接口，我們可以通過實(shí)現(xiàn)這些接口的第三方擴(kuò)展包完成與 MySQL 數(shù)據(jù)庫的交互，本項(xiàng)目選擇的擴(kuò)展包是?go-mysql-driver?。

我們可以在 Go 應(yīng)用中編寫模型類基于這個擴(kuò)展包提供的方法與 MySQL 交互完成增刪改查操作，開始之前，可以運(yùn)行如下命令安裝這個依賴：

go get github.com/go-sql-driver/mysql

2)數(shù)據(jù)庫連接

然后在?chitchat/models?目錄下創(chuàng)建?db.go，并編寫數(shù)據(jù)庫連接初始化方法以及生成 UUID、哈希加密方法：

package modelsimport ( "crypto/rand" "crypto/sha1" "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "log")var Db *sql.DBfunc init() { var err error Db, err = sql.Open("mysql", "root:root@/chitchat?charset=utf8&parseTime=true") if err != nil { log.Fatal(err) } return}// create a random UUID with from RFC 4122// adapted from http://github.com/nu7hatch/gouuidfunc createUUID() (uuid string) { u := new([16]byte) _, err := rand.Read(u[:]) if err != nil { log.Fatalln("Cannot generate UUID", err) } // 0x40 is reserved variant from RFC 4122 u[8] = (u[8] | 0x40) & 0x7F // Set the four most significant bits (bits 12 through 15) of the // time_hi_and_version field to the 4-bit version number. u[6] = (u[6] & 0xF) | (0x4 << 4) uuid = fmt.Sprintf("%x-%x-%x-%x-%x", u[0:4], u[4:6], u[6:8], u[8:10], u[10:]) return}// hash plaintext with SHA-1func Encrypt(plaintext string) (cryptext string) { cryptext = fmt.Sprintf("%x", sha1.Sum([]byte(plaintext))) return}

其中，Db?變量代表數(shù)據(jù)庫連接池，通過?init?方法在 Web 應(yīng)用啟動時自動初始化數(shù)據(jù)庫連接，這樣，我們就可以在應(yīng)用中通過?Db?變量對數(shù)據(jù)庫進(jìn)行增刪改查操作了，這也是該變量首字母大寫的原因，方便在?models?包之外被引用，具體的操作實(shí)現(xiàn)我們放到獨(dú)立的模型文件中處理。

注：這里通過 sql.Open 初始化數(shù)據(jù)庫連接，我們寫死了數(shù)據(jù)庫連接配置，在實(shí)際生產(chǎn)環(huán)境，這塊配置值應(yīng)該從配置文件或系統(tǒng)環(huán)境變量獲取。

3)用戶相關(guān)模型類

有了代表數(shù)據(jù)庫連接池的?Db?變量之后，就可以為每個數(shù)據(jù)表編寫對應(yīng)的模型類實(shí)現(xiàn)增刪改查操作了，首先在?models?目錄下創(chuàng)建?user.go?用于定義用戶模型類?User?與?users?表進(jìn)行交互，以及與?sessions?表進(jìn)行關(guān)聯(lián)：

package modelsimport "time"type User struct { Id int Uuid string Name string Email string Password string CreatedAt time.Time}// Create a new session for an existing userfunc (user *User) CreateSession() (session Session, err error) { statement := "insert into sessions (uuid, email, user_id, created_at) values (?, ?, ?, ?)" stmtin, err := Db.Prepare(statement) if err != nil { return } defer stmtin.Close() uuid := createUUID() stmtin.Exec(uuid, user.Email, user.Id, time.Now()) stmtout, err := Db.Prepare("select id, uuid, email, user_id, created_at from sessions where uuid = ?") if err != nil { return } defer stmtout.Close() // use QueryRow to return a row and scan the returned id into the Session struct err = stmtout.QueryRow(uuid).Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt) return}// Get the session for an existing userfunc (user *User) Session() (session Session, err error) { session = Session{} err = Db.QueryRow("SELECT id, uuid, email, user_id, created_at FROM sessions WHERE user_id = ?", user.Id). Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt) return}// Create a new user, save user info into the databasefunc (user *User) Create() (err error) { // Postgres does not automatically return the last insert id, because it would be wrong to assume // you're always using a sequence.You need to use the RETURNING keyword in your insert to get this // information from postgres. statement := "insert into users (uuid, name, email, password, created_at) values (?, ?, ?, ?, ?)" stmtin, err := Db.Prepare(statement) if err != nil { return } defer stmtin.Close() uuid := createUUID() stmtin.Exec(uuid, user.Name, user.Email, Encrypt(user.Password), time.Now()) stmtout, err := Db.Prepare("select id, uuid, created_at from users where uuid = ?") if err != nil { return } defer stmtout.Close() // use QueryRow to return a row and scan the returned id into the User struct err = stmtout.QueryRow(uuid).Scan(&user.Id, &user.Uuid, &user.CreatedAt) return}// Delete user from databasefunc (user *User) Delete() (err error) { statement := "delete from users where id = ?" stmt, err := Db.Prepare(statement) if err != nil { return } defer stmt.Close() _, err = stmt.Exec(user.Id) return}// Update user information in the databasefunc (user *User) Update() (err error) { statement := "update users set name = ?, email = ? where id = ?" stmt, err := Db.Prepare(statement) if err != nil { return } defer stmt.Close() _, err = stmt.Exec(user.Name, user.Email, user.Id) return}// Delete all users from databasefunc UserDeleteAll() (err error) { statement := "delete from users" _, err = Db.Exec(statement) return}// Get all users in the database and returns itfunc Users() (users []User, err error) { rows, err := Db.Query("SELECT id, uuid, name, email, password, created_at FROM users") if err != nil { return } for rows.Next() { user := User{} if err = rows.Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt); err != nil { return } users = append(users, user) } rows.Close() return}// Get a single user given the emailfunc UserByEmail(email string) (user User, err error) { user = User{} err = Db.QueryRow("SELECT id, uuid, name, email, password, created_at FROM users WHERE email = ?", email). Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt) return}// Get a single user given the UUIDfunc UserByUUID(uuid string) (user User, err error) { user = User{} err = Db.QueryRow("SELECT id, uuid, name, email, password, created_at FROM users WHERE uuid = ?", uuid). Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.Password, &user.CreatedAt) return}

創(chuàng)建?session.go?用于定義會話模型類?Session：

package modelsimport "time"type Session struct { Id int Uuid string Email string UserId int CreatedAt time.Time}// Check if session is valid in the databasefunc (session *Session) Check() (valid bool, err error) { err = Db.QueryRow("SELECT id, uuid, email, user_id, created_at FROM sessions WHERE uuid = ?", session.Uuid). Scan(&session.Id, &session.Uuid, &session.Email, &session.UserId, &session.CreatedAt) if err != nil { valid = false return } if session.Id != 0 { valid = true } return}// Delete session from databasefunc (session *Session) DeleteByUUID() (err error) { statement := "delete from sessions where uuid = ?" stmt, err := Db.Prepare(statement) if err != nil { return } defer stmt.Close() _, err = stmt.Exec(session.Uuid) return}// Get the user from the sessionfunc (session *Session) User() (user User, err error) { user = User{} err = Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", session.UserId). Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt) return}// Delete all sessions from databasefunc SessionDeleteAll() (err error) { statement := "delete from sessions" _, err = Db.Exec(statement) return}

這里面定義了基于?Db?數(shù)據(jù)庫連接實(shí)例實(shí)現(xiàn)用戶模型和會話模型相關(guān)的增刪改查操作，具體的語法可以參考?go-mysql-driver?的?官方文檔，這里不詳細(xì)展開，具體細(xì)節(jié)，我們留到后面專門介紹數(shù)據(jù)庫模塊時再詳細(xì)說明。

4)主題相關(guān)模型類

編寫好用戶相關(guān)模型類后，接下來在同級目錄下創(chuàng)建?thread.go，定義群組模型類?Thread?與?threads?表進(jìn)行交互：

package modelsimport "time"type Thread struct { Id int Uuid string Topic string UserId int CreatedAt time.Time}// format the CreatedAt date to display nicely on the screenfunc (thread *Thread) CreatedAtDate() string { return thread.CreatedAt.Format("Jan 2, 2006 at 3:04pm")}// get the number of posts in a threadfunc (thread *Thread) NumReplies() (count int) { rows, err := Db.Query("SELECT count(*) FROM posts where thread_id = ?", thread.Id) if err != nil { return } for rows.Next() { if err = rows.Scan(&count); err != nil { return } } rows.Close() return}// get posts to a threadfunc (thread *Thread) Posts() (posts []Post, err error) { rows, err := Db.Query("SELECT id, uuid, body, user_id, thread_id, created_at FROM posts where thread_id = ?", thread.Id) if err != nil { return } for rows.Next() { post := Post{} if err = rows.Scan(&post.Id, &post.Uuid, &post.Body, &post.UserId, &post.ThreadId, &post.CreatedAt); err != nil { return } posts = append(posts, post) } rows.Close() return}// Get all threads in the database and returns itfunc Threads() (threads []Thread, err error) { rows, err := Db.Query("SELECT id, uuid, topic, user_id, created_at FROM threads ORDER BY created_at DESC") if err != nil { return } for rows.Next() { conv := Thread{} if err = rows.Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt); err != nil { return } threads = append(threads, conv) } rows.Close() return}// Get a thread by the UUIDfunc ThreadByUUID(uuid string) (conv Thread, err error) { conv = Thread{} err = Db.QueryRow("SELECT id, uuid, topic, user_id, created_at FROM threads WHERE uuid = ?", uuid). Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt) return}// Get the user who started this threadfunc (thread *Thread) User() (user User) { user = User{} Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", thread.UserId). Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt) return}

以及?post.go?編寫主題模型類與?posts?表進(jìn)行交互：

package modelsimport "time"type Post struct { Id int Uuid string Body string UserId int ThreadId int CreatedAt time.Time}func (post *Post) CreatedAtDate() string { return post.CreatedAt.Format("Jan 2, 2006 at 3:04pm")}// Get the user who wrote the postfunc (post *Post) User() (user User) { user = User{} Db.QueryRow("SELECT id, uuid, name, email, created_at FROM users WHERE id = ?", post.UserId). Scan(&user.Id, &user.Uuid, &user.Name, &user.Email, &user.CreatedAt) return}

此外，我們到?user.go?中為?User?模型新增如下兩個方法與?Thread、Post?模型進(jìn)行關(guān)聯(lián)，用于創(chuàng)建新的群組和主題：

// Create a new threadfunc (user *User) CreateThread(topic string) (conv Thread, err error) { statement := "insert into threads (uuid, topic, user_id, created_at) values (?, ?, ?, ?)" stmtin, err := Db.Prepare(statement) if err != nil { return } defer stmtin.Close() uuid := createUUID() stmtin.Exec(uuid, topic, user.Id, time.Now()) stmtout, err := Db.Prepare("select id, uuid, topic, user_id, created_at from threads where uuid = ?") if err != nil { return } defer stmtout.Close() // use QueryRow to return a row and scan the returned id into the Session struct err = stmtout.QueryRow(uuid).Scan(&conv.Id, &conv.Uuid, &conv.Topic, &conv.UserId, &conv.CreatedAt) return}// Create a new post to a threadfunc (user *User) CreatePost(conv Thread, body string) (post Post, err error) { statement := "insert into posts (uuid, body, user_id, thread_id, created_at) values (?, ?, ?, ?, ?)" stmtin, err := Db.Prepare(statement) if err != nil { return } defer stmtin.Close() uuid := createUUID() stmtin.Exec(uuid, body, user.Id, conv.Id, time.Now()) stmtout, err := Db.Prepare("select id, uuid, body, user_id, thread_id, created_at from posts where uuid = ?") if err != nil { return } defer stmtout.Close() // use QueryRow to return a row and scan the returned id into the Session struct err = stmtout.QueryRow(uuid).Scan(&post.Id, &post.Uuid, &post.Body, &post.UserId, &post.ThreadId, &post.CreatedAt) return}

4、小結(jié)

在上述編寫的模型類中，模型類與數(shù)據(jù)表是如何映射的呢？這個由?go-mysql-driver?底層實(shí)現(xiàn)，每次從數(shù)據(jù)庫查詢到結(jié)果之后，可以通過?Scan?方法將數(shù)據(jù)表字段值映射到對應(yīng)的結(jié)構(gòu)體模型類，而將模型類保存到數(shù)據(jù)庫時，又可以基于字段映射關(guān)系將結(jié)構(gòu)體屬性值轉(zhuǎn)化為對應(yīng)的數(shù)據(jù)表字段值。對應(yīng)的底層交互邏輯如下所示：

再次重申，本項(xiàng)目旨在幫助大家快速熟悉 Go Web 項(xiàng)目的完整開發(fā)流程和代碼組織架構(gòu)，具體的技術(shù)實(shí)現(xiàn)細(xì)節(jié)留到后面單獨(dú)的數(shù)據(jù)庫模塊詳細(xì)介紹，不過，如果你之前有過其他語言的 Web 開發(fā)經(jīng)驗(yàn)，比如 PHP、Python、Java，基本上看一眼這些代碼就能知道個大概了，因?yàn)樗麄兊膶?shí)現(xiàn)思路都是相通的，無非是語言的語法不同而已，所以這也是學(xué)院君反復(fù)強(qiáng)調(diào)新人應(yīng)該花個三五年時間先精通一門語言，再學(xué)習(xí)其他語言的原因。

底層數(shù)據(jù)庫交互邏輯定義好了之后，接下來，我們就可以編寫上層實(shí)現(xiàn)代碼了，下一篇學(xué)院君將給大家演示在線論壇項(xiàng)目上層路由和處理器方法的實(shí)現(xiàn)。

推薦閱讀

• 基于 Go 語言編寫在線論壇(一)：整體設(shè)計(jì)與數(shù)據(jù)模型

---

喜歡本文的朋友，歡迎關(guān)注“Go語言中文網(wǎng)”：

Go語言中文網(wǎng)啟用微信學(xué)習(xí)交流群，歡迎加微信：274768166，投稿亦歡迎

總結(jié)

以上是生活随笔為你收集整理的go 连接服务器 并存放图片_基于 Go 语言开发在线论坛（二）：通过模型类与MySQL数据库交互...的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。