Go to the first, previous, next, last section, table of contents.

6 Référence du langage MySQL

MySQL possède une interface SQL assez complexe, mais facile à apprendre. Ca chapitre décrit les différentes commandes, types, et fonctions que vous aurez besoin de connaître pour utiliser MySQL de façon optimale. Ce chapitre sert aussi de référence pour toutes les fonctionnalités incluses au sein de MySQL. Afin d'utiliser ce chapitre efficacement, vous pourrez trouver utile de vous reporter aux différents index.

6.1 Structure du langage

6.1.1 Literals: 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.

6.1.1.1 Chaînes

Une chaîne est une séquence de caractères, entourée de guillemets simples (`'') ou doubles (`"') (simple seulement si vous êtes en mode ANSI). Exemples: 

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

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 `%'. Cette notation est utilisée pour trouver les occurrences de `%' au cas où `%' peut aussi être interprété comme un caractère spécial. See section 6.3.2.1 Opérateurs de comparaison pour les chaînes de caractères.
\_
Un `_'. Cette notation est utilisée pour trouver les occurrences de `_' au cas où `_' peut aussi être interprété comme un caractère spécial. See section 6.3.2.1 Opérateurs de comparaison pour les chaînes de caractères.

Notez que si vous utilisez `\%' ou `\_' dans un contexte de chaînes de caractères, ces séquences retourneront `\%' et `\_' et non `%' et `_'.

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

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 :

NUL
ASCII 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 `\"'.

Si vous écrivez du code C, vous pouvez utiliser la fonction mysql_real_escape_string(), fournie par l'API, pour échapper les caractères pour les requêtes INSERT. See section 8.4.2 Vue d'ensemble des fonctions de l'API C. Avec Perl, vous pouvez utiliser la méthode quote du package DBI pour convertir les caractères spéciaux en leurs équivalents échappés. See section 8.2.2 L'interface DBI.

Vous devez utiliser une fonction d'échappement pour chaque chaîne susceptible de contenir l'un des caractères spéciaux listés plus haut.

Alternativement, beaucoup d'API MySQL, fournissent des fonctionnalités qui vous permettent d'insérer des marqueurs spéciaux dans une requête puis de les remplacer par des valeurs lors de l'exécution de celle-ci. Dans ce cas, l'API s'occupe d'échapper les caractères spéciaux pour vous.

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

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

La syntaxe x'chaînehexa' (nouvelle en 4.0) est basée sur ANSI SQL et la syntaxe 0x est basée sur ODBC. Les chaînes hexadécimales sont souvent utilisée par ODBC pour fournir des valeurs aux colonnes BLOB. Vous pouvez convertir une chaîne ou un nombre en hexadécimal avec la fonction HEX().

6.1.1.4 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 6.4.9 Syntaxe de LOAD DATA INFILE.

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

Notez que les règles ont changé à partir de la version 3.23.6 de MySQL lorsqu'à été introduite la protection des noms d'identifiant avec les guillemets obliques ``'. `"' fonctionne aussi si vous fonctionnez en mode AINSI. See section 1.8.2 Exécuter MySQL en mode ANSI.

Identifiant Longueur maximale Caractères autorisés
Base de données 64 Tous les caractères autorisés dans un nom de dossier à part `/', `\' et `.'.
Table 64 Tous les caractères autorisés dans le nom d'un fichier à part `/' et `.'.
Colonne 64 Tous.
Alias 255 tous.

Notez qu'en plus de ce qui précède, vous n'avez pas droit au caractères ASCII(0), ASCII(255) ou aux guillemets dans un identifiant.

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

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

See section 6.1.7 Est-ce que MySQL est sensible aux mots réservés ?.

Dans les versions antérieures à la 3.23.6, les règles étaient les suivantes :

Il est conseillé de ne pas utiliser de nom comme 1e, car une expression comme 1e + 1 est ambigue. Ce nom pourra être interprété comme l'expression 1e + 1 ou le nombre 1e+1.

Avec MySQL vous pouvez indiquer une colonne avec l'une des syntaxes suivantes :

Référence Signification
nom_colonne La colonne nom_colonne de chaque table utilisée dans la requête qui comporte une telle colonne.
nom_de_table.nom_colonne La colonne nom_colonne de la table nom_de_table de la base de données courante.
nom_de_base.nom_de_table.nom_colonne La colonne nom_colonne de la table nom_de_table de la base nom_de_base. Cette forme est disponible à partir de la version 3.22 de MySQL.
`nom_colonne` Une colonne qui est un mot réservé ou qui contient un caractère spécial.

Vous n'avez pas besoin de spécifier nom_de_table ou nom_de_base.nom_de_table avant une colonne sauf si la référence risque d'être ambigue. Par exemple, supposons que t1 et t2 contiennent toutes les deux une colonne nommée c, et que vous lisez c dans une requête SELECT qui utilise t1 et t2 à la fois. Dans ce cas, c est ambigu car il peut se référer t1 aussi bien qu'à t2. Vous devez donc indiquer la table ciblée en écrivant t1.c ou t2.c. De même, si vous lisez à partir d'une table t située dans la base db1 et de la table t dans la base db2, vous devez vous référer aux colonnes de ces tables en écrivant db1.t.nom_colonne et db2.t.nom_colonne.

La syntaxe .nom_de_table référence la table nom_de_table dans la base de données courante. Cette syntaxe est acceptée pour assurer la compatibilité avec ODBC, car certains pilotes ODBC préfixent les noms des tables avec des points (`.').

6.1.3 Sensibilité à la casse pour les noms

En MySQL, les bases et les tables correspondent à des dossiers et des fichiers. Par conséquent, la sensibilité à la casse du système détérminera la sensisbilité à 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 étant une exception). See section 1.8.3 Extensions de MySQL à la norme ANSI SQL92.

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

Un moyen d'éviter ce problème est de démarrer mysqld avec -O lower_case_table_names=1. Par défaut, cette option vaut 1 sur Windows et 0 sur Unix.

Si lower_case_table_names est à 1 MySQL convertira tout les noms de tables en minuscules lors des sauvegardes et des récupérations. (A partir de la version 4.0.2, cette opération s'applique aussi aux noms de bases de données.). Notez que si vous changez cette option, vous devrez repasser vos anciens noms de tables en minuscules avant de démarrer mysqld.

Si vous déplacez les fichiers MyISAM d'un Windows à une architecture *nix, vous aurez peut être besoin d'utiliser l'utilitaire `mysql_fix_extensions' pour corriger la casse des extensions des fichiers dans chaque répertoire spécifique à la base de données (minuscules `.frm', majuscules `.MYI' et `.MYD'). `mysql_fix_extensions' peut être trouvé dans le sous-répertoire `script'.

6.1.4 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 4.6.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.

6.1.5 Variables système

A partir de la version 4.0.3 de MySQL, 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.

Il y a deux sortes de variables système : Les variables spécifiques aux threads (ou à la connexion) qui sont uniques à la connexion courante et les variables globales qui sont utilisées pour configurer les évènements globaux. Les variables globales sont aussi utilisées pour configurer les valeurs initales des variables spécifiques au threads pour les nouvelles connexions.

Lorsque mysqld démarre, toutes les variables globales sont initialisées à partir des arguements 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.

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) 

SET GLOBAL sort_buffer_size=valeur;
SET @@global.sort_buffer_size=valeur;

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

SET SESSION sort_buffer_size=valeur;
SET @@session.sort_buffer_size=valeur;
SET sort_buffer_size=valeur;

Si vous ne spécifiez pas GLOBAL ou SESSION alors SESSION est utilisé. See section 5.5.6 Syntaxe de SET.

LOCAL est un synonyme de SESSION.

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

SELECT @@global.sort_buffer_size;
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 : 

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

Lorsque vous récuperez 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.

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

Voilà la liste complète de toutes les variables que que vous pouvez récuperer ou modifier et une indication quant à l'utilisation de GLOBAL ou SESSION avec elles.

Nom de la variable Type de valeur Type
autocommit bool SESSION
big_tables bool SESSION
binlog_cache_size num GLOBAL
bulk_insert_buffer_size num GLOBAL | SESSION
concurrent_insert bool GLOBAL
connect_timeout num GLOBAL
convert_character_set string SESSION
delay_key_write OFF | ON | ALL GLOBAL
delayed_insert_limit num GLOBAL
delayed_insert_timeout num GLOBAL
delayed_queue_size num GLOBAL
error_count num LOCAL
flush bool GLOBAL
flush_time num GLOBAL
foreign_key_checks bool SESSION
identity num SESSION
insert_id bool SESSION
interactive_timeout num GLOBAL | SESSION
join_buffer_size num GLOBAL | SESSION
key_buffer_size num GLOBAL
last_insert_id bool SESSION
local_infile bool GLOBAL
log_warnings bool GLOBAL
long_query_time num GLOBAL | SESSION
low_priority_updates bool GLOBAL | SESSION
max_allowed_packet num GLOBAL | SESSION
max_binlog_cache_size num GLOBAL
max_binlog_size num GLOBAL
max_connect_errors num GLOBAL
max_connections num GLOBAL
max_error_count num GLOBAL | SESSION
max_delayed_threads num GLOBAL
max_heap_table_size num GLOBAL | SESSION
max_join_size num GLOBAL | SESSION
max_sort_length num GLOBAL | SESSION
max_tmp_tables num GLOBAL
max_user_connections num GLOBAL
max_write_lock_count num GLOBAL
myisam_max_extra_sort_file_size num GLOBAL | SESSION
myisam_max_sort_file_size num GLOBAL | SESSION
myisam_sort_buffer_size num GLOBAL | SESSION
net_buffer_length num GLOBAL | SESSION
net_read_timeout num GLOBAL | SESSION
net_retry_count num GLOBAL | SESSION
net_write_timeout num GLOBAL | SESSION
query_cache_limit num GLOBAL
query_cache_size num GLOBAL
query_cache_type enum GLOBAL
read_buffer_size num GLOBAL | SESSION
read_rnd_buffer_size num GLOBAL | SESSION
rpl_recovery_rank num GLOBAL
safe_show_database bool GLOBAL
server_id num GLOBAL
slave_compressed_protocol bool GLOBAL
slave_net_timeout num GLOBAL
slow_launch_time num GLOBAL
sort_buffer_size num GLOBAL | SESSION
sql_auto_is_null bool SESSION
sql_big_selects bool SESSION
sql_big_tables bool SESSION
sql_buffer_result bool SESSION
sql_log_binlog bool SESSION
sql_log_off bool SESSION
sql_log_update bool SESSION
sql_low_priority_updates bool GLOBAL | SESSION
sql_max_join_size num GLOBAL | SESSION
sql_quote_show_create bool SESSION
sql_safe_updates bool SESSION
sql_select_limit bool SESSION
sql_slave_skip_counter num GLOBAL
sql_warnings bool SESSION
table_cache num GLOBAL
table_type enum GLOBAL | SESSION
thread_cache_size num GLOBAL
timestamp bool SESSION
tmp_table_size enum GLOBAL | SESSION
tx_isolation enum GLOBAL | SESSION
version string GLOBAL
wait_timeout num GLOBAL | SESSION
warning_count num LOCAL
unique_checks bool SESSION

Les variables suivies d'un num peuvent se voir affecter une valeur numérique. Celles marquées avec bool peuvent prendre 0, 1, ON ou OFF. Les variables qui sont du type enum doivent normalement comporter au moins l'une des valeurs disponibles pour la variable, mais peuvent aussi contenir les index des membres de la collection. (Le premier index est 0).

Voilà une description de quelques variables :

Variable Description
identity Alias pour last_insert_id (compatibilité Sybase)
sql_low_priority_updates Alias pour low_priority_updates
sql_max_join_size Alias pour max_join_size
delay_key_write_for_all_tables Si cette variable et delay_key_write sont définies, alors toutes les nouvelles tables MyISAM qui sont ouvertes utiliseront les écritures de clefs reportées.
version Alias for VERSION() (compatibilité Sybase (?))

Une déscription des autres variables peut être trouvée dans la section des options de démarrage, la description de SHOW VARIABLES et dans la section de SET. See section 4.1.1 Options de ligne de commande de mysqld. See section 4.5.6.4 Syntaxe de SHOW VARIABLES. See section 5.5.6 Syntaxe de SET.

6.1.6 Syntaxe des commentaires

Le serveur MySQL supporte les commentaires de la forme # jusqu'à la fin de la ligne, -- jusqu'à la fin de la ligne et /* dans la ligne ou sur plusieurs lignes */ : 

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;

Notez que le style de commentaire -- requiert au moins un espace après le second tiret !

Même si le serveur interprète correctement les syntaxes de commentaires décrites plus haut, il y a quelques limitations dans la façon où le client interprète les commentaires multi-lignes :

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

MySQL ne supporte les commentaires du style `--' ANSI SQL que si le second tiret est suivi d'un espace. See section 1.8.4.7 `--' comme début de commentaire.

6.1.7 Est-ce que MySQL est sensible aux mots réservés ?

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.

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 :

Word Word Word
ADD ALL ALTER
ANALYZE AND AS
ASC ASENSITIVE AUTO_INCREMENT
BDB BEFORE BERKELEYDB
BETWEEN BIGINT BINARY
BLOB BOTH BY
CALL CASCADE CASE
CHANGE CHAR CHARACTER
CHECK COLLATE COLUMN
COLUMNS CONDITION CONNECTION
CONSTRAINT CONTINUE CREATE
CROSS CURRENT_DATE CURRENT_TIME
CURRENT_TIMESTAMP CURSOR DATABASE
DATABASES DAY_HOUR DAY_MICROSECOND
DAY_MINUTE DAY_SECOND DEC
DECIMAL DECLARE DEFAULT
DELAYED DELETE DESC
DESCRIBE DETERMINISTIC DISTINCT
DISTINCTROW DIV DOUBLE
DROP ELSE ELSEIF
ENCLOSED ESCAPED EXISTS
EXIT EXPLAIN FALSE
FETCH FIELDS FLOAT
FOR FORCE FOREIGN
FOUND FRAC_SECOND FROM
FULLTEXT GRANT GROUP
HAVING HIGH_PRIORITY HOUR_MICROSECOND
HOUR_MINUTE HOUR_SECOND IF
IGNORE IN INDEX
INFILE INNER INNODB
INOUT INSENSITIVE INSERT
INT INTEGER INTERVAL
INTO IO_THREAD IS
ITERATE JOIN KEY
KEYS KILL LEADING
LEAVE LEFT LIKE
LIMIT LINES LOAD
LOCALTIME LOCALTIMESTAMP LOCK
LONG LONGBLOB LONGTEXT
LOOP LOW_PRIORITY MASTER_SERVER_ID
MATCH MEDIUMBLOB MEDIUMINT
MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND
MINUTE_SECOND MOD NATURAL
NOT NO_WRITE_TO_BINLOG NULL
NUMERIC ON OPTIMIZE
OPTION OPTIONALLY OR
ORDER OUT OUTER
OUTFILE PRECISION PRIMARY
PRIVILEGES PROCEDURE PURGE
READ REAL REFERENCES
REGEXP RENAME REPEAT
REPLACE REQUIRE RESTRICT
RETURN REVOKE RIGHT
RLIKE SECOND_MICROSECOND SELECT
SENSITIVE SEPARATOR SET
SHOW SMALLINT SOME
SONAME SPATIAL SPECIFIC
SQL SQLEXCEPTION SQLSTATE
SQLWARNING SQL_BIG_RESULT SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT SQL_TSI_DAY SQL_TSI_FRAC_SECOND
SQL_TSI_HOUR SQL_TSI_MINUTE SQL_TSI_MONTH
SQL_TSI_QUARTER SQL_TSI_SECOND SQL_TSI_WEEK
SQL_TSI_YEAR SSL STARTING
STRAIGHT_JOIN STRIPED TABLE
TABLES TERMINATED THEN
TIMESTAMPADD TIMESTAMPDIFF TINYBLOB
TINYINT TINYTEXT TO
TRAILING TRUE UNDO
UNION UNIQUE UNLOCK
UNSIGNED UPDATE USAGE
USE USER_RESOURCES USING
UTC_DATE UTC_TIME UTC_TIMESTAMP
VALUES VARBINARY VARCHAR
VARCHARACTER VARYING WHEN
WHERE WHILE WITH
WRITE XOR YEAR_MONTH
ZEROFILL

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 du au fait que ces noms sont très courants, et de nombreux programmeur les ont déjà utilisés.

6.2 Types de colonnes

MySQL supporte un grand nombre de types de colonnes, qui peuvent être rassemblés en trois groupes : les nombres, les dates et les chaînes de caractères. Cette section présente les types disponibles et leurs tailles de stockage, puis présente en détail chaque type. L'introduction est volontairement brève. Une section plus précise est dédiée à chaque type, qui présente tous les formats valides.

Les types de colonnes de MySQL sont listés ci-dessous. Les codes suivants sont utilisés dans les descriptions :

M
Indique la taille maximale d'affichage. Le maximum légal est 255.
D
S'applique aux nombres à virgule flottante, et indique le nombre de décimales qui suivent la virgule. Le nombre maximum est de 30, mais ne doit pas être plus grand que M-2.

Les crochets (`[' et `]') indiquent les spécifications optionnelles.

Notez que si vous spécifiez ZEROFILL pour une colonne, MySQL ajoutera automatiquement l'attribut UNSIGNED à la colonne.

Attention : vous devez garder à l'esprit que lors de la soustraction de deux entiers dont l'un est de type UNSIGNED, le résultat ne sera pas signé ! See section 6.3.5 Fonctions de transtypage.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
Un très petit entier. S'il est signé, sa valeur varie entre -128 et 127, sinon elle varie de 0 à 255.
BIT
BOOL
Ce sont des synonymes de TINYINT(1).
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
Un petit entier. S'il est signé, sa valeur varie entre -32768 et 32767, sinon elle varie de 0 à 65535.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
Un entier de taille moyenne. S'il est signé, sa valeur varie entre -8388608 et 8388607, sinon elle varie entre 0 et 16777215.
INT[(M)] [UNSIGNED] [ZEROFILL]
Un entier. S'il est signé, sa valeur varie entre -2147483648 et 2147483647, sinon elle varie entre 0 et 4294967295.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
C'est un synonyme de INT.
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
Un grand entier. S'il est signé, sa valeur varie entre -9223372036854775808 et 9223372036854775807, sinon elle varie entre 0 et 18446744073709551615. Quelques choses à savoir pour bien utiliser les colonnes de type BIGINT :
FLOAT(précision) [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante. précision<=24 pour un nombre à virgule flottante de précision simple, entre 25 et 53 pour une précision double. Ces types correspondent aux types FLOAT et DOUBLE décrits ci-dessus. FLOAT(X) a le même intervalle de validité que FLOAT et DOUBLE, mais la taille d'affichage et le nombre de décimale sont indéfinies. Dans la version 3.23 de MySQL, c'est un véritable nombre à virgule flottante. Dans les anciennes versions de MySQL, FLOAT(précision) avait toujours deux décimales. Notez que l'utilisation du type FLOAT peut vous créér des problèmes inattendus car tous les calculs internes de MySQL sont fait en double précision. See section A.5.6 Résoudre les problèmes des lignes non retournées. Cette syntaxe est fournie pour assurer la compatibilité avec ODBC.
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
Un petit nombre à virgule flottante (précision simple). L'intervalle de validité va de -3.402823466E+38 à -1.175494351E-38, 0 et de 1.175494351E-38 à 3.402823466E+38. Si UNSIGNED est spécifié, les valeurs négatives sont interdites. M représente la taille d'affichage et D est le nombre de décimales. FLOAT sans arguments, ou avec un argument <= 24 représente un nombre à virgule flottante de précision simple.
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante (précision double). L'intervalle de validité va de -1.7976931348623157E+308 à -2.2250738585072014E-308, 0 et de 2.2250738585072014E-308 à 1.7976931348623157E+308. Si UNSIGNED est spécifié, les valeurs négatives sont interdites. M représente la taille d'affichage et D est le nombre de décimales. FLOAT sans arguments, ou avec un argument compris entre 25 et 53 (inclus) représente un nombre à virgule flottante de précision double.
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
C'est un synonyme de DOUBLE.
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
Un nombre à virgule flottante. Il doit être signé. Ce type se comporte comme une colonne de type CHAR : la valeur est stockée comme une chaîne, chaque caractère représentant un chiffre de la valeur. La virgule et le signe `-' des nombres négatifs ne sont pas comptés dans l'option M (mais de l'espace de stockage est reservé pour eux). Si D vaut 0, les valeurs n'auront pas de valeur décimale. L'intervalle de validité des valeurs DECIMAL est le même que DOUBLE, mais il peut être limité par les valeurs choisies pour M et D. Si UNSIGNED est spécifié, les valeurs négatives sont interdites. Si D est ommis, la valeur par défaut est 0. Si M est ommis, la valeur par défaut est 10. Avant la version 3.23 de MySQL, l'argument M doit inclure l'espace requis pour le signe et le point des décimales.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
Ce sont des synonymes de DECIMAL.
DATE
Une date. L'intervalle de validité va de '1000-01-01' à '9999-12-31'. MySQL affiche les valeurs DATE au format 'AAAA-MM-JJ' (année-mois-jour), mais vous pouvez assigner des valeurs aux colonnes DATE en utilisant différents formats numériques ou de chaînes de caractères. See section 6.2.2.2 Les types DATETIME, DATE, et TIMESTAMP.
DATETIME
Une combinaison de date et d'heure. L'intervalle de validité va de '1000-01-01 00:00:00' à '9999-12-31 23:59:59'. MySQL affiche les valeurs DATETIME au format 'AAAA-MM-JJ HH:MM:SS' (année-mois-jour heure:minutes:secondes), mais vous pouvez assigner des valeurs aux colonnes DATETIME en utilisant différents formats numériques ou de chaînes de caractères. See section 6.2.2.2 Les types DATETIME, DATE, et TIMESTAMP.
TIMESTAMP[(M)]
Un timestamp. L'intervalle de validité va de '1970-01-01 00:00:00' à quelque part durant l'année 2037. MySQL affiche les valeurs de type TIMESTAMP au format AAAAMMJJHHMMSS, AAMMJJHHMMSS, AAAAMMJJ, ou AAMMJJ, suivant que le paramètre M vaut 14 (ou omis), 12, 8, ou 6, mais vous pouvez assigner des valeurs aux colonnes TIMESTAMP sous forme de nombre ou de chaînes. Une colonne de type TIMESTAMP est pratique pour enregistrer une date, lors d'une commande INSERT ou UPDATE, car elle est automatiquement mise à la date et l'heure du moment de la commande, si vous ne fournissez pas de valeur vous-même. Vous pouvez aussi lui donner la date et l'heure courante en lui assignant la valeur NULL. See section 6.2.2 Les types date et heure. Un TIMESTAMP est toujours stocké sur 4 octets. L'argument M n'affecte que le mode d'affichage de ce type de colonne. Notez bien que les colonnes de type TIMESTAMP(X) où X vaut 8 ou 14 sont considérées comme des nombres tandis que les autres colonnes TIMESTAMP(X) sont considérées comme des chaînes. Ceci est fait pour s'assurer que l'on peut exporter et importer les tables avec ces types. See section 6.2.2.2 Les types DATETIME, DATE, et TIMESTAMP.
TIME
Une heure. L'intervalle de validité va de '-838:59:59' à '838:59:59'. MySQL affiche les colonnes de type TIME au format 'HH:MM:SS', mais vous pouvez assigner une valeur de type TIME en lui passant des chaînes ou des entiers. See section 6.2.2.3 Le type TIME.
YEAR[(2|4)]
Une année au format 2 ou 4 chiffres (par défaut, c'est 4 chiffres). L'intervalle de validité va de 1901 à 2155, 0000 pour le format à 4 chiffres, et de 1970 à 2069 pour le format à deux chiffres. MySQL affiche les valeurs de type YEAR au format AAAA, mais vous pouvez leur assigner des chaînes ou des nombres. (le type YEAR est nouveau depuis la version 3.22 de MySQL) See section 6.2.2.4 Le type YEAR.
[NATIONAL] CHAR(M) [BINARY]
Une chaîne de caractères de taille fixe, qui est toujours complétée à droite, par des espaces, lors du stockage. Le paramètre M peut valoir de 0 à 255 caractères. (1 à 255 dans les versions antérieures à la 3.23) Les espaces terminaux sont supprimés lorsque la chaîne est lue dans la base. Les valeurs de type CHAR sont triées et comparées sans tenir compte de la casse et en utilisant le jeu de caractères par défaut. Toutefois, vous pouvez utiliser l'opérateur BINARY pour effectuer des recherches sensibles à la casse. NATIONAL CHAR (forme courte : NCHAR) est la dénomination ANSI SQL pour les colonnes de type CHAR qui doivent utiliser le jeu de caractères par défaut. C'est la configuration par défaut de MySQL. MySQL autorise la création de colonne de type CHAR(0). Cela ne sert réellement que si vous devez être compatible avec une vieille application, dont le bon fonctionnement repose sur l'existence de la colonne, mais qui n'utilise pas vraiment ses valeurs. C'est aussi pratique lorsque vous devez stocker un booléen (ou une valeur à deux états) dans une colonne : une colonne de type CHAR(0), qui n'a pas l'attribut NOT NULL, n'occupera qu'un seul octet, et peut prendre deux valeurs : NULL et "". See section 6.2.3.1 Les types CHAR et VARCHAR. CHAR est une forme courte de CHARACTER.
CHAR
C'est un synonyme de CHAR(1).
[NATIONAL] VARCHAR(M) [BINARY]
Une chaîne de caractères de longueur variable. NOTE : les espaces terminaux sont supprimés lors du stockage des valeurs (ce qui diffère des spécifications ANSI SQL). L'intervalle de taille de M va de 1 à 255 caractères. Les valeurs de type VARCHAR sont triées et comparées sans tenir compte de la casse et en utilisant le jeu de caractères par défaut. Toutefois, vous pouvez utiliser l'opérateur BINARY pour effectuer des recherches sensibles à la casse. See section 6.5.3.1 Modification automatique du type de colonnes. VARCHAR est un forme courte de CHARACTER VARYING. See section 6.2.3.1 Les types CHAR et VARCHAR.
TINYBLOB
TINYTEXT
Une colonne BLOB ou TEXT avec une longueur maximale de 255 (2^8 - 1) caractères. See section 6.5.3.1 Modification automatique du type de colonnes. See section 6.2.3.2 Les types BLOB et TEXT.
BLOB
TEXT
Une colonne BLOB ou TEXT avec une longueur maximale de 65535 (2^16 - 1) caractères. See section 6.5.3.1 Modification automatique du type de colonnes. See section 6.2.3.2 Les types BLOB et TEXT.
MEDIUMBLOB
MEDIUMTEXT
Une colonne BLOB ou TEXT avec une longueur maximale de 16777215 (2^24 - 1) caractères. See section 6.5.3.1 Modification automatique du type de colonnes. See section 6.2.3.2 Les types BLOB et TEXT.
LONGBLOB
LONGTEXT
Une colonne BLOB ou TEXT avec une longueur maximale de 4294967295 (2^32 - 1) caractères. See section 6.5.3.1 Modification automatique du type de colonnes. Notez que puisque le protocole serveur/client et les tables de type MyISAM ont une limitation de 16M par paquet communiqué / ligne, vous ne pourrez utiliser la totalité de la longueur de ce type. See section 6.2.3.2 Les types BLOB et TEXT.
ENUM('valeur1','valeur2',...)
Une énumération. Une chaîne de caractères qui ne peut prendre qu'une valeur, issue d'une liste de valeurs 'valeur1', 'valeur2', ..., NULL ou la valeur spéciale d'erreur "". Un ENUM peut avoir un maximum de 65535 valeurs distinctes. See section 6.2.3.3 Le type ENUM.
SET('valeur1','valeur2',...)
Un ensemble. Une chaîne de caractères qui a zéro ou plusieurs valeurs issues d'une liste : 'valeur1', 'valeur2', ... Un SET peut avoir au maximum 60 éléments. See section 6.2.3.4 Le type SET.

6.2.1 Types numériques

MySQL supporte tous les types numériques de la norme ANSI/ISO SQL92. Ceux ci représentent les types numériques exacts (NUMERIC, DECIMAL, INTEGER, et SMALLINT), ainsi que les types approchés (FLOAT, REAL, et DOUBLE PRECISION). Le mot clef INT est un synonyme de INTEGER, et le mot clef DEC est un synonyme de DECIMAL.

Les types NUMERIC et DECIMAL sont considérés comme identiques par MySQL, comme l'autorise le standard SQL92. Ils sont utilisées par des valeurs dont il est primordial de conserver la précision exacte, comme pour des données financières. Lorsque vous déclarez des colonnes avec l'un de ces types, vous pouvez indiquer la précision et l'échelle comme ceci : 

    salaire DECIMAL(5,2)

Dans cet exemple, 5 (précision) représente le nombre de décimales signifiantes qui seront stockées pour les valeurs, et 2 (échelle) représente le nombre de chiffres qui seront stockés après le point des décimales. Dans ce cas, toutefois, l'intervalle des valeurs pouvant être stockés dans la colonne salaire varie de -99.99 à 99.99. (MySQL peut actuellement stocker des nombres allant jusqu'à 999.99 dans cette colonne car il n'a pas besoin de l'espace qui sert à stocker le signe pour un nombre positif)

En ANSI/ISO SQL92, la syntaxe DECIMAL(p) est équivalente à DECIMAL(p,0). De manière similaire, la syntaxe DECIMAL est équivalente à to DECIMAL(p,0), où l'implémentation est autorisée à choisir la valeur de p. MySQL ne supporte pas actuellement ces formes variantes des types de données DECIMAL/NUMERIC. Cela ne pose pas généralement de grand problèmes, vu que le principal bénéfice de ces types est de pouvoir contrôler explicitement la précision et l'échelle.

Les valeurs de type DECIMAL et NUMERIC sont stockées sous forme de chaînes de caractères, plutôt que comme des nombres à virgule flottante, afin de préserver la précision décimale des valeurs. Un caractère est donc nécessaire pour chaque chiffre, plus la virgule (si scale > 0), et le signe moins `-' (pour les nombres négatifs). Si scale vaut 0, les valeurs de type DECIMAL et NUMERIC ne comporteront pas de valeur décimale, ni de virgule.

L'intervalle de validité maximale des valeurs de type DECIMAL et NUMERIC est le même que pour le type DOUBLE, mais l'intervalle réel peut être limité par le choix des paramètres précision et scale. Lorsqu'une valeur ayant trop de décimales est affectée à une colonne, la valeur est arrondie à scale décimales. Lorsqu'une valeur est hors des limites de validité de la colonne DECIMAL ou NUMERIC, MySQL enregistre la plus grande valeur qu'il peut à la place.

En extension de la norme ANSI/ISO SQL92, MySQL supporte aussi les types entiers TINYINT, MEDIUMINT, et BIGINT, comme présenté ci-dessus. Un autre extension supportée par MySQL permet de spécifier optionnellement la taille d'affichage, sous la forme d'une valeur entière entre parenthèses, juste après le mot clé spécifiant le type (par exemple, INT(4)). Cette spécification de taille est utilisée pour remplir à gauche, avec le caractère de remplissage par défaut, les nombres dont la taille est inférieure à celle spécifiée mais uniquement à l'affichage : cela ne réduit pas l'intervalle de validité des valeurs qui peuvent être stockées dans la colonne.

Lorsqu'elle est utilisée avec l'attribut de colonne optionnel ZEROFILL, le caractère de remplissage par défaut est remplacé par le caractère zéro. Par exemple, pour une colonne dont le type est INT(5) ZEROFILL, la valeur 4 sera lue 00004.

Notez que si vous stockez des nombres plus grands que la taille maximale d'affichage, vous pouvez rencontrer des problèmes lors de jointures de tables particulièrement compliquées, surtout si MySQL génére des tables temporaires : dans ce cas, MySQL pense que les données étaient limitées par l'affichage.

Tous les types entiers ont un attribut optionnel (non-standard) UNSIGNED (non-signé, en français). Les valeurs non-signées peuvent être utilisées pour n'autoriser que des valeurs positives dans une colonne, ou bien pour exploiter un intervalle de validité plus haut. Depuis la version 4.0.2 de MySQL, les nombres à virgule flottante peuvent aussi être UNSIGNED Comme avec les types entiers, cet attribut interdit les valeurs négatives dans la colonne, mais n'élève pas l'intervalle de validité.

Le type FLOAT est utilisé pour représenter des données numériques approchées. La norme ANSI/ISO SQL92 permet la spécification optionnelle de la précision (mais pas de l'intervalle de validité) en fournissant le nombre de décimales voulues après la spécification de type, et entre parenthèses. L'implémentation de MySQL supporte aussi le paramétrage de la précision. Si le mot clé FLOAT est utilisé pour une colonne sans précision supplémentaire, MySQL utilise quatre octets pour stocker les valeurs. Une syntaxe alternative existe aussi, elle utilise deux paramètre optionnel après le mot clé FLOAT. Avec cette option, le premier nombre représente toujours la taille de stockage nécessaire pour la valeur, et le second nombre représente le nombre de chiffres à stocker et afficher, après la virgule décimale (comme pour les types DECIMAL et NUMERIC). Lorsque MySQL stocke un nombre pour une telle colonne, et que cette valeur a plus de décimale que requis, la valeur est arrondie pour éliminer les chiffres surnuméraires.

Les types REAL et DOUBLE PRECISION n'acceptent pas de paramétrage de la précision. En extension du standard ANSI/ISO SQL92, MySQL reconnaît DOUBLE comme un synonyme du type DOUBLE PRECISION. Contrairement à la norme qui requiert que REAL soit plus petit que DOUBLE PRECISION, MySQL implémente ces deux types comme des nombres à virgule flottante de 8 octets, en double précision (lorsque le mode ``ANSI'' n'est pas activé). Pour une portabilité maximale, les applications réclamant le stockage de nombres approché doivent utiliser les types FLOAT ou DOUBLE PRECISION sans spécification de précision ou de nombre de décimales.

Lorsque MySQL doit stocker une valeur qui est hors de l'intervalle de validité d'une colonne, il ramène la valeur à la plus proche possible, et stocke cette valeur. Par exemple, l'intervalle de validité d'une colonne d'entiers INT va de -2147483648 à 2147483647. Si vous essayez d'insérer -9999999999 dans une colonne de ce type, la valeur sera ramenée à la plus proche possible, c'est à dire -2147483648. De même, si vous essayez d'insérer 9999999999, 2147483647 sera stocké à la place.

Si la colonne INT possède l'attribut UNSIGNED, l'intervalle de validité est aussi large, mais les valeurs extrêmes se décalent vers 0 et 4294967295. Si vous essayez de stocker -9999999999 et 9999999999 dans cette colonne, vous obtiendrez respectivement 0 et 4294967296.

Les dépassements de capacité entraînant des tronquage sont affichés comme des alertes (``warnings'') lors de l'utilisation des commandes ALTER TABLE, LOAD DATA INFILE, UPDATE, et les insertions INSERT multiples.

Type Octets De A
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

6.2.2 Les types date et heure

Les types dates et heures sont DATETIME, DATE, TIMESTAMP, TIME, et YEAR. Chacun d'eux à une échelle de valeurs légales, de même que la valeur ``zéro'' quand vous spécifiez une valeur illégale. A noter que MySQL vous permet d'enregistrer certaines dates qui ne sont pas strictement légales, par exemple 1999-11-31. La raison est que nous pensons que la vérification des dates est à faire niveau application. Pour accélérer les tests, MySQL vérifie juste que le mois est entre 0 et 12 et que le jour est entre 0 et 31. Les intervalles précédentes sont définies de cette façon car MySQL vous permet d'enregistrer dans une colonne DATE ou DATETIME, des dates où le jour de la semaine ou le jour du mois est zéro. C'est extrêmement utile pour les applications où vous avez besoin d'enregistrer une date d'anniversaire pour laquelle vous n'avez pas la date exacte. Dans ce cas, vous enregistrez simplement la date comme 1999-00-00 ou 1999-01-00. (Vous ne devez pas vous attendre à obtenir de valeurs correctes de fonctions tel que DATE_SUB() ou DATE_ADD pour des dates comme cela.)

Voici quelques considérations à garder à l'esprit quand vous manipulerez ce type de champs :

6.2.2.1 An 2000 et les types date

MySQL lui même est compatible an 2000. (see section 1.2.5 Compatibilité an 2000), mais les valeurs manipulées par MySQL peuvent ne pas l'être. N'importe quelle valeur n'ayant que deux chiffres pour représenter l'année est ambigu, car le siècle n'est pas précisé. Ces valeurs doivent être interprétées comme des valeurs à 4 chiffres, car MySQL stocke les années en interne en utilisant 4 chiffres.

Pour les types DATETIME, DATE, TIMESTAMP, et YEAR, MySQL interprète les dates ambigus en se basant sur les règles suivantes :

Gardez bien à l'esprit que ces règles ne sont que la meilleure approximation possible d'une valeur. Si l'heuristique proposée par MySQL ne fournit pas les valeurs attendues, vous devrez fournir une valeur sans ambiguïté. (à 4 chiffres)

ORDER BY ordonnera correctement les types YEAR/DATE/DATETIME à deux chiffres.

Notez aussi que quelques fonctions comme MIN() et MAX() convertiront un TIMESTAMP/DATE en nombre. Cela signifie qu'un timestamp avec une année à deux chiffres ne donneront pas de résultats corrects avec ces fonctions. Une solution dans ce cas est de convertir le TIMESTAMP/DATE en une année à 4 chiffres ou d'utiliser quelque chose comme MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).

6.2.2.2 Les types DATETIME, DATE, et TIMESTAMP

Les types DATETIME, DATE, et TIMESTAMP sont liés. Cette section décrit leurs caractéristiques, leur similarités et leurs différences.

Le type DATETIME est prévu lorsque vous souhaitez stocker une date et une heure. MySQL affiche les valeurs de type DATETIME au format 'AAAA-MM-JJ HH:MM:SS'. L'intervalle de validité va de '1000-01-01 00:00:00' à '9999-12-31 23:59:59'. (``validité'' signifie que même si d'autres valeurs plus anciennes peuvent être manipulées, il n'est pas garantit qu'elles le seront).

Le type DATE est prévu lorsque vous souhaitez stocker une date. MySQL affiche les valeurs de type DATE au format 'AAAA-MM-JJ'. L'intervalle de validité va de '1000-01-01' à '9999-12-31'.

Le type TIMESTAMP est prévu pour stocker automatiquement l'heure courante lors d'une commande INSERT ou UPDATE. Si vous avez plusieurs colonnes de type TIMESTAMP, seule la première colonne sera mise à jour automatiquement.

La modification automatique de la première colonne de type TIMESTAMP survient si l'une des conditions suivantes est remplie :

Les autres colonnes de type TIMESTAMP, hormis la première, peuvent aussi prendre la valeur courante. Affectez lui alors la valeur NULL ou la fonction NOW().

Vous pouvez affecter à n'importe quelle colonne de type TIMESTAMP une valeur différente de l'heure et la date courant en fournissant une valeur explicite. Cela s'applique aussi à la première colonne de type TIMESTAMP. Par exemple, si vous voulez affecter la date de création d'une ligne à une colonne de type TIMESTAMP, mais ne plus y toucher ultérieurement :

D'un autre coté, vous pouvez aussi facilement initialiser la colonne TIMESTAMP avec NOW() lors de sa création, puis ne plus la modifier ultérieurement.

L'intervalle de validité des valeurs TIMESTAMP va du début de l'année 1970 jusque quelque part durant l'année 2037, avec une précision d'une seconde. Les valeurs sont affichés comme des nombres entiers.

Le format d'affichage des valeurs TIMESTAMP dépend de la taille d'affichage, comme illustré ci-dessous. Le format total TIMESTAMP a 14 chiffres, mais les colonnes TIMESTAMP peuvent être créées avec des formats plus courts :

Type de colonne Format d'affichage
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

Toutes les colonnes de type TIMESTAMP ont la même taille de stockage, indépendamment de la taille d'affichage. Les formats les plus courants sont 6, 8, 12, et 14. Vous pouvez spécifier une taille arbitraire lors de la création de la table, mais 0 et les valeurs supérieures à 14 sont ramenées à 14. Les valeurs impaires sont aussi ramenées au nombre pair supérieur.

Vous pouvez spécifier les valeurs des colonnes DATETIME, DATE et TIMESTAMP, avec les formats communs suivants :

Les valeurs invalides DATETIME, DATE, ou TIMESTAMP sont remplacées par la date ``zéro'' du type approprié (respectivement '0000-00-00 00:00:00', '0000-00-00', ou 00000000000000).

Pour la valeurs spécifiées sous forme de chaînes avec des délimiteurs de date, il n'est pas nécessaire de spécifier les deux chiffres pour les mois ou les dates qui sont inférieurs à 10. Par exemple, '1979-6-9' est valide et est équivalent à '1979-06-09'. Similairement, pour les valeurs spécifiées sous forme de chaîne avec des délimiteurs d'heure, il n'est pas obligatoire de spécifier les deux chiffres des heures, minutes et secondes qui sont inférieures à 10. '1979-10-30 1:2:3' est valide et est équivalent à '1979-10-30 01:02:03'.

Les valeurs spécifiées sous forme de nombres doivent avoir 6, 8, 12, ou 14 chiffres de long. Si le nombre a 8 ou 14 chiffres, MySQL suppose que le format est AAAAMMJJ ou AAAAMMJJHHMMSS (respectivement) et que l'année est représentées par les 4 premiers chiffres. Si le nombre a 6 ou 12 chiffres, MySQL suppose que le format est AAMMJJ ou AAMMJJHHMMSS (respectivement) et format et que l'année est représentées par les 2 premiers chiffres. Les nombres qui ne sont pas d'une taille valide, sont complétés avec des 0 jusqu'à la taille lisible la plus proche. Les valeurs spécifiées sous forme de chaînes sans délimiteurs sont interprétés en fonction de leur taille. Si la chaîne à 8 ou 14 caractères de long, l'année est supposée avoir 4 chiffres. Sinon, l'année est supposée avoir 2 chiffres. La chaîne est interprétée de gauche à droite, en lisant successivement l'année, le mois, la date, l'heure, les minutes et les secondes, tant qu'il y a des valeurs dans la chaîne. Cela signifie que vous ne devez pas utiliser de chaînes qui ont moins de 6 caractères. Par exemple, si vous spécifiez '9903', en pensant qu'il représente Mars 1999, vous vous apercevrez que MySQL insère à la place la date ``zéro'' dans votre table. Cela est dû au fait que si l'année et le mois sont 99 et 03, la date est 0, ce qui en fait une date invalide, qui est rejetée par MySQL.

Les colonnes TIMESTAMP stocke des valeurs légales en utilisant la précision maximum fournie, indépendamment de la taille d'affichage. Cela a plusieurs implications :

Dans une certaines mesure, vous pouvez assigner des valeurs d'une colonne à une autre colonne d'un autre type. Cependant, vous devez vous attendre à quelques altération ou pertes de valeurs durant la conversion :

Attention à certains pièges concernant les spécifications de dates :

6.2.2.3 Le type TIME

MySQL lit et affiche les colonnes de type TIME au format 'HH:MM:SS' (ou 'HHH:MM:SS' pour les grandes quantités d'heures). Les valeurs de TIME vont de '-838:59:59' à '838:59:59'. La raison de cet intervalle de validité si large est que les colonnes de type TIME peuvent être utilisés pour représenter non seulement des heures du jour, mais aussi des durées entre deux événements (ce qui peut dépasser largement les 24 heures, ou même, être négatif).

Vous pouvez spécifier une valeur de type TIME avec différents formats :

Pour les valeurs TIME spécifiées avec des délimiteurs, il n'est pas nécessaire de préciser deux chiffres pour les valeurs inférieurs à 10 pour les heures, minutes et secondes. '8:3:2' est la même chose que '08:03:02'.

Soyez soigneux lors de l'utilisation de valeurs ``courtes'' à une colonne de type TIME. MySQL interprète les valeurs en supposant que les chiffres de droite représentent les secondes (MySQL interprète les valeurs TIME comme des durées et non comme des heures d'une journée). Par exemple, vous pouvez penser que les valeurs '11:12', '1112' et 1112 représentent '11:12:00' (12 minutes après 11 heures), mais MySQL les interprétera comme '00:11:12' (11 minutes, 12 secondes). Similairement, '12' et 12 représentent '00:00:12'. Les valeurs de TIME déclarées avec des :, au contraire, sont toujours traités comme des heures de journée. '11:12' signifiera '11:12:00' et non pas 00:11:12

Les valeurs hors de l'intervalle de validité de TIME mais qui sont valides sont ramenées à la valeur maximale stockable la plus proche. Par exemple, '-850:00:00' et '850:00:00' sont respectivement converties en '-838:59:59' et '838:59:59'.

Les valeurs TIME non valides sont transformées en date zéro '00:00:00'. Notez que comme '00:00:00' est elle-même une valeur TIME valide, vous n'aurez pas le moyen de faire la différence entre une valeur '00:00:00' stockée en connaissance de cause, et '00:00:00' stockée à cause d'une erreur.

6.2.2.4 Le type YEAR

Le type YEAR est un type d'1 byte utilisé pour représenter les années.

MySQL extrait et affiche la valeur de YEAR au format YYYY. L'échelle va de 1901 à 2155.

Vous pouvez spécifier la valeur de YEAR en plusieurs formats :

Les valeurs illégales pour YEAR sont converties en 0000.

6.2.3 Les types chaînes

Les types chaînes sont CHAR, VARCHAR, BLOB, TEXT, ENUM, et SET. Cette section décrit comment ces types fonctionnent, leur besoins en espace et comment les utiliser dans vos requêtes.

Type Taille maximale Bytes
TINYTEXT ou TINYBLOB 2^8-1 255
TEXT ou BLOB 2^16-1 (64K-1) 65535
MEDIUMTEXT ou MEDIUMBLOB 2^24-1 (16M-1) 16777215
LONGBLOB 2^32-1 (4G-1) 4294967295

6.2.3.1 Les types CHAR et VARCHAR

Les types CHAR et VARCHAR sont similaires, mais diffèrent dans la manière dont ils sont stockés et récupérés.

La longueur d'une colonne CHAR est fixée à la longueur que vous avez défini lors de la création de la table. La longueur peut être n'importe quelle valeur entre 1 and 255. (Dans la version 3.23 de MySQL, la longueuer est comprise entre 0 et 255.) Quand une valeur CHAR est enregistrée, elle est complété à droite avec des espaces jusqu'à atteindre la valeur fixée. Quand une valeur de CHAR est lue, les espaces en trop sont retirés.

Les valeurs contenues dans les colonnes de type VARCHAR sont de tailles variables. Vous pouvez déclarer une colonne VARCHAR pour que sa taille soit comprise entre 1 et 255, exactement comme pour les colonnes CHAR. Par contre, contrairement à CHAR, les valeurs de VARCHAR sont stockées en utilisant autant de caractères que nécessaire, plus un octet pour mémoriser la longueur. Les valeurs ne sont pas complétées. Au contraire, les espaces finaux sont supprimés avant stockage (ce qui ne fait pas partie des spécifications ANSI SQL).

Si vous assignez une chaîne de caractères qui dépasse la capacité de la colonne CHAR ou VARCHAR, celle ci est tronquée jusqu'à la taille maximale du champ.

Le tableau suivant illustre les différences entre les deux types de colonnes en montrant les différences entre l'enregistrement dans une colonne CHAR(4) ou VARCHAR(4) :

Valeur CHAR(4) Espace requis VARCHAR(4) Espace requis
'' ' ' 4 octets '' 1 octet
'ab' 'ab ' 4 octets 'ab' 3 octets
'abcd' 'abcd' 4 octets 'abcd' 5 octets
'abcdefgh' 'abcd' 4 octets 'abcd' 5 octets

Les valeurs lues dans les colonnes de type CHAR(4) et VARCHAR(4) seront les mêmes dans tous les cas, car les espaces finaux sont retirés des valeurs issues de colonnes de type CHAR lors de la lecture.

Les valeurs dans les colonnes CHAR et VARCHAR sont classées et comparées sans tenir compte de la casse, à moins que l'attribut BINARY n'ai été spécifié lors de la création de la table. L'attribut BINARY signifie que les valeurs sont classées et triées en tenant compte de la casse, suivant l'ordre des caractères ASCII de la machine ou est installé le serveur MySQL. BINARY n'affecte pas les méthodes de lecture et de stockage des valeurs.

L'attribut BINARY se propage dans une expression : il suffit qu'une seule colonne, utilisée dans une expression, ait l'attribut BINARY pour que toute l'expression ne tienne plus compte de la casse.

MySQL peut changer automatiquement le type d'une colonne CHAR ou VARCHAR lors de la création de la table. See section 6.5.3.1 Modification automatique du type de colonnes.

6.2.3.2 Les types BLOB et TEXT

Une valeur de type BLOB est un objet binaire de grande taille, qui peut contenir une quantité variable de données. Les quatre types BLOB (TINYBLOB, BLOB, MEDIUMBLOB, et LONGBLOB) ne différent que par la taille maximale de données qu'ils peuvent stocker. See section 6.2.6 Capacités des colonnes.

Les quatre types TEXT (TINYTEXT, TEXT, MEDIUMTEXT, et LONGTEXT correspondent aux types BLOB équivalents, et ont les mêmes contraintes de stockage. Les seules différences entre les colonnes de type BLOB et celles de type TEXT se situent aux niveau des tris et comparaisons : Les tris, faits sur les BLOB, contrairement à ceux faits sur les TEXT, tiennent compte de la casse. En d'autres termes, une valeur TEXT est une valeur BLOB insensible à la casse.

Si vous assignez une valeur trop grande à une colonne de type BLOB ou TEXT, la valeur sera tronquée à la taille maximale possible.

Dans la majorité des cas, vous pouvez considérer une colonne de type TEXT comme une colonne de type VARCHAR, aussi grande que vous le souhaitez. De même, vous pouvez considérer une colonne de type BLOB comme une colonne de type VARCHAR BINARY. Les seules différences sont :

MyODBC considère les valeurs BLOB comme des LONGVARBINARY et les valeurs TEXT comme des LONGVARCHAR.

Vous pouvez rencontrer les problèmes suivants, à cause de la grande taille des colonnes de type BLOB et TEXT, lors de leur utilisation :

Notez que chaque valeur BLOB ou TEXT est représentée en interne par un objet alloué séparément, contrairement à tous les autres types de colonne, pour lesquels la place de stockage est allouée une fois pour chaque colonne, lorsque la table est ouverte.

6.2.3.3 Le type ENUM

Une énumération ENUM est une chaîne dont la valeur est choisie parmi une liste de valeurs autorisées lors de la création de la table.

Cette chaîne peut aussi être la chaîne vide ("") ou NULL dans certaines circonstances :

Chaque élément de l'énumération dispose d'un index :

Par exemple, une colonne créée comme ENUM("un", "deux", "trois") peut prendre n'importe quelle valeur ci-dessous. L'index de chaque valeur est aussi présenté :

Valeur Index
NULL NULL
"" 0
"un" 1
"deux" 2
"trois" 3

Une énumération peut avoir un maximum de 65535 éléments.

A partir de la version 3.23.51, les espaces en début et fin de chaîne sont automatiquement supprimés des éléments de l'énumération ENUM lorsque la table est créée.

La casse des lettres est sans importance lors de l'assignation de valeurs dans une énumération. Cependant, les valeurs lues dans la base auront la même casse que celle spécifiée lors de la création de la table.

Si vous lisez le contenu d'une énumération dans un contexte numérique, l'index de la valeur ENUM sera retournée. Par exemple, vous pouvez lire des valeurs numériques comme ceci : 

mysql> SELECT enum_col+0 FROM nom_de_table;

Si vous stockez un nombre dans une colonne de type ENUM, le nombre sera traité comme un index, et la valeur stockée sera celle de l'élément ayant cet index (Attention, cela ne fonctionnera pas avec les commandes LOAD DATA, car cette dernière traite toutes les valeurs comme des chaînes). Il est déconseillé de stocker des valeurs numériques dans un ENUM car cela engendre des confusions.

Les valeurs de type ENUM sont triées en fonction de l'ordre des éléments, fixé à la création de la table (en d'autres termes, les valeurs ENUM sont stockées en fonction de leur index). Par exemple, "a" précède "b" dans l'énumération ENUM("a", "b"), mais "b" précède "a" dans l'énumération ENUM("b", "a"). La chaîne vide précède toujours les chaînes non vides, et NULL précède toutes les valeurs.

Si vous voulez connaître toutes les valeurs possibles d'une colonne de type ENUM, pensez à utiliser cette commande : SHOW COLUMNS FROM nom_de_table LIKE enum_column_name, puis analysez la définition de la colonne de type ENUM (deuxième colonne dans le résultat).

6.2.3.4 Le type SET

Un SET est une chaîne qui peut avoir zéro ou plusieurs valeurs, chacune doit être choisie dans une liste de valeurs définies lors de la création de la table. Les valeurs des colonnes SET composées de plusieurs membres sont définies en séparant celles-ci avec des virgules (`,'). Ce qui fait que la valeur d'un membre de SET ne peut contenir lui même de virgule.

Par exemple, une colonne définie en tant que SET("un", "deux") NOT NULL peut avoir l'une de ces valeurs : 

""
"un"
"deux"
"un,deux"

Un SET peut avoir au plus 64 membres.

A partir de la version 3.23.51, les espaces en trop sont automatiquement effacés des membres de SET lorsque la table est créée.

MySQL enregistre les valeurs de SET numériquement. Le bit de poids faible de la valeur correspond alors au premier élément de la liste. Si vous utilisez une valeur SET dans un contexte numérique, les bits des éléments dans cet ensemble seront mis à un, et les autres à zéro. Par exemple, vous pouvez obtenir un entier à partir d'un ensemble comme ceci : 

mysql> SELECT col_set+0 FROM nom_de_table;

Si un nombre est enregistré dans une colonne SET, les bits un à un de ce nombre représenteront les éléments placés dans cet ensemble. Supposons qu'une colonne est spécifiée en tant que SET("a","b","c","d"), les membres ont alors les valeurs suivantes :

SET membre Valeur décimale Valeur binaire
a 1 0001
b 2 0010
c 4 0100
d 8 1000

Si vous assignez 9 à cette colonne, cela donne 1001 en binaire, ce qui fait que les valeurs du premier et quatrième membres "a" et "d" sont sélectionnés et la valeur résultante est "a,d".

Pour les valeurs se composant de plus d'un membre du SET, l'ordre des membres n'a pas d'importance lors des insertions. Le nombre d'occurence d'un élément n'importe pas non plus. Lorsque la valeur sera lue ultérieurement, chaque élément n'appara&icrirc;tra qu'une seule fois, et dans l'ordre donné à la déclaration de la colonne. Par exemple, si une colonne est spécifiée comme SET("a","b","c","d"), alors "a,d", "d,a", et "d,a,a,d,d" seront tous représentés par "a,d".

Si vous spécifiez une valeur incorrecte dans une colonne SET, la valeur sera ignorée.

Les valeurs de SET sont triées numériquement. La valeur NULL précède toutes les autres.

Normalement, vous exécuterez un SELECT sur une colonne SET en utilisant l'opérateur LIKE ou la fonction FIND_IN_SET() : 

mysql> SELECT * FROM nom_de_table WHERE set_col LIKE '%value%';
mysql> SELECT * FROM nom_de_table WHERE FIND_IN_SET('value',set_col)>0;

Mais ce qui suit fonctionnera aussi : 

mysql> SELECT * FROM nom_de_table WHERE set_col = 'val1,val2';
mysql> SELECT * FROM nom_de_table WHERE set_col & 1;

La première requête cherche les lignes qui correspondent exactement. La seconde ne cherche que les lignes contenant le premier membre du set.

Si vous voulez connaître toutes les valeurs possible d'une colonne SET, vous devez utiliser : SHOW COLUMNS FROM nom_de_table LIKE nom_colonne_set et étudier la définition du SET dans la seconde colonne.

6.2.4 Choisir le bon type de colonne

Pour une utilisation optimale des capacitées de stockage, essayez d'utiliser le type le plus optimal dans chaque cas. Par exemple, si une colonnes du type entier sera utilisée pour des valeurs entre 1 et 99999, le type MEDIUMINT UNSIGNED sera le plus approprié.

La représentation des valeurs monétaires est un problème commun. Avec MySQL, vous devrez utiliser le type DECIMAL. Il est sauvegardé en tant que chaîne, aucune perte de précision ne devrait avoir lieu. Si la précision n'est pas très importante, vous pouvez utiliser le type DOUBLE.

Pour une haute précision, vous pouvez toujours transcrire en nombre décimaux, et les enregistrer dans des BIGINT. Cela vous permettra d'effectuer tout vos calculs avec des entiers et de convertir à nouveau en nombre décimaux au besoin.

6.2.5 Utilisation des types de données issues d'autres SGBDR

Pou faciliter l'importation de code SQL issu d'autres systèmes de gestion de bases de données, MySQL convertit les types de colonnes comme le montre le tableau suivant. Cette conversion facilite l'import de structures de tables :

Other vendor type MySQL type
BINARY(NUM) CHAR(NUM) BINARY
CHAR VARYING(NUM) VARCHAR(NUM)
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
MIDDLEINT MEDIUMINT
VARBINARY(NUM) VARCHAR(NUM) BINARY

La conversion du type de colonnes s'effectue lors de la création. Si vous créez une table avec des types issus d'un autre SGBDR puis que vous exécutez la commande DESCRIBE nom_de_table, MySQL fournira la structure de la table en utilisant les types équivalents.

6.2.6 Capacités des colonnes

Les capacités de stockage de chaque type de colonnes de MySQL sont listés par catégories.

Capacités de stockage des colonnes numériques

Type de colonne Espace requis
TINYINT 1 octet
SMALLINT 2 octets
MEDIUMINT 3 octets
INT 4 octets
INTEGER 4 octets
BIGINT 8 octets
FLOAT(X) 4 if X <= 24 or 8 if 25 <= X <= 53
FLOAT 4 octets
DOUBLE 8 octets
DOUBLE PRECISION