DELETE Statement，懂你不容易

电玩女神 2023-10-03 11:07 25阅读 0赞

### 闲话少叙 ###

书接上文，自从团队对外公布了TiDB for PostgreSQL项目之后，团队成员正在紧锣密鼓的为下一个里程碑做努力，目前我们主要的工作集中在以下几个方面：

1.  完善PgSQL的通信协议
2.  实现PgSQL关键字和语法
3.  修复单元测试
4.  数据库Chaos测试平台，[基于 k8s 与 Chaos Mesh 构建数据库混沌实验日报系统][k8s _ Chaos Mesh]

我们先回顾一下往期内容，再进入本文主题：

[TiDB for PostgreSQL—牛刀小试][TiDB for PostgreSQL]

[TiDB for PostgreSQL 学习指南][TiDB for PostgreSQL 1]

[TiDB Parser模块的简单解读与改造方法][TiDB Parser]

[SQL只是CRUD？][SQL_CRUD]

除此之外，我们的项目也有幸入选PingCap DevCon 2021大会论坛话题，同时还进入了第二期TiDB Hacking Camp，欢迎大家多多关注我们，更欢迎大家加入我们。

[Hacking Camp 第二期开启，六大开源项目重磅来袭！][Hacking Camp]

### 语法定义 ###

书归正文，这期文章主要介绍Data Manipulation Language中的Delete Statement，也就是CRUD中的最后一个字母。让我们先看看Mysql，PostgreSQL，SQL-2003 Standard都是怎么定义Delete的语法。

### MySQL ###

**Single-Table Syntax**

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
      [PARTITION (partition_name [, partition_name] ...)]
      [WHERE where_condition]
      [ORDER BY ...]
      [LIMIT row_count]

From <[https://dev.mysql.com/doc/refman/8.0/en/delete.html][https_dev.mysql.com_doc_refman_8.0_en_delete.html]>

**Multiple-Table Syntax**

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      tbl_name[.*] [, tbl_name[.*]] ...
      FROM table_references
      [WHERE where_condition]
    DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
      FROM tbl_name[.*] [, tbl_name[.*]] ...
      USING table_references
      [WHERE where_condition]

From <[https://dev.mysql.com/doc/refman/8.0/en/delete.html][https_dev.mysql.com_doc_refman_8.0_en_delete.html]>

### PostgreSQL ###

[ WITH [ RECURSIVE ] with_query [, ...] ]
     DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
      [ USING from_item [, ...] ]
      [ WHERE condition | WHERE CURRENT OF cursor_name ]
      [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

This command conforms to the SQL standard, except that the USING and RETURNING clauses are PostgreSQL extensions, as is the ability to use WITH with DELETE.

From <[https://www.postgresql.org/docs/current/sql-delete.html][https_www.postgresql.org_docs_current_sql-delete.html]>

### SQL-2003 BNF ###

<delete statement: positioned>::= DELETE FROM <target table> WHERE CURRENT OF <cursor name>
    <delete statement: searched>::=DELETE FROM <target table> [ WHERE <search condition> ]
     
    <target table>::=<table name>|ONLY <left paren> <table name> <right paren>
     
    <search condition>::=<boolean value expression>

从这三个数据库的定义是不是觉得只有SQL-2003跟常用的Delete From Table删表跑路的语法差不多，其他两个数据库加入了一些自定义的语法功能，尤其是MySQL，长得像个远房亲戚。这里不对各个自定义语法功能做解释，想了解的读者可以点击对应的官方链接查阅。

### Yacc实现 ###

了解语法定义是远远不够的，我们还需要看看parser.y对Delete Statement的MySQL实现，从Delete Statement开始，分成了DeleteWithoutUsingStmt和DeleteWithUsingStmt两个分支，每个分支中又定义了对应的语法，可以看出每一个语法定义都基本符合MySQL的定义，整个过程就是把SQL解析成AST，下一步就是拿现有的parser做个demo，看看是否能正常解析我们的Delete SQL。

可以点击这个链接直接查看文件 [DigitalChinaOpenSource/DCParser][DigitalChinaOpenSource_DCParser]

[DELETE][]

我们直接定位到Delete Statement部分，需要了解tidb官方文档对Delete的解释，可以点击上方链接。

/*******************************************************************
     *
     *  Delete Statement
     *
     *******************************************************************/
    DeleteWithoutUsingStmt:
     "DELETE" TableOptimizerHints PriorityOpt QuickOptional IgnoreOptional "FROM" TableName PartitionNameListOpt TableAsNameOpt IndexHintListOpt WhereClauseOptional OrderByOptional LimitClause
        {
            // Single Table
            tn := $7.(*ast.TableName)
     tn.IndexHints = $10.([]*ast.IndexHint)
     tn.PartitionNames = $8.([]model.CIStr)
            join := &ast.Join{Left: &ast.TableSource{Source: tn, AsName: $9.(model.CIStr)}, Right: nil}
     x := &ast.DeleteStmt{
                TableRefs: &ast.TableRefsClause{TableRefs: join},
                Priority:  $3.(mysql.PriorityEnum),
                Quick:     $4.(bool),
                IgnoreErr: $5.(bool),
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $11 != nil {
     x.Where = $11.(ast.ExprNode)
            }
     if $12 != nil {
     x.Order = $12.(*ast.OrderByClause)
            }
     if $13 != nil {
     x.Limit = $13.(*ast.Limit)
            }
            $$ = x
        }
    | "DELETE" TableOptimizerHints PriorityOpt QuickOptional IgnoreOptional TableAliasRefList "FROM" TableRefs WhereClauseOptional
        {
            // Multiple Table
            x := &ast.DeleteStmt{
                Priority:     $3.(mysql.PriorityEnum),
                Quick:        $4.(bool),
                IgnoreErr:    $5.(bool),
                IsMultiTable: true,
                BeforeFrom:   true,
                Tables:       &ast.DeleteTableList{Tables: $6.([]*ast.TableName)},
                TableRefs:    &ast.TableRefsClause{TableRefs: $8.(*ast.Join)},
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $9 != nil {
     x.Where = $9.(ast.ExprNode)
            }
            $$ = x
        }
    DeleteWithUsingStmt:
     "DELETE" TableOptimizerHints PriorityOpt QuickOptional IgnoreOptional "FROM" TableAliasRefList "USING" TableRefs WhereClauseOptional
        {
            // Multiple Table
            x := &ast.DeleteStmt{
                Priority:     $3.(mysql.PriorityEnum),
                Quick:        $4.(bool),
                IgnoreErr:    $5.(bool),
                IsMultiTable: true,
                Tables:       &ast.DeleteTableList{Tables: $7.([]*ast.TableName)},
                TableRefs:    &ast.TableRefsClause{TableRefs: $9.(*ast.Join)},
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $10 != nil {
     x.Where = $10.(ast.ExprNode)
            }
            $$ = x
        }
    DeleteFromStmt:
        DeleteWithoutUsingStmt
    |   DeleteWithUsingStmt

### 验证Yacc ###

我们新建一个golang项目，如下。 这个demo解析了两个Delete SQL，第一个比较简单，第二个几乎把所有的参数都填上了，我们看看执行后的效果。

package main
    import (
     "fmt"
        parser "github.com/DigitalChinaOpenSource/DCParser"
     "github.com/DigitalChinaOpenSource/DCParser/ast"
        _ "github.com/DigitalChinaOpenSource/DCParser/test_driver"
    )
     
    func parse(sql string) (*ast.StmtNode, error) {
     p := parser.New()
     stmtNodes, _, err := p.Parse(sql, "", "")
     if err != nil {
     return nil, err
        }
     return &stmtNodes[0], nil
    }
     
    func main() {
     astNode, err := parse("DELETE FROM somelog;")
     if err != nil {
            fmt.Printf("parse error: %v\n", err.Error())
     return
        }
        fmt.Printf("%v\n", *astNode)
     astNode, err = parse("DELETE /*+ BKA(somelog) */ LOW_PRIORITY QUICK IGNORE FROM somelog PARTITION (p1,p2) AS t FORCE INDEX FOR ORDER BY(timestamp_column) WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;")
     if err != nil {
            fmt.Printf("parse error: %v\n", err.Error())
     return
        }
        fmt.Printf("%v\n", *astNode)
    }

项目设置断点后运行，对"DELETE FROM somelog;"的解析呈现AST结构，可以看到TableName正确获取到了，其他的参数where, order, limit，priority, ignore, quick等等都是空值。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw_size_16_color_FFFFFF_t_70][]

对二个SQL解析时"DELETE /\*+ BKA(somelog) \*/ LOW\_PRIORITY QUICK IGNORE FROM somelog PARTITION (p1,p2) AS t FORCE INDEX FOR ORDER BY(timestamp\_column) WHERE user = 'jcole' ORDER BY timestamp\_column LIMIT 1;"，整个AST就变得庞大很多，包括where里的相等操作也能正确解析，说明这个parser可以工作正常，接下来我们开始删除对于PG来说不需要的语法参数。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw_size_16_color_FFFFFF_t_70 1][]

### 修改Yacc ###

我们开始删除PostgreSQL中没有的参数，如以下代码所示，对应的代码解析部分也要做相应的调整，不然会编译报错，当然中间也确实遇到了一个很麻烦的问题，如果你也遇到了类似conflict的问题，可以看看文末的trouble shooting。

/*******************************************************************
     *
     *  Delete Statement
     *
     *******************************************************************/
    DeleteWithoutUsingStmt:
     "DELETE" TableOptimizerHints PriorityOpt QuickOptional "FROM" TableName TableAsNameOpt WhereClauseOptional
        {
            // Single Table
            tn := $6.(*ast.TableName)
            join := &ast.Join{Left: &ast.TableSource{Source: tn, AsName: $7.(model.CIStr)}, Right: nil}
            x := &ast.DeleteStmt{
                TableRefs: &ast.TableRefsClause{TableRefs: join},
                Priority:  $3.(mysql.PriorityEnum),
                Quick:     $4.(bool),
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $8 != nil {
     x.Where = $8.(ast.ExprNode)
            }
            $$ = x
        }
    | "DELETE" TableOptimizerHints PriorityOpt QuickOptional TableAliasRefList "FROM" TableRefs WhereClauseOptional
        {
            // Multiple Table
            x := &ast.DeleteStmt{
                Priority:     $3.(mysql.PriorityEnum),
                Quick:        $4.(bool),
                IsMultiTable: true,
                BeforeFrom:   true,
                Tables:       &ast.DeleteTableList{Tables: $5.([]*ast.TableName)},
                TableRefs:    &ast.TableRefsClause{TableRefs: $7.(*ast.Join)},
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $8 != nil {
     x.Where = $8.(ast.ExprNode)
            }
            $$ = x
        }
    DeleteWithUsingStmt:
     "DELETE" TableOptimizerHints PriorityOpt QuickOptional "FROM" TableAliasRefList "USING" TableRefs WhereClauseOptional
        {
            // Multiple Table
            x := &ast.DeleteStmt{
                Priority:     $3.(mysql.PriorityEnum),
                Quick:        $4.(bool),
                IsMultiTable: true,
                Tables:       &ast.DeleteTableList{Tables: $6.([]*ast.TableName)},
                TableRefs:    &ast.TableRefsClause{TableRefs: $8.(*ast.Join)},
            }
     if $2 != nil {
     x.TableHints = $2.([]*ast.TableOptimizerHint)
            }
     if $9 != nil {
     x.Where = $9.(ast.ExprNode)
            }
            $$ = x
        }
    DeleteFromStmt:
        DeleteWithoutUsingStmt
    |   DeleteWithUsingStmt

利用项目自带的goyacc进行编译，记住，一定是用项目再带的goyacc编译，因为tidb对yyLexer接口一些改动，如果用go自带的goyacc编译会报没有实现方法的错误。好了，执行完之后就会生成一个新的parser.go文件，恭喜修改成功~！

![c0c192c00f5b47ffa71cb37e553466cf.png][]

不过，最后还需要把yacc修改导致的UT错误给修复了:)

![20210715171009791.png][]

本文删除MySQL自定义语法的目标已经接近完成，剩下的几个语法功能就留给读者自己尝试删除，关于如何加入PostgreSQL语法功能，我们会在下次分解。

### Conflicts问题 ###

当你在编译y文件时出现conflicts问题的时候，需要添加-v来查阅解析步骤。

![bb7e6550ce95a7fc5b3da9a8a9015f40.png][]

可以通过正确和错误文件对比提高排查效率，当然这次问题属于第一次修改没经验造成的，我还特地翻阅了Bison官方文档[Shift/Reduce (Bison 3.7.6) (gnu.org)][Shift_Reduce _Bison 3.7.6_ _gnu.org]。最后根据Yacc的工作原理和output文件，把问题修复的。原因其实也非常简单，就是只改了一个delete分支里的语法，导致编译之后Delete AST解析路劲不一致的冲突。

![86cc0866f1b1cf0b7f6be191f184299c.png][]

[k8s _ Chaos Mesh]: https://zhuanlan.zhihu.com/p/389684754
[TiDB for PostgreSQL]: https://zhuanlan.zhihu.com/p/379181280
[TiDB for PostgreSQL 1]: https://zhuanlan.zhihu.com/p/383500491
[TiDB Parser]: https://zhuanlan.zhihu.com/p/387798263
[SQL_CRUD]: https://zhuanlan.zhihu.com/p/385376858
[Hacking Camp]: https://zhuanlan.zhihu.com/p/386709045
[https_dev.mysql.com_doc_refman_8.0_en_delete.html]: https://link.zhihu.com/?target=https%3A//dev.mysql.com/doc/refman/8.0/en/delete.html
[https_www.postgresql.org_docs_current_sql-delete.html]: https://link.zhihu.com/?target=https%3A//www.postgresql.org/docs/current/sql-delete.html
[DigitalChinaOpenSource_DCParser]: https://link.zhihu.com/?target=https%3A//github.com/DigitalChinaOpenSource/DCParser/blob/main/parser.y
[DELETE]: https://docs.pingcap.com/zh/tidb/stable/sql-statement-delete
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210715170847824.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210715170919686.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NCR0NhbXB1cw==,size_16,color_FFFFFF,t_70
[c0c192c00f5b47ffa71cb37e553466cf.png]: https://img-blog.csdnimg.cn/img_convert/c0c192c00f5b47ffa71cb37e553466cf.png
[20210715171009791.png]: https://img-blog.csdnimg.cn/20210715171009791.png
[bb7e6550ce95a7fc5b3da9a8a9015f40.png]: https://img-blog.csdnimg.cn/img_convert/bb7e6550ce95a7fc5b3da9a8a9015f40.png
[Shift_Reduce _Bison 3.7.6_ _gnu.org]: https://link.zhihu.com/?target=https%3A//www.gnu.org/software/bison/manual/html_node/Shift_002fReduce.html%23Shift_002fReduce
[86cc0866f1b1cf0b7f6be191f184299c.png]: https://img-blog.csdnimg.cn/img_convert/86cc0866f1b1cf0b7f6be191f184299c.png