Go Mysql增删改查

  • go mysql增删改查 实例代码
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    package main

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

    func checkErr(err error) {
    if err != nil{
    panic(err)
    }
    }

    func main() {
    db , err := sql.Open("mysql","root:@tcp(127.0.0.1:3306)/test")
    /**
    连接参数其他形式
    mysql","user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname
    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
    */
    checkErr(err)

    //插入操作
    stmt, err := db.Prepare(`INSERT go_test (username,password) values (?,?)`)
    checkErr(err)
    res, err := stmt.Exec("tony", "hello")
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)

    //查询操作
    rows, err := db.Query("SELECT * FROM go_test")
    checkErr(err)

    for rows.Next() {
    var id int
    var username string
    var password string
    rows.Columns()
    err = rows.Scan(&id, &username, &password)
    checkErr(err)
    fmt.Println(id)
    fmt.Println(username)
    fmt.Println(password)
    }


    //构造scanArgs、values两个数组,scanArgs的每个值指向values相应值的地址
    columns, _ := rows.Columns() //获取字段 [id username password]

    scanArgs := make([]interface{}, len(columns)) //[<nil> <nil> <nil>]
    values := make([]interface{}, len(columns))

    for i := range values {
    scanArgs[i] = &values[i]
    }


    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)
    }


    //修改操作
    stmt1, err := db.Prepare(`UPDATE go_test SET username=?,password=? WHERE id=?`)
    checkErr(err)
    res1, err := stmt1.Exec("bird", "bird", 13)
    checkErr(err)
    num, err := res1.RowsAffected() //1修改成功 0 修改失败
    checkErr(err)
    fmt.Println(num)

    //删除操作
    stmt2, err := db.Prepare(`DELETE FROM go_test WHERE id=?`)
    checkErr(err)
    res2, err := stmt2.Exec(13)
    checkErr(err)
    num2, err := res2.RowsAffected()
    checkErr(err)
    fmt.Println(num2)



    }