Movatterモバイル変換

• Expression Syntax

• Expression Term Notes

• Temporal Intervals

The following grammar rules define expression syntax in MySQL. The grammar shown here is based on that given in thesql/sql_yacc.yy file of MySQL source distributions. For additional information about some of the expression terms, seeExpression Term Notes.

expr:expr ORexpr  |expr ||expr  |expr XORexpr  |expr ANDexpr  |expr &&expr  | NOTexpr  | !expr  |boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}  |boolean_primaryboolean_primary:boolean_primary IS [NOT] NULL  |boolean_primary <=>predicate  |boolean_primarycomparison_operatorpredicate  |boolean_primarycomparison_operator {ALL | ANY} (subquery)  |predicatecomparison_operator: = | >= | > | <= | < | <> | !=predicate:bit_expr [NOT] IN (subquery)  |bit_expr [NOT] IN (expr [,expr] ...)  |bit_expr [NOT] BETWEENbit_expr ANDpredicate  |bit_expr SOUNDS LIKEbit_expr  |bit_expr [NOT] LIKEsimple_expr [ESCAPEsimple_expr]  |bit_expr [NOT] REGEXPbit_expr  |bit_exprbit_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 DIVbit_expr  |bit_expr MODbit_expr  |bit_expr %bit_expr  |bit_expr ^bit_expr  |bit_expr +interval_expr  |bit_expr -interval_expr  |simple_exprsimple_expr:literal  |identifier  |function_call  |simple_expr COLLATEcollation_name  |param_marker  |variable  |simple_expr ||simple_expr  | +simple_expr  | -simple_expr  | ~simple_expr  | !simple_expr  | BINARYsimple_expr  | (expr [,expr] ...)  | ROW (expr,expr [,expr] ...)  | (subquery)  | EXISTS (subquery)  | {identifierexpr}  |match_expr  |case_expr  |interval_expr

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

SeeSection 5.1.10, “Server SQL Modes”.

For literal value syntax, seeSection 9.1, “Literal Values”.

For identifier syntax, seeSection 9.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. SeeSection 13.5.1, “PREPARE Statement”.

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

{identifierexpr} is ODBC escape syntax and is accepted for ODBC compatibility. The value isexpr. The{ and} curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

match_expr indicates aMATCH expression. SeeSection 12.9, “Full-Text Search Functions”.

case_expr indicates aCASE expression. SeeSection 12.5, “Flow Control Functions”.

interval_expr represents a temporal interval. SeeTemporal Intervals.

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

INTERVALexprunit

expr represents a quantity.unit represents the unit for interpreting the quantity; it is a specifier such asHOUR,DAY, orWEEK. TheINTERVAL keyword and theunit specifier are not case-sensitive.

The following table shows the expected form of theexpr argument for eachunit value.

MySQL permits any punctuation delimiter in theexpr format. Those shown in the table are the suggested delimiters.

Temporal intervals are used for certain functions, such asDATE_ADD() andDATE_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 usingINTERVAL together with the+ or- operator:

date + INTERVALexprunitdate - INTERVALexprunit

INTERVALexprunit is permitted on either side of the+ operator if the expression on the other side is a date or datetime value. For the- operator,INTERVALexprunit 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'

TheEXTRACT() function uses the same kinds ofunit specifiers asDATE_ADD() orDATE_SUB(), but extracts parts from the date rather than performing date arithmetic:

mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');        -> 2019mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');        -> 201907

Temporal intervals can be used inCREATE 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 theunit keyword), MySQL assumes that you have left out the leftmost parts of the interval value. For example, if you specify aunit ofDAY_SECOND, the value ofexpr 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 interpretsTIME 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 withINTERVAL. For example, with an interval specifier ofHOUR_MINUTE, '6/4' is treated as 6 hours, four minutes, whereas6/4 evaluates to1.5000 and is treated as 1 hour, 5000 minutes:

mysql> SELECT '6/4', 6/4;        -> 1.5000mysql> 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, aCAST() operation may be used. To treat6/4 as 1 hour, 5 minutes, cast it to aDECIMAL value with a single fractional digit:

mysql> SELECT CAST(6/4 AS DECIMAL(3,1));        -> 1.5mysql> 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 addMONTH,YEAR_MONTH, orYEAR 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);        -> NULLmysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;        -> NULL