Go语言与PostgreSQL：解决lib/pq驱动的参数占位符语法错误

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

import (
  "database/sql"
  _ "github.com/lib/pq" // PostgreSQL driver
  "golang.org/x/crypto/bcrypt" // Corrected import for bcrypt
  "fmt"
  // ... 其他必要的包
)

// 假设 conn 已经是一个有效的 *sql.DB 连接
func insertUserIncorrect(conn *sql.DB, email string, rawPassword string) error {
  passwordHash, err := bcrypt.GenerateFromPassword([]byte(rawPassword), bcrypt.DefaultCost)
  if err != nil {
    return fmt.Errorf("failed to hash password: %w", err)
  }

  // 错误示范：使用 ? 作为参数占位符
  res, err := conn.Exec("INSERT INTO users (email, password_hash) VALUES (?, ?)", email, string(passwordHash))
  if err != nil {
    return fmt.Errorf("failed to insert user: %w", err)
  }

  rowsAffected, _ := res.RowsAffected()
  fmt.Printf("Rows affected: %d\n", rowsAffected)
  return nil
}

pq: P:"51" S:"ERROR" L:"1002" C:"42601" M:"syntax error at or near \",\"" F:"scan.l" R:"scanner_yyerror"

import (
  "database/sql"
  _ "github.com/lib/pq" // PostgreSQL driver
  "golang.org/x/crypto/bcrypt" // Corrected import for bcrypt
  "fmt"
  "log"
)

// OpenConnection 示例函数，用于建立数据库连接
func OpenConnection() (*sql.DB, error) {
  // 替换为你的PostgreSQL连接字符串
  // 例如: "user=postgres password=yourpassword dbname=yourdb sslmode=disable"
  connStr := "user=postgres password=mysecretpassword dbname=mydb sslmode=disable"
  db, err := sql.Open("pq", connStr)
  if err != nil {
    return nil, fmt.Errorf("failed to open database connection: %w", err)
  }
  // 验证数据库连接
  err = db.Ping()
  if err != nil {
    db.Close() // 如果ping失败，关闭连接
    return nil, fmt.Errorf("failed to connect to database: %w", err)
  }
  return db, nil
}

// insertUserCorrect 正确的插入用户函数
func insertUserCorrect(db *sql.DB, email string, rawPassword string) error {
  passwordHash, err := bcrypt.GenerateFromPassword([]byte(rawPassword), bcrypt.DefaultCost)
  if err != nil {
    return fmt.Errorf("failed to hash password: %w", err)
  }

  // 正确示范：使用 $1, $2 作为参数占位符
  query := "INSERT INTO users (email, password_hash) VALUES ($1, $2)"

  // 使用 db.Exec 直接执行，适用于非预处理场景
  res, err := db.Exec(query, email, string(passwordHash))
  if err != nil {
    return fmt.Errorf("failed to insert user: %w", err)
  }

  rowsAffected, err := res.RowsAffected()
  if err != nil {
    return fmt.Errorf("failed to get rows affected: %w", err)
  }
  log.Printf("Successfully inserted user %s. Rows affected: %d\n", email, rowsAffected)
  return nil
}

func main() {
  db, err := OpenConnection()
  if err != nil {
    log.Fatalf("Error opening database: %v", err)
  }
  defer db.Close() // 确保连接在使用完毕后关闭

  // 示例调用
  testEmail := "test@example.com"
  testPassword := "securepassword123"

  err = insertUserCorrect(db, testEmail, testPassword)
  if err != nil {
    log.Printf("Error inserting user: %v", err)
  } else {
    log.Println("User inserted successfully!")
  }

  // 尝试插入另一个用户
  err = insertUserCorrect(db, "another@example.com", "anotherpassword")
  if err != nil {
    log.Printf("Error inserting another user: %v", err)
  } else {
    log.Println("Another user inserted successfully!")
  }
}

// insertUserWithPrepare 演示如何使用预处理语句插入用户
func insertUserWithPrepare(db *sql.DB, email string, rawPassword string) error {
  passwordHash, err := bcrypt.GenerateFromPassword([]byte(rawPassword), bcrypt.DefaultCost)
  if err != nil {
    return fmt.Errorf("failed to hash password: %w", err)
  }

  // 1. 准备语句
  // 注意：Prepare 返回的 stmt 必须在使用完毕后关闭
  stmt, err := db.Prepare("INSERT INTO users (email, password_hash) VALUES ($1, $2)")
  if err != nil {
    return fmt.Errorf("failed to prepare statement: %w", err)
  }
  defer stmt.Close() // 确保预处理语句在使用完毕后关闭

  // 2. 执行语句
  res, err := stmt.Exec(email, string(passwordHash))
  if err != nil {
    return fmt.Errorf("failed to execute prepared statement: %w", err)
  }

  rowsAffected, err := res.RowsAffected()
  if err != nil {
    return fmt.Errorf("failed to get rows affected from prepared statement: %w", err)
  }
  log.Printf("Successfully inserted user %s via prepared statement. Rows affected: %d\n", email, rowsAffected)
  return nil
}

// 可以在 main 函数中这样调用：
/*
  err = insertUserWithPrepare(db, "prepared@example.com", "preparedpassword")
  if err != nil {
    log.Printf("Error inserting user with prepare: %v", err)
  } else {
    log.Println("User inserted successfully via prepared statement!")
  }
*/