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. 以下语法规则定义MySQL中的表达式语法。这里显示的语法基于MySQL源发行版的sql/sql_yacc.yy文件中给出的语法。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 12.4.1, “Operator Precedence”. The precedence and meaning of some operators depends on the SQL mode:运算符优先级，请参阅第12.4.1节，“运算符优先级”。某些运算符的优先级和含义取决于SQL模式：

See Section 5.1.11, “Server SQL Modes”.请参阅第5.1.11节，“服务器SQL模式”。

For literal value syntax, see Section 9.1, “Literal Values”.有关文字值语法，请参阅第9.1节，“文字值”。

For identifier syntax, see Section 9.2, “Schema Object Names”.有关标识符语法，请参阅第9.2节，“模式对象名称”。

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 13.5.1, “PREPARE Statement”.param_marker是?如占位符的准备语句中所用。参见第13.5.1节，“PREPARE语句”。

(subquery) indicates a subquery that returns a single value; that is, a scalar subquery. See Section 13.2.11.1, “The Subquery as Scalar Operand”.(subquery)表示返回单个值的子查询；即标量子查询。参见第13.2.11.1节，“作为标量操作数的子查询”。

{identifier expr} is ODBC escape syntax and is accepted for ODBC compatibility. The value is expr. {identifier expr}是ODBC转义语法，可用于ODBC兼容性。值为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 12.10, “Full-Text Search Functions”.match_expr表示MATCH表达式。参见第12.10节，“全文搜索功能”。

case_expr indicates a CASE expression. See Section 12.5, “Flow Control Functions”.case_expr表示CASE表达式。参见第12.5节，“流量控制功能”。

interval_expr represents a temporal interval. See Temporal Intervals.interval_expr表示时间间隔。请参见时间间隔。

interval_expr in expressions represents a temporal interval. Intervals have this syntax:表达式中的interval_expr表示时间间隔。间隔的语法如下：

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. expr表示一个量。unit表示解释数量的单位；它是一个说明符，如HOUR、DAY或WEEK。The INTERVAL keyword and the unit specifier are not case-sensitive.INTERVAL关键字和unit说明符不区分大小写。

The following table shows the expected form of the expr argument for each unit value.下表显示了每个unit值的expr参数的预期形式。

MySQL permits any punctuation delimiter in the expr format. MySQL允许使用expr格式的任何标点分隔符。Those shown in the table are the suggested delimiters.表中所示为建议的分隔符。

Temporal intervals are used for certain functions, such as DATE_ADD() and DATE_SUB():时间间隔用于某些函数，如DATE_ADD()和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:时间算术也可以在使用INTERVAL和+或-运算符的表达式中执行：

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. 如果+运算符的任一侧的表达式是日期或日期时间值，则允许在其任一侧使用INTERVAL expr unit。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.对于-运算符，只允许在右侧使用INTERVAL expr unit，因为从间隔中减去日期或日期时间值是没有意义的。

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:EXTRACT()函数使用与DATE_ADD()或DATE_SUB()相同类型的unit说明符，但从日期中提取部分，而不是执行日期运算：

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语句：

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. 如果您指定的间隔值太短（不包括unit关键字中预期的所有间隔部分），MySQL会认为您遗漏了间隔值的最左边部分。For example, if you specify a unit of DAY_SECOND, the value of expr is expected to have days, hours, minutes, and seconds parts. 例如，如果指定unit为DAY_SECOND，则expr的值应包含天、小时、分钟和秒。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. 如果指定像'1:10'这样的值，MySQL将假定缺少天和小时部分，该值表示分钟和秒。In other words, '1:10' DAY_SECOND is interpreted in such a way that it is equivalent to '1:10' MINUTE_SECOND. 换句话说，'1:10' DAY_SECOND被解释为等效于'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.这类似于MySQL将TIME值解释为表示经过的时间，而不是一天中的时间。

expr is treated as a string, so be careful if you specify a nonstring value with INTERVAL. expr被视为字符串，因此如果使用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:例如，间隔说明符为HOUR_MINUTE时，“6/4”被视为6小时4分钟，而6/4的计算结果为1.5000，被视为1小时5000分钟：

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. 为了确保按预期解释间隔值，可以使用CAST()操作。To treat 6/4 as 1 hour, 5 minutes, cast it to a DECIMAL value with a single fractional digit:要将6/4视为1小时5分钟，请将其强制转换为带一个小数的DECIMAL值：

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:如果添加MONTH、YEAR_MONTH或YEAR，并且生成的日期的日期大于新月份的最大日期，则该日期将调整为新月份的最长日期：

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:日期算术运算需要完整的日期，不适用于不完整的日期（如'2016-07-00'或格式错误的日期）：

mysql> SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
        -> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
        -> NULL