Rechercher dans le manuel MySQL

9.5 Expressions

Inhaltsverzeichnis

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

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

Inhaltsverzeichnis 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

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-expressions.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut