Repository: sqlfluff/sqlfluff
Pull request: Add mysql dialect support for DECLARE and IF
Also, added support for `DEFINER` to resolve [#1131](https://github.com/sqlfluff/sqlfluff/issues/1131)

Problem statement:
Add support for `DEFINER`
Within the mysql dialect there is a keyword for `DEFINER` that exists for `CREATE PROCEDURE`/`CREATE FUNCTION` but is not currently supported.  This would be good to add so that the mysql dialect is more completely supported.


Add mysql dialect support for `DECLARE` and `IF`
Add support for the various uses of the `DECLARE` keyword and the `IF-THEN-ELSEIF-ELSE` syntaxes

Hints from issue discussion:

Commit message: Merge branch 'AddDefinerSyntax' of https://github.com/silverbullettruck/sqlfluff

Patch to review:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 18787440ae5..fdb5040ae54 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -19,6 +19,9 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 - Support for function as a default column value [#849](https://github.com/sqlfluff/sqlfluff/issues/849).
 - Support for the CREATE/DROP PROCEDURE statement for the mysql dialect [#901](https://github.com/sqlfluff/sqlfluff/issues/901))
 - Specific allowed/required syntaxes for CREATE/DROP FUNCTION within the mysql dialect [#901](https://github.com/sqlfluff/sqlfluff/issues/901))
+- Support for DECLARE statement for the mysql dialect [#1140](https://github.com/sqlfluff/sqlfluff/issues/1140))
+- Support for the IF-THEN-ELSEIF-ELSE syntax for the mysql dialect [#1140](https://github.com/sqlfluff/sqlfluff/issues/1140))
+- Support for the DEFINER syntax for the mysql dialect [#1131](https://github.com/sqlfluff/sqlfluff/issues/1131))
 
 ### Changed
 
@@ -48,7 +51,7 @@ and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0
 Contributors:
 
 - [@bolajiwahab](https://github.com/bolajiwahab) ([#1063])(https://github.com/sqlfluff/sqlfluff/pull/1063)
-- [@silverbullettruck2001](https://github.com/silverbullettruck2001) ([#1126](https://github.com/sqlfluff/sqlfluff/pull/1126)), ([#1099](https://github.com/sqlfluff/sqlfluff/pull/1099))
+- [@silverbullettruck2001](https://github.com/silverbullettruck2001) ([#1126](https://github.com/sqlfluff/sqlfluff/pull/1126)), ([#1099](https://github.com/sqlfluff/sqlfluff/pull/1099)), ([#1141](https://github.com/sqlfluff/sqlfluff/pull/1141))
 
 ## [0.6.0a2] - 2021-05-27
 
diff --git a/src/sqlfluff/dialects/dialect_mysql.py b/src/sqlfluff/dialects/dialect_mysql.py
index de7119d131c..c869bbb12a5 100644
--- a/src/sqlfluff/dialects/dialect_mysql.py
+++ b/src/sqlfluff/dialects/dialect_mysql.py
@@ -17,8 +17,8 @@
     CodeSegment,
     StringParser,
     SymbolSegment,
-    Anything,
     Delimited,
+    RegexParser,
 )
 from sqlfluff.core.dialects import load_raw_dialect
 
@@ -68,7 +68,14 @@
         name="quoted_literal",
         type="literal",
         trim_chars=('"',),
-    )
+    ),
+    AmpersandLiteralSegment=NamedParser(
+        "ampersand",
+        CodeSegment,
+        name="ampersand_literal",
+        type="literal",
+        trim_chars=("@",),
+    ),
 )
 
 
@@ -129,6 +136,18 @@ class CreateTableStatementSegment(
         ),
         Ref("DatatypeSegment"),
     ),
+    LocalVariableNameSegment=RegexParser(
+        r"`?[a-zA-Z0-9_]*`?",
+        CodeSegment,
+        name="declared_variable",
+        type="variable",
+    ),
+    SessionVariableNameSegment=RegexParser(
+        r"[@][a-zA-Z0-9_]*",
+        CodeSegment,
+        name="declared_variable",
+        type="variable",
+    ),
 )
 
 mysql_dialect.replace(
@@ -136,14 +155,110 @@ class CreateTableStatementSegment(
     TildeSegment=StringParser(
         "~", SymbolSegment, name="tilde", type="statement_terminator"
     ),
+    ParameterNameSegment=RegexParser(
+        r"`?[A-Za-z0-9_]*`?", CodeSegment, name="parameter", type="parameter"
+    ),
+)
+
+mysql_dialect.patch_lexer_matchers(
+    [
+        RegexLexer(
+            "ampersand",
+            r"[@][a-zA-Z0-9_]*",
+            CodeSegment,
+        ),
+    ]
 )
 
 
+@mysql_dialect.segment()
+class DeclareStatement(BaseSegment):
+    """DECLARE statement.
+
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-condition.html
+    https://dev.mysql.com/doc/refman/8.0/en/declare-cursor.html
+    """
+
+    type = "declare_statement"
+
+    match_grammar = OneOf(
+        Sequence(
+            "DECLARE",
+            Ref("NakedIdentifierSegment"),
+            "CURSOR",
+            "FOR",
+            Ref("StatementSegment"),
+        ),
+        Sequence(
+            "DECLARE",
+            OneOf("CONTINUE", "EXIT", "UNDO"),
+            "HANDLER",
+            "FOR",
+            OneOf(
+                "SQLEXCEPTION",
+                "SQLWARNING",
+                Sequence("NOT", "FOUND"),
+                Sequence(
+                    "SQLSTATE",
+                    Ref.keyword("VALUE", optional=True),
+                    Ref("QuotedLiteralSegment"),
+                ),
+                OneOf(
+                    Ref("QuotedLiteralSegment"),
+                    Ref("NumericLiteralSegment"),
+                    Ref("NakedIdentifierSegment"),
+                ),
+            ),
+            Sequence(Ref("StatementSegment")),
+        ),
+        Sequence(
+            "DECLARE",
+            Ref("NakedIdentifierSegment"),
+            "CONDITION",
+            "FOR",
+            OneOf(Ref("QuotedLiteralSegment"), Ref("NumericLiteralSegment")),
+        ),
+        Sequence(
+            "DECLARE",
+            Ref("LocalVariableNameSegment"),
+            Ref("DatatypeSegment"),
+            Sequence(
+                Ref.keyword("DEFAULT"),
+                OneOf(
+                    Ref("QuotedLiteralSegment"),
+                    Ref("NumericLiteralSegment"),
+                    Ref("FunctionSegment"),
+                ),
+                optional=True,
+            ),
+        ),
+    )
+
+
+@mysql_dialect.segment(replace=True)
+class StatementSegment(ansi_dialect.get_segment("StatementSegment")):  # type: ignore
+    """Overriding StatementSegment to allow for additional segment parsing."""
+
+    parse_grammar = ansi_dialect.get_segment("StatementSegment").parse_grammar.copy(
+        insert=[
+            Ref("DelimiterStatement"),
+            Ref("CreateProcedureStatementSegment"),
+            Ref("TransactionStatementSegment"),
+            Ref("DeclareStatement"),
+            Ref("SetAssignmentStatementSegment"),
+            Ref("IfExpressionStatement"),
+        ],
+    )
+
+
 @mysql_dialect.segment()
 class DelimiterStatement(BaseSegment):
     """DELIMITER statement."""
 
     type = "delimiter_statement"
+
     match_grammar = Ref.keyword("DELIMITER")
 
 
@@ -155,16 +270,10 @@ class CreateProcedureStatementSegment(BaseSegment):
     """
 
     type = "create_procedure_statement"
-    # purposefully left out DEFINER
-    # will be addressed when issue [#1131](https://github.com/sqlfluff/sqlfluff/issues/1131) is addressed
-    match_grammar = Sequence(
-        "CREATE",
-        "PROCEDURE",
-        Anything(),
-    )
 
-    parse_grammar = Sequence(
+    match_grammar = Sequence(
         "CREATE",
+        Ref("DefinerSegment", optional=True),
         "PROCEDURE",
         Ref("FunctionNameSegment"),
         Ref("ProcedureParameterListGrammar", optional=True),
@@ -174,26 +283,11 @@ class CreateProcedureStatementSegment(BaseSegment):
     )
 
 
-@mysql_dialect.segment(replace=True)
-class StatementSegment(ansi_dialect.get_segment("StatementSegment")):  # type: ignore
-    """Overriding StatementSegment to allow for additional segment parsing."""
-
-    parse_grammar = ansi_dialect.get_segment("StatementSegment").parse_grammar.copy(
-        insert=[
-            Ref("DelimiterStatement"),
-            Ref("CreateProcedureStatementSegment"),
-        ],
-    )
-
-
 @mysql_dialect.segment(replace=True)
 class FunctionDefinitionGrammar(BaseSegment):
     """This is the body of a `CREATE FUNCTION` statement."""
 
-    match_grammar = Sequence(
-        Ref("TransactionStatementSegment"),
-        Ref("StatementSegment"),
-    )
+    match_grammar = Ref("TransactionStatementSegment")
 
 
 @mysql_dialect.segment()
@@ -201,6 +295,7 @@ class CharacteristicStatement(BaseSegment):
     """A Characteristics statement for functions/procedures."""
 
     type = "characteristic_statement"
+
     match_grammar = Sequence(
         OneOf("DETERMINISTIC", Sequence("NOT", "DETERMINISTIC")),
         Sequence("LANGUAGE", "SQL", optional=True),
@@ -226,13 +321,7 @@ class CreateFunctionStatementSegment(BaseSegment):
 
     match_grammar = Sequence(
         "CREATE",
-        "FUNCTION",
-        Anything(),
-    )
-    # purposefully left out DEFINER
-    # will be addressed when issue [#1131](https://github.com/sqlfluff/sqlfluff/issues/1131) is addressed
-    parse_grammar = Sequence(
-        "CREATE",
+        Ref("DefinerSegment", optional=True),
         "FUNCTION",
         Ref("FunctionNameSegment"),
         Ref("FunctionParameterListGrammar", optional=True),
@@ -270,7 +359,6 @@ class DropStatementSegment(BaseSegment):
 class ProcedureParameterListGrammar(BaseSegment):
     """The parameters for a procedure ie. `(in/out/inout name datatype)`."""
 
-    # Function parameter list
     match_grammar = Bracketed(
         Delimited(
             Ref("ProcedureParameterGrammar"),
@@ -278,3 +366,109 @@ class ProcedureParameterListGrammar(BaseSegment):
             optional=True,
         ),
     )
+
+
+@mysql_dialect.segment()
+class SetAssignmentStatementSegment(BaseSegment):
+    """A `SET` statement.
+
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/set-variable.html
+    """
+
+    type = "set_statement"
+
+    match_grammar = Sequence(
+        "SET",
+        OneOf(Ref("SessionVariableNameSegment"), Ref("LocalVariableNameSegment")),
+        Ref("EqualsSegment"),
+        AnyNumberOf(
+            Ref("QuotedLiteralSegment"),
+            Ref("DoubleQuotedLiteralSegment"),
+            Ref("SessionVariableNameSegment"),
+            Ref("LocalVariableNameSegment"),
+            Ref("FunctionSegment"),
+        ),
+    )
+
+
+@mysql_dialect.segment(replace=True)
+class TransactionStatementSegment(BaseSegment):
+    """A `COMMIT`, `ROLLBACK` or `TRANSACTION` statement.
+
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/commit.html
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/begin-end.html
+    """
+
+    type = "transaction_statement"
+
+    match_grammar = OneOf(
+        Sequence("START", "TRANSACTION"),
+        Sequence(
+            Sequence(
+                Ref("SingleIdentifierGrammar"), Ref("ColonSegment"), optional=True
+            ),
+            Sequence(
+                "BEGIN",
+                Ref.keyword("WORK", optional=True),
+                Ref("StatementSegment"),
+            ),
+        ),
+        Sequence(
+            "LEAVE",
+            Ref("SingleIdentifierGrammar", optional=True),
+        ),
+        Sequence(
+            "COMMIT",
+            Ref.keyword("WORK", optional=True),
+            Sequence("AND", Ref.keyword("NO", optional=True), "CHAIN", optional=True),
+        ),
+        Sequence(
+            "ROLLBACK",
+            Ref.keyword("WORK", optional=True),
+        ),
+        Sequence(
+            "END",
+            Ref("SingleIdentifierGrammar", optional=True),
+        ),
+    )
+
+
+@mysql_dialect.segment()
+class IfExpressionStatement(BaseSegment):
+    """IF-THEN-ELSE-ELSEIF-END IF statement.
+
+    mysql:https://dev.mysql.com/doc/refman/8.0/en/if.html
+    """
+
+    type = "if_then_statement"
+
+    match_grammar = AnyNumberOf(
+        Sequence(
+            "IF",
+            Ref("ExpressionSegment"),
+            "THEN",
+            Ref("StatementSegment"),
+        ),
+        Sequence(
+            "ELSEIF",
+            Ref("ExpressionSegment"),
+            "THEN",
+            Ref("StatementSegment"),
+        ),
+        Sequence("ELSE", Ref("StatementSegment"), optional=True),
+    )
+
+
+@mysql_dialect.segment()
+class DefinerSegment(BaseSegment):
+    """This is the body of a `CREATE FUNCTION` statement."""
+
+    type = "definer_segment"
+
+    match_grammar = Sequence(
+        "DEFINER",
+        Ref("EqualsSegment"),
+        Ref("SingleIdentifierGrammar"),
+        Ref("AmpersandLiteralSegment"),
+        Ref("SingleIdentifierGrammar"),
+    )
diff --git a/test/fixtures/parser/mysql/begin.sql b/test/fixtures/parser/mysql/begin.sql
new file mode 100644
index 00000000000..25b84ab458d
--- /dev/null
+++ b/test/fixtures/parser/mysql/begin.sql
@@ -0,0 +1,4 @@
+blocks:BEGIN
+#set @abc = 1;
+select 1;
+END blocks~
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/begin.yml b/test/fixtures/parser/mysql/begin.yml
new file mode 100644
index 00000000000..67dcd3adf93
--- /dev/null
+++ b/test/fixtures/parser/mysql/begin.yml
@@ -0,0 +1,18 @@
+file:
+- statement:
+    transaction_statement:
+      identifier: blocks
+      colon: ':'
+      keyword: BEGIN
+      statement:
+        select_statement:
+          select_clause:
+            keyword: select
+            select_clause_element:
+              literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: END
+      identifier: blocks
+- statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/declare_condition.sql b/test/fixtures/parser/mysql/declare_condition.sql
new file mode 100644
index 00000000000..2f8c9b6e51a
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_condition.sql
@@ -0,0 +1 @@
+DECLARE random_condition_name CONDITION FOR 1051;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_condition.yml b/test/fixtures/parser/mysql/declare_condition.yml
new file mode 100644
index 00000000000..a834ed5df77
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_condition.yml
@@ -0,0 +1,9 @@
+file:
+  statement:
+    declare_statement:
+    - keyword: DECLARE
+    - identifier: random_condition_name
+    - keyword: CONDITION
+    - keyword: FOR
+    - literal: '1051'
+  statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.sql b/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.sql
new file mode 100644
index 00000000000..e52439d3494
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.sql
@@ -0,0 +1,4 @@
+DECLARE continue handler for sqlexception
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.yml b/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.yml
new file mode 100644
index 00000000000..5a68bbbb2aa
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_continue_handler_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: continue
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_cursor.sql b/test/fixtures/parser/mysql/declare_cursor.sql
new file mode 100644
index 00000000000..84112de6a06
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_cursor.sql
@@ -0,0 +1 @@
+DECLARE test CURSOR FOR SELECT 1;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_cursor.yml b/test/fixtures/parser/mysql/declare_cursor.yml
new file mode 100644
index 00000000000..d8712e2fdb9
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_cursor.yml
@@ -0,0 +1,14 @@
+file:
+  statement:
+    declare_statement:
+    - keyword: DECLARE
+    - identifier: test
+    - keyword: CURSOR
+    - keyword: FOR
+    - statement:
+        select_statement:
+          select_clause:
+            keyword: SELECT
+            select_clause_element:
+              literal: '1'
+  statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_default_numeric.sql b/test/fixtures/parser/mysql/declare_default_numeric.sql
new file mode 100644
index 00000000000..2d1f96a3f36
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_default_numeric.sql
@@ -0,0 +1 @@
+DECLARE abc int default 1;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_default_numeric.yml b/test/fixtures/parser/mysql/declare_default_numeric.yml
new file mode 100644
index 00000000000..0b96b42f5f6
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_default_numeric.yml
@@ -0,0 +1,10 @@
+file:
+  statement:
+    declare_statement:
+    - keyword: DECLARE
+    - variable: abc
+    - data_type:
+        data_type_identifier: int
+    - keyword: default
+    - literal: '1'
+  statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_default_quotedliteral.sql b/test/fixtures/parser/mysql/declare_default_quotedliteral.sql
new file mode 100644
index 00000000000..97dbe44adb0
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_default_quotedliteral.sql
@@ -0,0 +1 @@
+DECLARE abc longtext default 'test';
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_default_quotedliteral.yml b/test/fixtures/parser/mysql/declare_default_quotedliteral.yml
new file mode 100644
index 00000000000..08a7f88b503
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_default_quotedliteral.yml
@@ -0,0 +1,10 @@
+file:
+  statement:
+    declare_statement:
+    - keyword: DECLARE
+    - variable: abc
+    - data_type:
+        data_type_identifier: longtext
+    - keyword: default
+    - literal: "'test'"
+  statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.sql b/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.sql
new file mode 100644
index 00000000000..2045f42d2bf
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for sqlexception
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.yml b/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.yml
new file mode 100644
index 00000000000..37b371f1918
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_exit_handler_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_local_variable.sql b/test/fixtures/parser/mysql/declare_local_variable.sql
new file mode 100644
index 00000000000..30b1cc00a1d
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_local_variable.sql
@@ -0,0 +1 @@
+DECLARE abc int;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_local_variable.yml b/test/fixtures/parser/mysql/declare_local_variable.yml
new file mode 100644
index 00000000000..a6fdef92c17
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_local_variable.yml
@@ -0,0 +1,8 @@
+file:
+  statement:
+    declare_statement:
+      keyword: DECLARE
+      variable: abc
+      data_type:
+        data_type_identifier: int
+  statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.sql b/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.sql
new file mode 100644
index 00000000000..ad8fd8f4363
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.sql
@@ -0,0 +1,4 @@
+DECLARE undo handler for sqlexception
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.yml b/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.yml
new file mode 100644
index 00000000000..05879b995b7
--- /dev/null
+++ b/test/fixtures/parser/mysql/declare_undo_handler_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: undo
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/delimiter_function.yml b/test/fixtures/parser/mysql/delimiter_function.yml
index 90fde8c2f5c..9024034045e 100644
--- a/test/fixtures/parser/mysql/delimiter_function.yml
+++ b/test/fixtures/parser/mysql/delimiter_function.yml
@@ -31,15 +31,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/delimiter_procedure.yml b/test/fixtures/parser/mysql/delimiter_procedure.yml
index 144067d7598..dd6a3a501f7 100644
--- a/test/fixtures/parser/mysql/delimiter_procedure.yml
+++ b/test/fixtures/parser/mysql/delimiter_procedure.yml
@@ -28,15 +28,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_comment.yml b/test/fixtures/parser/mysql/function_comment.yml
index bef969ebe4e..dc62e86a112 100644
--- a/test/fixtures/parser/mysql/function_comment.yml
+++ b/test/fixtures/parser/mysql/function_comment.yml
@@ -23,7 +23,7 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          transaction_statement:
-            keyword: END
+          statement:
+            transaction_statement:
+              keyword: END
   statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/function_definer.sql b/test/fixtures/parser/mysql/function_definer.sql
new file mode 100644
index 00000000000..c008f63d929
--- /dev/null
+++ b/test/fixtures/parser/mysql/function_definer.sql
@@ -0,0 +1,6 @@
+CREATE DEFINER=`test`@`%` FUNCTION `testfunction`()
+RETURNS longtext
+DETERMINISTIC
+BEGIN
+SELECT 1;
+END~
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/function_definer.yml b/test/fixtures/parser/mysql/function_definer.yml
new file mode 100644
index 00000000000..1b4aefad793
--- /dev/null
+++ b/test/fixtures/parser/mysql/function_definer.yml
@@ -0,0 +1,36 @@
+file:
+- statement:
+    create_function_statement:
+    - keyword: CREATE
+    - definer_segment:
+      - keyword: DEFINER
+      - comparison_operator: '='
+      - identifier: '`test`'
+      - literal: '@'
+      - identifier: '`%`'
+    - keyword: FUNCTION
+    - function_name:
+        identifier: '`testfunction`'
+    - base:
+        bracketed:
+          start_bracket: (
+          end_bracket: )
+    - keyword: RETURNS
+    - data_type:
+        data_type_identifier: longtext
+    - characteristic_statement:
+        keyword: DETERMINISTIC
+    - base:
+        transaction_statement:
+          keyword: BEGIN
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: END
+- statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/function_modifies_sql.yml b/test/fixtures/parser/mysql/function_modifies_sql.yml
index 7fe1c2cadc1..015f9ad77da 100644
--- a/test/fixtures/parser/mysql/function_modifies_sql.yml
+++ b/test/fixtures/parser/mysql/function_modifies_sql.yml
@@ -23,15 +23,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_no_sql.yml b/test/fixtures/parser/mysql/function_no_sql.yml
index 4b995a6fad2..ff99aff0522 100644
--- a/test/fixtures/parser/mysql/function_no_sql.yml
+++ b/test/fixtures/parser/mysql/function_no_sql.yml
@@ -22,15 +22,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_notdeterministic.yml b/test/fixtures/parser/mysql/function_notdeterministic.yml
index 6facfb40e2a..2c996b954ed 100644
--- a/test/fixtures/parser/mysql/function_notdeterministic.yml
+++ b/test/fixtures/parser/mysql/function_notdeterministic.yml
@@ -21,15 +21,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_reads_sql.yml b/test/fixtures/parser/mysql/function_reads_sql.yml
index b5323b09c83..a6155e6c825 100644
--- a/test/fixtures/parser/mysql/function_reads_sql.yml
+++ b/test/fixtures/parser/mysql/function_reads_sql.yml
@@ -23,15 +23,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_sql_security_definer.yml b/test/fixtures/parser/mysql/function_sql_security_definer.yml
index f0cd89f73cc..5a17506a11e 100644
--- a/test/fixtures/parser/mysql/function_sql_security_definer.yml
+++ b/test/fixtures/parser/mysql/function_sql_security_definer.yml
@@ -23,15 +23,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/function_sql_security_invoker.yml b/test/fixtures/parser/mysql/function_sql_security_invoker.yml
index 740f725789c..1b962663118 100644
--- a/test/fixtures/parser/mysql/function_sql_security_invoker.yml
+++ b/test/fixtures/parser/mysql/function_sql_security_invoker.yml
@@ -23,15 +23,15 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          select_statement:
-            select_clause:
-              keyword: SELECT
-              select_clause_element:
-                expression:
-                - literal: '1'
-                - binary_operator: +
-                - literal: '2'
+          statement:
+            select_statement:
+              select_clause:
+                keyword: SELECT
+                select_clause_element:
+                  expression:
+                  - literal: '1'
+                  - binary_operator: +
+                  - literal: '2'
 - statement_terminator: ;
 - statement:
     transaction_statement:
diff --git a/test/fixtures/parser/mysql/handler_condition_name.sql b/test/fixtures/parser/mysql/handler_condition_name.sql
new file mode 100644
index 00000000000..9361c1ddb05
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_condition_name.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for conditionName
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_condition_name.yml b/test/fixtures/parser/mysql/handler_condition_name.yml
new file mode 100644
index 00000000000..b6f017fce72
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_condition_name.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - identifier: conditionName
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_continue_sqlexception.yml b/test/fixtures/parser/mysql/handler_continue_sqlexception.yml
new file mode 100644
index 00000000000..5a68bbbb2aa
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_continue_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: continue
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_error_code.sql b/test/fixtures/parser/mysql/handler_error_code.sql
new file mode 100644
index 00000000000..6c1cb212416
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_error_code.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for 1051
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_error_code.yml b/test/fixtures/parser/mysql/handler_error_code.yml
new file mode 100644
index 00000000000..3dab115b19d
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_error_code.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - literal: '1051'
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_exit_sqlexception.yml b/test/fixtures/parser/mysql/handler_exit_sqlexception.yml
new file mode 100644
index 00000000000..37b371f1918
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_exit_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_not_found.sql b/test/fixtures/parser/mysql/handler_not_found.sql
new file mode 100644
index 00000000000..1fbb31e5eac
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_not_found.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for not found
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_not_found.yml b/test/fixtures/parser/mysql/handler_not_found.yml
new file mode 100644
index 00000000000..7d9485f1897
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_not_found.yml
@@ -0,0 +1,23 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: not
+    - keyword: found
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_sqlstate.sql b/test/fixtures/parser/mysql/handler_sqlstate.sql
new file mode 100644
index 00000000000..45353685023
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlstate.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for SQLSTATE '1'
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_sqlstate.yml b/test/fixtures/parser/mysql/handler_sqlstate.yml
new file mode 100644
index 00000000000..38f6d4a1d60
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlstate.yml
@@ -0,0 +1,23 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: SQLSTATE
+    - literal: "'1'"
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_sqlstate_value.sql b/test/fixtures/parser/mysql/handler_sqlstate_value.sql
new file mode 100644
index 00000000000..c59aca51986
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlstate_value.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for SQLSTATE VALUE '1'
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_sqlstate_value.yml b/test/fixtures/parser/mysql/handler_sqlstate_value.yml
new file mode 100644
index 00000000000..473f8c04950
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlstate_value.yml
@@ -0,0 +1,24 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: SQLSTATE
+    - keyword: VALUE
+    - literal: "'1'"
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_sqlwarning.sql b/test/fixtures/parser/mysql/handler_sqlwarning.sql
new file mode 100644
index 00000000000..b820ebefc04
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlwarning.sql
@@ -0,0 +1,4 @@
+DECLARE exit handler for sqlwarning
+begin
+select 1;
+end;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/handler_sqlwarning.yml b/test/fixtures/parser/mysql/handler_sqlwarning.yml
new file mode 100644
index 00000000000..356f373b225
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_sqlwarning.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: exit
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlwarning
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/handler_undo_sqlexception.yml b/test/fixtures/parser/mysql/handler_undo_sqlexception.yml
new file mode 100644
index 00000000000..05879b995b7
--- /dev/null
+++ b/test/fixtures/parser/mysql/handler_undo_sqlexception.yml
@@ -0,0 +1,22 @@
+file:
+- statement:
+    declare_statement:
+    - keyword: DECLARE
+    - keyword: undo
+    - keyword: handler
+    - keyword: for
+    - keyword: sqlexception
+    - statement:
+        transaction_statement:
+          keyword: begin
+          statement:
+            select_statement:
+              select_clause:
+                keyword: select
+                select_clause_element:
+                  literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if.sql b/test/fixtures/parser/mysql/if.sql
new file mode 100644
index 00000000000..efdd0aab13b
--- /dev/null
+++ b/test/fixtures/parser/mysql/if.sql
@@ -0,0 +1,4 @@
+if (x = 0) then
+set @errmsg = '';
+select 1;
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if.yml b/test/fixtures/parser/mysql/if.yml
new file mode 100644
index 00000000000..6d733d65983
--- /dev/null
+++ b/test/fixtures/parser/mysql/if.yml
@@ -0,0 +1,33 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: x
+            comparison_operator: '='
+            literal: '0'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if_else.sql b/test/fixtures/parser/mysql/if_else.sql
new file mode 100644
index 00000000000..e7aa2f461c8
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_else.sql
@@ -0,0 +1,5 @@
+if (x = 0) then
+set @errmsg = '';
+select 1;
+else
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if_else.yml b/test/fixtures/parser/mysql/if_else.yml
new file mode 100644
index 00000000000..6ba05fffe1c
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_else.yml
@@ -0,0 +1,36 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: x
+            comparison_operator: '='
+            literal: '0'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    if_then_statement:
+      keyword: else
+      statement:
+        transaction_statement:
+          keyword: end
+          identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if_elseif.sql b/test/fixtures/parser/mysql/if_elseif.sql
new file mode 100644
index 00000000000..b3fbdb16bbe
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_elseif.sql
@@ -0,0 +1,8 @@
+if (x = 0) then
+set @errmsg = '';
+select 1;
+elseif (x = 1) then
+set _test = 1;
+else
+select 2;
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if_elseif.yml b/test/fixtures/parser/mysql/if_elseif.yml
new file mode 100644
index 00000000000..f84878394bf
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_elseif.yml
@@ -0,0 +1,63 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: x
+            comparison_operator: '='
+            literal: '0'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    if_then_statement:
+    - keyword: elseif
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: x
+            comparison_operator: '='
+            literal: '1'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+        - keyword: set
+        - variable: _test
+        - comparison_operator: '='
+        - variable: '1'
+- statement_terminator: ;
+- statement:
+    if_then_statement:
+      keyword: else
+      statement:
+        select_statement:
+          select_clause:
+            keyword: select
+            select_clause_element:
+              literal: '2'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if_multiple_expression.sql b/test/fixtures/parser/mysql/if_multiple_expression.sql
new file mode 100644
index 00000000000..2214fdb8c57
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_multiple_expression.sql
@@ -0,0 +1,4 @@
+if ((select count(*) from table) = 0 and x = 1) then
+set @errmsg = '';
+select 1;
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if_multiple_expression.yml b/test/fixtures/parser/mysql/if_multiple_expression.yml
new file mode 100644
index 00000000000..c00b05364d2
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_multiple_expression.yml
@@ -0,0 +1,58 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+          - bracketed:
+              start_bracket: (
+              expression:
+                select_statement:
+                  select_clause:
+                    keyword: select
+                    select_clause_element:
+                      function:
+                        function_name:
+                          function_name_identifier: count
+                        bracketed:
+                          start_bracket: (
+                          star: '*'
+                          end_bracket: )
+                  from_clause:
+                    keyword: from
+                    from_expression:
+                      from_expression_element:
+                        table_expression:
+                          table_reference:
+                            identifier: table
+              end_bracket: )
+          - comparison_operator: '='
+          - literal: '0'
+          - binary_operator: and
+          - column_reference:
+              identifier: x
+          - comparison_operator: '='
+          - literal: '1'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if_nested.sql b/test/fixtures/parser/mysql/if_nested.sql
new file mode 100644
index 00000000000..ccf19f6da60
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_nested.sql
@@ -0,0 +1,7 @@
+if (x = 0) then
+select 0;
+if (y = 1) then
+set @errmsg = '';
+select 1;
+end if;
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if_nested.yml b/test/fixtures/parser/mysql/if_nested.yml
new file mode 100644
index 00000000000..83579f8637b
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_nested.yml
@@ -0,0 +1,58 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: x
+            comparison_operator: '='
+            literal: '0'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        select_statement:
+          select_clause:
+            keyword: select
+            select_clause_element:
+              literal: '0'
+- statement_terminator: ;
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            column_reference:
+              identifier: y
+            comparison_operator: '='
+            literal: '1'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/if_subquery_expression.sql b/test/fixtures/parser/mysql/if_subquery_expression.sql
new file mode 100644
index 00000000000..7a85b20065c
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_subquery_expression.sql
@@ -0,0 +1,4 @@
+if ((select count(*) from table) = 0) then
+set @errmsg = '';
+select 1;
+end if;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/if_subquery_expression.yml b/test/fixtures/parser/mysql/if_subquery_expression.yml
new file mode 100644
index 00000000000..f9dd1bfdc16
--- /dev/null
+++ b/test/fixtures/parser/mysql/if_subquery_expression.yml
@@ -0,0 +1,53 @@
+file:
+- statement:
+    if_then_statement:
+    - keyword: if
+    - expression:
+        bracketed:
+          start_bracket: (
+          expression:
+            bracketed:
+              start_bracket: (
+              expression:
+                select_statement:
+                  select_clause:
+                    keyword: select
+                    select_clause_element:
+                      function:
+                        function_name:
+                          function_name_identifier: count
+                        bracketed:
+                          start_bracket: (
+                          star: '*'
+                          end_bracket: )
+                  from_clause:
+                    keyword: from
+                    from_expression:
+                      from_expression_element:
+                        table_expression:
+                          table_reference:
+                            identifier: table
+              end_bracket: )
+            comparison_operator: '='
+            literal: '0'
+          end_bracket: )
+    - keyword: then
+    - statement:
+        set_statement:
+          keyword: set
+          variable: '@errmsg'
+          comparison_operator: '='
+          literal: "''"
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: select
+        select_clause_element:
+          literal: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: if
+- statement_terminator: ;
diff --git a/test/fixtures/parser/mysql/nested_begin.sql b/test/fixtures/parser/mysql/nested_begin.sql
new file mode 100644
index 00000000000..12cfc17eb17
--- /dev/null
+++ b/test/fixtures/parser/mysql/nested_begin.sql
@@ -0,0 +1,5 @@
+blocks:BEGIN
+nest:begin
+set @abc = 1;
+end nest;
+END blocks~
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/nested_begin.yml b/test/fixtures/parser/mysql/nested_begin.yml
new file mode 100644
index 00000000000..d938d5845f6
--- /dev/null
+++ b/test/fixtures/parser/mysql/nested_begin.yml
@@ -0,0 +1,28 @@
+file:
+- statement:
+    transaction_statement:
+      identifier: blocks
+      colon: ':'
+      keyword: BEGIN
+      statement:
+        transaction_statement:
+          identifier: nest
+          colon: ':'
+          keyword: begin
+          statement:
+            set_statement:
+            - keyword: set
+            - variable: '@abc'
+            - comparison_operator: '='
+            - variable: '1'
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: end
+      identifier: nest
+- statement_terminator: ;
+- statement:
+    transaction_statement:
+      keyword: END
+      identifier: blocks
+- statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/procedure_definer.sql b/test/fixtures/parser/mysql/procedure_definer.sql
new file mode 100644
index 00000000000..33a465b12f7
--- /dev/null
+++ b/test/fixtures/parser/mysql/procedure_definer.sql
@@ -0,0 +1,3 @@
+CREATE DEFINER=`test`@`%` PROCEDURE `testprocedure`()
+BEGIN
+END~
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/procedure_definer.yml b/test/fixtures/parser/mysql/procedure_definer.yml
new file mode 100644
index 00000000000..952beb5eec5
--- /dev/null
+++ b/test/fixtures/parser/mysql/procedure_definer.yml
@@ -0,0 +1,24 @@
+file:
+  statement:
+    create_procedure_statement:
+    - keyword: CREATE
+    - definer_segment:
+      - keyword: DEFINER
+      - comparison_operator: '='
+      - identifier: '`test`'
+      - literal: '@'
+      - identifier: '`%`'
+    - keyword: PROCEDURE
+    - function_name:
+        identifier: '`testprocedure`'
+    - base:
+        bracketed:
+          start_bracket: (
+          end_bracket: )
+    - base:
+        transaction_statement:
+          keyword: BEGIN
+          statement:
+            transaction_statement:
+              keyword: END
+  statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/function_in_param.sql b/test/fixtures/parser/mysql/procedure_in_param.sql
similarity index 100%
rename from test/fixtures/parser/mysql/function_in_param.sql
rename to test/fixtures/parser/mysql/procedure_in_param.sql
diff --git a/test/fixtures/parser/mysql/function_in_param.yml b/test/fixtures/parser/mysql/procedure_in_param.yml
similarity index 84%
rename from test/fixtures/parser/mysql/function_in_param.yml
rename to test/fixtures/parser/mysql/procedure_in_param.yml
index af268713e53..eef16271de3 100644
--- a/test/fixtures/parser/mysql/function_in_param.yml
+++ b/test/fixtures/parser/mysql/procedure_in_param.yml
@@ -16,7 +16,7 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          transaction_statement:
-            keyword: END
+          statement:
+            transaction_statement:
+              keyword: END
   statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/function_inout_param.sql b/test/fixtures/parser/mysql/procedure_inout_param.sql
similarity index 100%
rename from test/fixtures/parser/mysql/function_inout_param.sql
rename to test/fixtures/parser/mysql/procedure_inout_param.sql
diff --git a/test/fixtures/parser/mysql/function_inout_param.yml b/test/fixtures/parser/mysql/procedure_inout_param.yml
similarity index 84%
rename from test/fixtures/parser/mysql/function_inout_param.yml
rename to test/fixtures/parser/mysql/procedure_inout_param.yml
index 3dcde4b5499..8b450b67ee2 100644
--- a/test/fixtures/parser/mysql/function_inout_param.yml
+++ b/test/fixtures/parser/mysql/procedure_inout_param.yml
@@ -16,7 +16,7 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          transaction_statement:
-            keyword: END
+          statement:
+            transaction_statement:
+              keyword: END
   statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/function_out_param.sql b/test/fixtures/parser/mysql/procedure_out_param.sql
similarity index 100%
rename from test/fixtures/parser/mysql/function_out_param.sql
rename to test/fixtures/parser/mysql/procedure_out_param.sql
diff --git a/test/fixtures/parser/mysql/function_out_param.yml b/test/fixtures/parser/mysql/procedure_out_param.yml
similarity index 84%
rename from test/fixtures/parser/mysql/function_out_param.yml
rename to test/fixtures/parser/mysql/procedure_out_param.yml
index f506eb39e91..8fa3d9db878 100644
--- a/test/fixtures/parser/mysql/function_out_param.yml
+++ b/test/fixtures/parser/mysql/procedure_out_param.yml
@@ -16,7 +16,7 @@ file:
     - base:
         transaction_statement:
           keyword: BEGIN
-        statement:
-          transaction_statement:
-            keyword: END
+          statement:
+            transaction_statement:
+              keyword: END
   statement_terminator: '~'
diff --git a/test/fixtures/parser/mysql/set_session_variable.sql b/test/fixtures/parser/mysql/set_session_variable.sql
new file mode 100644
index 00000000000..610700d6d94
--- /dev/null
+++ b/test/fixtures/parser/mysql/set_session_variable.sql
@@ -0,0 +1 @@
+set @abc = 1;
\ No newline at end of file
diff --git a/test/fixtures/parser/mysql/set_session_variable.yml b/test/fixtures/parser/mysql/set_session_variable.yml
new file mode 100644
index 00000000000..cb2eef8081f
--- /dev/null
+++ b/test/fixtures/parser/mysql/set_session_variable.yml
@@ -0,0 +1,8 @@
+file:
+  statement:
+    set_statement:
+    - keyword: set
+    - variable: '@abc'
+    - comparison_operator: '='
+    - variable: '1'
+  statement_terminator: ;


Code review comments:
diff_hunk: @@ -129,21 +136,129 @@ class CreateTableStatementSegment(
         ),
         Ref("DatatypeSegment"),
     ),
+    LocalVariableNameSegment=RegexParser(
+        r"`?[a-zA-Z0-9_]*`?",
+        CodeSegment,
+        name="declared_variable",
+        type="variable",
+    ),
+    SessionVariableNameSegment=RegexParser(
+        r"[@][a-zA-Z0-9_]*",
+        CodeSegment,
+        name="declared_variable",
+        type="variable",
+    ),
 )
 
 mysql_dialect.replace(
     DelimiterSegment=OneOf(Ref("SemicolonSegment"), Ref("TildeSegment")),
     TildeSegment=StringParser(
         "~", SymbolSegment, name="tilde", type="statement_terminator"
     ),
+    ParameterNameSegment=RegexParser(
+        r"`?[A-Za-z0-9_]*`?", CodeSegment, name="parameter", type="parameter"
+    ),
+)
+
+mysql_dialect.patch_lexer_matchers(
+    [
+        RegexLexer(
+            "ampersand",
+            r"[@][a-zA-Z0-9_]*",
+            CodeSegment,
+        ),
+    ]
 )
 
 
+@mysql_dialect.segment()
+class DeclareStatement(BaseSegment):
+    """DECLARE statement.
+
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-local-variable.html
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html
+    mysql: https://dev.mysql.com/doc/refman/8.0/en/declare-condition.html
+    https://dev.mysql.com/doc/refman/8.0/en/declare-cursor.html
+    """
+
+    type = "declare_statement"
+
+    match_grammar = OneOf(
+        Sequence(
+            "DECLARE",
+            Ref("NakedIdentifierSegment"),
+            "CURSOR",
+            "FOR",
+            Ref("StatementSegment"),
+        ),
+        Sequence(
+            "DECLARE",
+            OneOf("CONTINUE", "EXIT", "UNDO"),
+            "HANDLER",
+            "FOR",
+            OneOf(
+                "SQLEXCEPTION",
+                "SQLWARNING",
+                Sequence("NOT", "FOUND"),
+                Sequence(
+                    "SQLSTATE",
+                    Ref.keyword("VALUE", optional=True),
+                    Ref("QuotedLiteralSegment"),
+                ),
+                OneOf(
+                    Ref("QuotedLiteralSegment"),
+                    Ref("NumericLiteralSegment"),
+                    Ref("NakedIdentifierSegment"),
+                ),
+            ),
+            Sequence(Ref("StatementSegment")),
+        ),
+        Sequence(
+            "DECLARE",
+            Ref("NakedIdentifierSegment"),
+            "CONDITION",
+            "FOR",
+            OneOf(Ref("QuotedLiteralSegment"), Ref("NumericLiteralSegment")),
+        ),
+        Sequence(
+            "DECLARE",
+            Ref("LocalVariableNameSegment"),
+            Ref("DatatypeSegment"),
+            Sequence(
+                Ref.keyword("DEFAULT"),
+                OneOf(
+                    Ref("QuotedLiteralSegment"),
+                    Ref("NumericLiteralSegment"),
+                    Ref("FunctionSegment"),
+                ),
+                optional=True,
+            ),
+        ),
+    )
+
+
+@mysql_dialect.segment(replace=True)
+class StatementSegment(ansi_dialect.get_segment("StatementSegment")):  # type: ignore
+    """Overriding StatementSegment to allow for additional segment parsing."""
+
+    parse_grammar = ansi_dialect.get_segment("StatementSegment").parse_grammar.copy(
+        insert=[
+            Ref("DelimiterStatement"),
+            Ref("CreateProcedureStatementSegment"),
+            Ref("TransactionStatementSegment"),
path: src/sqlfluff/dialects/dialect_mysql.py
text: @user1:
Because you're replacing an existing segment (e.g. `TransactionStatementSegment`) you don't need to reinsert it here, it's already part of `StatementSegment`. You should only need to add segments which don't exist at all in the parent dialect.

@author:
@user1  I got that cleaned up now

diff_hunk: @@ -0,0 +1,4 @@
+blocks:BEGIN
+#set @abc = 1;
path: test/fixtures/parser/mysql/begin.sql
text: @user1:
did you mean to comment out line 2 here? I'm not sure what purpose it serves either way.

@author:
yeah...sorry, I cleaned that up now.