使用Axum+PostgreSQL+SQLx查询数据库时列解码报错求助

以下是针对你遇到的查询解码错误的具体排查方向和解决方法：

错误信息 #

error: error occurred while decoding column 0: expected value at line 13 column 5
   --> src/handler.rs:98:16
    |
98  |       let user = sqlx::query_as!
    |  __^
99  | |         User,
100 | |         "SELECT * FROM users WHERE pan = $1",
101 | |         body.pan
102 | |     )
    | |__^
    |
 = note: this error originates in the macro $crate::sqlx_macros::expand_query which comes from the expansion of the macro sqlx::query_as (in Nightly builds, run with -Z macro-backtrace for more info)

相关代码 #

let user = sqlx::query_as!(
        User,
        "SELECT * FROM users WHERE pan = $1",
        body.pan
    )
    .fetch_optional(&data.db)
    .await

依赖配置 #

[dependencies]
argon2 = "0.5.0"
axum = "0.6.18"
axum-extra = { version = "0.7.4", features = ["cookie"] }
chrono = { version = "0.4.26", features = ["serde"] }
dotenv = "0.15.0"
sqlx = { version = "0.7.0", features = ["runtime-async-std-native-tls", "postgres", "chrono", "uuid"] }
time = "0.3.22"
tokio = { version = "1.29.1", features = ["full"] }
uuid = { version = "1.4.0", features = ["serde", "v4"] }

排查与解决方法 #

• 替换SELECT *，明确指定列顺序：SQLx的query_as!是按列的返回顺序而非字段名匹配结构体的。如果数据库表的列顺序和User结构体的字段顺序不一致，会直接导致类型不匹配的解码错误。修改查询语句为明确列出列，且顺序与结构体字段完全一致，示例：

  let user = sqlx::query_as!(
          User,
          "SELECT id, pan, email, created_at FROM users WHERE pan = $1",
          body.pan
      )
      .fetch_optional(&data.db)
      .await
  

• 严格核对字段类型匹配：逐个确认User结构体字段与PostgreSQL表列的类型兼容性：

  • PostgreSQL的uuid类型对应Rust的uuid::Uuid
  • timestamp with time zone对应chrono::DateTime<Utc>（你的依赖已启用sqlx的chrono特性，符合要求）
  • text/varchar对应String（非空列）或Option<String>（允许为空的列）
  • 若表中有JSON类型列，需给sqlx添加json特性，结构体字段使用serde_json::Value或实现了serde序列化的自定义类型

• 对齐空值与Option类型：数据库中允许为空的列，结构体必须用Option<T>类型；非空列则用T类型，否则空值解码到非Option字段会触发错误。

• 开启SQLx日志定位问题：给sqlx添加log特性，配合tracing或log库打印SQL执行细节和返回结果，可直接看到哪一列的数据不符合预期，快速定位问题。

内容的提问来源于stack exchange，提问作者dhiraka