This section lists the grammar rules that expressions must follow in MySQL and provides additional information about the types of terms that may appear in expressions.
*note expression-syntax::
*note expression-term-notes::
*note temporal-intervals::
Expression Syntax
The following grammar rules define expression syntax in MySQL. The grammar shown here is based on that given in the 'sql/sql_yacc.yy' file of MySQL source distributions. For additional information about some of the expression terms, see *note expression-term-notes::.
EXPR:
EXPR OR EXPR
| EXPR || EXPR
| EXPR XOR EXPR
| EXPR AND EXPR
| EXPR && EXPR
| NOT EXPR
| ! EXPR
| BOOLEAN_PRIMARY IS [NOT] {TRUE | FALSE | UNKNOWN}
| BOOLEAN_PRIMARY
BOOLEAN_PRIMARY:
BOOLEAN_PRIMARY IS [NOT] NULL
| BOOLEAN_PRIMARY <=> PREDICATE
| BOOLEAN_PRIMARY COMPARISON_OPERATOR PREDICATE
| BOOLEAN_PRIMARY COMPARISON_OPERATOR {ALL | ANY} (SUBQUERY)
| PREDICATE
COMPARISON_OPERATOR: = | >= | > | <= | < | <> | !=
PREDICATE:
BIT_EXPR [NOT] IN (SUBQUERY)
| BIT_EXPR [NOT] IN (EXPR [, EXPR] ...)
| BIT_EXPR [NOT] BETWEEN BIT_EXPR AND PREDICATE
| BIT_EXPR SOUNDS LIKE BIT_EXPR
| BIT_EXPR [NOT] LIKE SIMPLE_EXPR [ESCAPE SIMPLE_EXPR]
| BIT_EXPR [NOT] REGEXP BIT_EXPR
| BIT_EXPR
BIT_EXPR:
BIT_EXPR | BIT_EXPR
| BIT_EXPR & BIT_EXPR
| BIT_EXPR << BIT_EXPR
| BIT_EXPR >> BIT_EXPR
| BIT_EXPR + BIT_EXPR
| BIT_EXPR - BIT_EXPR
| BIT_EXPR * BIT_EXPR
| BIT_EXPR / BIT_EXPR
| BIT_EXPR DIV BIT_EXPR
| BIT_EXPR MOD BIT_EXPR
| BIT_EXPR % BIT_EXPR
| BIT_EXPR ^ BIT_EXPR
| BIT_EXPR + INTERVAL_EXPR
| BIT_EXPR - INTERVAL_EXPR
| SIMPLE_EXPR
SIMPLE_EXPR:
LITERAL
| IDENTIFIER
| FUNCTION_CALL
| SIMPLE_EXPR COLLATE COLLATION_NAME
| PARAM_MARKER
| VARIABLE
| SIMPLE_EXPR || SIMPLE_EXPR
| + SIMPLE_EXPR
| - SIMPLE_EXPR
| ~ SIMPLE_EXPR
| ! SIMPLE_EXPR
| BINARY SIMPLE_EXPR
| (EXPR [, EXPR] ...)
| ROW (EXPR, EXPR [, EXPR] ...)
| (SUBQUERY)
| EXISTS (SUBQUERY)
| {IDENTIFIER EXPR}
| MATCH_EXPR
| CASE_EXPR
| INTERVAL_EXPRFor operator precedence, see *note operator-precedence::. The precedence and meaning of some operators depends on the SQL mode:
By default, '||' is a logical 'OR' operator. With 'PIPES_AS_CONCAT' enabled, '||' is string concatenation, with a precedence between '^' and the unary operators.
By default, '!' has a higher precedence than 'NOT'. With 'HIGH_NOT_PRECEDENCE' enabled, '!' and 'NOT' have the same precedence.
See *note sql-mode::.
Expression Term Notes
For literal value syntax, see *note literals::.
For identifier syntax, see *note identifiers::.
Variables can be user variables, system variables, or stored program local variables or parameters:
User variables: *note user-variables::
System variables: *note using-system-variables::
Stored program local variables: *note declare-local-variable::
Stored program parameters: *note create-procedure::
PARAM_MARKER is '?' as used in prepared statements for placeholders. See *note prepare::.
'(SUBQUERY)' indicates a subquery that returns a single value; that is, a scalar subquery. See *note scalar-subqueries::.
'{IDENTIFIER EXPR}' is ODBC escape syntax and is accepted for ODBC compatibility. The value is EXPR. The '{' and '}' curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.
MATCH_EXPR indicates a 'MATCH' expression. See *note fulltext-search::.
CASE_EXPR indicates a 'CASE' expression. See *note flow-control-functions::.
INTERVAL_EXPR represents a temporal interval. See *note temporal-intervals::.
Temporal Intervals
INTERVAL_EXPR in expressions represents a temporal interval. Intervals have this syntax:
INTERVAL EXPR UNITEXPR represents a quantity. UNIT represents the unit for interpreting the quantity; it is a specifier such as 'HOUR', 'DAY', or 'WEEK'. The 'INTERVAL' keyword and the UNIT specifier are not case-sensitive.
The following table shows the expected form of the EXPR argument for each UNIT value.
Temporal Interval Expression and Unit Arguments
UNIT Value Expected EXPR Format
'MICROSECOND' 'MICROSECONDS'
'SECOND' 'SECONDS'
'MINUTE' 'MINUTES'
'HOUR' 'HOURS'
'DAY' 'DAYS'
'WEEK' 'WEEKS'
'MONTH' 'MONTHS'
'QUARTER' 'QUARTERS'
'YEAR' 'YEARS'
'SECOND_MICROSECOND' ''SECONDS.MICROSECONDS''
'MINUTE_MICROSECOND' ''MINUTES:SECONDS.MICROSECONDS''
'MINUTE_SECOND' ''MINUTES:SECONDS''
'HOUR_MICROSECOND' ''HOURS:MINUTES:SECONDS.MICROSECONDS''
'HOUR_SECOND' ''HOURS:MINUTES:SECONDS''
'HOUR_MINUTE' ''HOURS:MINUTES''
'DAY_MICROSECOND' ''DAYS HOURS:MINUTES:SECONDS.MICROSECONDS''
'DAY_SECOND' ''DAYS HOURS:MINUTES:SECONDS''
'DAY_MINUTE' ''DAYS HOURS:MINUTES''
'DAY_HOUR' ''DAYS HOURS''
'YEAR_MONTH' ''YEARS-MONTHS''
MySQL permits any punctuation delimiter in the EXPR format. Those shown in the table are the suggested delimiters.
Temporal intervals are used for certain functions, such as 'DATE_ADD()' and 'DATE_SUB()':
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
-> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
-> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'Temporal arithmetic also can be performed in expressions using 'INTERVAL' together with the '+' or '-' operator:
date + INTERVAL EXPR UNIT
date - INTERVAL EXPR UNIT'INTERVAL EXPR UNIT' is permitted on either side of the '+' operator if the expression on the other side is a date or datetime value. For the '-' operator, 'INTERVAL EXPR UNIT' is permitted only on the right side, because it makes no sense to subtract a date or datetime value from an interval.
mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '2019-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
-> '2019-01-01'
mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
-> '2024-12-31 23:59:59'The 'EXTRACT()' function uses the same kinds of UNIT specifiers as 'DATE_ADD()' or 'DATE_SUB()', but extracts parts from the date rather than performing date arithmetic:
mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');
-> 2019
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
-> 201907Temporal intervals can be used in *note 'CREATE EVENT': create-event. statements:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;If you specify an interval value that is too short (does not include all the interval parts that would be expected from the UNIT keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify a UNIT of 'DAY_SECOND', the value of EXPR is expected to have days, hours, minutes, and seconds parts. If you specify a value like ''1:10'', MySQL assumes that the days and hours parts are missing and the value represents minutes and seconds. In other words, ''1:10' DAY_SECOND' is interpreted in such a way that it is equivalent to ''1:10' MINUTE_SECOND'. This is analogous to the way that MySQL interprets *note 'TIME': time. values as representing elapsed time rather than as a time of day.
EXPR is treated as a string, so be careful if you specify a nonstring value with 'INTERVAL'. For example, with an interval specifier of 'HOUR_MINUTE', '6/4' is treated as 6 hours, four minutes, whereas '6/4' evaluates to '1.5000' and is treated as 1 hour, 5000 minutes:
mysql> SELECT '6/4', 6/4;
-> 1.5000
mysql> SELECT DATE_ADD('2019-01-01', INTERVAL '6/4' HOUR_MINUTE);
-> '2019-01-01 06:04:00'
mysql> SELECT DATE_ADD('2019-01-01', INTERVAL 6/4 HOUR_MINUTE);
-> '2019-01-04 12:20:00'To ensure interpretation of the interval value as you expect, a 'CAST()' operation may be used. To treat '6/4' as 1 hour, 5 minutes, cast it to a *note 'DECIMAL': fixed-point-types. value with a single fractional digit:
mysql> SELECT CAST(6/4 AS DECIMAL(3,1));
-> 1.5
mysql> SELECT DATE_ADD('1970-01-01 12:00:00',
-> INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
-> '1970-01-01 13:05:00'If you add to or subtract from a date value something that contains a time part, the result is automatically converted to a datetime value:
mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
-> '2023-01-02'
mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
-> '2023-01-01 01:00:00'If you add 'MONTH', 'YEAR_MONTH', or 'YEAR' and the resulting date has a day that is larger than the maximum day for the new month, the day is adjusted to the maximum days in the new month:
mysql> SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
-> '2019-02-28'Date arithmetic operations require complete dates and do not work with incomplete dates such as ''2016-07-00'' or badly malformed dates:
mysql> SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
-> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
-> NULLFile: manual.info.tmp, Node: comments, Prev: expressions, Up: language-structure