| title | summary | aliases | ||
|---|---|---|---|---|
Expression Syntax |
Learn about the expression syntax in TiDB. |
|
An expression is a combination of one or more values, operators, or functions. In TiDB, expressions are mainly used in various clauses of the SELECT statement, including Group by clause, Where clause, Having clause, Join condition and window function. In addition, some DDL statements also use expressions, such as the setting of the default values, columns, and partition rules when creating tables.
The expressions can be divided into the following types:
-
Identifier. For reference, see Schema object names.
-
Predicates, numeric values, strings, date expressions. The Literal values of these types are also expressions.
-
Function calls and window functions. For reference, see Functions and operators overview and Window functions
-
ParamMarker (
?), system variables, user variables and CASE expressions.
The following rules are the expression syntax, which is based on the parser.y rules of TiDB parser. For the navigable version of the following syntax diagram, refer to TiDB SQL Syntax Diagram.
Expression ::=
( singleAtIdentifier assignmentEq | 'NOT' | Expression ( logOr | 'XOR' | logAnd ) ) Expression
| 'MATCH' '(' ColumnNameList ')' 'AGAINST' '(' BitExpr FulltextSearchModifierOpt ')'
| PredicateExpr ( IsOrNotOp 'NULL' | CompareOp ( ( singleAtIdentifier assignmentEq )? PredicateExpr | AnyOrAll SubSelect ) )* ( IsOrNotOp ( trueKwd | falseKwd | 'UNKNOWN' ) )?
PredicateExpr ::=
BitExpr ( BetweenOrNotOp BitExpr 'AND' BitExpr )* ( InOrNotOp ( '(' ExpressionList ')' | SubSelect ) | LikeOrNotOp SimpleExpr LikeEscapeOpt | RegexpOrNotOp SimpleExpr )?
BitExpr ::=
BitExpr ( ( '|' | '&' | '<<' | '>>' | '*' | '/' | '%' | 'DIV' | 'MOD' | '^' ) BitExpr | ( '+' | '-' ) ( BitExpr | "INTERVAL" Expression TimeUnit ) )
| SimpleExpr
SimpleExpr ::=
SimpleIdent ( ( '->' | '->>' ) stringLit )?
| FunctionCallKeyword
| FunctionCallNonKeyword
| FunctionCallGeneric
| SimpleExpr ( 'COLLATE' CollationName | pipes SimpleExpr )
| WindowFuncCall
| Literal
| paramMarker
| Variable
| SumExpr
| ( '!' | '~' | '-' | '+' | 'NOT' | 'BINARY' ) SimpleExpr
| 'EXISTS'? SubSelect
| ( ( '(' ( ExpressionList ',' )? | 'ROW' '(' ExpressionList ',' ) Expression | builtinCast '(' Expression 'AS' CastType | ( 'DEFAULT' | 'VALUES' ) '(' SimpleIdent | 'CONVERT' '(' Expression ( ',' CastType | 'USING' CharsetName ) ) ')'
| 'CASE' ExpressionOpt WhenClause+ ElseOpt 'END'