以下语法规则定义了 MySQL 中的表达式语法。此处显示的语法基于 MySQL 源代码分发中的 sql/sql_yacc.yy 文件所提供的语法。有关某些表达式项的附加信息，请参见 表达式项注释。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

有关运算符优先级，请参见 第 14.4.1 节，“运算符优先级”。某些运算符的优先级和含义取决于 SQL 模式：For operator precedence, see Section 14.4.1, “Operator Precedence”. The precedence and meaning of some operators depends on the SQL mode:

参见 第 7.1.11 节，“服务器 SQL 模式”。See Section 7.1.11, “Server SQL Modes”.

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

有关标识符语法，请参见 第 11.2 节，“模式对象名称”。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 是 ?，用于准备语句中的占位符。参见 第 15.5.1 节，“PREPARE 语句”。param_marker is ? as used in prepared statements for placeholders. See Section 15.5.1, “PREPARE Statement”.

(subquery) 表示返回单个值的子查询；也就是标量子查询。参见 第 15.2.15.1 节，“作为标量操作数的子查询”。(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} 是 ODBC 转义语法，并且被接受用于 ODBC 兼容性。该值是 expr。语法中的大括号 { 和 } 应该按字面书写；它们不是像其他语法描述中那样的元语法。{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 表示一个 MATCH 表达式。参见 第 14.9 节，“全文搜索函数”。match_expr indicates a MATCH expression. See Section 14.9, “Full-Text Search Functions”.

case_expr 表示一个 CASE 表达式。参见 第 14.5 节，“流程控制函数”。case_expr indicates a CASE expression. See Section 14.5, “Flow Control Functions”.

interval_expr 表示一个时间间隔。参见 时间间隔。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 unit

expr 表示一个数量。unit 表示解释该数量的单位；它是一个指定符，例如 HOUR、DAY 或 WEEK。INTERVAL 关键字和 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.

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

MySQL 允许在 expr 格式中使用任何标点符号作为分隔符。表中显示的是推荐使用的分隔符。MySQL permits any punctuation delimiter in the expr format. Those shown in the table are the suggested delimiters.

时间间隔用于某些函数，例如 DATE_ADD() 和 DATE_SUB()：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'

时间算术也可以在表达式中使用 INTERVAL 结合 + 或 - 运算符来执行：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 可以放在 + 运算符的任一侧，如果另一侧的表达式是日期或日期时间值。对于 - 运算符，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'

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

时间间隔可以在 CREATE EVENT 语句中使用：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;

如果你指定的间隔值过短（不包括 unit 关键字所期望的所有间隔部分），MySQL 会假设你遗漏了间隔值的最左边部分。例如，如果你指定的 unit 是 DAY_SECOND，则 expr 的值应包含天、小时、分钟和秒部分。如果你指定一个像 '1:10' 这样的值，MySQL 会假设缺少了天和小时部分，并将该值解释为分钟和秒。换句话说，'1:10' DAY_SECOND 会被解释为等同于 '1:10' MINUTE_SECOND。这类似于 MySQL 将 TIME 值解释为经过的时间，而不是一天中的时间。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 被视为字符串，因此如果使用 INTERVAL 指定非字符串值时需要小心。例如，对于间隔说明符 HOUR_MINUTE，'6/4' 被视为 6 小时 4 分钟，而 6/4 的计算结果是 1.5000，被视为 1 小时 5000 分钟：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'

为了确保间隔值按预期解释，可以使用 CAST() 操作。要将 6/4 视为 1 小时 5 分钟，可以将其转换为具有一位小数的 DECIMAL 值：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'

如果你添加 MONTH、YEAR_MONTH 或 YEAR，并且结果日期的日大于新月份的最大天数，那么日期会被调整为新月份的最大天数：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'

日期算术操作需要完整的日期，不能与不完整的日期（如 '2016-07-00'）或格式错误的日期一起使用：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