golang-mysql sql 例子

1、查询

　　　一、查询多条

func ExampleDB_QueryContext() {
    age := 27
    rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()   // 释放了连接
    names := make([]string, 0)

    for rows.Next() {
        var name string
        if err := rows.Scan(&name); err != nil {
            // Check for a scan error.
            // Query rows will be closed with defer.
            log.Fatal(err)
        }
        names = append(names, name)
    }
    // If the database is being written to ensure to check for Close
    // errors that may be returned from the driver. The query may
    // encounter an auto-commit error and be forced to rollback changes.

　　// 如果正在写入数据库以确保检查可能从驱动程序返回的关闭错误。查询可能会遇到自动提交错误并被迫回滚更改。
    rerr := rows.Close()
    if rerr != nil {
        log.Fatal(rerr)
    }

    // Rows.Err will report the last error encountered by Rows.Scan.
　　// Rows.Err 将报告 Rows.Scan 遇到的最后一个错误。　
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
}

　　　　二、查询单条 【有可能没有结果】

　　　　　　　　err == sql.ErrNoRows  没有结果

func ExampleDB_QueryRowContext() {
    id := 123
    var username string
    var created time.Time
　　// 这里调用 Scan 就释放了连接　
    err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
    switch {
    case err == sql.ErrNoRows:     // 可能是没有结果
        log.Printf("no user with id %d\n", id)
    case err != nil:
        log.Fatalf("query error: %v\n", err)
    default:
        log.Printf("username is %q, account created on %s\n", username, created)
    }
}

　　　三、预查询

func ExampleStmt_QueryRowContext() {
    // In normal use, create one Stmt when your process starts.
    stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    // Then reuse it each time you need to issue the query.
    id := 43
    var username string
    err = stmt.QueryRowContext(ctx, id).Scan(&username)
    switch {
    case err == sql.ErrNoRows:
        log.Fatalf("no user with id %d", id)
    case err != nil:
        log.Fatal(err)
    default:
        log.Printf("username is %s\n", username)
    }
}

 

　　

 

 

 

 

 

 

 

 

2、增删改

　　1、直接增删改

func ExampleDB_ExecContext() {
    id := 47
    result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := result.RowsAffected()   // 受影响的行数
    if err != nil {
        log.Fatal(err)
    }
    if rows != 1 {
        log.Fatalf("expected to affect 1 row, affected %d", rows)
    }
}

　　2、预增删改 【推荐使用3】

　　　　sql 语句一样，仅仅是传入字段的参数不一样而已。

func ExampleDB_Prepare() {
    projects := []struct {
        mascot  string
        release int
    }{
        {"tux", 1991},
        {"duke", 1996},
        {"gopher", 2009},
        {"moby dock", 2013},
    }

    stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

    for id, project := range projects {
        if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
            log.Fatal(err)
        }
    }
}

　　3、预增删改+事务 【多条推荐使用】也可以使用sqlx

func ExampleTx_Prepare() {
    projects := []struct {
        mascot  string
        release int
    }{
        {"tux", 1991},
        {"duke", 1996},
        {"gopher", 2009},
        {"moby dock", 2013},
    }

    tx, err := db.Begin()
    if err != nil {
        log.Fatal(err)
    }
    defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.

    stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

    for id, project := range projects {
        if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
            log.Fatal(err)
        }
    }
    if err := tx.Commit(); err != nil {
        log.Fatal(err)
    }
}

　　4、增删改设置隔离级别+ 事务

func ExampleDB_BeginTx() {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
    if err != nil {
        log.Fatal(err)
    }
    id := 37
    _, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
    if execErr != nil {
        _ = tx.Rollback()
        log.Fatal(execErr)
    }
    if err := tx.Commit(); err != nil {
        log.Fatal(err)
    }
}

　　5、长连接 【慎用】

func ExampleConn_ExecContext() {
    // A *DB is a pool of connections. Call Conn to reserve a connection for
    // exclusive use.
    conn, err := db.Conn(ctx)
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close() // Return the connection to the pool.
    id := 41
    result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id)
    if err != nil {
        log.Fatal(err)
    }
    rows, err := result.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    if rows != 1 {
        log.Fatalf("expected single row affected, got %d rows affected", rows)
    }
}

　　6、事务+回滚

func ExampleTx_ExecContext() {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
    if err != nil {
        log.Fatal(err)
    }
    id := 37
    _, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
    if execErr != nil {
        if rollbackErr := tx.Rollback(); rollbackErr != nil {
            log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
        }
        log.Fatalf("update failed: %v", execErr)
    }
    if err := tx.Commit(); err != nil {
        log.Fatal(err)
    }
}

func ExampleTx_Rollback() {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
    if err != nil {
        log.Fatal(err)
    }
    id := 53
    _, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id)
    if err != nil {
        if rollbackErr := tx.Rollback(); rollbackErr != nil {
            log.Fatalf("update drivers: unable to rollback: %v", rollbackErr)
        }
        log.Fatal(err)
    }
    _, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id)
    if err != nil {
        if rollbackErr := tx.Rollback(); rollbackErr != nil {
            log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr)
        }
        log.Fatal(err)
    }
    if err := tx.Commit(); err != nil {
        log.Fatal(err)
    }
}