DELETE Statement
DELETE 应该是增删改查里最简单的语句了
语法分析
由于不同类型 db 包括 SQLite3、 MySQL、PostgreSQL 的 DELETE 最简单形态的语法是一样的,本文只以实现最简单的形态为目标,所以,这里只拿 MySQL 举例
MYSQL 的 DELETE 语句也有两种形态:
- 删除单表的: 额外支持了 ORDER BY 和 LIMIT
-
删除多表的:只支持 WHERE 条件
开源实例
Beego ORM
Beego 的 DELETE API 定义和 UPDATE 一样,如果 cols 没有传,默认是根据主键进行删除。
GORM
DELETE 相关方法只有一个, 具体实现思路是,删除一条记录时,删除对象需要指定主键,否则会触发批量Delete,例如:
// Email 的 ID 是 `10`
db.Delete(&email)
// DELETE from emails where id = 10;
// 带额外条件的删除
db.Where("name = ?", "jinzhu").Delete(&email)
// DELETE from emails where id = 10 AND name = "jinzhu";
db.Delete(&users, []int{1,2,3})
// DELETE FROM users WHERE id IN (1,2,3);
API 设计
与 Update 类似, 需要结构体 Deletor 结构体去实现 QueryBuilder 与 Executor 接口。以及需要包含条件语句的拼接。
type Deleter[T any] struct {
}
func (d *Deleter[T]) Build() (*Query, error) {
panic("implement me")
}
// From accepts model definition
func (d *Deleter[T]) From(table string) *Deleter[T] {
panic("implement me ")
}
// Where accepts predicates
func (d *Deleter[T]) Where(predicates ...Predicate) *Deleter[T] {
panic("implement me")
}
具体实现
Build 方法
// Deleter builds DELETE query
type Deleter[T any] struct {
builder
table string
where []Predicate
}
// Build returns DELETE query
func (d *Deleter[T]) Build() (*Query, error) {
_, _ = d.sb.WriteString("DELETE FROM ")
if d.table == "" {
var t T
d.sb.WriteByte('`')
d.sb.WriteString(reflect.TypeOf(t).Name())
d.sb.WriteByte('`')
} else {
d.sb.WriteString(d.table)
}
if len(d.where) > 0 {
d.sb.WriteString(" WHERE ")
err := d.buildPredicates(d.where)
if err != nil {
return nil, err
}
}
d.sb.WriteByte(';')
return &Query{SQL: d.sb.String(), Args: d.args}, nil
}
// From accepts model definition
func (d *Deleter[T]) From(table string) *Deleter[T] {
d.table = table
return d
}
// Where accepts predicates
func (d *Deleter[T]) Where(predicates ...Predicate) *Deleter[T] {
d.where = predicates
return d
}
与 Update 一样需要实现一个 Excute方法文章来源:https://www.toymoban.com/news/detail-405887.html
func (d *Deleter[T]) Exec(ctx context.Context) Result {
q, err := d.Build()
if err != nil {
return Result{err: err}
}
res, err := d.db.db.ExecContext(ctx, q.SQL, q.Args...)
return Result{err: err, res: res}
}
单元测试
func TestDeleter_Build(t *testing.T) {
testCases := []struct {
name string
builder QueryBuilder
wantErr error
wantQuery *Query
}{
{
name: "no where",
builder: (&Deleter[TestModel]{}).From("`test_model`"),
wantQuery: &Query{
SQL: "DELETE FROM `test_model`;",
},
},
{
name: "where",
builder: (&Deleter[TestModel]{}).Where(C("Id").EQ(16)),
wantQuery: &Query{
SQL: "DELETE FROM `TestModel` WHERE `Id` = ?;",
Args: []any{16},
},
},
{
name: "from",
builder: (&Deleter[TestModel]{}).From("`test_model`").Where(C("Id").EQ(16)),
wantQuery: &Query{
SQL: "DELETE FROM `test_model` WHERE `Id` = ?;",
Args: []any{16},
},
},
}
for _, tc := range testCases {
c := tc
t.Run(c.name, func(t *testing.T) {
query, err := c.builder.Build()
assert.Equal(t, c.wantErr, err)
if err != nil {
return
}
assert.Equal(t, tc.wantQuery, query)
})
}
}
只要了解前面几个模块的设计,就会发现最简单的 delete 的构造基本没有难点,也没啥好总结的。文章来源地址https://www.toymoban.com/news/detail-405887.html
到了这里,关于DELETE Statement的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!