Rechercher dans le manuel MySQL

13.2.14 VALUES Statement

Inhoudsopgave

VALUES is a DML statement introduced in MySQL 8.0.19 which returns a set of one or more rows as a table. In other words, it is a table value constructor which also functions as a standalone SQL statement. 

  1. VALUES row_constructor_list [ORDER BY column_designator] [LIMIT BY number]
  2.  
  3. row_constructor_list:
  4.     ROW(value_list)[, ROW(value_list)][, ...]
  5.  
  6. value_list:
  7.     value[, value][, ...]
  8.  
  9. column_designator:
  10.     column_index

The VALUES statement consists of the VALUES keyword followed by a list of one or more row constructors, separated by commas. A row constructor consists of the ROW() row constructor clause with a value list of one or more scalar values enclosed in the parentheses. A value can be a literal of any MySQL data type or an expression that resolves to a scalar value.

ROW() cannot be empty (but each of the supplied scalar values can be NULL). Each ROW() in the same VALUES statement must have the same number of values in its value list.

The DEFAULT keyword is not supported by VALUES and causes a syntax error, except when it is used to supply values in an INSERT statement.

The output of VALUES is a table: 

  1. mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
  2. +----------+----------+----------+
  3. | column_0 | column_1 | column_2 |
  4. +----------+----------+----------+
  5. |        1 |       -2 |        3 |
  6. |        5 |        7 |        9 |
  7. |        4 |        6 |        8 |
  8. +----------+----------+----------+
  9. 3 rows in set (0.00 sec)

The columns of the table output from VALUES have the implicitly named columns column_0, column_1, column_2, and so on, always beginning with 0. This fact can be used to order the rows by column using an optional ORDER BY clause in the same way that this clause works with a SELECT statement, as shown here: 

  1. mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8) ORDER BY column_1;
  2. +----------+----------+----------+
  3. | column_0 | column_1 | column_2 |
  4. +----------+----------+----------+
  5. |        1 |       -2 |        3 |
  6. |        4 |        6 |        8 |
  7. |        5 |        7 |        9 |
  8. +----------+----------+----------+
  9. 3 rows in set (0.00 sec)

The VALUES statement also supports a LIMIT clause for limiting the number of rows in the output.

The VALUES statement is permissive regarding data types of column values; you can mix types within the same column, as shown here: 

  1. mysql> VALUES ROW("q", 42, '2019-12-18'),
  2.     ->     ROW(23, "abc", 98.6),
  3.     ->     ROW(27.0002, "Mary Smith", '{"a": 10, "b": 25}');
  4. +----------+------------+--------------------+
  5. | column_0 | column_1   | column_2           |
  6. +----------+------------+--------------------+
  7. | q        | 42         | 2019-12-18         |
  8. | 23       | abc        | 98.6               |
  9. | 27.0002  | Mary Smith | {"a": 10, "b": 25} |
  10. +----------+------------+--------------------+
  11. 3 rows in set (0.00 sec)
Important

VALUES with one or more instances of ROW() acts as a table value constructor; although it can be used to supply values in an INSERT or REPLACE statement, do not confuse it with the VALUES keyword that is also used for this purpose. You should also not confuse it with the VALUES() function that refers to column values in INSERT ... ON DUPLICATE KEY UPDATE.

You should also bear in mind that ROW() is a row value constructor (see Section 13.2.11.5, “Row Subqueries”, whereas VALUES ROW() is a table value constructor; the two cannot be used interchangeably.

VALUES can be used in many cases where you could employ SELECT, including those listed here:

  • With UNION, as shown here: 

    1. mysql> SELECT 1,2 UNION SELECT 10,15;
    2. +----+----+
    3. | 1  | 2  |
    4. +----+----+
    5. |  1 |  2 |
    6. | 10 | 15 |
    7. +----+----+
    8. 2 rows in set (0.00 sec)
    9.  
    10. mysql> VALUES ROW(1,2) UNION VALUES ROW(10,15);
    11. +----------+----------+
    12. | column_0 | column_1 |
    13. +----------+----------+
    14. |        1 |        2 |
    15. |       10 |       15 |
    16. +----------+----------+
    17. 2 rows in set (0.00 sec)

    It is also possible in this fashion to union together constructed tables having more than one row, like this: 

    1. mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6)
    2.      >     UNION VALUES ROW(10,15),ROW(20,25);
    3. +----------+----------+
    4. | column_0 | column_1 |
    5. +----------+----------+
    6. |        1 |        2 |
    7. |        3 |        4 |
    8. |        5 |        6 |
    9. |       10 |       15 |
    10. |       20 |       25 |
    11. +----------+----------+
    12. 5 rows in set (0.00 sec)

    You can also (and it is usually preferable to) omit UNION altogether in such cases and use a single VALUES statement, like this: 

    1. mysql> VALUES ROW(1,2), ROW(3,4), ROW(5,6), ROW(10,15), ROW(20,25);
    2. +----------+----------+
    3. | column_0 | column_1 |
    4. +----------+----------+
    5. |        1 |        2 |
    6. |        3 |        4 |
    7. |        5 |        6 |
    8. |       10 |       15 |
    9. |       20 |       25 |
    10. +----------+----------+

    VALUES can also be used in unions with SELECT statements, TABLE statements, or both.

    The constructed tables in the UNION must contain the same number of columns, just as if you were using SELECT. See Section 13.2.10.3, “UNION Clause”, for further examples.

  • In joins. See Section 13.2.10.2, “JOIN Clause”, for more information and examples.

  • In place of VALUES() in an INSERT or REPLACE statement, in which case its semantics differ slightly from what is described here. See Section 13.2.6, “INSERT Statement”, for details.

  • In place of the source table in CREATE TABLE ... SELECT and CREATE VIEW ... SELECT. See the descriptions of these statements for more information and examples.


Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-values.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut