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 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_expr

For operator precedence, see Section 14.4.1, “Operator Precedence”. The precedence and meaning of some operators depends on the SQL mode:

See Section 7.1.11, “Server SQL Modes”.

For literal value syntax, see Section 11.1, “Literal Values”.

For identifier syntax, see Section 11.2, “Schema Object Names”.

Variables can be user variables, system variables, or stored program local variables or parameters:

param_marker is ? as used in prepared statements for placeholders. See Section 15.5.1, “PREPARE Statement”.

(subquery) indicates a subquery that returns a single value; that is, a scalar subquery. See Section 15.2.15.1, “The Subquery as Scalar Operand”.

{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 Section 14.9, “Full-Text Search Functions”.

case_expr indicates a CASE expression. See Section 14.5, “Flow Control Functions”.

interval_expr represents a temporal interval. See Temporal Intervals.

interval_expr in expressions represents a temporal interval. Intervals have this syntax:

INTERVAL expr unit

expr 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.

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'); -> 201907

Temporal intervals can be used in 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 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 DECIMAL 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; -> NULL