快速指南：通过Go语言操作PostgreSQL数据库

go get github.com/lib/pq

import (
    "database/sql"
    _ "github.com/lib/pq" // 导入但不直接使用，触发 init 函数
    "log"
)

func main() {
    // 连接信息，替换成你的实际信息
    connStr := "user=your_user dbname=your_db password=your_password host=your_host sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    log.Println("Successfully connected to PostgreSQL!")
}

// 插入数据
sqlStatement := `
INSERT INTO users (name, email)
VALUES ($1, $2)
RETURNING id`
id := 0
err = db.QueryRow(sqlStatement, "John Doe", "john.doe@example.com").Scan(&id)
if err != nil {
    log.Fatal(err)
}
log.Println("New record ID is:", id)

// 查询数据
sqlStatement := `SELECT id, name, email FROM users WHERE id = $1`
var id int
var name string
var email string
row := db.QueryRow(sqlStatement, 1)
switch err := row.Scan(&id, &name, &email); err {
case sql.ErrNoRows:
    log.Println("No rows were returned!")
case nil:
    log.Println("ID:", id, "Name:", name, "Email:", email)
default:
    log.Fatal(err)
}

// 更新数据
sqlStatement := `UPDATE users SET email = $2 WHERE id = $1`
res, err := db.Exec(sqlStatement, 1, "john.new@example.com")
if err != nil {
    log.Fatal(err)
}
count, err := res.RowsAffected()
if err != nil {
    log.Fatal(err)
}
log.Println("Rows affected:", count)

// 删除数据
sqlStatement := `DELETE FROM users WHERE id = $1`
res, err := db.Exec(sqlStatement, 1)
if err != nil {
    log.Fatal(err)
}
count, err := res.RowsAffected()
if err != nil {
    log.Fatal(err)
}
log.Println("Rows affected:", count)

// 开启事务
tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

// 执行多个 SQL 操作
sqlStatement1 := `UPDATE accounts SET balance = balance - $1 WHERE id = $2`
_, err = tx.Exec(sqlStatement1, 100, 1)
if err != nil {
    tx.Rollback() // 回滚事务
    log.Fatal(err)
}

sqlStatement2 := `UPDATE accounts SET balance = balance + $1 WHERE id = $2`
_, err = tx.Exec(sqlStatement2, 100, 2)
if err != nil {
    tx.Rollback() // 回滚事务
    log.Fatal(err)
}

// 提交事务
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}

log.Println("Transaction completed successfully!")

db.SetMaxOpenConns(10) // 设置最大打开连接数
db.SetMaxIdleConns(5)  // 设置最大空闲连接数
db.SetConnMaxLifetime(time.Hour) // 设置连接的最大生存时间

// 预编译语句
stmt, err := db.Prepare("SELECT id, name FROM users WHERE name = $1")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

// 执行预编译语句
var id int
var name string
err = stmt.QueryRow("John Doe").Scan(&id, &name)
if err != nil {
    log.Fatal(err)
}

log.Println("ID:", id, "Name:", name)

result, err := someDatabaseOperation()
if err != nil {
    // 处理错误
    log.Printf("Error: %v", err)
    // 可以选择返回错误，重试操作，或者记录错误并继续
    return err
}
// 使用 result