postgresql - Sqlx 获取准备好的语句

Question

我正在尝试使用准备好的语句从 postgress 表中获取一些数据

如果我尝试使用 database.Get() 返回所有内容。

表格:

create table accounts
(
  id            bigserial not null
    constraint accounts_pkey
    primary key,
  identificator text      not null,
  password      text      not null,
  salt          text      not null,
  type          smallint  not null,
  level         smallint  not null,
  created_at    timestamp not null,
  updated       timestamp not null,
  expiry_date   timestamp,
  qr_key        text
);

账户结构:

type Account struct {
    ID            string `db:"id"`
    Identificator string `db:"identificator"`

    Password   string         `db:"password"`
    Salt       string         `db:"salt"`
    Type       int            `db:"type"`
    Level      int            `db:"level"`
    ExpiryDate time.Time      `db:"expiry_date"`
    CreatedAt  time.Time      `db:"created_at"`
    UpdateAt   time.Time      `db:"updated_at"`
    QrKey      sql.NullString `db:"qr_key"`
}

顺便说一句，我试过使用 ?而不是 $1 和 $2

stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)

if err != nil {
    panic(err)
}
accounts := []account.Account{}
err = stmt.Get(&accounts, "asd", 123)
if err != nil {
    panic(err)
}

我得到的错误是

"errorMessage": "scannable dest type slice with \u003e1 columns (10) in result",

在表中没有记录我试图从帐户(结构)中删除除 ID 之外的所有字段，但是它不起作用。

Answer 1

描述了 sqlx 的文档 Get and Select作为:

Get and Select use rows.Scan on scannable types and rows.StructScan on non-scannable types. They are roughly analagous to QueryRow and Query, where Get is useful for fetching a single result and scanning it, and Select is useful for fetching a slice of results:

要获取单个记录，请使用 Get。

stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)
var account Account
err = stmt.Get(&account, "asd", 123)

如果您的查询返回多条记录，请使用 Select with statement as:

stmt, err := database.Preparex(`SELECT * FROM accounts where identificator = $1 and type = $2`)
var accounts []Account
err = stmt.Select(&accounts, "asd", 123)

在您的情况下，如果您使用 stmt.Select 而不是 stmt.Get。它会起作用。