2019-05-17

连接mysql

github.com/go-sql-driver/mysql

• 引入包

import (
    "fmt"
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

• 连接

db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/cxy?charset=utf8")
    if err != nil {
        fmt.Println("连接错误")
        return
    }
    fmt.Println("连接成功")

• 添加

insert into T_USER(id,name) values(null, 'jeking')

{
    _, err := db.Exec("insert into T_USER(id,name) values(null, 'jeking')")
    if err != nil {
        fmt.Println("添加失败")
    }
    fmt.Println("添加成功")
}

• 更新

update T_USER set name='sujeking' where name='jeking'

{
    _, err := db.Exec("update T_USER set name='sujeking' where name='jeking'")
    if err != nil {
        fmt.Println("更新失败")
        return
    }
    fmt.Println("更新成功")
}

• 删除

delete from T_USER where name='sujeking'

{
    _, err := db.Exec("delete from T_USER where name='sujeking'")
    if err != nil {
        fmt.Println("删除失败")
        return
    }
    fmt.Println("删除成功")
}

• 查询/指定字段

select name from T_USER

{
    rows, err := db.Query("select name from T_USER") //db.QueryOne
    if err != nil {
        fmt.Println("查询失败")
        return
    }
    columns, err := rows.Columns() //获取列名
    fmt.Println(columns)

    for rows.Next() {
        var name string //此处的变量需要和sql的字段一样 
        err = rows.Scan(&name)
        if err != nil {
            panic(err)
        }
        fmt.Println(name)
    }
}

• 查询/全部

select * from T_USER

{
        type Result interface{}
        rows, err := db.Query("select * from T_USER")
        if err != nil {
            fmt.Println("查询失败")
            return
        }
        columns, err := rows.Columns() //获取列名
        scanArgs := make([]interface{}, len(columns))
        values := make([]interface{}, len(columns))
        for i := range values {
            scanArgs[i] = &values[i]
        }

        fmt.Println(values...);

        for rows.Next() {
            //将行数据保存到record字典
            err = rows.Scan(scanArgs...)
            record := make(map[string]string)
            for i, col := range values {
                if col != nil {
                    record[columns[i]] = string(col.([]byte))
                }
            }
            fmt.Println(record)
        }
    }