Chapitre 9 Structure du langage

Table des matières

9.1 Littéraux : comment écrire les chaînes et les nombres
9.1.1 Chaînes
9.1.2 Nombres
9.1.3 Valeurs hexadécimales
9.1.4 Valeurs booléennes
9.1.5 Champs de bits
9.1.6 Valeurs NULL
9.2 Noms de bases, tables, index, colonnes et alias
9.2.1 Identifiants
9.2.2 Sensibilité à la casse pour les noms
9.3 Variables utilisateur
9.4 Variables système
9.4.1 Variables système structurées
9.5 Syntaxe des commentaires
9.6 Cas des mots réservés MySQL

Ce chapitre présente les règles d'écriture des commandes SQL avec MySQL :

9.1 Littéraux : comment écrire les chaînes et les nombres

Cette section décrit les différents fa¸ons d'écrire les chaînes et les nombres en MySQL. Elle couvre aussi les différentes nuances et quiproquos que vous pouvez rencontrer lorsque vous manipulez ces types de données.

9.1.1 Chaînes

Une chaîne est une séquence de caractères, entourée de guillemets simples (‘'’) ou doubles (‘"’). Exemples:

Si le serveur SQL est en mode ANSI_QUOTES, les chaînes littérales ne peuvent être mises qu'entre guillemets simples. Une chaîne avec des guillemets double sera interprétée comme un identifiant.

'une chaîne'
"une autre chaîne"

Depuis MySQL 4.1.1, les littéraux disposent d'une option de jeu de caractères et de collation avec la clause COLLATE :

[_charset_name]'string' [COLLATE collation_name]

Exemples :

SELECT _latin1'string';
SELECT _latin1'string' COLLATE latin1_danish_ci;

Pour plus d'informations sur ces formes de chaîne de caractères, voyez Section 10.3.7, « Jeu de caractères et collation des chaînes littérales ».

A l'intérieur d'une chaîne, certains séquences de caractères ont une signification spéciale. Chacune d'elle commence par un anti-slash (‘\’), connu comme le caractère d'échappement. MySQL reconnaît les séquences suivantes :

\0 Un 0 ASCII (NUL).
\' Un guillemet simple (‘'’).
\" Un guillemet double (‘"’).
\b Un effacement.
\n Une nouvelle ligne.
\r Un retour chariot.
\t Une tabulation.
\z ASCII(26) (Contrôle-Z). Ce caractère peut être encodé pour vous éviter des problèmes avec Windows, vu qu'il équivaut à une fin de fichier sur cet OS. (ASCII(26) vous posera des problèmes si vous utilisez mysql base < fichier.)
\\ Un anti-slash (‘\’).
\% Un signe pourcentage littéral : ‘%’. Voir les notes ci-dessous.
\_ Un signe souligné littéral : ‘_’. Voir les notes ci-dessous.

Ces séquences sont sensibles à la casse. Par exemple, ‘\b’ est interprétée comme un anti-slash, mais ‘\B’ est interprété comme la lettre ‘B’.

Les caractères ‘\%’ et ‘\_’ sont utilisés pour rechercher des chaînes littérales ‘%’ et ‘_’ dans un contexte d'expressions régulières. Sinon, ces caractères sont interprétés comme des caractères joker. See Section 12.3.1, « Opérateurs de comparaison pour les chaînes de caractères ». Notez que si vous utilisez ‘\%’ ou ‘\_’ dans d'autres contextes, ces séquences retourneront ‘\%’ et ‘\_’ et non ‘%’ et ‘_’.

Il y a plusieurs fa¸ons d'intégrer un guillemet dans une chaîne :

  • Un ‘'’ à l'intérieur d'une chaîne entourée de ‘'’ peut être noté ‘''’.

  • Un ‘"’ à l'intérieur d'une chaîne entourée de ‘"’ peut être noté ‘""’.

  • Vous pouvez faire précéder le guillemet par caractère d'échappement (‘\’).

  • Un guillemet simple ‘'’ à l'intérieur d'une chaîne à guillemets doubles ‘"’ n'a besoin d'aucun traitement spécial (ni doublage, ni échappement). De même, aucun traitement spécial n'est requis pour un guillemet double ‘"’ à l'intérieur d'une chaîne à guillemets simples ‘'’.

Le SELECT montré ici explique comment les guillemets et les échappements fonctionnent :

mysql> SELECT 'bonjour', '"bonjour"', '""bonjour""', 'bon''jour', '\'bonjour';
+---------+-----------+-------------+----------+----------+
| bonjour | "bonjour" | ""bonjour"" | bon'jour | 'bonjour |
+---------+-----------+-------------+----------+----------+

mysql> SELECT "bonjour", "'bonjour'", "''bonjour''", "bon""jour", "\"bonjour";
+---------+-----------+-------------+----------+----------+
| bonjour | 'bonjour' | ''bonjour'' | bon"jour | "bonjour |
+---------+-----------+-------------+----------+----------+

mysql> SELECT "Voilà\n3\nlignes";
+--------------------+
| Voilà
3
lignes |
+--------------------+

Si vous voulez insérer des données binaires dans un champ chaîne (comme un BLOB), les caractères suivants doivent être échappés :

NULASCII 0. Représentez le avec ‘\0’ (un anti-slash suivi du caractère ASCII ‘0’).
\ASCII 92, anti-slash. A représenter avec ‘\\’.
'ASCII 39, guillemet simple. A représenter avec ‘\'’.
"ASCII 34, guillemet double. A représenter avec ‘\"’.

Lorsque vous écrivez des applications, toutes les chaînes qui risquent de contenir ces caractères spéciaux doivent être protégés avant d'être intégrée dans la commande SQL. Vous pouvez faire cela de deux manières différentes :

  • passez la chaîne à une fonction qui protège les caractères spéciaux. Par exemple, en langage C, vous pouvez utiliser la fonction mysql_real_escape_string(). See Section 24.2.3.47, « mysql_real_escape_string() ». L'interface Perl DBI fournit une méthode basée sur les guillemets pour convertir les caractères spéciaux en leur séquence correspondante. See Section 24.4, « API Perl pour MySQL ».

  • Au lieu de protéger explicitement tous les caractères, de nombreuses interfaces MySQL fournissent un système de variables qui vous permettent de mettre des marqueurs dans la requête, et de lier les variables à leur valeur au moment de leur exécution. Dans ce cas, l'interface se charge de protéger les caractères spéciaux pour vous.

9.1.2 Nombres

Les entiers sont représentés comme une séquence de chiffres. Les décimaux utilisent ‘.’ comme séparateur. Tous les types de nombres peuvent être précédés d'un ‘-’ pour indiquer une valeur négative.

Exemples d'entiers valides :

1221
0
-32

Exemples de nombres à virgule flottante :

294.42
-32032.6809e+10
148.00

Un entier peut être utilisé dans un contexte décimal, il sera interprété comme le nombre décimal équivalent.

9.1.3 Valeurs hexadécimales

MySQL supporte les valeurs hexadécimales. Dans un contexte numérique, elles agissent comme des entiers (précision 64-bit). Dans un contexte de chaîne, elles agissent comme une chaîne binaire où chaque paire de caractères hexadécimaux est convertie en caractère :

mysql> SELECT x'4D7953514C';
        -> 'MySQL'
mysql> SELECT 0xa+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> 'Paul'

En MySQL 4.1 (et en MySQL 4.0 si vous utilisez l'option --new), le type par défaut d'une valeur hexadécimale est chaîne. Si vous voulez vous assurer qu'une telle valeur est traitée comme un nombre, vous pouvez utiliser CAST(... AS UNSIGNED) :

mysql> SELECT 0x41, CAST(0x41 AS UNSIGNED);
        -> 'A', 65

La syntaxe 0x est basée sur ODBC. Les chaînes hexadécimales sont souvent utilisées par ODBC pour fournir des valeurs aux colonnes BLOB. La syntaxe x'hexstring' est nouvelle en 4.0 et est basée sur le standard SQL.

Depuis MySQL 4.0.1, vous pouvez convertir une chaîne ou nombre en chaîne au format hexadécimal avec la fonction HEX() :

mysql> SELECT HEX('cat');
        -> '636174'
mysql> SELECT 0x636174;
        -> 'cat'

9.1.4 Valeurs booléennes

Depuis MySQL version 4.1, la constante TRUE vaut 1 et la constante FALSE vaut 0. Les noms des constantes peuvent être écrit en minuscules ou majuscules.

mysql> SELECT TRUE, true, FALSE, false;
        -> 1, 1, 0, 0

9.1.5 Champs de bits

Depuis MySQL 5.0.3, les champs de bits peuvent être écrits avec la notation b'value'. value est une valeur binaire écrite avec des zéros et des uns.

La notation en champ de bit est pratique pour spécifier des données qui doivent être assignées à une colonne de type BIT :

mysql> CREATE TABLE t (b BIT(8));
mysql> INSERT INTO t SET b = b'11111111';
mysql> INSERT INTO t SET b = b'1010';
+------+----------+----------+----------+
| b+0  | BIN(b+0) | OCT(b+0) | HEX(b+0) |
+------+----------+----------+----------+
|  255 | 11111111 | 377      | FF       |
|   10 | 1010     | 12       | A        |
+------+----------+----------+----------+

9.1.6 Valeurs NULL

La valeur NULL signifie ``pas de données'' et est différente des valeurs comme 0 pour les nombres ou la chaîne vide pour les types chaîne. See Section A.5.3, « Problèmes avec les valeurs NULL ».

NULL peut être représenté par \N lors de la récupération ou écriture avec des fichiers (LOAD DATA INFILE, SELECT ... INTO OUTFILE). See Section 13.1.5, « Syntaxe de LOAD DATA INFILE ».

9.2 Noms de bases, tables, index, colonnes et alias

Les noms des bases de données, tables, index, colonnes et alias suivent tous les mêmes règles en MySQL.

La table suivante décrit la taille maximale et les caractères autorisées pour chaque type d'identifiant.

IdentifiantLongueur maximaleCaractères autorisés
Base de données64Tous les caractères autorisés dans un nom de dossier à part ‘/’, ‘\’ et ‘.’.
Table64Tous les caractères autorisés dans le nom d'un fichier à part ‘/’ et ‘.’.
Colonne64Tous.
Index64Tous.
Alias255tous.

Notez qu'en plus de ce qui précède, vous n'avez pas droit au caractères ASCII(0) ou ASCII(255) dans un identifiant. Avant MySQL 4.1, les identifiants que pouvaient pas contenir de guillemets.

Depuis MySQL 4.1, les identifiants sont stockés en Unicode (UTF8). Cela s'applique aux identifiants stockés dans les tables de définitions du fichier .frm, et aux identifiants stockés dans les tables de droits de la base mysql. Même si les identifiants Unicode peuvent inclure des caractères multi-octets, notez que les tailles maximales affichées dans la table sont donnés en octets. Si un identifiant contient un caractère multi-octet, le nombre de caractères autorisé est alors inférieur aux chiffres affichés.

Un identifiant peut être entre guillemet ou pas. Si un identifiant est un mot réservé, ou qu'il contient des caractères spéciaux, vous devez le mettre entre guillemets lorsque vous l'utilisez. Pour une liste des mots réservés, voyez Section 9.6, « Cas des mots réservés MySQL ». Les caractères spéciaux sont hors de la plage des caractères alpha-numérique et ‘_’ et ‘$’.

Notez que si un identifiant est un mot réservé, ou contient des caractères spéciaux, vous devez absolument le protéger avec ‘`’ :

Le caractère de protection des identifiants est le guillemet oblique ‘`’ :

mysql> SELECT * FROM `select` WHERE `select`.id > 100;

Si vous utilisez MySQL avec les modes MAXDB ou ANSI_QUOTES, il est aussi possible d'utiliser les guillemets doubles pour les identifiants :

mysql> CREATE TABLE "test" (col INT);
ERROR 1064: You have an error in your SQL syntax. (...)
mysql> SET SQL_MODE="ANSI_QUOTES";
mysql> CREATE TABLE "test" (col INT);
Query OK, 0 rows affected (0.00 sec)

See Section 1.5.2, « Sélectionner les modes SQL ».

Depuis MySQL 4.1, les guillemets peuvent être inclus dans les noms d'identifiants. Si le caractère inclus dans l'identifiant est le même que celui qui est utilisé pour protéger l'identifiant, doublez-le. La commande suivante crée la table a`b, qui contient la colonne c"d :

mysql> CREATE TABLE `a``b` (`c"d` INT);

La protection d'identifiant par guillemets a été introduite en MySQL 3.23.6 pour permettre l'utilisation de mots réservés ou des caractères spéciaux dans les noms de colonnes. Avant la version 3.23.6, vous ne pouviez pas utiliser les identifiants qui utilisent des guillemets, et les règles sont plus restrictives :

  • Un nom d'identifiant est constitué de caractères alpha-numériques issus du jeu de caractères courant, plus ‘_’ et ‘$’. Le jeu de caractères par défaut est ISO-8859-1 (Latin1). Cela peut être modifié avec l'option de démarrage --default-character-set de mysqld. See Section 5.8.1, « Le jeu de caractères utilisé pour les données et le stockage ».

  • Un nom peut commencer avec n'importe quel caractère qui est valide dans un identifiant. En particulier, un nom peut commencer par un chiffre : cela est différent dans de nombreuses autres bases de données. Cependant, un nom sans guillemets ne peut pas contenir uniquement des chiffres.

  • Vous ne pouvez pas utiliser le caractère ‘.’ dans les noms, car il est utilisé pour les formats complets de références aux colonnes, en utilisant les noms de tables ou de bases. (see Section 9.2.1, « Identifiants »).

Il est recommandé de ne pas utiliser de noms comme 1e, car une expression comme 1e+1 est ambigue. Elle pourrait être interprétée comme l'expression 1e + 1 ou comme le nombre 1e+1, suivant le contexte.

9.2.1 Identifiants

MySQL autorise l'utilisation de nom qui sont constitués d'un seul identifiant, ou d'identifiants multiples. Les composants d'un tel nom doivent être séparés par le caractère point (‘.’). Le premier composant détermine le contexte dans lequel l'identifiant final est interprété.

En MySQL, vous pouvez faire référence à une colonne en utilisant l'une des trois formes suivantes :

Référence de colonneSignification
col_nameLa colonne col_name de n'importe quelle table de la requête qui contient une colonne de ce nom.
tbl_name.col_nameLa colonne col_name de la table tbl_name de la base de données par défaut.
db_name.tbl_name.col_nameLa colonne col_name de la table tbl_name de la base db_name. Cette syntaxe n'est pas disponible avant MySQL version 3.22.

Si un composant d'un nom complexe requiert des guillemets de protection, vous devez protéger chaque composant individuellement plutôt que l'identifiant dans son ensemble. Par exemple, `my-table`.`my-column` est valide, mais `my-table.my-column` ne l'est pas.

Vous n'êtes pas obligé de spécifier le nom d'une table tbl_name ou le nom de la base db_name.tbl_name comme préfixe dans une requête, à moins que la référence soit ambigüe. Supposez que les tables t1 et t2 contiennent toutes les deux une colonne c, et que vous lisez le contenu de la colonne c dans une commande SELECT qui utilise les deux tables t1 et t2. Dans ce cas, c est ambigue, car ce n'est pas un nom unique dans les différentes tables de la commande. Vous devez le préciser avec un nom de table, comme dans t1.c ou t2.c. Similairement, si vous lisez des colonnes dans une table t de la base db1 et dans la table t de la base db2 dans la même commande, vous devez utiliser les noms complets de colonnes, comme db1.t.col_name et db2.t.col_name.

La syntaxe .tbl_name correspond à la table tbl_name de la base courante. Cette syntaxe est acceptée pour la compatibilité avec ODBC, car certains programmes ODBC préfixent le nom de la table avec un caractère point ‘.’.

9.2.2 Sensibilité à la casse pour les noms

En MySQL, les bases et les tables correspondent à des dossiers et des fichiers. Les tables dans une base correspondent au moins à un fichier dans le dossier de base et possiblement plusieurs, suivant le moteur de table utilisé. Par conséquent, la sensibilité à la casse du système déterminera la sensibilité à la casse des noms de bases de données et tables. Cela signifie que les noms sont insensibles à la casse sous Windows, et sensibles sous la plupart des variétés Unix. Mac OS X est une exception car il est basé sur Unix, mais le système de fichiers par défaut (HFS+) n'est pas sensible à la casse. Cependant, Mac OS X supporte aussi les volumes UFS, qui sont sensibles à la casse, comme les autres Unix. See Section 1.5.4, « Extensions MySQL au standard SQL-92 ».

Note : même si les noms ne sont pas sensibles à la casse sous Windows, vous ne devez pas vous réfèrer à une entité en utilisant différentes casse dans la même requête. La requête suivante ne fonctionnera pas car elle se référe à une table avec ma_table et MA_TABLE :

mysql> SELECT * FROM ma_table WHERE MA_TABLE.col=1;

Les noms de colonnes et d'alias sont insensibles à la casse dans tous les cas.

Les alias sur tables sont sensibles à la casse avant MySQL 4.1.1. La requête suivante ne marchera pas car elle se réfère à a et A :

mysql> SELECT nom_de_colonne FROM nom_de_table AS a
    ->                 WHERE a.nom_de_colonne = 1 OR A.nom_de_colonne = 2;

Si vous avez du mal à vous souvenir de la casse des noms de bases et de tables, adoptez une convention, comme toujours créer les bases et les tables en utilisant des minuscules.

La fa¸on de stocker les noms sur le disque et leur utilisation dans les syntaxes MySQL est définie par la variable lower_case_table_names, qui peuvent être spécifié au lancement de mysqld. lower_case_table_names peut prendre l'une des valeurs suivantes :

ValeurSignification
0Les noms de tables et bases sont stockées sur le disque avec la casse utilisée dans la commande CREATE TABLE ou CREATE DATABASE. Les comparaisons de nom sont sensibles à la casse. C'est le comportement par défaut sous Unix. Notez que si vous forcer cette valeur à 0 avec l'option --lower-case-table-names=0 sur un système insensible à la casse, et que vous accéder à la table avec une autre casse, alors vous pouvez avoir des corruptions d'index.
1Les noms de tables sont stockées en minuscules sur le disque, et les comparaisons de nom de tables sont insensibles à la casse. Ce comportement s'applique aussi aux noms de bases de données depuis MySQL 4.0.2, et aux alias de tables depuis 4.1.1. C'est la valeur par défaut sur les systèmes Windows et Mac OS X.
2Les tables et bases sont stockées sur le disque avec la casse spécifiée dans CREATE TABLE et CREATE DATABASE, mais MySQL les convertis en minuscules lors des recherches. Les comparaisons de noms sont insensibles à la casse. Note : cela ne fonctionne que sur les systèmes de fichiers qui sont insensibles à la casse. Les noms de tables InnoDB sont stockées en minuscules, comme pour lower_case_table_names=1. Donne à lower_case_table_names la valeur de 2 est possible depuis MySQL 4.0.18.

Si vous utilisez MySQL sur une seule plate-forme, vous n'aurez pas à changer la valeur de lower_case_table_names variable. Cependant, vous pouvez rencontrer des problèmes lors des transferts entre plates-formes, où les systèmes de fichiers différent de par leur sensibilité à la casse. Par exemple, sous Unix, vous pouvez avoir deux tables ma_table et MA_TABLE, alors que sous Windows, ces deux noms représentent la même table. Pour éviter les problèmes de transferts de noms, vous avez deux choix :

  • Utilisez l'option lower_case_table_names=1 sur tous les systèmes. L'inconvénient le plus sérieux est que lorsque vous utilisez SHOW TABLES ou SHOW DATABASES, vous ne verrez pas la casse originale des noms.

  • Utilisez lower_case_table_names=0 sous Unix et lower_case_table_names=2 sous Windows. Cela préserver la casse des noms de tables ou bases. L'inconvénient est que vous devez vous assurer que les requêtes utilisent toujours la bonne casse sous Windows. Si vous transférez vos requêtes vers Unix, où la casse des noms aura son importance, les requêtes ne fonctionneraient plus.

Notez qu'avant de passer la valeur de lower_case_table_names à 1 sous Unix, vous devez commencer par convertir vos anciens noms de tables et bases en minuscules, avant de redémarrer mysqld.

9.3 Variables utilisateur

MySQL supporte les variables utilisateur spécifiques à la connexion avec la syntaxe @variablename. Un nom de variable consiste de caractères alpha-numériques, basés sur le jeu de caractères courant, de ‘_’, ‘$’, et ‘.’. Le jeu de caractères par défaut est ISO-8859-1 Latin1. Cette valeur peut être changée en utilisant l'option --default-character-set de mysqld. See Section 5.8.1, « Le jeu de caractères utilisé pour les données et le stockage ».

Les variables n'ont pas besoin d'être initialisés. Elles sont à NULL par défaut et peuvent contenir un entier, un réel ou une chaîne. Toutes les variables d'un thread sont automatiquement libérées lorsque le thread se termine.

Vous pouvez déclarer une variable avec la syntaxe de SET :

SET @variable= { expression entier | expression réel | expression chaîne }
[,@variable= ...].

Vous pouvez aussi assigner une valeur à une variable avec d'autres commande que SET. Par contre, dans ce cas là, l'opérateur d'assignation est := au lieu de =, parce que = est réservé aux comparaisons dans les requêtes autres que SET :

mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

Les variables utilisateur peuvent être utilisés là où les expressions sont allouées. Notez que cela n'inclut pas pour l'instant les contextes où un nombre est explicitement requis, comme ce qui est le cas avec la clause LIMIT dans une requête SELECT, ou la clause IGNORE nombre LINES dans une requête LOAD DATA.

Note : dans une requête SELECT, chaque expression est n'évaluée que lors de l'envoi au client. Cela signifie que pour les clauses HAVING, GROUP BY, ou ORDER BY, vous ne pouvez vous référer à une expression qui implique des variables qui sont définies dans la partie SELECT. Par exemple, la requête suivante ne produira pas le résultat escompté :

mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM nom_de_table HAVING b=5;

La raison est que @aa ne contiendra pas la valeur de la ligne courante, mais celle de id pour la dernière ligne acceptée.

La règle générale est de ne jamais assigner et utiliser la même variable dans la même commande.

Un autre problème avec l'affectation de variable et son utilisation dans la même commande est que le type de valeur par défaut est basé sur le type de la variable dans la commande de départ. Une variable non-assignée est supposée être de type NULL ou de type STRING. L'exemple suivant illustre bien ce cas de figure :

mysql> SET @a="test";
mysql> SELECT @a,(@a:=20) FROM table_name;

Dans ce cas, MySQL va indiquer au client que la colonne 1 est une chaîne, et il convertira tous les accès à la variable @a en chaîne, même si @a recevra finalement un nombre dans la seconde ligne. Après l'exécution de la commande, @a sera considéré comme un nombre.

Si vous avez des problèmes avec cela, essayer d'éviter d'affecter et d'utiliser la même variable dans la même commande, ou bien initialisez la variable avec les valeurs de 0, 0.0 ou "" avant de l'utiliser.

9.4 Variables système

A partir de la version 4.0.3, nous fournissons un meilleur accès à beaucoup de variables système et variables de connexion. On peut changer la plupart d'entre elle sans avoir à stopper le serveur.

Le serveur mysqld dispose de deux types de variables. Les variables globales, qui affectent l'ensemble du serveur. Les variables de session qui affectent des connexions individuelles.

Lorsque mysqld démarre, toutes les variables globales sont initialisées à partir des arguments passés en ligne de commande et des fichiers de configuration. Vous pouvez changer ces valeurs avec la commande SET GLOBAL. Lorsqu'un nouveau thread est créé, les variables spécifiques aux threads sont initialisées à partir des variables globales et ne changeront pas même si vous utilisez la commande SET GLOBAL.

Le serveur entretient aussi un jeu de variables de session pour chaque client qui se connecte. Les variables de session d'un client sont initialisées au moment de la connexion, en utilisant les valeurs correspondantes globales. Pour les variables de session qui sont dynamiques, le client peut les changer avec la commande SET SESSION var_name. Modifier les variables de session d'une connexion ne requiert aucun droit spécifique, mais le client ne peut changer que ses propres variables de session, et non pas celle d'un autre client.

Une modification à une variable globale est visible à tous les clients qui accèdent à cette variable. Mais, cela n'affecte la variable de session correspondante que lors de la prochaine connexion. Les connexions déjà établies ne sont pas affectées par un changement de variable globale. (pas même le client qui a émis la commande SET GLOBAL).

Pour définir la valeur d'une variable GLOBAL, vous devez utiliser l'une des syntaxes suivantes. Ici nous utilisons la variable sort_buffer_size à titre d'exemple.

Pour donner la valeur à une variable GLOBAL, utilisez l'une de ces syntaxes :

mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;

Pour définir la valeur d'une variable SESSION, vous devez utiliser l'une des syntaxes suivantes :

mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;

LOCAL est un synonyme de SESSION.

Si vous ne spécifiez pas GLOBAL ou SESSION alors SESSION est utilisé. See Section 13.5.2.8, « Syntaxe de SET ».

Pour récupérer la valeur d'une variable de type GLOBAL vous pouvez utiliser l'une des commandes suivantes :

mysql> SELECT @@global.sort_buffer_size;
mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';

Pour récupérer la valeur d'une variable de type SESSION vous pouvez utiliser l'une des commandes suivantes :

mysql> SELECT @@sort_buffer_size;
mysql> SELECT @@session.sort_buffer_size;
mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';

Ici aussi, LOCAL est un synonyme de SESSION.

Lorsque vous récupérez une valeur de variable avec la syntaxe @@nom_variable et que vous ne spécifiez pas GLOBAL ou SESSION, MySQL retournera la valeur spécifique au thread (SESSION) si elle existe. Sinon, MySQL retournera la valeur globale.

Pour la commande SHOW VARIABLES, si vous ne spécifiez pas GLOBAL, SESSION ou LOCAL, MySQL retourne les valeurs de SESSION.

La raison d'imposer la présence du mot GLOBAL pour configurer une variable de type GLOBAL mais non pour la lire est pour être sur que vous n'aurez pas de problèmes plus tard si vous voulez introduire ou effacer une variable spécifique au thread qui aurait le même nom. Dans ce cas, vous pourriez changer accidentellement l'état du serveur pour toutes les connexions (et non la votre uniquement).

Plus d'informations sur les options de démarrage du système et les variables système sont dans les sections Section 5.2.1, « Options de ligne de commande de mysqld » et Section 5.2.3, « Variables serveur système ». Une liste des variables qui peuvent être modifiées durant l'exécution est présentée dans Section 5.2.3.1, « Variables système dynamiques ».

9.4.1 Variables système structurées

Les variables système structurées sont supportées depuis MySQL 4.1.1. Une variable structurée diffère d'une variable système classique sur deux points :

  • Sa valeur est une structure avec des composants qui spécifient des paramètres serveurs qui sont étroitement liés.

  • Il peu y avoir plusieurs instances d'une même variable système structurée. Chacun d'entre elles a un nom différent, et fait référence à une ressource différente, gérée sur le serveur.

Actuellement, MySQL supporte un type de variables système structurées. Il spécifie les paramètres qui gouvernement les caches de clé. Une variable système structurée de cache de clé contient les composants suivants :

  • key_buffer_size

  • key_cache_block_size

  • key_cache_division_limit

  • key_cache_age_threshold

L'objectif de cette section est de décrire la syntaxe d'opération avec les variables système structurées. Les variables du cache de clé sont utilisées comme exemple syntaxique, mais les détails concernant le cache de clé sont disponibles dans la section Section 7.4.6, « Le cache de clé des tables MyISAM ».

Pour faire référence à un composant d'une variable système structurée, vous pouvez utiliser un nom composé, au format nom_d_instance.nom_du_composant. Par exemple :

hot_cache.key_buffer_size
hot_cache.key_cache_block_size
cold_cache.key_cache_block_size

Pour chaque variables système structurée, un instance avec le nom default est toujours prédéfinie. Si vous faites référence à un composant d'une variables système structurée sans aucun nom d'instance, l'instance default sera utilisée. Par conséquent, default.key_buffer_size et key_buffer_size font références à la même variable système structurée.

Les règles de nommage pour les variables système structurées et ses composants sont les suivantes :

  • Pour un type donnée de variables système structurées, chaque instance doit avoir un nom unique à l'intérieur de ce type. Cependant, les noms d'instances doivent être unique à l'intérieur des types de variables système structurées. Par exemple, chaque variable système structurée aura une instance default, ce qui fait que default n'est pas unique à l'intérieur des types de variables.

  • Les noms de composants de chaque variable système structurée doit être unique à travers tous les noms de variables systèmes. Si ce n'était pas vrai (c'est à dire, si deux types de variables structurées pouvaient partager des noms de composants), il ne serait pas facile de trouver la variable structurée par défaut, pour ce type.

  • Si un nom de variable système structurée n'est pas valide en tant qu'identifiant non protégé, il faut utiliser les guillemets obliques pour le protéger. Par exemple, hot-cache n'est pas valide, mais `hot-cache` l'est.

  • global, session, et local ne sont pas des noms de composants valides. Cela évite les conflits avec des notations comme @@global.var_name, qui fait référence à des variables système non-structurées.

Actuellement, les deux premières règles ne peuvent pas être violées, puisqu'il n'y a qu'un seul type de variables système structurées, celui des caches de clés. Ces règles prendront leur importance si d'autres types de variables structurées sont créées dans le futur.

A une exception près, il est possible de faire référence à une variable système structurée en utilisant des noms composés, dans un contexte où un nom de variable simple est utilisé. Par exemple, vous pouvez assigner une valeur à une variable structurée en utilisant la ligne de commande suivante :

shell> mysqld --hot_cache.key_buffer_size=64K

Dans un fichier d'options, faites ceci :

[mysqld]
hot_cache.key_buffer_size=64K

Si vous lancez le serveur avec cette option, il va créer un cache de clé appelé hot_cache, avec une taille de 64 ko, en plus du cache de clé par défaut, qui a une taille de 8 Mo.

Supposez que vous démarriez le serveur avec ces options :

shell> mysqld --key_buffer_size=256K \
         --extra_cache.key_buffer_size=128K \
         --extra_cache.key_cache_block_size=2096

Dans ce cas, le serveur utilise une taille par défaut de 256 ko pour le cache. Vous pourriez aussi écrire --default.key_buffer_size=256K. De plus, le serveur crée un second cache de clé appelé extra_cache, de taille 128 ko, avec une taille de bloc de buffer de 2096 octets.

L'exemple suivant démarre le serveur avec trois cache de clé différents, avec des tailles de ratio 3:1:1 :

shell> mysqld --key_buffer_size=6M \
         --hot_cache.key_buffer_size=2M \
         --cold_cache.key_buffer_size=2M

Les variables système structurées peuvent être affectées et lue durant l'exécution. Par exemple, pour créer un cache de clé appelé hot_cache, de taille 10 Mo, utilisez une des commandes suivantes :

mysql> SET GLOBAL hot_cache.key_buffer_size = 10*1024*1024;
mysql> SET @@global.hot_cache.key_buffer_size = 10*1024*1024;

Pour lire la taille du cache, faites ceci :

mysql> SELECT @@global.hot_cache.key_buffer_size;

Cependant, la commande suivante ne fonctionne pas. La variable n'est pas interprétée comme un nom composé, mais comme une simple chaîne pour l'opérateur LIKE :

mysql> SHOW GLOBAL VARIABLES LIKE 'hot_cache.key_buffer_size';

C'est la exception où vous ne pouvez pas utiliser une variable système structurée, là où une variable simple fonctionnerait.

9.5 Syntaxe des commentaires

Le serveur MySQL supporte trois types de commentaires :

  • Depuis ‘#’ jusqu'à la fin de la ligne.

  • Depuis '-- ' jusqu'à la fin de la ligne. Ce style est supporté depuis MySQL 3.23.3. Notez que le commentaire '-- ' (double-tiret) requiert que le second tiret soit suivi d'un espace ou par un autre caractère de contrôle comme une nouvelle ligne. Cette syntaxe diffère légèrement des standards SQL, comme discuté dans la section Section 1.5.5.7, « '--' comme début de commentaire ».

  • Depuis ‘/*’ jusqu'à ‘*/’. La séquence de fermeture n'a pas besoin d'être sur la même ligne, ce qui permet de répartir le commentaire sur plusieurs lignes.

Voici un exemple avec les trois types de commentaires :

mysql> SELECT 1+1;     # Ce commentaire se continue jusqu'à la fin de la ligne
mysql> SELECT 1+1;     -- Ce commentaire se continue jusqu'à la fin de la ligne
mysql> SELECT 1 /* Ceci est un commentaire dans la ligne */ + 1;
mysql> SELECT 1+
/*
Ceci est un commentaire
sur plusieurs lignes
*/
1;

La syntaxe des commentaires décrites ici s'applique à l'analyseur du serveur mysqld, lorsqu'il traite les commandes SQL. Le client mysql peut aussi effectuer des analyses de commandes avant de les envoyer : par exemple, il recherche les limites de requêtes dans les commandes multi-lignes. Cependant, il y a des limitations dans la fa¸on de gérer les commentaires /* ... */ :

  • Les guillemets (simples et doubles) sont considérés comme des indications de début de chaîne, même dans un commentaire. Si le guillemet n'est pas refermé (par un second guillemet), l'analyseur ne réalisera pas que le commentaire est fini. Si vous utilisez mysql interactivement, vous pouvez vous en apercevoir, car il va modifier l'invite de commande de mysql> en '> ou ">.

  • Un point-virgule sert à indiquer la fin de la commande SQL, et tout ce qui suit un point-virgule est considéré comme étant une nouvelle requête.

Ces limitations s'appliquent aussi bien à mysql en ligne de commande, que lorsque vous demandez à mysql de lire des commandes depuis un fichier (mysql < un-fichier).

9.6 Cas des mots réservés MySQL

Un problème récurrent provient de la tentative de création de tables avec des noms de colonnes qui sont des types de champs ou des fonctions natives de MySQL, comme TIMESTAMP ou GROUP. Il vous st permis de le faire (par exemple ABS est permis comme nom de colonne), mais les espaces ne sont pas permis entre le nom d'une fonction et la première ‘(’ suivante lors de l'utilisation de fonctions qui sont aussi des noms de colonnes.

Un effet secondaire de ce comportement est que l'omission d'espace dans certains contexte fait que l'identifiant est interprété comme un nom de fonction. Par exemple, cette commande est valide :

mysql> CREATE TABLE abs (val INT);

Mais omettre l'espace après abs génère une erreur de syntaxe, car la commande semble utiliser la fonction ABS() :

mysql> CREATE TABLE abs(val INT);

Si vous lancez le serveur l'option de mode IGNORE_SPACE, le serveur autorisera l'appel de fonction avec un espace entre le nom de la fonction et le caractère de parenthèse ouvrante ‘(’ suivant. Les noms de fonctions sont alors considérés comme des mots réservés. Comme pour les résultats, les noms de colonnes qui sont identiques au nom de fonctions doivent être placés entre guillemets, tels que décrit dans Section 9.2, « Noms de bases, tables, index, colonnes et alias ». Le mode SQL du serveur est contrôlé par la procédure de la section Section 1.5.2, « Sélectionner les modes SQL ».

Les mots suivants sont explicitement réservés en MySQL. La plupart sont interdits par ANSI SQL92 en tant que nom de colonnes ou de tables (par exemple, GROUP). Quelques uns sont réservés parce que MySQL en a besoin et utilise (actuellement) un analyseur yacc :

ADDALLALTER
ANALYZEANDAS
ASCASENSITIVEBEFORE
BETWEENBIGINTBINARY
BLOBBOTHBY
CALLCASCADECASE
CHANGECHARCHARACTER
CHECKCOLLATECOLUMN
CONDITIONCONSTRAINTCONTINUE
CONVERTCREATECROSS
CURRENT_DATECURRENT_TIMECURRENT_TIMESTAMP
CURRENT_USERCURSORDATABASE
DATABASESDAY_HOURDAY_MICROSECOND
DAY_MINUTEDAY_SECONDDEC
DECIMALDECLAREDEFAULT
DELAYEDDELETEDESC
DESCRIBEDETERMINISTICDISTINCT
DISTINCTROWDIVDOUBLE
DROPDUALEACH
ELSEELSEIFENCLOSED
ESCAPEDEXISTSEXIT
EXPLAINFALSEFETCH
FLOATFLOAT4FLOAT8
FORFORCEFOREIGN
FROMFULLTEXTGRANT
GROUPHAVINGHIGH_PRIORITY
HOUR_MICROSECONDHOUR_MINUTEHOUR_SECOND
IFIGNOREIN
INDEXINFILEINNER
INOUTINSENSITIVEINSERT
INTINT1INT2
INT3INT4INT8
INTEGERINTERVALINTO
ISITERATEJOIN
KEYKEYSKILL
LEADINGLEAVELEFT
LIKELIMITLINES
LOADLOCALTIMELOCALTIMESTAMP
LOCKLONGLONGBLOB
LONGTEXTLOOPLOW_PRIORITY
MATCHMEDIUMBLOBMEDIUMINT
MEDIUMTEXTMIDDLEINTMINUTE_MICROSECOND
MINUTE_SECONDMODMODIFIES
NATURALNOTNO_WRITE_TO_BINLOG
NULLNUMERICON
OPTIMIZEOPTIONOPTIONALLY
ORORDEROUT
OUTEROUTFILEPRECISION
PRIMARYPROCEDUREPURGE
READREADSREAL
REFERENCESREGEXPRELEASE
RENAMEREPEATREPLACE
REQUIRERESTRICTRETURN
REVOKERIGHTRLIKE
SCHEMASCHEMASSECOND_MICROSECOND
SELECTSENSITIVESEPARATOR
SETSHOWSMALLINT
SONAMESPATIALSPECIFIC
SQLSQLEXCEPTIONSQLSTATE
SQLWARNINGSQL_BIG_RESULTSQL_CALC_FOUND_ROWS
SQL_SMALL_RESULTSSLSTARTING
STRAIGHT_JOINTABLETERMINATED
THENTINYBLOBTINYINT
TINYTEXTTOTRAILING
TRIGGERTRUEUNDO
UNIONUNIQUEUNLOCK
UNSIGNEDUPDATEUSAGE
USEUSINGUTC_DATE
UTC_TIMEUTC_TIMESTAMPVALUES
VARBINARYVARCHARVARCHARACTER
VARYINGWHENWHERE
WHILEWITHWRITE
XORYEAR_MONTHZEROFILL

Voici de nouveaux mots réservés en MySQL : 5.0:

ASENSITIVECALLCONDITION
CONTINUECURSORDECLARE
DETERMINISTICEACHELSEIF
EXITFETCHINOUT
INSENSITIVEITERATELEAVE
LOOPMODIFIESOUT
READSRELEASEREPEAT
RETURNSCHEMASCHEMAS
SENSITIVESPECIFICSQL
SQLEXCEPTIONSQLSTATESQLWARNING
TRIGGERUNDOWHILE

Les symboles suivants (issus de la table ci-dessus) sont interdits par ANSI SQL mais permis par MySQL en tant que noms de colonnes ou de tables. Cela est dû au fait que ces noms sont très courants, et de nombreux programmeur les ont déjà utilisés.

  • ACTION

  • BIT

  • DATE

  • ENUM

  • NO

  • TEXT

  • TIME

  • TIMESTAMP