Rechercher dans le manuel MySQL
9.4 User-Defined Variables
You can store a value in a user-defined variable in one statement and refer to it later in another statement. This enables you to pass values from one statement to another.
User variables are written as
@
, where the
variable name var_name
var_name
consists of
alphanumeric characters, .
,
_
, and $
. A user variable
name can contain other characters if you quote it as a string or
identifier (for example, @'my-var'
,
@"my-var"
, or @`my-var`
).
User-defined variables are session specific. A user variable
defined by one client cannot be seen or used by other clients.
(Exception: A user with access to the Performance Schema
user_variables_by_thread
table can
see all user variables for all sessions.) All variables for a
given client session are automatically freed when that client
exits.
User variable names are not case-sensitive. Names have a maximum length of 64 characters.
One way to set a user-defined variable is by issuing a
SET
statement:
For SET
,
either =
or
:=
can be
used as the assignment operator.
User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or nonbinary
string, or NULL
value. Assignment of decimal
and real values does not preserve the precision or scale of the
value. A value of a type other than one of the permissible types
is converted to a permissible type. For example, a value having a
temporal or spatial data type is converted to a binary string. A
value having the JSON
data type is
converted to a string with a character set of
utf8mb4
and a collation of
utf8mb4_bin
.
If a user variable is assigned a nonbinary (character) string value, it has the same character set and collation as the string. The coercibility of user variables is implicit. (This is the same coercibility as for table column values.)
Hexadecimal or bit values assigned to user variables are treated
as binary strings. To assign a hexadecimal or bit value as a
number to a user variable, use it in numeric context. For example,
add 0 or use CAST(... AS UNSIGNED)
:
- +------+------+------+
- | @v1 | @v2 | @v3 |
- +------+------+------+
- | A | 65 | 65 |
- +------+------+------+
- +------+------+------+
- | @v1 | @v2 | @v3 |
- +------+------+------+
- | A | 65 | 65 |
- +------+------+------+
If the value of a user variable is selected in a result set, it is returned to the client as a string.
If you refer to a variable that has not been initialized, it has a
value of NULL
and a type of string.
User variables may be used in most contexts where expressions are
permitted. This does not currently include contexts that
explicitly require a literal value, such as in the
LIMIT
clause of a
SELECT
statement, or the
IGNORE
clause of a N
LINESLOAD DATA
statement.
Previous releases of MySQL made it possible to assign a value to a
user variable in statements other than
SET
. This
functionality is supported in MySQL 8.0 for backward
compatibility but is subject to removal in a future release of
MySQL.
When making an assignment in this way, you must use
:=
as the
assignment operator; =
is treated as the
comparison operator in statements other than
SET
.
The order of evaluation for expressions involving user variables
is undefined. For example, there is no guarantee that
SELECT @a, @a:=@a+1
evaluates
@a
first and then performs the assignment.
In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.
To avoid problems with this behavior, either do not assign a value
to and read the value of the same variable within a single
statement, or else set the variable to 0
,
0.0
, or ''
to define its
type before you use it.
HAVING
, GROUP BY
, and
ORDER BY
, when referring to a variable that is
assigned a value in the select expression list do not work as
expected because the expression is evaluated on the client and
thus can use stale column values from a previous row.
User variables are intended to provide data values. They cannot be
used directly in an SQL statement as an identifier or as part of
an identifier, such as in contexts where a table or database name
is expected, or as a reserved word such as
SELECT
. This is true even if the
variable is quoted, as shown in the following example:
- +----+
- | c1 |
- +----+
- | 0 |
- +----+
- | 1 |
- +----+
- Query OK, 0 rows affected (0.00 sec)
- +------+
- | @col |
- +------+
- | c1 |
- +------+
- Query OK, 0 rows affected (0.00 sec)
- +------+
- | @col |
- +------+
- | `c1` |
- +------+
An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement. The following example illustrates how this can be done:
- Query OK, 0 rows affected (0.00 sec)
- Query OK, 0 rows affected (0.00 sec)
- Query OK, 0 rows affected (0.04 sec)
- Statement prepared
- mysql> EXECUTE stmt;
- +----+
- | c1 |
- +----+
- | 0 |
- +----+
- | 1 |
- +----+
- mysql> DEALLOCATE PREPARE stmt;
- Query OK, 0 rows affected (0.00 sec)
See Section 13.5, “Prepared SQL Statement Syntax”, for more information.
A similar technique can be used in application programs to construct SQL statements using program variables, as shown here using PHP 5:
<?php
$mysqli = new mysqli("localhost", "user", "pass", "test");
if( mysqli_connect_errno() )
die("Connection failed: %s\n", mysqli_connect_error());
$col = "c1";
$query = "SELECT $col FROM t";
$result = $mysqli->query($query);
while($row = $result->fetch_assoc())
{
echo "<p>" . $row["$col"] . "</p>\n";
}
$result->close();
$mysqli->close();
?>
Assembling an SQL statement in this fashion is sometimes known as “Dynamic SQL”.
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-user-variables.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
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.