溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》

Go語(yǔ)言中mysql數(shù)據(jù)庫(kù)操作（一）

發(fā)布時(shí)間：2020-06-01 02:55:29 來(lái)源：網(wǎng)絡(luò) 閱讀：13314 作者：thao888 欄目：編程語(yǔ)言

數(shù)據(jù)的持久化是程序中必不可少的，所以編程語(yǔ)言中對(duì)數(shù)據(jù)庫(kù)的操作是非常重要的一塊，本文介紹Go語(yǔ)言對(duì)mysql數(shù)據(jù)庫(kù)的操作。

基本操作

建立連接

db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/betting?charset=utf8")
errDeal("連接數(shù)據(jù)庫(kù)", err)
defer db.Close()

連接參數(shù)一般有以下幾種

user@unix(/path/to/socket)/dbname?charset=utf8
user:password@tcp(localhost:5555)/dbname?charset=utf8
user:password@/dbname
user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname

增刪改操作

// 插入數(shù)據(jù)---------------------方法1
result, err := db.Exec("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +
" VALUES(?,now(),?,?,?)", "admin", "127.0.0.1", "登錄", "登錄成功")
errDeal("插入數(shù)據(jù)", err)
// 查看返回信息
count, err := result.RowsAffected()
errDeal("查看插入數(shù)據(jù)條數(shù)", err)
fmt.Printf("插入數(shù)據(jù)條數(shù)：%d\n", count)
id, err := result.LastInsertId()
errDeal("查看最后插入數(shù)據(jù)的id", err)
fmt.Printf("最后插入數(shù)據(jù)的id：%d\n", id)

// 插入數(shù)據(jù)---------------------方法2，先創(chuàng)建一個(gè)預(yù)處理語(yǔ)句，再執(zhí)行
stmt, err := db.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +
" VALUES(?,now(),?,?,?)")
result1, err1 := stmt.Exec("admin", "127.0.0.1", "登錄", "登錄成功")
errDeal("方法2插入數(shù)據(jù)", err1)
fmt.Println(result1.LastInsertId())

// 刪除數(shù)據(jù)
result2, err := db.Exec("DELETE FROM admin_log WHERE id=?", 2014)
fmt.Println(result2.RowsAffected())

// 更新數(shù)據(jù)
result3, err := db.Exec("UPDATE admin_log SET log_ip=? WHERE id=?", "192.168.8.9", 2017)
fmt.Println(result3.RowsAffected())

單條數(shù)據(jù)查詢(xún)

// 先定義存儲(chǔ)查詢(xún)結(jié)果的變量
var rid int
var username, time, ip, logType string
var desc, remark, spare interface{} // 如果字段中可能出現(xiàn)值為nil的情況，可以將變量申明為interface{}類(lèi)型
err2 := db.QueryRow("SELECT id,admin_username,log_time,log_ip,log_type,log_desc,remark,spare" +
    " FROM admin_log WHERE id=?", 2017).Scan(&rid, &username, &time, &ip, &logType, &desc, &remark, &spare) // 傳入的是變量的指針
errDeal("查詢(xún)單條數(shù)據(jù)", err2)
fmt.Printf("id=%d,username=%s,time=%s,ip=%s,logType=%s,desc=%s,remark=%v,spare=%v\n", rid, username, time, ip, logType, desc, remark, spare)

多條數(shù)據(jù)查詢(xún)

// 查詢(xún)多條數(shù)據(jù)
rows, err3 := db.Query("SELECT admin_username,log_time,log_ip,log_type,log_desc" +
    " FROM admin_log WHERE id=? OR id=?", 2017, 2019)
errDeal("查詢(xún)多條數(shù)據(jù)", err3)
// 對(duì)多條數(shù)據(jù)進(jìn)行遍歷
for rows.Next() {
    err4 := rows.Scan(&username, &time, &ip, &logType, &desc)
    errDeal("遍歷多條數(shù)據(jù)", err4)
    fmt.Printf("username=%s,time=%s,ip=%s,logType=%s,desc=%s\n", username, time, ip, logType, desc)
}

// 如果查詢(xún)中不指定具體字段，使用*
rows, err33 := db.Query("SELECT *" +
    " FROM admin_log WHERE id>?", 2017)
errDeal("查詢(xún)多條數(shù)據(jù)", err33)
// 查詢(xún)所有字段名，返回string切片
columes, err333 := rows.Columns()
errDeal("rows.Columns()方法調(diào)用", err333)
fmt.Printf("%T----%v\n", columes, columes)
var scanColumes = make([]interface{}, len(columes))
var values = make([]interface{}, len(columes))
for index, _ := range  scanColumes {
    scanColumes[index] = &values[index]
}
for rows.Next() {
    err4 := rows.Scan(scanColumes...)
    errDeal("遍歷多條數(shù)據(jù)", err4)
    for i, val := range values {
        if strings.EqualFold(judgeType(val), "[]uint8") {
            fmt.Printf("%s(%T)==%s\t", columes[i], val, val)
        } else {
            fmt.Printf("%s(%T)==%v\t", columes[i], val, val)
        }

    }
    fmt.Println()
}

事務(wù)

開(kāi)啟事務(wù)

// 開(kāi)啟事務(wù)，tx是從連接池中取出一個(gè)連接，在關(guān)閉之前都是使用這個(gè)連接，提交事務(wù)和回滾事務(wù)都是操作tx
tx, err5 := db.Begin()
errDeal("開(kāi)啟事務(wù)", err5)
_, err6 := tx.Exec("UPDATE admin_log SET log_desc=? WHERE id=?", "測(cè)試事務(wù)222", 2019)
//if err6 != nil {
if err6 == nil {
    tx.Rollback() // 回滾
}
tx.Commit() // 提交

批量插入數(shù)據(jù)

// 批量數(shù)據(jù)插入
tx, err7 := db.Begin()
errDeal("數(shù)據(jù)批量插入，開(kāi)啟事務(wù)", err7)
insertValues := [][]interface{}{{"admin", "127.0.0.1", "登錄", "登錄成功"},{"admin", "127.0.0.1", "刪除", "刪除數(shù)據(jù)"},{"admin", "127.0.0.1", "退出", "退出系統(tǒng)"}}
stmt, err8 := tx.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")
errDeal("數(shù)據(jù)批量插入，預(yù)處理", err8)
for _, val := range  insertValues {
    _, err := stmt.Exec(val...)
    if err != nil {
        fmt.Printf("出現(xiàn)錯(cuò)誤回滾，錯(cuò)誤信息：%v", err)
        tx.Rollback()
    }
}
tx.Commit()

sqlx的使用及批量插入

xdb, err9 := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/betting?charset=utf8")
errDeal("sqlx連接數(shù)據(jù)庫(kù)", err9)
txx, err10 := xdb.Beginx()
errDeal("sqlx開(kāi)啟事務(wù)", err10)
insertValuesx := [][]interface{}{{"admin", "127.0.0.1", "登錄", "登錄成功X"},{"admin", "127.0.0.1", "刪除", "刪除數(shù)據(jù)X"},{"admin", "127.0.0.1", "退出", "退出系統(tǒng)X"}}
stmtx, err11 := txx.Preparex("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")
errDeal("數(shù)據(jù)批量插入，預(yù)處理", err11)
for _, val := range insertValuesx {
    _, err := stmtx.Exec(val...)
    if err != nil {
        fmt.Printf("sqlx出現(xiàn)錯(cuò)誤回滾，錯(cuò)誤信息：%v", err)
        txx.Rollback()
    }
}
txx.Commit()

完整代碼

package main

import (
    "database/sql"
    _"github.com/go-sql-driver/mysql" // 這里很重要，導(dǎo)入自己本地使用的數(shù)據(jù)庫(kù)驅(qū)動(dòng)，前面是下劃線，否則會(huì)報(bào)錯(cuò)：sql: unknown driver "mysql" (forgotten import?)
    "fmt"
    "strings"
    "github.com/jmoiron/sqlx"
)

func main() {
    // 連接數(shù)據(jù)庫(kù)，用戶(hù)名:密碼@協(xié)議(地址:端口)/數(shù)據(jù)庫(kù)?參數(shù)=參數(shù)值，常用"用戶(hù)名:密碼@tcp(ip:端口)/數(shù)據(jù)庫(kù)名?charset=字符集"
    db, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/betting?charset=utf8")
    errDeal("連接數(shù)據(jù)庫(kù)", err)
    defer db.Close()

    //=========================================================================================增刪改
    // 插入數(shù)據(jù)---------------------方法1
    result, err := db.Exec("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +
        " VALUES(?,now(),?,?,?)", "admin", "127.0.0.1", "登錄", "登錄成功")
    errDeal("插入數(shù)據(jù)", err)
    // 查看返回信息
    count, err := result.RowsAffected()
    errDeal("查看插入數(shù)據(jù)條數(shù)", err)
    fmt.Printf("插入數(shù)據(jù)條數(shù)：%d\n", count)
    id, err := result.LastInsertId()
    errDeal("查看最后插入數(shù)據(jù)的id", err)
    fmt.Printf("最后插入數(shù)據(jù)的id：%d\n", id)

    // 插入數(shù)據(jù)---------------------方法2，先創(chuàng)建一個(gè)預(yù)處理語(yǔ)句，再執(zhí)行
    stmt, err := db.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc)" +
        " VALUES(?,now(),?,?,?)")
    result1, err1 := stmt.Exec("admin", "127.0.0.1", "登錄", "登錄成功")
    errDeal("方法2插入數(shù)據(jù)", err1)
    fmt.Println(result1.LastInsertId())

    // 刪除數(shù)據(jù)
    result2, err := db.Exec("DELETE FROM admin_log WHERE id=?", 2014)
    fmt.Println(result2.RowsAffected())

    // 更新數(shù)據(jù)
    result3, err := db.Exec("UPDATE admin_log SET log_ip=? WHERE id=?", "192.168.8.9", 2017)
    fmt.Println(result3.RowsAffected())

    //=============================================================================================查詢(xún)
    // 查詢(xún)單條數(shù)據(jù)
    // 先定義存儲(chǔ)查詢(xún)結(jié)果的變量
    var rid int
    var username, time, ip, logType string
    var desc, remark, spare interface{} // 如果字段中可能出現(xiàn)值為nil的情況，可以將變量申明為interface{}類(lèi)型
    err2 := db.QueryRow("SELECT id,admin_username,log_time,log_ip,log_type,log_desc,remark,spare" +
        " FROM admin_log WHERE id=?", 2017).Scan(&rid, &username, &time, &ip, &logType, &desc, &remark, &spare) // 傳入的是變量的指針
    errDeal("查詢(xún)單條數(shù)據(jù)", err2)
    fmt.Printf("id=%d,username=%s,time=%s,ip=%s,logType=%s,desc=%s,remark=%v,spare=%v\n", rid, username, time, ip, logType, desc, remark, spare)

    // 查詢(xún)多條數(shù)據(jù)
    rows, err3 := db.Query("SELECT admin_username,log_time,log_ip,log_type,log_desc" +
        " FROM admin_log WHERE id=? OR id=?", 2017, 2019)
    errDeal("查詢(xún)多條數(shù)據(jù)", err3)
    // 對(duì)多條數(shù)據(jù)進(jìn)行遍歷
    for rows.Next() {
        err4 := rows.Scan(&username, &time, &ip, &logType, &desc)
        errDeal("遍歷多條數(shù)據(jù)", err4)
        fmt.Printf("username=%s,time=%s,ip=%s,logType=%s,desc=%s\n", username, time, ip, logType, desc)
    }

    // 如果查詢(xún)中不指定具體字段，使用*
    rows, err33 := db.Query("SELECT *" +
        " FROM admin_log WHERE id>?", 2017)
    errDeal("查詢(xún)多條數(shù)據(jù)", err33)
    // 查詢(xún)所有字段名，返回string切片
    columes, err333 := rows.Columns()
    errDeal("rows.Columns()方法調(diào)用", err333)
    fmt.Printf("%T----%v\n", columes, columes)
    var scanColumes = make([]interface{}, len(columes))
    var values = make([]interface{}, len(columes))
    for index, _ := range  scanColumes {
        scanColumes[index] = &values[index]
    }
    for rows.Next() {
        err4 := rows.Scan(scanColumes...)
        errDeal("遍歷多條數(shù)據(jù)", err4)
        for i, val := range values {
            if strings.EqualFold(checkType(val), "[]uint8") {
                fmt.Printf("%s(%T)==%s\t", columes[i], val, val)
            } else {
                fmt.Printf("%s(%T)==%v\t", columes[i], val, val)
            }

        }
        fmt.Println()
    }

    // ==================================================================================================事務(wù)
    // 開(kāi)啟事務(wù)，tx是從連接池中取出一個(gè)連接，在關(guān)閉之前都是使用這個(gè)連接，提交事務(wù)和回滾事務(wù)都是操作tx
    tx, err5 := db.Begin()
    errDeal("開(kāi)啟事務(wù)", err5)
    _, err6 := tx.Exec("UPDATE admin_log SET log_desc=? WHERE id=?", "測(cè)試事務(wù)222", 2019)
    //if err6 != nil {
    if err6 == nil {
        tx.Rollback() // 回滾
    }
    tx.Commit() // 提交

    // 批量數(shù)據(jù)插入
    tx, err7 := db.Begin()
    errDeal("數(shù)據(jù)批量插入，開(kāi)啟事務(wù)", err7)
    insertValues := [][]interface{}{{"admin", "127.0.0.1", "登錄", "登錄成功"},{"admin", "127.0.0.1", "刪除", "刪除數(shù)據(jù)"},{"admin", "127.0.0.1", "退出", "退出系統(tǒng)"}}
    stmt, err8 := tx.Prepare("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")
    errDeal("數(shù)據(jù)批量插入，預(yù)處理", err8)
    defer stmt.Close()
    // 通過(guò)循環(huán)將每條SQL的參數(shù)寫(xiě)到目標(biāo)表緩沖區(qū)。
    for _, val := range  insertValues {
        _, err := stmt.Exec(val...)
        if err != nil {
            fmt.Printf("出現(xiàn)錯(cuò)誤回滾，錯(cuò)誤信息：%v", err)
            tx.Rollback()
        }
    }
    tx.Commit()

    // 使用sqlx批量數(shù)據(jù)插入
    xdb, err9 := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/betting?charset=utf8")
    errDeal("sqlx連接數(shù)據(jù)庫(kù)", err9)
    defer xdb.close()
    txx, err10 := xdb.Beginx()
    errDeal("sqlx開(kāi)啟事務(wù)", err10)
    insertValuesx := [][]interface{}{{"admin", "127.0.0.1", "登錄", "登錄成功X"},{"admin", "127.0.0.1", "刪除", "刪除數(shù)據(jù)X"},{"admin", "127.0.0.1", "退出", "退出系統(tǒng)X"}}
    stmtx, err11 := txx.Preparex("INSERT INTO admin_log(admin_username,log_time,log_ip,log_type,log_desc) VALUES(?,now(),?,?,?)")
    errDeal("數(shù)據(jù)批量插入，預(yù)處理", err11)
    defer stmtx.Close()
    // 通過(guò)循環(huán)將每條SQL的參數(shù)寫(xiě)到目標(biāo)表緩沖區(qū)。
    for _, val := range insertValuesx {
        _, err := stmtx.Exec(val...)
        if err != nil {
            fmt.Printf("sqlx出現(xiàn)錯(cuò)誤回滾，錯(cuò)誤信息：%v", err)
            txx.Rollback()
        }
    }
    txx.Commit()
}

func errDeal(info string, err error) {
    if err != nil {
        panic(fmt.Sprintf("%s，錯(cuò)誤信息：%v", info, err))
    }
}

func checkType(val interface{}) string {
    switch val.(type) {
    case []uint8 :
        return "[]uint8"
    }
    return ""
}
向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手