gobox中mysql操作

今天来说下使用gobox中mysql操作相关

说明

本包的driver部分使用了go-sql-driver：https://github.com/go-sql-driver/mysql

用法示例

示例表结构为：

| demo  | CREATE TABLE `demo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `add_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `edit_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `name` varchar(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `status` tinyint(4) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `status` (`status`),
  KEY `add_time` (`add_time`),
  KEY `edit_time` (`edit_time`)
) ENGINE=InnoDB AUTO_INCREMENT=478 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |

package main

import (
    "github.com/goinbox/mysql"

    "database/sql"
    "strconv"
    "fmt"
)

type tableDemoRowItem struct {
    Id       int64
    AddTime  string
    EditTime string
    Name     string
    Status   int
}

var client *mysql.Client

func main() {
    config := mysql.NewConfig("root", "123", "127.0.0.1", "3306", "gobox-demo")
    client, _ = mysql.NewClient(config, nil)

    client.Exec("DELETE FROM demo")

    fmt.Println("testClientExec：")
    testClientExec()
    fmt.Println("")

    fmt.Println("testClientQuery：")
    testClientQuery()
    fmt.Println("")

    fmt.Println("testClientQueryRow：")
    testClientQueryRow()
    fmt.Println("")

    fmt.Println("testClientTrans：")
    testClientTrans()
    fmt.Println("")
}

func testClientExec() {
    result, err := client.Exec("INSERT INTO demo (name) VALUES (?),(?)", "a", "b")
    if err != nil {
        fmt.Println("exec error: " + err.Error())
    } else {
        li, err := result.LastInsertId()
        if err != nil {
            fmt.Println("lastInsertId error: " + err.Error())
        } else {
            fmt.Println("lastInsertId: " + strconv.FormatInt(li, 10))
        }

        rf, err := result.RowsAffected()
        if err != nil {
            fmt.Println("rowsAffected error: " + err.Error())
        } else {
            fmt.Println("rowsAffected: " + strconv.FormatInt(rf, 10))
        }
    }
}

func testClientQuery() {
    rows, err := client.Query("SELECT * FROM demo WHERE name IN (?,?)", "a", "b")
    if err != nil {
        fmt.Println("query error: " + err.Error())
    } else {
        for rows.Next() {
            item := new(tableDemoRowItem)
            err = rows.Scan(&item.Id, &item.AddTime, &item.EditTime, &item.Name, &item.Status)
            if err != nil {
                fmt.Println("rows scan error: " + err.Error())
            } else {
                fmt.Println(item)
            }
        }
    }
}

func testClientQueryRow() {
    row := client.QueryRow("SELECT * FROM demo WHERE name = ?", "a")
    item := new(tableDemoRowItem)
    err := row.Scan(&item.Id, &item.AddTime, &item.EditTime, &item.Name, &item.Status)
    if err != nil {
        if err == sql.ErrNoRows {
            fmt.Println("no rows: " + err.Error())
        } else {
            fmt.Println("row scan error: " + err.Error())
        }
    } else {
        fmt.Println(item)
    }
}

func testClientTrans() {
    client.Begin()

    row := client.QueryRow("SELECT * FROM demo WHERE name = ?", "a")
    item := new(tableDemoRowItem)
    err := row.Scan(&item.Id, &item.AddTime, &item.EditTime, &item.Name, &item.Status)
    if err != nil {
        fmt.Println("row scan error: " + err.Error())
    } else {
        fmt.Println(item)
    }

    client.Commit()

    err = client.Rollback()
    fmt.Println(err)
}

程序输出：

testClientExec：
lastInsertId: 474
rowsAffected: 2

testClientQuery：
&{474 2018-07-21 09:53:43 2018-07-21 09:53:43 a 0}
&{475 2018-07-21 09:53:43 2018-07-21 09:53:43 b 0}

testClientQueryRow：
&{474 2018-07-21 09:53:43 2018-07-21 09:53:43 a 0}

testClientTrans：
&{474 2018-07-21 09:53:43 2018-07-21 09:53:43 a 0}
Not in trans

ligang@vm-xubuntu ~/tmp/go $ go run main.go 
testClientExec：
lastInsertId: 476
rowsAffected: 2

testClientQuery：
&{476 2018-07-21 09:53:52 2018-07-21 09:53:52 a 0}
&{477 2018-07-21 09:53:52 2018-07-21 09:53:52 b 0}

testClientQueryRow：
&{476 2018-07-21 09:53:52 2018-07-21 09:53:52 a 0}

testClientTrans：
&{476 2018-07-21 09:53:52 2018-07-21 09:53:52 a 0}
Not in trans

欢迎大家使用，使用中有遇到问题随时反馈，我们会尽快响应，谢谢！