Go操作Mysql數(shù)據(jù)庫

使用Go操作MySQL等數(shù)據(jù)庫，一般有兩種方式：一是使用database/sql接口，直接在代碼里硬編碼sql語句；二是使用gorm，即對象關(guān)系映射的方式在代碼里抽象的操作數(shù)據(jù)庫。一般推薦使用第二種方式。

使用database/sql接口

Go沒有內(nèi)置的驅(qū)動支持任何數(shù)據(jù)庫，但是Go定義了database/sql接口，用戶可以基于驅(qū)動接口開發(fā)相應(yīng)數(shù)據(jù)庫的驅(qū)動。但缺點(diǎn)是，直接用 github.com/go-sql-driver/mysql 訪問數(shù)據(jù)庫都是直接寫 sql，取出結(jié)果然后自己拼成對象，使用上面不是很方便，可讀性也不好。

下載包

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

源碼如下：

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql""time" )//數(shù)據(jù)庫連接信息 const (USERNAME = "root"PASSWORD = "123qwe"NETWORK = "tcp"SERVER = "127.0.0.1"PORT = 3306DATABASE = "test" )//user表結(jié)構(gòu)體定義 type User struct {Id int `json:"id" form:"id"`Username string `json:"username" form:"username"`Password string `json:"password" form:"password"`Status int `json:"status" form:"status"` // 0 正常狀態(tài)， 1刪除Createtime int64 `json:"createtime" form:"createtime"` }func CreateTable(DB *sql.DB) {sql := `CREATE TABLE IF NOT EXISTS users(id INT(4) PRIMARY KEY AUTO_INCREMENT NOT NULL,username VARCHAR(64),password VARCHAR(64),status INT(4),createtime INT(10)); `if _, err := DB.Exec(sql); err != nil {fmt.Println("create table failed:", err)return}fmt.Println("create table successd") }//插入數(shù)據(jù) func InsertData(DB *sql.DB) {result, err := DB.Exec("insert INTO users(username,password) values(?,?)", "demo", "123qwe")if err != nil {fmt.Printf("Insert data failed,err:%v", err)return}lastInsertID, err := result.LastInsertId() //獲取插入數(shù)據(jù)的自增IDif err != nil {fmt.Printf("Get insert id failed,err:%v", err)return}fmt.Println("Insert data id:", lastInsertID)rowsaffected, err := result.RowsAffected() //通過RowsAffected獲取受影響的行數(shù)if err != nil {fmt.Printf("Get RowsAffected failed,err:%v", err)return}fmt.Println("Affected rows:", rowsaffected) }//查詢單行 func QueryOne(DB *sql.DB) {user := new(User) //用new()函數(shù)初始化一個結(jié)構(gòu)體對象row := DB.QueryRow("select id,username,password from users where id=?", 1)//row.scan中的字段必須是按照數(shù)據(jù)庫存入字段的順序，否則報錯if err := row.Scan(&user.Id, &user.Username, &user.Password); err != nil {fmt.Printf("scan failed, err:%v\n", err)return}fmt.Println("Single row data:", *user) }//查詢多行 func QueryMulti(DB *sql.DB) {user := new(User)rows, err := DB.Query("select id,username,password from users where id = ?", 2)defer func() {if rows != nil {rows.Close() //關(guān)閉掉未scan的sql連接 }}()if err != nil {fmt.Printf("Query failed,err:%v\n", err)return}for rows.Next() {err = rows.Scan(&user.Id, &user.Username, &user.Password) //不scan會導(dǎo)致連接不釋放if err != nil {fmt.Printf("Scan failed,err:%v\n", err)return}fmt.Println("scan successd:", *user)} }//更新數(shù)據(jù) func UpdateData(DB *sql.DB) {result, err := DB.Exec("UPDATE users set password=? where id=?", "111111", 1)if err != nil {fmt.Printf("Insert failed,err:%v\n", err)return}fmt.Println("update data successd:", result)rowsaffected, err := result.RowsAffected()if err != nil {fmt.Printf("Get RowsAffected failed,err:%v\n", err)return}fmt.Println("Affected rows:", rowsaffected) }//刪除數(shù)據(jù) func DeleteData(DB *sql.DB) {result, err := DB.Exec("delete from users where id=?", 1)if err != nil {fmt.Printf("Insert failed,err:%v\n", err)return}fmt.Println("delete data successd:", result)rowsaffected, err := result.RowsAffected()if err != nil {fmt.Printf("Get RowsAffected failed,err:%v\n", err)return}fmt.Println("Affected rows:", rowsaffected) }func main() {conn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)fmt.Println("conn: ", conn)DB, err := sql.Open("mysql", conn)if err != nil {fmt.Println("connection to mysql failed:", err)return}DB.SetConnMaxLifetime(100 * time.Second) //最大連接周期，超時的連接就closeDB.SetMaxOpenConns(100) //設(shè)置最大連接數(shù) CreateTable(DB)InsertData(DB)QueryOne(DB)QueryMulti(DB)UpdateData(DB)DeleteData(DB) }

運(yùn)行結(jié)果如下：

[root@localhost mysql]# go run main.go conn: root:123qwe@tcp(127.0.0.1:3306)/test create table successd Insert data id: 2 Affected rows: 1 scan failed, err:sql: no rows in result set scan successd: {2 demo 123qwe 0 0} update data successd: {0xc0000d8000 0xc0000d60b0} Affected rows: 0 delete data successd: {0xc0000d8000 0xc000016330} Affected rows: 0

?

轉(zhuǎn)載于:https://www.cnblogs.com/wangjq19920210/p/11577314.html

總結(jié)

以上是生活随笔為你收集整理的golang mysql demo的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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