Go语言与PostgreSQL：正确插入数据的参数占位符使用指南

CREATE TABLE Users (
  user_id BIGSERIAL PRIMARY KEY,
  email VARCHAR(50) NOT NULL,
  password_hash VARCHAR(100) NOT NULL,
  points INT DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq" // PostgreSQL 驱动
    "golang.org/x/crypto/bcrypt" // bcrypt 用于密码哈希
)

// OpenConnection 模拟一个打开数据库连接的函数
func OpenConnection() *sql.DB {
    connStr := "user=postgres password=your_password dbname=your_db sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    // 尝试连接以确保数据库可用
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("成功连接到PostgreSQL数据库！")
    return db
}

func main() {
    conn := OpenConnection()
    defer conn.Close()

    email := "test@example.com"
    rawPassword := "securepassword123"

    // 生成密码哈希
    passwordHash, err := bcrypt.GenerateFromPassword([]byte(rawPassword), bcrypt.DefaultCost)
    if err != nil {
        log.Fatal("生成密码哈希失败:", err)
    }

    // 错误的插入语句：使用了 '?' 占位符
    // res, err := conn.Exec("INSERT INTO users (email, password_hash) VALUES (?, ?)", email, passwordHash)
    // if err != nil {
    //  log.Printf("插入数据失败: %v", err)
    //  // 错误输出示例：pq: P:"51" S:"ERROR" L:"1002" C:"42601" M:"syntax error at or near \",\"" F:"scan.l" R:"scanner_yyerror"
    //  return
    // }
    // rowsAffected, _ := res.RowsAffected()
    // fmt.Printf("成功插入 %d 行数据 (使用错误占位符，此代码不会执行到这里)\n", rowsAffected)
}

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
    "golang.org/x/crypto/bcrypt"
)

// OpenConnection 模拟一个打开数据库连接的函数
func OpenConnection() *sql.DB {
    // 请替换为您的PostgreSQL连接字符串
    connStr := "user=postgres password=your_password dbname=your_db sslmode=disable"
    db, err := sql.Open("postgres", connStr)
    if err != nil {
        log.Fatal(err)
    }
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("成功连接到PostgreSQL数据库！")
    return db
}

func main() {
    conn := OpenConnection()
    defer conn.Close()

    email := "newuser@example.com"
    rawPassword := "anothersecurepassword"

    passwordHash, err := bcrypt.GenerateFromPassword([]byte(rawPassword), bcrypt.DefaultCost)
    if err != nil {
        log.Fatal("生成密码哈希失败:", err)
    }

    // 正确的插入语句：使用了 '$1', '$2' 占位符
    insertSQL := "INSERT INTO Users (email, password_hash) VALUES ($1, $2)"
    res, err := conn.Exec(insertSQL, email, passwordHash)
    if err != nil {
        log.Fatal("插入数据失败:", err)
    }

    rowsAffected, err := res.RowsAffected()
    if err != nil {
        log.Fatal("获取受影响行数失败:", err)
    }
    fmt.Printf("成功插入 %d 行数据。\n", rowsAffected)

    // 如果需要获取新插入的user_id (BIGSERIAL类型)
    var newUserID int64
    err = conn.QueryRow("INSERT INTO Users (email, password_hash) VALUES ($1, $2) RETURNING user_id", "user_with_id@example.com", passwordHash).Scan(&newUserID)
    if err != nil {
        log.Fatal("插入数据并获取ID失败:", err)
    }
    fmt.Printf("成功插入用户，新用户ID为: %d\n", newUserID)
}