MySQL itself doesn’t have native support for RETURNING after using INSERT. There is only one way to do it for primary keys with autoincrement (or serial) types, where you can access insertId and affectedRows fields. We’ve prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects
Also with Drizzle, you can specify a primary key with $default function that will generate custom primary keys at runtime. We will also return those generated keys for you in the $returningId() call
If there is no primary keys -> type will be {}[] for such queries
Insert $returningId
PostgreSQL
SQLite
MySQL
MySQL itself doesn’t have native support for RETURNING after using INSERT. There is only one way to do it for primary keys with autoincrement (or serial) types, where you can access insertId and affectedRows fields. We’ve prepared an automatic way for you to handle such cases with Drizzle and automatically receive all inserted IDs as separate objects
Also with Drizzle, you can specify a primary key with $default function that will generate custom primary keys at runtime. We will also return those generated keys for you in the $returningId() call
If there is no primary keys -> type will be {}[] for such queries
插入多行
Upserts 和冲突
Drizzle ORM 提供了处理 Upserts 和冲突的简单接口。
冲突时不做任何操作
PostgreSQL
SQLite
MySQL
onConflictDoNothing 如果存在冲突将取消插入:
冲突时更新
PostgreSQL
SQLite
MySQL
onConflictDoUpdate 如果存在冲突将更新行:
where 子句
on conflict do update 可以有两个不同的位置使用 where 子句 -
作为冲突目标的一部分(即部分索引)或作为 update 子句的一部分:
在 Drizzle 中指定这些条件,可以使用 setWhere 和 targetWhere 子句:
使用复合索引的 Upsert,或者 onConflictDoUpdate 的复合主键:
重复键更新
PostgreSQL
SQLite
MySQL
MySQL 支持 ON DUPLICATE KEY UPDATE 而不是 ON CONFLICT 子句。MySQL 将自动根据主键和唯一索引确定冲突目标,如果存在任何唯一索引冲突,将更新行。
Drizzle 通过 onDuplicateKeyUpdate 方法支持此功能:
虽然 MySQL 不直接支持在冲突时不做任何操作,但可以通过将任何列的值设置为自己来实现相同的效果: