update 与 delete
主线位置:写入主线的 update 分支,以及和它形状相同的 delete。
am.update(&db)是ActiveModelTrait::update:before_save(self, db, false)→Entity::update(am)造一个UpdateOne<A>→exec(db)→after_save。这一页讲UpdateOne怎么用主键做WHERE、用Set的列做SET,执行时三种情况(有列要写、没有列要写、没匹配到行)各发什么、报什么;然后是不经过ActiveModel的update_many,最后是delete一族。钩子在 save 与 ActiveModelBehavior。
flowchart TB
am[ActiveModel] --> pk{"主键有 NotSet"}
pk -->|是| e1["存 PrimaryKeyNotSet"]
pk -->|否| setc["Set 的非主键列进 SET"]
setc --> noop{"SET 为空"}
noop -->|是| sel["按主键 SELECT 一次"]
noop -->|否| upd["UPDATE ... RETURNING"]
upd -->|无返回行| e2[RecordNotUpdated]
upd -->|一行| model[Model]
sel -->|无行| e3[RecordNotFound]
sel -->|一行| model
实验沿用 ActiveValue 与 ActiveModel 的 item 表(id serial primary key、sku text not null unique、name text not null、qty int not null default 0、note text),每个程序开头重建表并插入三行:(1, 'a1', 'apple', 3)、(2, 'b2', 'banana', 5)、(3, 'c3', 'cherry', 7)。SQL 取自 sqlx 的语句日志(sqlx=info)。平台 M2 Max、macOS、rustc 1.99.0、Postgres 16.14,sea-orm 2.0.4;1.1.20 的对照在单独的 crate 里。
Update::one:先 WHERE,后 SET
Update::one 分两遍扫列:
//
// (non-doc comment for maintainers)
// Ideally, we would make this method fallible instead of stashing and delaying the error.
// But that's a bigger breaking change.
pub fn one<E, A>(model: A) -> UpdateOne<A>
where
E: EntityTrait,
A: ActiveModelTrait<Entity = E>,
{
let mut myself = ValidatedUpdateOne {
query: UpdateStatement::new()
.table(A::Entity::default().table_ref())
.to_owned(),
model,
};
// Build the SQL condition from the primary key columns.
for key in <A::Entity as EntityTrait>::PrimaryKey::iter() {
let col = key.into_column();
match myself.model.get(col) {
ActiveValue::Set(value) | ActiveValue::Unchanged(value) => {
myself = myself.filter(col.eq(value));
}
ActiveValue::NotSet => {
return UpdateOne(Err(DbErr::PrimaryKeyNotSet { ctx: "UpdateOne" }));
}
}
}
// Set the values to update (from the other columns).
for col in <A::Entity as EntityTrait>::Column::iter() {
if <A::Entity as EntityTrait>::PrimaryKey::from_column(col).is_some() {
continue;
}
match myself.model.get(col) {
ActiveValue::Set(value) => {
let expr = col.save_as(Expr::val(value));
myself.query.value(col, expr);
}
ActiveValue::Unchanged(_) | ActiveValue::NotSet => {}
}
}
UpdateOne(Ok(myself))
}
第一遍只看主键列:Set 或 Unchanged 都变成 WHERE pk = $n,有一列 NotSet 就返回错误。第二遍跳过主键列,只有 Set 的进 SET。由此有两个直接的推论:主键无论 Set 成什么都只进 WHERE,update 改不了主键;Unchanged 的列一定不写,哪怕它的值和数据库里的不一样。
返回类型 UpdateOne<A> 是 Result<ValidatedUpdateOne<A>, DbErr> 的包装,错误存在里面,执行时(self.0?)才返回,这就是上面那段给维护者的注释说的「stashing and delaying the error」。UpdateOne 也因此不实现 QueryTrait,要看 SQL 先 validate():
let am = item::ActiveModel { id: Set(1), qty: Set(4), ..Default::default() };
let stmt = item::Entity::update(am.clone()).validate()?.build(DbBackend::Postgres);
let stmt = item::Entity::update(am).build(DbBackend::Postgres);
error[E0599]: no method named `build` found for struct `UpdateOne<A>` in the current scope
--> src/bin/build_update.rs:8:39
|
8 | let stmt = item::Entity::update(am).build(DbBackend::Postgres);
| ^^^^^ method not found in `UpdateOne<ActiveModel>`
CHANGELOG 的 2.0.0「Breaking Changes」给的理由是:「Those implementations could expose an incomplete SQL query with an incomplete condition that touches too many records.」主键没设时 WHERE 是空的,那样的语句一旦执行就是全表更新。
1.1.20 的 UpdateOne::prepare_filters 在同一个位置写的是 ActiveValue::NotSet => panic!("PrimaryKey is not set")。用 1.1.20 对一个只设了 name 的 ActiveModel 调 update(exp-seaorm-write1-old 的 write_old,放在 tokio::spawn 里接住 panic):
--- update, primary key NotSet
thread 'tokio-rt-worker' (3790922) panicked at ~/.cargo/registry/src/index.crates.io-1949cf8c6b5b557f/sea-orm-1.1.20/src/query/update.rs:99:40:
PrimaryKey is not set
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
=> Err(true)
panic 发生在构造 UpdateOne 时,还没碰数据库。PR #2627(「Remove panics from select / update / delete」)说明它去掉了两种 panic:「arity mismatch (becomes DbErr::KeyArityMismatch)」和「primary key not set (becomes DbErr::PrimaryKeyNotSet)」,CHANGELOG 记为「Added new error variant PrimaryKeyNotSet. Previously, it panics with "PrimaryKey is not set"」。Update::one 的签名没改,错误只好存进返回值里,于是有了 UpdateOne / ValidatedUpdateOne 两层。仓库里最早包含这个提交的 tag 是 2.0.0-rc.28(更早的候选版本没有 tag)。新错误的 Display 至今拼错了一个词:#[error("Primay key not set for {ctx}")]。
exec:有没有列要写
ValidatedUpdateOne::exec 交给 Updater::exec_update_and_return_updated:
if self.is_noop() {
return find_updated_model_by_id(model, db).await;
}
match db.support_returning() {
true => {
let db_backend = db.get_database_backend();
let returning = Query::returning().exprs(
Column::<A>::iter().map(|c| c.select_as(c.into_returning_expr(db_backend))),
);
self.query.returning(returning);
let found: Option<Model<A>> =
ReturningSelector::<SelectModel<Model<A>>, _>::from_query(self.query)
.one(db)
.await?;
// If we got `None` then we are updating a row that does not exist.
match found {
Some(model) => Ok(model),
None => Err(DbErr::RecordNotUpdated),
}
}
false => {
// If we updating a row that does not exist then an error will be thrown here.
self.check_record_exists = true;
self.exec(db).await?;
find_updated_model_by_id(model, db).await
}
}
is_noop 是 self.query.get_values().is_empty(),也就是 SET 里一项都没有。这时 UPDATE "item" SET WHERE ... 不是合法的 SQL,sea-orm 改为调 find_updated_model_by_id:按 ActiveModel 里的主键 find_by_id 一次,查不到就是 RecordNotFound("Failed to find updated item")。有列要写时,Postgres 走 RETURNING 分支,一条语句写完并读回整行;没有返回行(WHERE 没匹配到)就是 RecordNotUpdated。
ActiveModel、什么都不改就 update,一共发几条语句?分别是什么?let m = item::Entity::find_by_id(2).one(&db).await?.unwrap();
let r = m.into_active_model().update(&db).await;
println!("=> {r:?}");
平台 M2 Max、macOS、rustc 1.99.0、Postgres 16.14,find_by_id 那一条也算。
实验:七种 update
src/bin/update.rs 和 src/bin/delete.rs 的前半,每段之前打印一行 --- 说明(摘录):
println!("--- read, change one field, update");
let a1 = item::Entity::find_by_id(1).one(&db).await?.unwrap();
let mut am = a1.into_active_model();
am.qty = Set(4);
let m = am.update(&db).await?;
println!("--- nothing Set, id 99 does not exist");
let ghost = item::Model { id: 99, sku: "z".into(), name: "z".into(), qty: 0, note: None };
let r = ghost.clone().into_active_model().update(&db).await;
println!("--- one field Set, id 99 does not exist");
let mut am = ghost.clone().into_active_model();
am.qty = Set(1);
let r = am.update(&db).await;
println!("--- update_without_returning, nothing Set, id 99");
let r = ghost.into_active_model().update_without_returning(&db).await;
println!("--- primary key NotSet");
let am = item::ActiveModel { name: Set("x".into()), ..Default::default() };
let r = am.update(&db).await;
// delete.rs:把读来的 c3 的主键 Set 成 30,再改 name
println!("--- change the primary key through Set");
let mut am = c3.clone().into_active_model();
am.id = Set(30);
am.name = Set("cherry v2".into());
println!("=> {:?}", am.update(&db).await);
--- read, change one field, update
INFO sqlx::query: summary="SELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", …" db.statement="\n\nSELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", \"item\".\"qty\", \"item\".\"note\" FROM \"item\" WHERE \"item\".\"id\" = $1 LIMIT $2\n" rows_affected=1 rows_returned=1 elapsed=2.0005ms elapsed_secs=0.0020005
INFO sqlx::query: summary="UPDATE \"item\" SET \"qty\" …" db.statement="\n\nUPDATE \"item\" SET \"qty\" = $1 WHERE \"item\".\"id\" = $2 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=1 rows_returned=1 elapsed=740.834µs elapsed_secs=0.000740834
=> Model { id: 1, sku: "a1", name: "apple", qty: 4, note: None }
...
--- nothing Set, id 99 does not exist
INFO sqlx::query: summary="SELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", …" db.statement="\n\nSELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", \"item\".\"qty\", \"item\".\"note\" FROM \"item\" WHERE \"item\".\"id\" = $1 LIMIT $2\n" rows_affected=0 rows_returned=0 elapsed=183.333µs elapsed_secs=0.000183333
=> Err(RecordNotFound("Failed to find updated item"))
--- one field Set, id 99 does not exist
INFO sqlx::query: summary="UPDATE \"item\" SET \"qty\" …" db.statement="\n\nUPDATE \"item\" SET \"qty\" = $1 WHERE \"item\".\"id\" = $2 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=0 rows_returned=0 elapsed=186.25µs elapsed_secs=0.00018625
=> Err RecordNotUpdated / None of the records are updated
--- update_without_returning, nothing Set, id 99
=> Ok(UpdateResult { rows_affected: 0 })
--- primary key NotSet
=> Err PrimaryKeyNotSet { ctx: "UpdateOne" } / Primay key not set for UpdateOne
--- change the primary key through Set
INFO sqlx::query: summary="SELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", …" db.statement="\n\nSELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", \"item\".\"qty\", \"item\".\"note\" FROM \"item\" WHERE \"item\".\"id\" = $1 LIMIT $2\n" rows_affected=1 rows_returned=1 elapsed=1.736416ms elapsed_secs=0.001736416
INFO sqlx::query: summary="UPDATE \"item\" SET \"name\" …" db.statement="\n\nUPDATE \"item\" SET \"name\" = $1 WHERE \"item\".\"id\" = $2 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=0 rows_returned=0 elapsed=483.584µs elapsed_secs=0.000483584
=> Err(RecordNotUpdated)
(... 处是动脑筋那一段,答案见文末。)汇总成表:
| 情况 | 发出的语句 | 结果 |
|---|---|---|
| 改了一列,行存在 | UPDATE ... SET "qty" = $1 WHERE "item"."id" = $2 RETURNING ... | 更新后的 Model |
一列都没 Set,行不存在 | 一条 SELECT | RecordNotFound("Failed to find updated item") |
| 改了一列,行不存在 | UPDATE ... RETURNING,0 行 | RecordNotUpdated,Display 是 None of the records are updated |
update_without_returning,一列都没 Set,行不存在 | 无 | Ok(UpdateResult { rows_affected: 0 }) |
主键 NotSet | 无 | PrimaryKeyNotSet { ctx: "UpdateOne" } |
主键 Set(30)(原来是 3) | UPDATE ... WHERE "item"."id" = $2,绑的是 30 | RecordNotUpdated |
同样是「行不存在」,报什么取决于有没有列要写:有就是 RecordNotUpdated,没有就是 RecordNotFound,按 RecordNotUpdated 匹配错误的代码会漏掉后者。update_without_returning(2.0.0-rc.41 加的,CHANGELOG 说它「returns DbErr::RecordNotUpdated when no row matches」)在 is_noop 时更干脆,什么都不发就返回 Ok,行存不存在都一样。
问: 把主键 Set 成新值,update 为什么不生成 SET "id" = 30 WHERE "id" = 3?
答: 因为 ActiveModel 里没有 3 了。into_active_model() 后 id 是 Unchanged(3),赋成 Set(30) 就把 3 覆盖了(ActiveValue 只装得下一个值,见 ActiveValue 与 ActiveModel)。Update::one 只能把主键当作「要找的那一行」,所以它跳过主键列的 SET,拿 30 去做 WHERE。要改主键,用下面的 update_many:.col_expr(Column::Id, Expr::value(30)).filter(Column::Id.eq(3)),新旧两个值都由调用方给出。
update_many:不经过 ActiveModel
EntityTrait::update_many() 返回一个空的 UpdateMany<E>,SET 和 WHERE 都由调用方填:col_expr(col, expr) 放任意表达式,set(am) 把一个 ActiveModel 里 Set 的列放进去(不看主键,主键 Set 了也照写),filter 来自 QueryFilter。exec 返回 UpdateResult { rows_affected },不检查是否为 0;exec_with_returning 返回 Vec<Model>。sea-query 1.0 里 add 这类运算符方法在 ExprTrait 上,要 use sea_orm::ExprTrait(不导入时 rustc 会提示):
let r = item::Entity::update_many()
.col_expr(item::Column::Qty, Expr::col(item::Column::Qty).add(10))
.filter(item::Column::Qty.lt(6))
.exec(&db)
.await?;
let v = item::Entity::update_many()
.set(item::ActiveModel { note: Set(Some("sale".into())), ..Default::default() })
.filter(item::Column::Id.is_in([1, 2]))
.exec_with_returning(&db)
.await?;
let r = item::Entity::update_many().exec(&db).await?;
--- update_many().col_expr(..).filter(..)
INFO sqlx::query: summary="UPDATE \"item\" SET \"qty\" …" db.statement="\n\nUPDATE \"item\" SET \"qty\" = \"qty\" + $1 WHERE \"item\".\"qty\" < $2\n" rows_affected=2 rows_returned=0 elapsed=582.625µs elapsed_secs=0.000582625
=> UpdateResult { rows_affected: 2 }
--- update_many().set(am).filter(..).exec_with_returning
INFO sqlx::query: summary="UPDATE \"item\" SET \"note\" …" db.statement="\n\nUPDATE \"item\" SET \"note\" = $1 WHERE \"item\".\"id\" IN ($2, $3) RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=2 rows_returned=2 elapsed=803.667µs elapsed_secs=0.000803667
=> [Model { id: 1, sku: "a1", name: "apple", qty: 14, note: Some("sale") }, Model { id: 2, sku: "b2", name: "banana", qty: 15, note: Some("sale") }]
--- update_many() with no column, no filter
=> UpdateResult { rows_affected: 0 }
Expr::col(Qty) 写出来是不带表名的 "qty",列方法(Column::Qty.lt(6))写的是 "item"."qty"。第一条的 lt(6) 匹配 qty 为 4(id 1,前面改过)和 5(id 2)的两行,所以第二条读回的是 14 和 15。最后一条没有 SET 也没有 WHERE,同样被 is_noop 拦下,没有发出语句;但只要有一个 col_expr,不加 filter 的 update_many 就是全表更新,sea-orm 不会拦。update_many 也不走 ActiveModelBehavior 的钩子,见 save 与 ActiveModelBehavior。
delete:只用主键,不查影响行数
Delete::one 和 Update::one 的第一遍一样:主键列 Set 或 Unchanged 进 WHERE,NotSet 存一个 PrimaryKeyNotSet { ctx: "DeleteOne" },结果包在 DeleteOne<E> 里。其他列不管是什么状态都不看。入口有四个:
| 入口 | 返回 | 钩子 |
|---|---|---|
ActiveModelTrait::delete | DeleteResult | before_delete / after_delete |
ModelTrait::delete | DeleteResult | 同上(into_active_model().delete(db)) |
EntityTrait::delete(am) | DeleteOne<Self> | 无 |
EntityTrait::delete_by_id(id) | ValidatedDeleteOne<Self> | 无 |
delete_by_id 从一个全 NotSet 的 ActiveModel 出发,把参数逐个 set 到主键列,再 Delete::one(am).validate().expect("Must be valid"):主键刚刚设过,validate 不会失败,所以它直接返回已验证的那一层。1.1.20 的 delete_by_id 返回 DeleteMany<Self>,CHANGELOG 的 2.0.0「Breaking Changes」写着「It doesn't change normal exec usage, but would change return type of exec_with_returning to Option<Model>」(那一句说的是 DeleteOne,给出的新签名是 ValidatedDeleteOne)。
执行都在 executor/delete.rs:exec 发一条 DELETE,返回 DeleteResult { rows_affected },0 行不是错误,这一点和 update 不同;exec_with_returning 加上 RETURNING 全部列,单行版本返回 Option<Model>,DeleteMany 返回 Vec<Model>。src/bin/delete.rs 的后半:
println!("--- ActiveModel::delete");
let r = c3.into_active_model().delete(&db).await?;
println!("--- Model::delete, row already gone");
let gone = item::Model { id: 3, sku: "c3".into(), name: "cherry".into(), qty: 7, note: None };
println!("=> {:?}", gone.delete(&db).await);
println!("--- delete with primary key NotSet");
let am = item::ActiveModel { sku: Set("a1".into()), ..Default::default() };
// ... 打印 Err 的 Debug 和 Display
println!("--- delete_by_id(2).exec_with_returning");
let r: Option<item::Model> = item::Entity::delete_by_id(2).exec_with_returning(&db).await?;
let r = item::Entity::delete_by_id(2).exec_with_returning(&db).await?;
println!("--- delete_many().filter(..).exec_with_returning");
let v = item::Entity::delete_many().filter(item::Column::Qty.gt(0)).exec_with_returning(&db).await?;
--- ActiveModel::delete
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"id\" = $1\n" rows_affected=1 rows_returned=0 elapsed=699.041µs elapsed_secs=0.000699041
=> DeleteResult { rows_affected: 1 }
--- Model::delete, row already gone
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"id\" = $1\n" rows_affected=0 rows_returned=0 elapsed=196.083µs elapsed_secs=0.000196083
=> Ok(DeleteResult { rows_affected: 0 })
--- delete with primary key NotSet
=> Err PrimaryKeyNotSet { ctx: "DeleteOne" } / Primay key not set for DeleteOne
--- delete_by_id(2).exec_with_returning
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"id\" = $1 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=1 rows_returned=1 elapsed=684.833µs elapsed_secs=0.000684833
=> Some(Model { id: 2, sku: "b2", name: "banana", qty: 5, note: None })
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"id\" = $1 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=0 rows_returned=0 elapsed=231.167µs elapsed_secs=0.000231167
=> again None
--- delete_many().filter(..).exec_with_returning
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"qty\" > $1 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=1 rows_returned=1 elapsed=740.625µs elapsed_secs=0.000740625
=> [Model { id: 1, sku: "a1", name: "apple", qty: 3, note: None }]
删一行已经不存在的记录,delete 照样返回 Ok,要知道「删没删到」得看 rows_affected 或 exec_with_returning 的 None。同一个调用在 1.1.20 下(write_old)返回的是 Vec:
--- delete_by_id(2).exec_with_returning
INFO sqlx::query: summary="DELETE FROM \"item\" WHERE …" db.statement="\n\nDELETE FROM \"item\" WHERE \"item\".\"id\" = $1 RETURNING \"id\", \"sku\", \"name\", \"qty\", \"note\"\n" rows_affected=1 rows_returned=1 elapsed=1.3595ms elapsed_secs=0.0013595
=> [Model { id: 2, sku: "b2", name: "banana", qty: 5, note: None }]
SQL 一样,只有返回类型变了。DeleteMany 和 update_many 一样,没有 filter 就是全表删除;delete_many 不走钩子。
动脑筋答案
--- nothing Set (into_active_model only)
INFO sqlx::query: summary="SELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", …" db.statement="\n\nSELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", \"item\".\"qty\", \"item\".\"note\" FROM \"item\" WHERE \"item\".\"id\" = $1 LIMIT $2\n" rows_affected=1 rows_returned=1 elapsed=198.417µs elapsed_secs=0.000198417
INFO sqlx::query: summary="SELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", …" db.statement="\n\nSELECT \"item\".\"id\", \"item\".\"sku\", \"item\".\"name\", \"item\".\"qty\", \"item\".\"note\" FROM \"item\" WHERE \"item\".\"id\" = $1 LIMIT $2\n" rows_affected=1 rows_returned=1 elapsed=157.375µs elapsed_secs=0.000157375
=> Ok(Model { id: 2, sku: "b2", name: "banana", qty: 5, note: None })
两条,都是 SELECT,一条 UPDATE 也没有。第一条是 find_by_id(2);into_active_model() 之后全是 Unchanged,SET 为空,is_noop 成立,update 改为 find_updated_model_by_id,按主键再查一次,所以第二条和第一条一模一样。结果是 Ok,看不出「其实什么也没写」。1.1.20 的 Updater 有同样的 is_noop 分支,行为相同(write_old 里对不存在的 id 99 也是一条 SELECT 加 RecordNotFound("Failed to find updated item"))。
小结
Update::one先用主键列(Set或Unchanged)拼WHERE,再把Set的非主键列放进SET;主键改不了,Unchanged的列一定不写。- 主键
NotSet:2.0 的UpdateOne/DeleteOne里存着PrimaryKeyNotSet,执行时返回,Display是Primay key not set for UpdateOne;1.1.20 构造时 panic,PrimaryKey is not set。要build先validate()。 - Postgres 上
update是一条UPDATE ... RETURNING;没匹配到行报RecordNotUpdated(None of the records are updated)。 - 一列都没
Set时不发UPDATE,按主键SELECT一次;行不存在时报的是RecordNotFound("Failed to find updated item");update_without_returning直接返回Ok。 update_many由调用方给SET(col_expr/set)和WHERE,返回rows_affected或Vec<Model>;SET为空时不发语句。delete不检查影响行数,删不存在的行也是Ok;delete_by_id在 2.0 返回ValidatedDeleteOne,exec_with_returning是Option<Model>,1.1.20 是DeleteMany和Vec<Model>。