Rechercher dans le manuel MySQL

9.5 Expressions

Contents

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.

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 Expression Term Notes.

  1. expr:
  2.     expr OR expr
  3.   | expr || expr
  4.   | expr XOR expr
  5.   | expr AND expr
  6.   | expr && expr
  7.   | NOT expr
  8.   | ! expr
  9.   | boolean_primary IS [NOT] {TRUE | FALSE | UNKNOWN}
  10.   | boolean_primary
  11.  
  12. boolean_primary:
  13.     boolean_primary IS [NOT] NULL
  14.   | boolean_primary <=> predicate
  15.   | boolean_primary comparison_operator predicate
  16.   | boolean_primary comparison_operator {ALL | ANY} (subquery)
  17.   | predicate
  18.  
  19. comparison_operator: = | >= | > | <= | < | <> | !=
  20.  
  21. predicate:
  22.     bit_expr [NOT] IN (subquery)
  23.   | bit_expr [NOT] IN (expr [, expr] ...)
  24.   | bit_expr [NOT] BETWEEN bit_expr AND predicate
  25.   | bit_expr SOUNDS LIKE bit_expr
  26.   | bit_expr [NOT] LIKE simple_expr [ESCAPE simple_expr]
  27.   | bit_expr [NOT] REGEXP bit_expr
  28.   | bit_expr
  29.  
  30. bit_expr:
  31.     bit_expr | bit_expr
  32.   | bit_expr & bit_expr
  33.   | bit_expr << bit_expr
  34.   | bit_expr >> bit_expr
  35.   | bit_expr + bit_expr
  36.   | bit_expr - bit_expr
  37.   | bit_expr * bit_expr
  38.   | bit_expr / bit_expr
  39.   | bit_expr DIV bit_expr
  40.   | bit_expr MOD bit_expr
  41.   | bit_expr % bit_expr
  42.   | bit_expr ^ bit_expr
  43.   | bit_expr + interval_expr
  44.   | bit_expr - interval_expr
  45.   | simple_expr
  46.  
  47. simple_expr:
  48.     literal
  49.   | identifier
  50.   | function_call
  51.   | simple_expr COLLATE collation_name
  52.   | param_marker
  53.   | variable
  54.   | simple_expr || simple_expr
  55.   | + simple_expr
  56.   | - simple_expr
  57.   | ~ simple_expr
  58.   | ! simple_expr
  59.   | BINARY simple_expr
  60.   | (expr [, expr] ...)
  61.   | ROW (expr, expr [, expr] ...)
  62.   | (subquery)
  63.   | EXISTS (subquery)
  64.   | {identifier expr}
  65.   | match_expr
  66.   | case_expr
  67.   | interval_expr

For operator precedence, see Section 12.3.1, “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 Section 5.1.11, “Server SQL Modes”.

Contents Haut

Expression Term Notes

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

For identifier syntax, see Section 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. See Section 13.5.1, “PREPARE Syntax”.

(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”.

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

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

interval_expr represents a temporal interval. See Temporal Intervals.

Contents Haut

Temporal Intervals

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

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

Table 9.2 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():

  1. mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
  2.         -> '2018-05-02'
  3. mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
  4.         -> '2017-05-01'
  5. mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
  6.     ->                 INTERVAL 1 SECOND);
  7.         -> '2021-01-01 00:00:00'
  8. mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
  9.     ->                 INTERVAL 1 DAY);
  10.         -> '2019-01-01 23:59:59'
  11. mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
  12.     ->                 INTERVAL '1:1' MINUTE_SECOND);
  13.         -> '2101-01-01 00:01:00'
  14. mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
  15.     ->                 INTERVAL '1 1:1:1' DAY_SECOND);
  16.         -> '2024-12-30 22:58:59'
  17. mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
  18.     ->                 INTERVAL '-1 10' DAY_HOUR);
  19.         -> '1899-12-30 14:00:00'
  20. mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
  21.         -> '1997-12-02'
  22. mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
  23.     ->            INTERVAL '1.999999' SECOND_MICROSECOND);
  24.         -> '1993-01-01 00:00:01.000001'

Temporal arithmetic also can be performed in expressions using INTERVAL together with the + or - operator:

  1. date + INTERVAL expr unit
  2. 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.

  1. mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
  2.         -> '2019-01-01 00:00:00'
  3. mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
  4.         -> '2019-01-01'
  5. mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
  6.         -> '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:

  1. mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');
  2.         -> 2019
  3. mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
  4.         -> 201907

Temporal intervals can be used in CREATE EVENT statements:

  1. CREATE EVENT myevent
  2.     ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
  3.     DO
  4.       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:

  1. mysql> SELECT '6/4', 6/4;
  2.         -> 1.5000
  3. mysql> SELECT DATE_ADD('2019-01-01', INTERVAL '6/4' HOUR_MINUTE);
  4.         -> '2019-01-01 06:04:00'
  5. mysql> SELECT DATE_ADD('2019-01-01', INTERVAL 6/4 HOUR_MINUTE);
  6.         -> '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:

  1. mysql> SELECT CAST(6/4 AS DECIMAL(3,1));
  2.         -> 1.5
  3. mysql> SELECT DATE_ADD('1970-01-01 12:00:00',
  4.     ->                 INTERVAL CAST(6/4 AS DECIMAL(3,1)) HOUR_MINUTE);
  5.         -> '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:

  1. mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
  2.         -> '2023-01-02'
  3. mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
  4.         -> '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:

  1. mysql> SELECT DATE_ADD('2019-01-30', INTERVAL 1 MONTH);
  2.         -> '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:

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

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-expressions.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut