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 8 octets
REAL 8 octets
DECIMAL(M,D) M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D)
NUMERIC(M,D) M+2 octets si D > 0, M+1 octets si D = 0 (D+2, si M < D)

Capacités de stockage des colonnes de date et heure

Type de colonne Espace requis
DATE 3 octets
DATETIME 8 octets
TIMESTAMP 4 octets
TIME 3 octets
YEAR 1 octet

Capacités de stockage des colonnes de texte

Type de colonne Espace requis
CHAR(M) M octets, 1 <= M <= 255
VARCHAR(M) L+1 octets, avec L <= M et 1 <= M <= 255
TINYBLOB, TINYTEXT L+1 octets, avec L < 2^8
BLOB, TEXT L+2 octets, avec L < 2^16
MEDIUMBLOB, MEDIUMTEXT L+3 octets, avec L < 2^24
LONGBLOB, LONGTEXT L+4 octets, avec L < 2^32
ENUM('valeur1','valeur2',...) 1 ou 2 octets, suivant le nombre d'éléments de l'énumération (65535 au maximum)
SET('valeur1','valeur2',...) 1, 2, 3, 4 ou 8 octets, suivant le nombre de membres de l'ensemble (64 au maximum)

Les types VARCHAR, BLOB et TEXT sont de longueur variable, et l'espace disque requis dépend de la taille réelle de la valeur présente dans la colonne, (taille représentée par L dans le tableau précédent) et non pas de la taille maximale de la colonne. Par exemple une colonne VARCHAR(10) peut contenir une chaîne de 10 caractères. L'espace requis est dans ce cas là la longueur de la chaîne (L), plus 1 octet pour enregistrer la longueur de celle ci. Pour la chaîne 'abcd', L est égal à 4 et l'espace requis est de 5 octets.

Les types BLOB et TEXT requièrent 1, 2, 3, ou 4 octets pour mémoriser la taille de la valeur dans la colonne, suivant la longueur maximale du type. See section 6.2.3.2 Les types BLOB et TEXT.

Si une table inclus au moins une colonne de taille variable, la ligne sera de taille variable. Notez que lorsqu'une table est créée, MySQL peut, dans certaines circonstances, changer automatiquement une colonne de taille variable en colonne à taille fixe (et vice-versa). See section 6.5.3.1 Modification automatique du type de colonnes.

La taille d'un ENUM est détérminée par le nombre d'éléments de l'énumération. Un octet est nécessaire pour les énumérations ayant jusqu'à 255 valeurs possibles. Deux octets sont nécessaires pour les énumerations ayant jusqu'à 65535 valeurs possibles. See section 6.2.3.3 Le type ENUM.

La taille d'un SET est détéerminé par le nombre de ses membres. Si il y'en a N, l'objet occupe (N+7)/8 octets, arrondis à 1, 2, 3, 4, or 8 octets. Un SET peut avoir au plus 64 membres. See section 6.2.3.4 Le type SET.

La taille maximale d'une ligne dans une table MyISAM est de 65534 octets. Les colonnes BLOB et TEXT accepentent jusqu'à 5-9 octets en dessous de cette taille.

6.3 Fonctions à utiliser dans les clauses SELECT et WHERE

Une sélection ou une clause WHERE dans une requête SQL peut se former d'expressions utilisant les fonctions décrites ci-dessous.

Une expression contenant NULL produira toujours la valeurNULL comme résultat. (Sauf contre-indication dans le manuel)

Note : Il ne doit pas y avoir d'espace entre le nom d'une fonction et la parenthèse ouvrante la suivant. Cela aide le parseur MySQL à distinguer les appels à ces fonction des références aux tables ou colonnes ayant le même nom qu'une fonction. Les espaces autour des arguments sont autorisés.

Vous pouvez forcer MySQL à accepter les espaces après les nom de fonctions grâce à l'option --ansi de mysqld, ou en utilisant l'option CLIENT_IGNORE_SPACE avec mysql_connect. Dans ce cas, toutes les fonctions définies deviendront des mots strictement réservés. See section 1.8.2 Exécuter MySQL en mode ANSI.

Dans un soucis de simplicité, les affichages des résultats de mysql sont fournis sous forme abrégée. Par exemple : 

mysql> SELECT MOD(29,9);
1 rows in set (0.00 sec)

+-----------+
| mod(29,9) |
+-----------+
|         2 |
+-----------+

est affiché comme ceci : 

mysql> SELECT MOD(29,9);
        -> 2

6.3.1 Opérateurs et fonctions tout-types

6.3.1.1 Parenthèses

( ... )

Utilisez les parenthèses pour forcer l'ordre des évaluations dans une expression. Par exemple : 

mysql> SELECT 1+2*3;
        -> 7
mysql> SELECT (1+2)*3;
        -> 9

6.3.1.2 Opérateurs de comparaison

Les opérations de comparaison donnent comme résultats 1 (TRUE), 0 (FALSE), ou NULL. Ces fonctions fonctionnent pour les nombres comme pour les chaînes. Les nombres sont automatiquement transformés en chaînes et les chaînes en nombres si besoin en est. (comme en Perl)

MySQL effectue les comparaisons suivant les règles suivantes :

Par défaut, la comparaison des chaînes s'effectue d'une façcon insensible à la casse en utilisant le jeu de charactères courant (ISO-8859-1 Latin1 par défaut, qui fonctionne aussi très bien pour l'anglais).

Les exemples suivants, montrent la conversion des chaînes en nombres pour les opérations de comparaison : 

mysql> SELECT 1 > '6x';
         -> 0
mysql> SELECT 7 > '6x';
         -> 1
mysql> SELECT 0 > 'x6';
         -> 0
mysql> SELECT 0 = 'x6';
         -> 1
=
Egal : 
mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1
<>
!=
Différent : 
mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1
<=
Inférieur ou égal : 
mysql> SELECT 0.1 <= 2;
        -> 1
<
Strictement inférieur : 
mysql> SELECT 2 < 2;
        -> 0
>=
Supérieur ou égal : 
mysql> SELECT 2 >= 2;
        -> 1
>
Strictement supérieur : 
mysql> SELECT 2 > 2;
        -> 0
<=>
Egalité sure avec NULL : 
mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1 1 0
IS NULL
IS NOT NULL
Tester si une valeur est ou n'est pas NULL: 
mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0 0 1
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1 1 0
Pour être compatible avec les autres programmes, MySQL gère les appels qui utilisent IS NULL de la façon suivante :
expression BETWEEN min AND max
Si expression est supérieure ou égale à min et expression est inférieure ou égale à max, BETWEEN retourne 1, sinon 0. Ceci est équivalent à l'expression (min <= expression AND expression <= max) si tous les arguments sont du même type. Dans tous les autres cas, la conversion de type prends place, selon les règles suivantes, mais appliquée aux trois arguments. Notez que avant la 4.0.5, les arguments étaient convertis au type de expr. 
mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0
expr NOT BETWEEN min AND max
Même chose que NOT (expr BETWEEN min AND max).
expr IN (valeur,...)
Retourne 1 si expr est l'une des valeurs dans la liste IN, sinon retourne 0. Si toutes les valeurs sont des constantes, toutes les valeurs sont évaluées avec le type de expr et triées. La recherche de l'élément est alors faite en utilisant la recherche binaire. Cela signifie que IN est très rapide si les valeurs contenues dans la liste IN sont toutes des constantes. Si expr est une chaîne sensible à la casse, la comparaison est faite dans un contexte sensible à la casse : 
mysql> SELECT 2 IN (0,3,5,'wefwf');
        -> 0
mysql> SELECT 'wefwf' IN (0,3,5,'wefwf');
        -> 1
expr NOT IN (value,...)
Même chose que NOT (expr IN (valeur,...)).
ISNULL(expr)
Si expr est NULL, ISNULL() retourne 1, sinon il retourne 0: 
mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1
Notez que la comparaison de deux valeurs NULL en utilisant = donnera toujours false !
COALESCE(list)
Retourne le premier élément non-NULL de la liste : 
mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL
INTERVAL(N,N1,N2,N3,...)
Retourne 0 si N < N1, 1 si N < N2 etc... Tous les arguments sont traités en tant qu'entiers. Il est requis que N1 < N2 < N3 < ... < Nn pour que cette fonction fonctionne correctement. Cela est due à la recherche binaire utilisée (très rapide) : 
mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0

Si vous comparez des chaînes non sensibles à la casse avec l'un des opérateurs standards (=, <>..., mais pas LIKE) les espaces en début et fin de chaîne (espaces, tabulations et nouvelles lignes) seront ignorées. 

mysql> SELECT "a" ="A \n";
        -> 1

6.3.1.3 Opérateurs logiques

En SQL, tous les opérateurs logiques évaluent à TRUE, FALSE ou NULL (INCONNU). En MySQL, c'est implémenté en 1 (TRUE), 0 (FALSE), et NULL. La plupart de ce qui suit est est commun entre les différents bases de données SQL, pourtant, certains système pourraient retourner une valeur non nulle pour TRUE (pas obligatoirement 1).

NOT
!
NOT (NON) logique. Evalue à 1 si l'opérande est 0, à 0 si l'opérande est non nulle, et NOT NULL retourne NULL. 
mysql> SELECT NOT 10;
        -> 0
mysql> SELECT NOT 0;
        -> 1
mysql> SELECT NOT NULL;
        -> NULL
mysql> SELECT ! (1+1);
        -> 0
mysql> SELECT ! 1+1;
        -> 1
Le dernier exemple donne 1 car l'expression est évaluée comme (!1)+1.
AND
&&
AND (ET) logique. Evalue à 1 si toutes les opérandes sont différentes de zéro et de NULL, à 0 si l'une des opérandes est 0, dans les autres cas, NULL est retourné. 
mysql> SELECT 1 && 1;
        -> 1
mysql> SELECT 1 && 0;
        -> 0
mysql> SELECT 1 && NULL;
        -> NULL
mysql> SELECT 0 && NULL;
        -> 0
mysql> SELECT NULL && 0;
        -> 0
Notez que pour les versions antérieures à la 4.0.5 l'évaluation est interrompue lorsque NULL est rencontré, au lieu de continuer à tester une éventuelle existence de 0. Cela signifie que dans ces versions, SELECT (NULL AND 0) retourne NULL au lieu de 0. En 4.0.5 le code a été revu pour que le résultat réponde toujours au normes ANSI tout en optimisant le plus possible.
OR
||
OR (OU inclusif) logique. Evalue à 1 si aucune opérande n'est nulle, à NULL si l'une des opérandes est NULL, sinon 0 est retourné. 
mysql> SELECT 1 || 1;
        -> 1
mysql> SELECT 1 || 0;
        -> 1
mysql> SELECT 0 || 0;
        -> 0
mysql> SELECT 0 || NULL;
        -> NULL
mysql> SELECT 1 || NULL;
        -> 1
XOR
XOR (OU exclusif) logique. Retourne NULL si l'une des opérandes est NULL. Pour les opérandes non-NULL, évalue à 1 si un nombre pair d'opérandes est non-nul, sinon 0 est retourné. 
mysql> SELECT 1 XOR 1;
        -> 0
mysql> SELECT 1 XOR 0;
        -> 1
mysql> SELECT 1 XOR NULL;
        -> NULL
mysql> SELECT 1 XOR 1 XOR 1;
        -> 1
a XOR b est mathématiquement égal à (a AND (NOT b)) OR ((NOT a) and b).

6.3.1.4 Les fonctions de contrôle

IFNULL(expr1,expr2)
Si l'argument expr1 n'est pas NULL, la fonction IFNULL() retournera l'argument expr1, sinon elle retournera l'argument expr2. La fonction IFNULL() retourne une valeur numérique ou une chaîne de caractères, suivant le contexte d'utilisation : 
mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'oui');
        -> 'oui'
En version 4.0.6 et plus rˇcent, le rˇsultat par dˇfaut de IFNULL(expr1,expr2) est le plus 'gˇnˇral' des deux expressions, dans l'ordre de type STRING, REAL ou INTEGER. La diffˇrence avec les anciennes versions de MySQL ne seront notables que si vous crˇez une table basˇe sur des expressions, ou si MySQL stocke en interne des valeurs issues de IFNULL() dans une table temporaire. 
CREATE TABLE foo SELECT IFNULL(1,"test") as test;
En MySQL 4.0.6, le type de la colonne 'test' est CHAR(4) tandis que dans les versions plus anciennes, vous auriez obtenu un BIGINT.
NULLIF(expr1,expr2)
Si l'expression expr1 = expr2 est vrai, la fonction retourne NULL sinon elle retourne expr1. Cela revient à faire CASE WHEN x = y THEN NULL ELSE x END: 
mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1
Notez que l'argument expr1 est évalué deux fois dans MySQL si les arguments sont égaux.
IF(expr1,expr2,expr3)
Si l'arguement expr1 vaut TRUE (expr1 <> 0 et expr1 <> NULL) alors la fonction IF() retourne l'argument expr2, sinon, elle retourne l'argument expr3. La fonction IF() retourne une valeur numérique ou une chaîne de caractères, suivant le contexte d'utilisation : 
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'oui','non');
        -> 'oui'
mysql> SELECT IF(STRCMP('test','test1'),'non','oui');
        -> 'non'
Si l'argument expr2 ou expr3 est explicitement NULL alors the le type du résultat de la fonction IF() est le type de la colonne non NULL. (Ce comportement est nouveau dans MySQL 4.0.3). L'argument expr1 est évalué comme un entier, cela signifie que si vous testez un nombre à virgule flottante ou une chaîne de caractères, vous devez utiliser une opération de comparaison : 
mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
        -> 1
Dans le premier exemple ci-dessus, IF(0.1) retourne 0 parce que 0.1 est converti en une chaîne de caractères, ce qui revient à tester IF(0). Ce n'est certainement pas ce que vous désireriez. Dans le second exemple, la comparaison teste si le nombre à virgule flottante est différent de zéro. Le résultat de cette comparaison sera un entier. Le type de la fonction IF() (ce qui peut être important s'il est stocké dans une table temporaire) est calculé, dans la Version 3.23 de MySQL, comme suit :
Expression Valeur retournée
expr2 ou expr3 retourne une chaîne chaîne
expr2 ou expr3 retourne un nombre à virgule nombre à virgule
expr2 ou expr3 retourne un entier entier
Si expr2 et expr3 sont des chaînes de caractères, alors le résultat est insensible à la casse si les deux chaînes de caractères sont insensibles à la casse. (A partir de la version 3.23.51 de MySQL)
CASE valeur WHEN [compare-value] THEN résultat [WHEN [compare-value] THEN résultat ...] [ELSE résultat] END
CASE WHEN [condition] THEN résultat [WHEN [condition] THEN résultat ...] [ELSE résultat] END
La première version retourne résultat si valeur=compare-value. La seconde version retourne le résultat de la première condition qui se réalise. Si aucune des conditions n'est réalisé, alors le résultat de la clasue ELSE est retourné. Si il n'y a pas de clause ELSE alors NULL est retourné : 
mysql> SELECT CASE 1 WHEN 1 THEN "un"
           WHEN 2 THEN "deux" ELSE "plus" END;
       -> "un"
mysql> SELECT CASE WHEN 1>0 THEN "vrai" ELSE "faux" END;
       -> "vrai"
mysql> SELECT CASE BINARY "B" WHEN "a" THEN 1 WHEN "b" THEN 2 END;
       -> NULL

Le type de la valeur retournée (INTEGER, DOUBLE ou STRING) est de même type que la première valeur retournée (l'expression après le premier THEN).

6.3.2 Fonctions de chaînes de caractères

Les fonctions qui traitent les chaînes de caractères retournent NULL si la longueur du résultat finit par dépasser la taille maximale du paramètre max_allowed_packet, défini dans la configuration du serveur. See section 5.5.2 Réglage des paramètres du serveur.

Pour les fonctions qui opèrent sur des positions à l'intérieur d'une chaîne, la position initiale est 0.

ASCII(str)
Retourne le code ASCII du premier caractère de la chaîne de caractères str. Retourne 0 si la chaîne de caractère str est vide. Retourne NULL si la chaîne de caractères str est NULL : 
mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100
Voir aussi la fonction ORD().
ORD(str)
Si le premier caractère de la chaîne str est un caractère multi-octets, la fonction retourne le code de ce caractère, calculé à partir du code ASCII retourné par cette formule : ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. Si le premier caractère n'est pas un caractère multi-octet, la fonction retournera la même valeur que la fonction ASCII() : 
mysql> SELECT ORD('2');
        -> 50
CONV(N,from_base,to_base)
Convertit des nombres entre différentes bases. Retourne une chaîne de caractères représentant le nombre N, convertit de la base from_base vers la base to_base. La fonction retourne NULL si un des arguments est NULL. L'argument N est interprété comme un entier, mais peut être spécifié comme un entier ou une chaîne de caractères. Le minimum pour la base est 2 et son maximum est 36. Si to_base est un nombre négatif, N sera considéré comme un nombre signé. Dans le cas contraire, N sera traité comme un nombre non-signé. La fonction CONV travaille avec une précision de 64 bits : 
mysql> SELECT CONV("a",16,2);
        -> '1010'
mysql> SELECT CONV("6E",18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
        -> '40'
BIN(N)
Retourne une chaîne de caractères représentant la valeur binaire de l'argument N, où l'argument N est un nombre de type BIGINT. Cette fonction est un équivalent de CONV(N,10,2). Retourne NULL si l'argument N est NULL : 
mysql> SELECT BIN(12);
        -> '1100'
OCT(N)
Retourne une chaîne de caractères représentant la valeur octal de l'argument N, où l'argument N est un nombre de type BIGINT. Cette fonction est un équivalent de CONV(N,10,8). Retourne NULL si l'argument N est NULL: 
mysql> SELECT OCT(12);
        -> '14'
HEX(N_or_S)
Si l'argument N_OR_S est un nombre, cette fonction retournera une chaîne de caractère représentant la valeur hexadécimale de l'argument N, où l'argument N est de type BIGINT. Cette fonction est un équivalent de CONV(N,10,16). Si N_OR_S est une chaîne de caractères, cette fonction retournera une chaîne de caractères hexadécimale de N_OR_S où chaque caractère de N_OR_S est converti en 2 chiffres hexadécimaux. C'est l'inverse de la chaîne 0xff. 
mysql> SELECT HEX(255);
        -> 'FF'
mysql> SELECT HEX("abc");
        -> 616263
mysql> SELECT 0x616263;
        -> "abc"
CHAR(N,...)
La fonction CHAR() interprète les arguments comme des entiers et retourne une chaîne de caractères, constituée des caractères, identifiés par leur code ASCII. Les valeurs NULL sont ignorées : 
mysql> SELECT CHAR(77,121,83,81,'76');
        -> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
        -> 'MMM'
CONCAT(str1,str2,...)
Retourne une chaîne représentant la concaténation des arguments. Retourne NULL si un des arguments est NULL. Cette fonction peut prendre plus de 2 arguments. Si un argument est un nombre, il sera converti en son équivalent sous forme de chaîne de caractères : 
mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'
CONCAT_WS(separator, str1, str2,...)
La fonction CONCAT_WS() signifie CONCAT With Separator, c'est-à-dire "concaténation avec séparateur. Le premier argument est le séparateur utilisé pour le reste des arguments. Le séparateur peut être une chaîne de caractères, tout comme le reste des arguments. Si le séparateur est NULL, le résultat sera NULL. Cette fonction ignorera tous les arguments de valeur NULL et vides, hormis le séparateur. Le séparateur sera ajouté entre tous les arguments à concaténer : 
mysql> SELECT CONCAT_WS(",","Premier nom","Deuxième nom","Dernier nom");
       -> 'Premier nom,Deuxième nom,Dernier nom'
mysql> SELECT CONCAT_WS(",","Premier nom",NULL,"Dernier nom");
       -> 'Premier nom,Dernier nom'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Retourne le nombre de caractères de la chaîne str: 
mysql> SELECT LENGTH('text');
        -> 4
mysql> SELECT OCTET_LENGTH('text');
        -> 4
Notez que pour la fonction CHAR_LENGTH() et la fonction CHARACTER_LENGTH(), les caractères multi-octets sont comptés une fois seulement.
BIT_LENGTH(str)
Retourne le nombre de bits de la chaîne de caractères str : 
mysql> SELECT BIT_LENGTH('text');
        -> 32
LOCATE(substr,str)
POSITION(substr IN str)
Retourne la position de la première occurrence de la chaîne substr dans la chaîne de caractères str. Retourne 0 si substr ne se trouve pas dans la chaîne de caractères str: 
mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
Cette fonction gère les caractères multi-octets. Dans la version 3.23 de MySQL, cette fonction est sensible à la casse, alors que dans la version 4.0 de MySQL, cette fonction sera sensible à la casse si l'argument est une chaîne de caractères binaire.
LOCATE(substr,str,pos)
Retourne la position de la première occurrence de la chaîne substr dans la chaîne de caractères str, à partir de la position pos. Retourne 0 si substr ne se trouve pas dans la chaîne de caractères str: 
mysql> SELECT LOCATE('bar', 'foobarbar',5);
        -> 7
Cette fonction gère les caractères multi-octets. Dans la version 3.23 de MySQL, cette fonction est sensible à la casse, alors que dans la version 4.0 de MySQL, cette fonction sera sensible à la casse si l'argument est une chaîne de caractères binaire.
INSTR(str,substr)
Retourne la position de la première occurence de la chaîne substr dans la chaîne de caractères str. Cette fonction est exactement la même que la fonction LOCATE(), à la différence que ces arguments sont inversés : 
mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
Cette fonction gère les caractères multi-octets. Dans la version 3.23 de MySQL, cette fonction est sensible à la casse, alors que dans la version 4.0 de MySQL, cette fonction sera sensible à la casse si l'argument est une chaîne de caractères binaire.
LPAD(str,len,padstr)
Retourne la chaîne de caractères str, complétée à gauche par la chaîne de caractères padstr jusqu'à ce que la chaîne de caractères str atteigne len caractères de long. Si la chaîne de caractères str est plus longue que len' caractères, elle sera raccourcie de len caractères. 
mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
RPAD(str,len,padstr)
Retourne la chaîne de caractères str, complétée à droite par la chaîne de caractères padstr jusqu'à ce que la chaîne de caractères str atteigne len caractères de long. Si la chaîne de caractères str est plus longue que len' caractères, elle sera raccourcie de len caractères. 
mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
LEFT(str,len)
Retourne les len caractères les plus à gauche de la chaîne de caractères str : 
mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
Cette fonction gère les caractères multi-octets.
RIGHT(str,len)
Retourne les len caractères les plus à droite de la chaîne de caractères str : 
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'
Cette fonction gère les caractères multi-octets.
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
Retourne une chaîne de len caractères de long dela chaîne str, à partir de la position pos. La syntaxe ANSI SQL92 utilise une variante de la fonction FROM : 
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
Cette fonction gère les caractères multi-octets.
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
Retourne une portion de la chaîne de caractères str à partir de la position pos : 
mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
Cette fonction gère les caractères multi-octets.
SUBSTRING_INDEX(str,delim,count)
Retourne une portion de la chaîne de caractères str, située avant count occurences du délimiteur delim. Si l'argument count est positif, tout ce qui précède le délimiteur final sera retourné. Si l'argument count est négatif, tout ce qui suit le délimiteur final sera retourné : 
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
Cette fonction gère les caractères multi-octets.
LTRIM(str)
Retourne la chaîne de caractères str sans les espaces initiaux : 
mysql> SELECT LTRIM('  barbar');
        -> 'barbar'
RTRIM(str)
Retourne la chaîne de caractères str sans les espaces finaux : 
mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'
Cette fonction gère les caractères multi-octets.
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
Retourne la chaîne de caractères str dont tous les préfixes et/ou suffixes remstr ont été supprimés. Si aucun des spécificateurs BOTH, LEADING ou TRAILING sont fournis, BOTH est utilisé comme valeur par défaut. Si remstr n'est pas spécifié, les espaces sont supprimés : 
mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'
Cette fonction gère les caractères multi-octets.
SOUNDEX(str)
Retourne la valeur Soundex de la chaîne de caractères str. Deux chaînes qui ont des sonorités proches auront des valeurs soundex proches. Une chaîne Soundex standart possède 4 caractères, mais la fonction SOUNDEX() retourne une chaîne de longueur arbitraire. Vous pouvez utiliser la fonction SUBSTRING() sur ce résultat pour obtenir une chaine Soundex standart. Tout caractère non alpha-numérique sera ignoré. Tous les caractères internationaux qui ne font pas partie de l'alphabet de base (A-Z) seront considérés comme des voyelles : 
mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'
SPACE(N)
Retourne une chaîne constituée de N espaces : 
mysql> SELECT SPACE(6);
        -> '      '
REPLACE(str,from_str,to_str)
Retourne une chaîne de caractères str dont toutes les occurences de la chaîne from_str sont remplacées par la chaîne to_str : 
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'
Cette fonction gère les caractères multi-octets.
REPEAT(str,count)
Retourne une chaîne de caractères constituée de la répétition de count fois la chaîne str. Si count <= 0, retourne une chaîne vide. Retourne NULL si str ou count sont NULL : 
mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'
REVERSE(str)
Retourne une chaîne dont l'ordre des caractères est l'inverse de la chaîne str : 
mysql> SELECT REVERSE('abc');
        -> 'cba'
Cette fonction gère les caractères multi-octets.
INSERT(str,pos,len,newstr)
Retourne une chaîne de caractères str, après avoir remplacé la portion de chaîne commençant à la position pos et de longueur len caractères, par la chaîne newstr : 
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
Cette fonction gère les caractères multi-octets.
ELT(N,str1,str2,str3,...)
Retourne str1 si N = 1, str2 si N = 2, et ainsi de suite. Retourne NULL si N est plus petit que 1 ou plus grand que le nombre d'arguments. La fonction ELT() est un complément de la fonction FIELD() : 
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
        -> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
        -> 'foo'
FIELD(str,str1,str2,str3,...)
Retourne l'index de la chaîne str dans la liste str1, str2, str3, .... Retourne 0 si str n'est pas trouvé. La fonction FIELD() est un complément de la fonction ELT(): 
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
        -> 0
FIND_IN_SET(str,strlist)
Retourne une valeur de 1 à N si la chaîne str se trouve dans la liste strlist constituée de N chaînes. Une liste de chaîne est une chaîne composée de sous-chaînes séparées par une virgule `,'. Si le premier argument est une chaîne constante et le second, une colonne de type SET, la fonction FIND_IN_SET() est optimisée pour utiliser une recherche binaire très rapide. Retourne 0 si str n'est pas trouvé dans la liste strlist ou si la liste strlist est une chaîne vide. Retourne NULL si l'un des arguments est NULL. Cette fonction ne fonctionne pas correctement si le premier argument contient une virgule `,' : 
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2
MAKE_SET(bits,str1,str2,...)
Retourne une liste (une chaîne contenant des sous-chaînes séparées par une virgule `,') constituée de chaînes qui ont le bit correspondant dans la liste bits. str1 correspond au bit 0, str2 au bit 1, etc... Les chaines NULL dans les listes str1, str2, ... sont ignorées : 
mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''
EXPORT_SET(bits,on,off,[séparateur,[nombre_de_bits]])
Retourne une chaîne dont tous les bits à 1 dans 'bit' sont représentés par la chaîne 'on', et dont tous les bits à 0 sont représentés par la chaîne 'off'. Chaque chaîne est séparée par 'séparateur' (par défaut, une virgule ',') et seul 'nombre_de_bits' (par défaut, 64) 'bits' est utilisé : 
mysql> SELECT EXPORT_SET(5,'Y','N',',',4)
        -> Y,N,Y,N
LCASE(str)
LOWER(str)
Retourne une chaîne str dont tous les caractères ont été mis en minuscule, en accord avec le charset courant (le charset par défaut est ISO-8859-1 Latin1) : 
	mysql> SELECT LCASE('QUADRATICALLY');
        -> 'quadratically'
Cette fonction gère les caractères multi-octets.
UCASE(str)
UPPER(str)
Retourne une chaîne str dont tous les caractères ont été mis en majuscule, en accord avec le charset courant (le charset par défaut est ISO-8859-1 Latin1) : 
mysql> SELECT UCASE('Hej');
        -> 'HEJ'
Cette fonction gère les caractères multi-octets.
LOAD_FILE(file_name)
Lit le fichier file_name et retourne son contenu sous la forme d'une chaîne de caractères. Le fichier doit se trouver sur le serveur qui exécute MySQL, vous devez spécifier le chemin absolu du fichier et vous devezavoir les droits en lecture sur celui-ci. Le fichier doit pouvoir être lisible par tous et doit être plus petit que max_allowed_packet. Si ce fichier n'existe pas ou ne peut pas être lu pour différentes raisons, la fonction retourne NULL : 
mysql> UPDATE tbl_name
           SET blob_column=LOAD_FILE("/tmp/picture")
           WHERE id=1;
Si vous n'utilisez pas la version 3.23 de MySQL, vous devez lire le fichier depuis votre application et créer ainsi votre requête INSERT vous-même, pour mettre à jour la base de données avec le contenu de ce fichier. Une des possibilités pour réaliser ceci, si vous utilisez la librairie MySQL++, peut être trouvée à http://www.mysql.com/documentation/mysql++/mysql++-examples.html.
QUOTE(str)
Echappe les caractères d'une chaîne pour produire un résultat qui sera exploitable dans une requête SQL. Les caractères suivants seront précédés d'un anti-slash dans la chaîne retournée : le guillement simple (`''), l'anti-slash (`\'), ASCII NUL, et le Control-Z. Si l'argument vaut NULL, la valeur retournée sera le mot ``NULL'' sans les guillements simples. La fonction QUOTE a été ajoutée en MySQL version 4.0.3. 
mysql> SELECT QUOTE("Don't");
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL

MySQL convertit automatiquement les nombres en une chaînes de caractères, si nécessaire et vice-versa : 

mysql> SELECT 1+"1";
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

Si vous voulez convertir explicitement un nombre en une chaîne de caractères, passez-le en argument à la fonction CONCAT().

Si une fonction de chaînes de caractères prend en argument une chaîne binaire, le résultat sera également une chaîne binaire. Un nombre converti en une chaîne sera traité comme étant une chaîne binaire. Cela affecte uniquement les comparaisons.

6.3.2.1 Opérateurs de comparaison pour les chaînes de caractères

Normalement, si l'une des expressions dans une comparaison de chaîne est sensible à la casse, la comparaison est exécutée en tenant compte de la casse.

expr LIKE pat [ESCAPE 'escape-char']
La réalisation d'expression utilisant les expressions régulières simples de comparaison de SQL. Retourne 1 (TRUE) ou 0 (FALSE). Avec LIKE, vous pouvez utiliser les deux jokers suivants :
Char Description
% Remplace n'importe quel nombre de caractères, y compris aucun
_ Remplace exactement un caractère 
mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1
Pour tester la présence littérale d'un joker, précédez-le d'un caractère d'échappement. Si vous ne spécifiez pas le caractère d'échappement ESCAPE, le caractère `\' sera utilisé :
String Description
\% Remplace le caractère littéral %
\_ Remplace le caractère littéral _ 
mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1
Pour spécifier un caractère d'échappement différent, utilisez la clause ESCAPE : 
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1
Les deux exemples suivants illustrent le fait que les comparaisons de chaînes de caractères ne sont pas sensibles à la casse à moins qu'une des opérandes soit une chaîne binaire. 
mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0
LIKE est également autorisé pour les expressions numériques. (C'est une extension MySQL à la norme ANSI SQL LIKE.) 
mysql> SELECT 10 LIKE '1%';
        -> 1
Note : Comme MySQL utilise la syntaxe d'échappement de caractères du langage C dans les chaînes (par exemple, `\n'), vous devez doubler tous les slashs `\' que vous utilisez dans les expressions LIKE. Par exemple, pour rechercher les nouvelles lignes (`\n'), vous devez le spécifier comme cela : `\\n'. Pour rechercher un anti-slash (`\'), vous devez le spécifier comme cela : `\\\\' (les anti-slashs sont supprimés une première fois pas l'analyseur syntaxique, puis une deuxième fois par le moteur d'expression régulières, ce qui ne laisse qu'un seul anti-slash à la fin).
expr NOT LIKE pat [ESCAPE 'escape-char']
Equivalent à NOT (expr LIKE pat [ESCAPE 'escape-char']).
expr REGEXP pat
expr RLIKE pat
Effectue une recherche de chaîne avec l'expression régulière pat. Le masque peut être une expression régulière étendue. Voir la section See section F Expressions régulières MySQL. Retourne 1 si expr correspond au masque pat, sinon, retourne 0. RLIKE est un synonyme de REGEXP, fourni pour assurer la compatiblité avec mSQL. Note : Comme MySQL utilise la syntaxe d'échappement de caractères du langage C dans les chaînes (par exemple, `\n'), vous devez doubler tous les anti-slashs `\' que vous utilisez dans les expressions REGEXP. A partir de la version 3.23.4 de MySQL, REGEXP est insensible à la casse pour les comparaisons de chaînes normales (non binaires) : 
mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> SELECT "a" REGEXP "A", "a" REGEXP BINARY "A";
        -> 1  0
mysql> SELECT "a" REGEXP "^[a-d]";
        -> 1
REGEXP et RLIKE utilise le jeu de caractères courant (ISO-8859-1 Latin1 par défaut) lorsqu'il faut décider du type d'un caractère.
expr NOT REGEXP pat
expr NOT RLIKE pat
Identique à NOT (expr REGEXP pat).
STRCMP(expr1,expr2)
STRCMP() retourne 0 si les chaînes sont identiques, -1 si la première chaîne est plus petite que la seconde et 1 dans les autres cas : 
mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0
MATCH (col1,col2,...) AGAINST (expr)
MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)
MATCH ... AGAINST() est utilisé pour les recherches en texte plein et retourne une information de pertinence - pertinence mesuré entre le texte des colonnes (col1,col2,...) et la valeur expr. La pertinence est un nombre à virgule flottante positif. La pertinence zéro signifie qu'il n'y a aucune similitude. MATCH ... AGAINST() est utilisable dans les versions 3.23.23 ou suivantes de MySQL. L'extension IN BOOLEAN MODE a été ajoutée dans la version 4.0.1. Pour plus des détails ainsi que des exemples, voir section 6.8 Recherche en Texte-entier (Full-text) dans MySQL.

6.3.2.2 Sensibilité à la casse

BINARY
L'opérateur BINARY modifie la chaîne qui le suit en une chaîne binaire. C'est une solution simple pour forcer la comparaison de colonnes à être sensible à la casse même si la colonne n'est pas définie comme étant de type BINARY ou BLOB : 
mysql> SELECT "a" = "A";
        -> 1
mysql> SELECT BINARY "a" = "A";
        -> 0
BINARY string est un raccourci pour CAST(string AS BINARY). See section 6.3.5 Fonctions de transtypage. BINARY a été introduit dans MySQL à partir de la version 3.23.0. Notez que dans quelques cas, MySQL n'est pas capable d'utiliser l'index efficacement lorsque vous modifiez une colonne indexée en BINARY.

Si vous voulez comparer un champ de type BLOB d'une manière insensible à la casse, vous pouvez toujours le convertir en majuscules avant d'effectuer la comparaison : 

SELECT 'A' LIKE UPPER(blob_col) FROM nom_de_table;

Nous avons planifié d'introduire bientôt de possibles modifications entre les différents jeux de caractères pour rendre les comparaisons de chaînes encore plus flexibles.

6.3.3 Fonctions numériques

6.3.3.1 Opérations arithmétiques

Les opérateurs arithmétiques usuels sont disponibles. Notez que dans le cas de `-', `+', and `*', le résultat est calculé avec en BIGINT avec une précision de 64 bits si les deux arguments sont des entiers ! Si l'un des arguments est un entier non signé, et que l'autre argument est aussi un entier, le résultat sera un entier non signé. See section 6.3.5 Fonctions de transtypage.

+
Addition : 
mysql> SELECT 3+5;
        -> 8
-
Soustraction : 
mysql> SELECT 3-5;
        -> -2
*
Multiplication : 
mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> 0
Le résultat du dernier calcul est incorrect car le résultat de la multiplication des deux entiers à dépassé la capacité de calcul de BIGINT (64 bits).
/
Division : 
mysql> SELECT 3/5;
        -> 0.60
La division par zéro produit un résultat NULL : 
mysql> SELECT 102/(1-1);
        -> NULL
Une division sera calculée en BIGINT seulement si elle est effectuée dans un contexte où le résultat est transformé en entier.

6.3.3.2 Fonctions mathématiques

Toutes les fonctions mathématiques retournent NULL en cas d'erreur.

-
Le signe moins. Change le signe de l'argument : 
mysql> SELECT - 2;
        -> -2
Notez que si cet opérateur est utilisé avec une valeur de type BIGINT, la valeur retournée sera de type BIGINT! Cela signifie que vous devez éviter d'utiliser l'opérateur - sur les valeurs de type entier inférieures à -2^63!
ABS(X)
Retourne la valeur absolue de X : 
mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32
Cette fonction est utilisable avec les valeurs issues des champs BIGINT.
SIGN(X)
Retourne le signe de l'argument sous la forme -1, 0, ou 1, selon que X est négatif, zéro, ou positif : 
mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1
MOD(N,M)
%
Modulo (équivalent de l'opérateur % dans le langage C). Retourne le reste de la division de N par M : 
mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
Cette fonction ne pose pas de problèmes avec les BIGINT.
FLOOR(X)
Retourne la valeur entière inférieure de X : 
mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2
Notez que la valeur retournée sera de type BIGINT!
CEILING(X)
Retourne la valeur entière supérieure de X : 
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1
Notez que la valeur retournée sera de type BIGINT!
ROUND(X)
Retourne l'entier le plus proche de X : 
mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
Notez que le comportement de l'opérateur ROUND(), lorsque l'argument est exactement entre deux entiers, dépend de la librairie C active. Certaines arrondissent toujours à l'entier pair le plus proche, toujours vers le haut, toujours vers le bas, ou toujours vers zéro. Si vous avez besoin d'un certain type d'arrondissement, vous devez utiliser une fonction bien définie comme TRUNCATE() ou FLOOR().
ROUND(X,D)
Retourne l'argument X, arrondi à un nombre à D décimales. Si D vaut 0, le résultat n'aura ni de partie décimale, ni de séparateur de décimal : 
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
EXP(X)
Retourne la valeur de e (la base des logarithmes naturels) élevé à la puissance X : 
mysql> SELECT EXP(2);
        -> 7.389056
mysql> SELECT EXP(-2);
        -> 0.135335
LN(X)
Retourne le logarithme naturel de X (népérien) : 
mysql> SELECT LN(2);
        -> 0.693147
mysql> SELECT LN(-2);
        -> NULL
Cette fonction a été ajoutée à MySQL à partir de la version 4.0.3. C'est un synonyme de la fonction LOG(X).
LOG(X)
LOG(B,X)
Appelée avec un seul paramètre, cette fonction retourne le logarithme naturel (népérien) de X : 
mysql> SELECT LOG(2);
        -> 0.693147
mysql> SELECT LOG(-2);
        -> NULL
Appelée avec deux paramètres, cette fonction retourne le logarithme naturel de X pour une base B arbitraire : 
mysql> SELECT LOG(2,65536);
        -> 16.000000
mysql> SELECT LOG(1,100);
        -> NULL
Cette base arbitraire a été ajoutée à MySQL à partir de la version 4.0.3. LOG(B,X) est l'équivalent de LOG(X)/LOG(B).
LOG2(X)
Retourne le logarithme en base 2 de X : 
mysql> SELECT LOG2(65536);
        -> 16.000000
mysql> SELECT LOG2(-100);
        -> NULL
LOG2() est utile pour trouver combien de bits sont nécessaires pour stocker un nombre. Cette fonction a été ajoutée à MySQL à partir de la version 4.0.3. Dans les versions antérieures, vous pouvez utiliser LOG(X)/LOG(2) en remplacement.
LOG10(X)
Retourne le logarithme en base 10 de X : 
mysql> SELECT LOG10(2);
        -> 0.301030
mysql> SELECT LOG10(100);
        -> 2.000000
mysql> SELECT LOG10(-100);
        -> NULL
POW(X,Y)
POWER(X,Y)
Retourne la valeur de X élevée à la puissance Y : 
mysql> SELECT POW(2,2);
        -> 4.000000
mysql> SELECT POW(2,-2);
        -> 0.250000
SQRT(X)
Retourne la racine carrée de X : 
mysql> SELECT SQRT(4);
        -> 2.000000
mysql> SELECT SQRT(20);
        -> 4.472136
PI()
Retourne la valeur de PI. Par défaut, 5 décimales sont retournées, mais MySQL utilise la double précision pour PI. 
mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116
COS(X)
Retourne le cosinus de X, où X est donné en radians : 
mysql> SELECT COS(PI());
        -> -1.000000
SIN(X)
Retourne le sinus de X, où X est donné en radians : 
mysql> SELECT SIN(PI());
        -> 0.000000
TAN(X)
Retourne la tangente de X, où X est donné en radians : 
mysql> SELECT TAN(PI()+1);
        -> 1.557408
ACOS(X)
Retourne l'arccosinus de X, c'est à dire, la valeur de l'angle dont X est la cosinus. Retourne NULL si X n'est pas dans l'intervalle -1 - 1 : 
mysql> SELECT ACOS(1);
        -> 0.000000
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.570796
ASIN(X)
Retourne l'arcsinus de X, c'est à dire, la valeur de l'angle dont le sinus est X. Retourne NULL si X n'est pas dans l'intervalle -1 - 1 : 
mysql> SELECT ASIN(0.2);
        -> 0.201358
mysql> SELECT ASIN('foo');
        -> 0.000000
ATAN(X)
Retourne l'arctangente de X, c'est à dire, la valeur de l'angle dont la tangente est X : 
mysql> SELECT ATAN(2);
        -> 1.107149
mysql> SELECT ATAN(-2);
        -> -1.107149
ATAN(Y,X)
ATAN2(Y,X)
Retourne l'arctangente des variables X et Y. Cela revient à calculer l'arctangennte de Y / X, excepté que les signes des deux arguments servent à déterminer le quadrant du résultat : 
mysql> SELECT ATAN(-2,2);
        -> -0.785398
mysql> SELECT ATAN2(PI(),0);
        -> 1.570796
COT(X)
Retourne la cotangente de X : 
mysql> SELECT COT(12);
        -> -1.57267341
mysql> SELECT COT(0);
        -> NULL
RAND()
RAND(N)
Retourne un nombre aléatoire à virgule flottante comprit dans l'intervalle 0 - 1.0. Si l'argument entier N est spécifié, il est utilisé comme initialisation du générateur de nombres aléatoires : 
mysql> SELECT RAND();
        -> 0.9233482386203
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND();
        -> 0.63553050033332
mysql> SELECT RAND();
        -> 0.70100469486881
Vous ne pouvez pas utiliser une colonne de valeur RAND() dans une clause ORDER BY, parce que ORDER BY va évaluer la colonne plusieurs fois. Dans la version 3.23 de MySQL, vous pouvez, tout de même, faire ceci : SELECT * FROM nom_de_table ORDER BY RAND() Cette syntaxe est très pratique pour faire une sélection aléatoire de lignes : SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000. Notez que la fonction RAND() dans une clause WHERE sera réévaluée à chaque fois que WHERE sera exécuté. RAND() n'est pas un générateur de nombres aléatoires parfait, mais reste une manière rapide de produire des nombres aléatoires ad hoc portables selon les différentes plate-formes pour une même version de MySQL.
LEAST(X,Y,...)
Avec deux arguments ou plus, cette fonction retourne la plus petite des valeurs des différents arguments. Les arguments sont comparés en respectant les règles suivantes : 
mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST("B","A","C");
        -> "A"
Dans les versions de MySQL 3.22.5 et antérieures, vous pouvez utiliser MIN() à la place de LEAST.
GREATEST(X,Y,...)
Retourne le plus grand des arguments. Les arguments sont comparés en suivant les mêmes règles que la fonction LEAST : 
mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST("B","A","C");
        -> "C"
Dans les versions de MySQL 3.22.5 et antérieures, vous pouvez utiliser MAX() à la place de GREATEST.
DEGREES(X)
Retourne l'argument X, convertit de radians en degrés : 
mysql> SELECT DEGREES(PI());
        -> 180.000000
RADIANS(X)
Retourne l'argument X, convertit de degrés en radians : 
mysql> SELECT RADIANS(90);
        -> 1.570796
TRUNCATE(X,D)
Retourne l'argument X, tronqué à D décimales. Si D vaut 0, le résultat n'aura ni séparateur décimal, ni partie décimale : 
mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9
A partir de MySQL 3.23.51 tous les nombres sont arrondis vers zéro. Si D est négatif, la partie entière du nombre est mise à zéro : 
mysql> SELECT TRUNCATE(122,-2);
       -> 100
Notez que les nombres décimaux ne sont pas stockés exactement comme les nombres entiers , mais comme des valeurs doubles. Vous pouvez être dupés par le résultat suivant : 
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1027
Ce résultat est normal car 10.28 est actuellement stocké comme cela 10.2799999999999999.

6.3.4 Fonctions de dates et d'heures

Voir section 6.2.2 Les types date et heure pour une description détaillée des intervalles de validité de chaque type, ainsi que les formats valides de spécifications des dates et heures.

Voici un exemple d'utilisation des fonctions de date. La requête suivante sélectionne toutes les lignes dont la colonne date_col représente une date de moins de 30 jours : 

mysql> SELECT quelquechose FROM nom_de_table
           WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;
DAYOFWEEK(date)
Retourne l'index du jour de la semaine : pour date (1 = Dimanche, 2 = Lundi, ... 7 = Samedi). Ces index correspondent au standart ODBC : 
mysql> SELECT DAYOFWEEK('1998-02-03');
        -> 3
WEEKDAY(date)
Retourne l'index du jour de la semaine, avec la convertion suivante : date (0 = Lundi, 1 = Mardi, ... 6 = Dimanche) : 
mysql> SELECT WEEKDAY('1997-10-04 22:23:00');
        -> 5
mysql> SELECT WEEKDAY('1997-11-05');
        -> 2
DAYOFMONTH(date)
Retourne le jour de la date date, dans un intervalle de 1 à 31 : 
mysql> SELECT DAYOFMONTH('1998-02-03');
        -> 3
DAYOFYEAR(date)
Retourne le jour de la date date, dans un intervalle de 1 à 366 : 
mysql> SELECT DAYOFYEAR('1998-02-03');
        -> 34
MONTH(date)
Retourne le numéro du mois de la date date, dans un intervalle de 1 à 12 : 
mysql> SELECT MONTH('1998-02-03');
        -> 2
DAYNAME(date)
Retourne le nom du jour de la semaine, en anglais, de la date date : 
mysql> SELECT DAYNAME("1998-02-05");
        -> 'Thursday'
MONTHNAME(date)
Retourne le nom du mois de la date date : 
mysql> SELECT MONTHNAME("1998-02-05");
        -> 'February'
QUARTER(date)
Retourne le numéro du trimestre de la date date, dans un intervalle de 1 à 4 : 
mysql> SELECT QUARTER('98-04-01');
        -> 2
WEEK(date)
WEEK(date,first)
Avec un seul argument, retourne le numéro de la semaine dans l'année de la date date, dans un intervalle de 0 à 53 (oui, il peut y avoir un début de semaine numéro 53), en considérant que Dimanche est le premier jour de la semaine. Avec deux arguments, la fonction WEEK() vous permet de spécifier si les semaines commencent le Dimanche ou le Lundi et la valeur retournée sera dans l'intervalle 0-53 ou bien 1-52. Voici un tableau explicatif sur le fonctionnement du second argument :
Value Meaning
0 La semaine comme le Dimanche et retourne une valeur dans l'intervalle 0-53
1 La semaine comme le Lundi et retourne une valeur dans l'intervalle 0-53
2 La semaine comme le Dimanche et retourne une valeur dans l'intervalle 1-53
3 La semaine comme le Lundi et retourne une valeur dans l'intervalle 1-53 (ISO 8601) 
mysql> SELECT WEEK('1998-02-20');
        -> 7
mysql> SELECT WEEK('1998-02-20',0);
        -> 7
mysql> SELECT WEEK('1998-02-20',1);
        -> 8
mysql> SELECT WEEK('1998-12-31',1);
        -> 53
Note : Dans la version 4.0 de MySQL, WEEK(#,0) a été modifié pour être utilisé avec le calendrier USA. Notez que si une semaine est la dernière semaine de l'année précédente, MySQL retournera 0 si vous n'utilisez pas 2 ou 3 comme argument optionnel : 
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0
mysql> SELECT WEEK('2000-01-01',2);
        -> 52
On peut prétendre que la fonction WEEK() de MySQL retourne 52, vu que la date donnée est en fait, la 52 ème semaine de l'année 1999. Nous avons décidé de retourner 0 au lieu de vouloir que la fonction retourne 'le numéro de la semaine dans une année donnée'. Ceci rend l'utilisation de la fonction WEEK() fiable une fois combinée avec d'autres fonctions qui extraient une partie de date à partir d'une date : 
mysql> SELECT YEARWEEK('2000-01-01');
        -> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
        -> 52
YEAR(date)
Retourne l'année de la date date, dans un intervalle de 1000 à 9999: 
mysql> SELECT YEAR('98-02-03');
        -> 1998
YEARWEEK(date)
YEARWEEK(date,first)
Retourne l'année et la semaine pour une date. Le second argument fonctionne exactement comme le second argument de la fonction WEEK(). Notez que l'année peut être différente de l'année de la date pouur la première et la dernière semaine de l'année : 
mysql> SELECT YEARWEEK('1987-01-01');
        -> 198653
Notez que le numéro de la semaine est différent de la valeur retournée par la fonction WEEK() (0) pour un argument optionnel 0 ou 1, alors que la fonction WEEK() retourne une semaine dans une année donnée.
HOUR(time)
Retourne le nombre d'heures pour l'heure time, dans un intervalle de 0 à 23 : 
mysql> SELECT HOUR('10:05:03');
        -> 10
MINUTE(time)
Retourne le nombre de minutes pour l'heure for time, dans un intervalle de 0 à 59 : 
mysql> SELECT MINUTE('98-02-03 10:05:03');
        -> 5
SECOND(time)
Retourne le nombre de secondes pour l'heure time, dans un intervalle de 0 à 59 : 
mysql> SELECT SECOND('10:05:03');
        -> 3
PERIOD_ADD(P,N)
Ajoute N mois à la période P (au format YYMM ou YYYYMM). Retourne une valeur dans le format YYYYMM. Notez que l'argument P n'est pas de type date : 
mysql> SELECT PERIOD_ADD(9801,2);
        -> 199803
PERIOD_DIFF(P1,P2)
Retourne le nombre de mois entre les périodes P1 et P2. P1 et P2 doivent être au format YYMM ou YYYYMM. Notez que les arguments P1 et P2 ne sont pas de type date : 
mysql> SELECT PERIOD_DIFF(9802,199703);
        -> 11
DATE_ADD(date,INTERVAL expr type)
DATE_SUB(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type)
Ces fonctions effectuent des calculs sur les dates. Elles sont nouvelles depuis la version 3.22 de MySQL. ADDDATE() et SUBDATE() sont synonymes de DATE_ADD() and DATE_SUB(). Dans la version 3.23 de MySQL, vous pouvez utiliser les opérateurs + et - en lieu et place des fonctions DATE_ADD() et DATE_SUB() si l'expression de droite est une colonne de type date ou datetime. (Voir l'exemple suivant) date est une valeur de type DATETIME ou DATE, indiquant la date du début. expr est une expression spécifiant la valeur de l'intervalle à ajouter ou à soustraire depuis le début de la date. expr est une chaîne ; elle peut commencer par le signe `-' pour les intervalles négatifs. type est un mot clé indiquant comment l'expression doit être interprétée. La fonction EXTRACT(type FROM date) retourne le 'type' d'intervalle pour la date. La table suivante illustre la relation entre les arguments type et expr :
type value Expected expr format
SECOND SECONDES
MINUTE MINUTES
HOUR HEURES
DAY JOURS
MONTH MOIS
YEAR ANNES
MINUTE_SECOND "MINUTES:SECONDES"
HOUR_MINUTE "HEURES:MINUTES"
DAY_HOUR "JOURS HEURES"
YEAR_MONTH "ANNEES-MOIS"
HOUR_SECOND "HEURES:MINUTES:SECONDES"
DAY_MINUTE "JOURS HEURES:MINUTES"
DAY_SECOND "JOURS HEURES:MINUTES:SECONDES"
MySQL autorise n'importe quel signe de ponctuation comme délimiteur dans expr. Ceux qui sont présentés dans la table ci-dessus ne sont que des suggestions. Si l'argument date est une DATE et que votre calcul implique seulement les années (YEAR), les mois (MONTH), et les jours (DAY) (c'est-à-dire, pas d'heures), le résultat est une DATE. Sinon, le résultat est une valeur de type DATETIME : 
mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
        -> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
        -> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
       -> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL 1 SECOND);
        -> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL 1 DAY);
        -> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",
    ->                 INTERVAL "1:1" MINUTE_SECOND);
        -> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",
    ->                 INTERVAL "1 1:1:1" DAY_SECOND);
        -> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",
    ->                 INTERVAL "-1 10" DAY_HOUR);
        -> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
        -> 1997-12-02
Si vous spécifiez un intervalle de valeur qui est trop court (c'est-à-dire qu'il n'inclut pas toutes les valeurs auxquelles ont peu s'attendre pour le type type), MySQL suppose que vous avez ignoré ces valeurs. Par exemple, si vous spécifiez un type de DAY_SECOND , la valeur expr doit avoir des jours, heures, minutes, secondes. Si vous spécifiez une valeur telle que "1:10", MySQL supposera que les jours et les heures sont ignorés, et que la valeur fournit représente uniquement les minutes et les secondes. En d'autres termes, "1:10" DAY_SECOND s'interprète comme "1:10" MINUTE_SECOND. C'est comparable à la manière dont MySQL interprète les valeurs TIME qui représente une durée plutôt qu'une heure du jour. Notez que si vous ajoutez ou soustrayez à une date une valeur qui contient des heures, le résultat final sera automatiquement converti en DATETIME : 
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 DAY);
       -> 1999-01-02
mysql> SELECT DATE_ADD("1999-01-01", INTERVAL 1 HOUR);
       -> 1999-01-01 01:00:00
Si vous utilisez des dates incorrectes, le résultat sera NULL. Si vous ajoutez des MONTH, YEAR_MONTH, ou YEAR, et que le résultat dépasse le nombre de jour dansle mois, ce nombre de jour sera ramené au maximun acceptable : 
mysql> SELECT DATE_ADD('1998-01-30', INTERVAL 1 MONTH);
        -> 1998-02-28
Notez que dans l'exemple précédent, le mot INTERVAL et le type type ne sont pas sensible à la casse.
EXTRACT(type FROM date)
La fonction EXTRACT() utilise les mêmes types d'intervalles que la fonction DATE_ADD() ou la fonction DATE_SUB(), mais extrait des parties de date plutôt que des opérations de date. 
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
       -> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
       -> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
       -> 20102
TO_DAYS(date)
Retourne le nombre de jours depuis la date 0 jusqu'à la date date : 
mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('1997-10-07');
        -> 729669
TO_DAYS() n'est pas fait pour travailler avec des dates qui précèdent l'avènement du calendrier Grégorien (1582), car elle ne prend pas en compte les jours perdus lors du changement de calendrier.
FROM_DAYS(N)
Retourne la date correspondant au nombre de jours (N) depuis la date 0 : 
mysql> SELECT FROM_DAYS(729669);
        -> '1997-10-07'
FROM_DAYS() n'est pas fait pour travailler avec des dates qui précèdent l'avènement du calendrier Grégorien (1582), car elle ne prend pas en compte les jours perdus lors du changement de calendrier.
DATE_FORMAT(date,format)
Formate la date date avec le format format. Les spécificateurs suivants peuvent être utilisé dans la chaîne format :
Specifier Description
%M Nom du mois (January..December)
%W Nom du jour de la semaine (Sunday..Saturday)
%D Jour du mois, avec un suffix anglais (1st, 2nd, 3rd, etc.)
%Y Année, au format numérique, sur 4 chiffres
%y Année, au format numérique, sur 2 chiffres
%X Année, pour les semaines qui commencent le Dimanche, au format numérique, sur 4 chiffres, utilisé avec '%V'
%x Année, pour les semaines qui commencent le Lundi, au format numérique, sur 4 chiffres, utilisé avec '%v'
%a Nom du jour de la semaine, en abrégé et en anglais (Sun..Sat)
%d Jour du mois, au format numerique (00..31)
%e Jour du mois, au format numerique (0..31)
%m Mois, au format numerique (01..12)
%c Mois, au format numérique (1..12)
%b Nom du mois, en abrégé et en anglais (Jan..Dec)
%j Jour de l'année (001..366)
%H Heure (00..23)
%k Heure (0..23)
%h Heure (01..12)
%I Heure (01..12)
%l Heure (1..12)
%i Minutes, au format numerique (00..59)
%r Heures, au format 12-heures (hh:mm:ss [AP]M)
%T Heures, au format 24-heures (hh:mm:ss)
%S Secondes (00..59)
%s Secondes (00..59)
%p AM ou PM
%w Numéro du jour de la semaine (0=Sunday..6=Saturday)
%U Numéro de la semaine (00..53), où Dimanche est le premier jour de la semaine
%u Numéro de la semaine (00..53), où Lundi est le premier jour de la semaine
%V Numéro de la semaine (01..53), où Dimanche est le premier jour de la semaine, utilisé avec '%X'
%v Numéro de la semaine (01..53), où Lundi est le premier jour de la semaine, utilisé avec '%x'
%% Un signe pourcentage littéral `%'.
Tous les autres caractères sont simplement copiés dans le résultat sans interprétation: 
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
        -> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%D %y %a %d %m %b %j');
        -> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
                          '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
A partir de MySQL version 3.23, le caractère `%' est nécessaire avant tous les caractères de spécification de format. Dans les anciennes versions, il était optionnel.
TIME_FORMAT(time,format)
Cette fonction est utilisée exactement comme la fonction DATE_FORMAT() ci-dessus, mais la chaîne format ne doit utiliser que des spécificateurs d'heures, qui gèrent les heures, minutes et secondes. Les autres spécificateurs génèreront la valeur NULL ou 0.
CURDATE()
CURRENT_DATE
Retourne la date courante au format 'YYYY-MM-DD' ou YYYYMMDD, suivant le contexte numérique ou chaîne : 
mysql> SELECT CURDATE();
        -> '1997-12-15'
mysql> SELECT CURDATE() + 0;
        -> 19971215
CURTIME()
CURRENT_TIME
Retourne l'heure courante au format 'HH:MM:SS' ou HHMMSS, suivant le contexte numérique ou chaîne : 
mysql> SELECT CURTIME();
        -> '23:50:26'
mysql> SELECT CURTIME() + 0;
        -> 235026
NOW()
SYSDATE()
CURRENT_TIMESTAMP
Retourne la date courante au format 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS, suivant le contexte numérique ou chaîne : 
mysql> SELECT NOW();
        -> '1997-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 19971215235026
Notez que NOW() est évalué une seule fois par requête, au début de l'exécution de celle-ci. Cela signifie que si il y a de multiples références à NOW() dans une requête, la même date sera utilisé.
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
Lorsqu'elle est appelé sans argument, cette fonction retourne un timestamp Unix (nombre de secondes depuis '1970-01-01 00:00:00' GMT). Si UNIX_TIMESTAMP() est appelé avec un argument date , elle retourne le timestamp correspondant à cette date. date peut être une chaîne de type DATE, DATETIME, TIMESTAMP, ou un nombre au format YYMMDD ou YYYYMMDD, en horaire local : 
mysql> SELECT UNIX_TIMESTAMP();
        -> 882226357
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
        -> 875996580
Lorsque UNIX_TIMESTAMP est utilisé sur une colonne de type TIMESTAMP, la fonction reçoit directement la valeur, sans conversion explicite. Si vous donnez à UNIX_TIMESTAMP() une date hors de son intervalle de validité, elle retourne 0. Si vous voulez soustraire une colonne de type UNIX_TIMESTAMP(), vous devez sûrement vouloir un résultat de type entier signé. See section 6.3.5 Fonctions de transtypage.
FROM_UNIXTIME(unix_timestamp)
Retourne une représentation de l'argument unix_timestamp sous la forme 'YYYY-MM-DD HH:MM:SS' ou YYYYMMDDHHMMSS, suivant si la fonction est utilisé dans un contexte numérique ou de chaîne. 
mysql> SELECT FROM_UNIXTIME(875996580);
        -> '1997-10-04 22:23:00'
mysql> SELECT FROM_UNIXTIME(875996580) + 0;
        -> 19971004222300
SEC_TO_TIME(seconds)
Retourne l'argument seconds, convertit en heures, minutes et secondes au format 'HH:MM:SS' ou HHMMSS, suivant le contexte numérique ou chaîne : 
mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938
TIME_TO_SEC(time)
Retourne l'argument time, convertit en secondes : 
mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

6.3.5 Fonctions de transtypage

La syntaxe de la fonction CAST est : 

CAST(expression AS type)

ou

CONVERT(expression,type)

Où type est l'un des suivants :

CAST() est la syntaxe ANSI SQL99 et CONVERT() est la syntaxe ODBC.

La fonction de transtypage est très pratique lorsque vous voulez créer une colonne avec un type spécifique dans une requête CREATE ... SELECT : 

CREATE TABLE nouvelle_table SELECT CAST('2000-01-01' AS DATE);

CAST(chaîne AS BINARY est l'équivalent de BINARY chaîne.

Pour transformer une chaîne de caractères en une valeur numérique, vous ne devez rien faire de particulier ; juste utiliser la valeur de la chaîne en lieu et place de la valeur numérique : 

mysql> SELECT 1+'1';
       -> 2

Si vous utilisez un nombre dans un contexte de chaîne, le nombre sera automatiquement converti en une chaîne binaire.

mysql> SELECT concat("salut toi ",2); -> "salut toi 2"

Si vous utilisez un nombre dans un contexte de cha”ne, le nombre sera automatiquement converti en cha”ne binaire (BINARY). 

mysql> SELECT CONCAT("Salut vous ",2);
       ->  "Salut vous 2"

MySQL supporte l'arithmétique avec les valeurs 64 bits signées et non signées. Si vous utilisez une opération numérique (comme le signe +) et qu'un des opérandes est de type unsigned integer, alors, le résultat sera une valeur non signé. Vous pouvez corriger cela en utilisant les opérateurs de transtypages SIGNED et UNSIGNED, qui transformeront l'opération respectivement en un entier signé sur 64 bits et un entier non signé sur 64 bits. 

mysql> SELECT CAST(1-2 AS UNSIGNED)
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
        -> -1

Notez que si l'une ou l'autre opération est une valeur à virgule flottante (Dans ce contexte, DECIMAL() est considéré comme une valeur à virgule flottante) le résultat devrait être une valeur à virgule flottante et ne sera pas affecté par la règle ci-dessus. 

mysql> SELECT CAST(1 AS UNSIGNED) -2.0
        -> -1.0

Si vous utilisez une chaîne dans une opération arithmétique, elle sera converti en un nombre à virgule flottante.

Les fonctions CAST() et CONVERT() ont été ajoutées dans la version 4.0.2 de MySQL.

Le rendu des valeurs non signées a été modifié dans la version 4.0 de MySQL pour pouvoir supporter correctement les valeurs de type BIGINT. Si vous voulez utiliser du code fonctionnant dans la version 4.0 et la version 3.23 de MySQL (dans ce cas, vous ne pouvez probablement pas utiliser les fonctions de transtypage), vous pouvez utiliser l'astuce suivante pour avoir un résultat signé lorsque vous soustrayez deux colonnes d'entier non signé : 

SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);

L'idée est que les colonnes sont convertis en un point mobile avant de faire la soustraction.

Si vous rencontrez un problème avec les colonnes UNSIGNED dans vos anciennes applications MySQL lorsque vous effectuez le port sous la version 4.0 de MySQL , vous pouvez utiliser l'option --sql-mode=NO_UNSIGNED_SUBTRACTION lorsque vous lancez mysqld. Notez cependant qu'aussi longtemps que vous employez ceci, vous ne serez pas capable d'utiliser efficacement les colonnes de type UNSIGNED BIGINT.

6.3.6 Autres fonctions

6.3.6.1 Fonctions sur les bits

MySQL utilise l'arithmétique des BIGINT (64-bits) pour les opérations sur les bits. Ces opérateurs travaillent donc sur 64 bits.

|
OU bit-à-bit (OR) 
mysql> SELECT 29 | 15;
        -> 31
Le résultat est un entier de 64 bits non signé.
&
ET bit-à-bit (AND) 
mysql> SELECT 29 & 15;
        -> 13
Le résultat est un entier de 64 bits non signé.
^
XOR bit-à-bit 
mysql> SELECT 1 ^ 1;
        -> 0
mysql> SELECT 1 ^ 0;
        -> 1
mysql> SELECT 11 ^ 3;
        -> 8
Le résultat est un entier de 64 bits non signé.
<<
Décale les bits de l'entier (BIGINT) sur la gauche : 
mysql> SELECT 1 << 2;
        -> 4
Le résultat est un entier de 64 bits non signé.
>>
Décale les bits de l'entier (BIGINT) sur la droite : 
mysql> SELECT 4 >> 2;
        -> 1
Le résultat est un entier de 64 bits non signé.
~
Inverse tous les bits : 
mysql> SELECT 5 & ~1;
        -> 4
Le résultat est un entier de 64 bits non signé.
BIT_COUNT(N)
Retourne le nombre de bits non nuls de l'argument N : 
mysql> SELECT BIT_COUNT(29);
        -> 4

6.3.6.2 Fonctions diverses

DATABASE()
Retourne le nom de la base de données courante : 
mysql> SELECT DATABASE();
        -> 'test'
Si aucune base de données n'a été sélectionnée, DATABASE() retourne une chaîne vide.
USER()
SYSTEM_USER()
SESSION_USER()
Retourne l'utilisateur MySQL courant : 
mysql> SELECT USER();
        -> 'davida@localhost'
A partir de la version 3.22.11 de MySQL, cette fonction inclut le nom de l'hôte du client tout comme le nom de l'utilisateur. Vous pouvez extraire le nom de l'utilisateur comme ceci (fonctionne également si le nom de l'hôte est présent) : 
mysql> SELECT SUBSTRING_INDEX(USER(),"@",1);
        -> 'davida'
PASSWORD(str)
Calcule un mot de passe chiffré à partir de la chaîne str. C'est cette fonction qui est utilisé pour crypter les mots de passes MySQL pour être stockés dans une colonne de type Password de la table user : 
mysql> SELECT PASSWORD('badpwd');
        -> '7f84554057dd964b'
Le chiffrage par PASSWORD() n'est pas réversible. PASSWORD() n'est pas un chiffrage comparable à la fonction Unix de chiffrage. Si votre mot de passe Unix et votre mot de passe MySQL sont identiques, il n'est ABSOLUMENT pas certain que Unix et MySQL le chiffreront de la même façon. Voir ENCRYPT().
ENCRYPT(str[,salt])
Chiffre la chaîne str en utilisant la fonction crypt(). L'argument salt doit être une chaîne de deux caractères. (A partir de la version 3.22.16, l'argument salt peut être plus long que deux caractères.) : 
mysql> SELECT ENCRYPT("hello");
        -> 'VxuFAJXVARROc'
Si la fonction crypt() n'est pas disponible sur votre système, la fonction ENCRYPT() retournera toujours NULL. La fonction ENCRYPT() conserve uniquement les 8 premiers caractères de la chaîne str, au moins, sur certains système. Le comportement exact est directement déterminé par la fonction système crypt() sous-jacente.
ENCODE(str,pass_str)
Chiffre la chaîne str en utilisant la clé pass_str. Pour déchiffrer le résultat, utilisez la fonction DECODE(). Le résultat est une chaîne binaire de la même longueur que string. Si vous voulez sauvegarder le résultat dans une colonne, utilisez une colonne de type BLOB.
DECODE(crypt_str,pass_str)
Déchiffre la chaîne chiffrée crypt_str en utilisant la clé pass_str. crypt_str doit être une chaîne qui a été renvoyée par la fonction ENCODE().
MD5(string)
Calcul la somme de vérification MD5 de la chaîne string. La valeur retournée est un entier hexadécimal de 32 caractères qui peut être utilisé, par exemple, comme clé de hachage : 
mysql> SELECT MD5("testing");
        -> 'ae2b1fca515949e5d54fb22b8ed95575'
C'est l'algorithme RSA ("RSA Data Security, Inc. MD5 Message-Digest Algorithm").
SHA1(string)
SHA(string)
Calcul la somme de vérification SHA1 160 bit de la chaîne string, comme décrit dans la RFC 3174 (Secure Hash Algorithm). La valeur retournée est un entier hexadécimal de 40 caractères, ou bien NULL dans le cas où l'argument vaut NULL. Une des possibilités d'utilisation de cette fonction est le hachage de clé. Vous pouvez aussi l'utilisé comme fonction de cryptographie sûre pour stocker les mots de passe. 
mysql> SELECT SHA1("abc");
        -> 'a9993e364706816aba3e25717850c26c9cd0d89d'
La fonction SHA1() a été ajoutée dans la version 4.0.2 de MySQL et peut être considérée comme une méthode de cryptographie plus sûre que la fonction MD5(). La fonction SHA() est un alias de la fonction SHA1().
AES_ENCRYPT(string,key_string)
AES_DECRYPT(string,key_string)
Ces fonctions permettent le chiffrement/déchiffrement de données utilisant l'algorithme AES (Advanced Encryption Standard), anciennement connu sous le nom de Rijndael. Une clé de 128 bits est utilisé pour le chiffrement, mais vous pouvez l'étendre à 256 bits en patchant la source. Nous avons choisi 128 bits parce que c'est plus rapide et assez sécurisé. Les arguments peuvent être de n'importe quelle taille. Si l'un des arguments est NULL, le résultat de cette fonction sera NULL. Vu que AES est un algorithme de niveau bloc, le capitonnage est utilisé pour chiffrer des chaînes de longueur inégales et donc, la longueur de la chaîne résultante peut être calculée comme ceci : 16*(trunc(string_length/16)+1). Si la fonction AES_DECRYPT() détecte des données invalides ou un capitonnage incorrect, elle retournera NULL. Il est également possible que la fonction AES_DECRYPT() retourne une valeur différente de NULL (valeur incohérente) si l'entrée de données ou la clé est invalide. Vous pouvez utiliser les fonctions AES pour stocker des données sous une forme chiffrée en modifiant vos requêtes: 
INSERT INTO t VALUES (1,AES_ENCRYPT("text","password"));
Vous pouvez obtenir encore plus de sécurité en évitant de transférer la clé pour chaque requête, en la stockant dans une variable sur le serveur au moment de la connexion : 
SELECT @password:="my password";
INSERT INTO t VALUES (1,AES_ENCRYPT("text",@password));
Les fonctions AES_ENCRYPT() et AES_DECRYPT() ont été ajoutées dans la version 4.0.2 de MySQL et peuvent être considérées comme étant les fonctions de cryptographie les plus sûres disponibles actuellement dans MySQL.
DES_ENCRYPT(string_to_encrypt [, (key_number | key_string) ] )
Chiffre la chaîne avec la clé donnée en utilisant l'algorithme DES. Notez que cette fonction fonctionne uniquement si vous avez configuré MySQL avec le support SSL. See section 4.3.9 Utilisation des connexions sécurisées. La clé de hachage utilisée est choisie en suivant les recommandations suivantes :
Argument Description
Un seul argument La première clé de des-key-file est utilisée.
Un numéro de clé Le numéro de la clé donnée (0-9) de des-key-file est utilisée.
Une chaîne La chaîne donnée key_string doit être utilisé pour chiffrer string_to_encrypt.
La chaîne retournée doit être une chaîne binaire où le premier caractère doit être CHAR(128 | key_number). Le nombre 128 a été ajouté pour reconnaître facilement une clé de hachage. Si vous utilisez une chaîne comme clé, key_number doit être 127. Si une erreur survient, la fonction retournera NULL. La longueur de la chaîne de résultat doit être : new_length= org_length + (8-(org_length % 8))+1. des-key-file a le format suivant : 
key_number des_key_string
key_number des_key_string
Chaque key_number doit être un nombre dans l'intervalle 0 à 9. Les lignes dans le fichier peuvent être dans n'importe quel ordre. des_key_string est la chaîne qui permettera le chiffrage du message. Entre le nombre et la clé, il doit y avoir au moins un espace. La première clé est la clé par défaut qui sera utilisé si vous ne spécifiez pas d'autres clés en arguments de la fonction DES_ENCRYPT(). Vous pouvez demander à MySQL de lire de nouvelles valeurs de clé dans le fichier de clés avec la commande FLUSH DES_KEY_FILE. Cela requière le privilège Reload_priv. Un des bénéfices d'avoir une liste de clés par défaut est que cela donne aux applications la possibilité de regarder l'existence de la valeur chiffrée de la colonne, sans pour autant donner la possibilité à l'utilisateur final de déchiffrer ces valeurs. 
mysql> SELECT customer_address FROM customer_table WHERE
       crypted_credit_card = DES_ENCRYPT("credit_card_number");
DES_DECRYPT(string_to_decrypt [, key_string])
Déchiffre une chaîne chiffrée à l'aide de la fonction DES_ENCRYPT(). Notez que cette fonction fonctionne uniquement si vous avez configuré MySQL avec le support SSL. See section 4.3.9 Utilisation des connexions sécurisées. Si l'argument key_string n'est pas donné, la fonction DES_DECRYPT() examine le premier bit de la chaîne chiffrée pour déterminer le numéro de clé DES utilisé pour chiffrer la chaîne originale, alors la clé est lu dans le fichier des-key-file pour déchiffrer le message. Pour pouvoir utiliser cela, l'utilisateur doit avoir le privilège SUPER. Si vous passé l'argument key_string à cette fonction, cette chaîne est utilisée comme clé pour déchiffrer le message. Si la chaîne string_to_decrypt ne semble pas être une chaîne chiffrée, MySQL retournera la chaîne string_to_decrypt. Si une erreur survient, cette fonction retourne NULL.
LAST_INSERT_ID([expr])
Retourne la dernière valeur générée automatiquement qui a été insérée dans une colonne de type AUTO_INCREMENT. See section 8.4.3.31 mysql_insert_id(). 
mysql> SELECT LAST_INSERT_ID();
        -> 195
Le dernier ID généré est conservé par le serveur pour chaque connexion. Un autre client ne le modifiera donc pas. Il ne sera pas modifié non plus si vous modifiez directement la valeur d'une colonne AUTO_INCREMENT avec une valeur simple (c'est à dire, une valeur qui n'est ni NULL, ni 0). Si vous insérez plusieurs lignes au même moment avec une requête insert, LAST_INSERT_ID() retourne la valeur de la première ligne insérée. La raison de cela est de rendre possible de reproduire facilement la même requête INSERT sur d'autres serveurs. Si expr est donnée en argument à la fonction LAST_INSERT_ID(), alors la valeur de l'argument sera retourné par la fonction et sera enregistré comme étant la prochaine valeur retournée par LAST_INSERT_ID(). Cela peut être utilisé pour simuler des séquences : Commencez par créer la table suivante : 
mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);
Alors cette table peut être utilisé pour générer des séquences de nombres, comme ceci : 
mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
Vous pouvez générer des séquences sans appeler la fonction LAST_INSERT_ID(), mais l'utilité d'utiliser cette fonction cette fois si est que la valeur ID est gérée par le serveur comme étant la dernière valeur générée automatiquement. (sécurité multi-utilisateur). Vous pouvez retrouver la nouvelle ID tout comme vous pouvez lire n'importe quelle valeur AUTO_INCREMENT dans MySQL. Par exemple, la fonction LAST_INSERT_ID() (sans argument) devrait retourner la nouvelle ID. La fonction C de l'API mysql_insert_id() peut être également utilisée pour trouver cette valeur. Notez que la fonction mysql_insert_id() est incrémentée uniquement après des requêtes INSERT et UPDATE, donc, vous ne pouvez pas utiliser la fonction C de l'API pour trouver la valeur de LAST_INSERT_ID(expr) après avoir exécuté d'autres types de requêtes, comme SELECT ou bien SET.
FORMAT(X,D)
Formate l'argument X en un format comme '#,###,###.##', arrondi à D décimales. Si D vaut 0, le résultat n'aura ni séparateur décimal, ni partie décimale : 
mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
VERSION()
Retourne une chaîne indiquant la version courante du serveur MySQL : 
mysql> SELECT VERSION();
        -> '3.23.13-log'
Notez que si votre version se termine par -log, cela signifie que le système d'historique est actif.
CONNECTION_ID()
Retourne l'identifiant de connexion courant (thread_id). Chaque connexion a son propre identifiant unique : 
mysql> SELECT CONNECTION_ID();
        -> 1
GET_LOCK(str,timeout)
Tente de poser un verrou nommé str, avec un délai d'expiration (timeout) exprimé en seconde. Retourne 1 si le verrou a été posé avec succès, 0 si il n'a pas pu être posé avant l'expiration du délai et NULL si une erreur est survenu (comme par exemple un manque de mémoire, ou la mort du thread lui-même, par mysqladmin kill). Un verrou sera levé lorsque vous exécuterez la commande RELEASE_LOCK(), GET_LOCK() ou si le thread se termine. Cette fonction peut être utilisée pour implémenter des verrous applicatifs ou pour simuler des verrous de lignes. Les requêtes concurrentes des autres clients de même nom seront bloquées ; les clients qui s'entendent sur un nom de verrou peuvent les utiliser pour effectuer des verrouillages coopératifs : 
mysql> SELECT GET_LOCK("lock1",10);
        -> 1
mysql> SELECT IS_FREE_LOCK("lock2");
        -> 1
mysql> SELECT GET_LOCK("lock2",10);
        -> 1
mysql> SELECT RELEASE_LOCK("lock2");
        -> 1
mysql> SELECT RELEASE_LOCK("lock1");
        -> NULL
Notez que le deuxième appel à RELEASE_LOCK() retourne NULL car le verrou "lock1" a été automatiquement libéré par le deuxième appel à GET_LOCK().
RELEASE_LOCK(str)
Libère le verrou nommé str, obtenu par la fonction GET_LOCK(). Retourne 1 si le verrou a bien été libéré, 0 si le verrou n'a pas été libéré par le thread (dans ce cas, le verrou reste posé) et NULL si le nom du verrou n'existe pas. Le verrou n'existe pas si il n'a pas été obtenu par la fonction GET_LOCK() ou si il a déjà été libéré. La commande DO est utilisable avec RELEASE_LOCK(). See section 6.4.10 Syntaxe de DO.
IS_FREE_LOCK(str)
Regarde si le verrou nommé str peut être librement utilisé (i.e., non verrouillé). Retourne 1 si le verrou est libre (personne ne l'utilise), 0 si le verrou est actuellement utilisé et NULL si une erreur survient (comme un argument incorrect).
BENCHMARK(count,expr)
La fonction BENCHMARK() exécute l'expression expr de manière répétée count fois. Elle permet de tester la vélocité de MySQL lors du traitement d'une requête. Le résultat est toujours 0. L'objectif de cette fonction ne se voit que du côté client, qui permet à ce dernier d'afficher la durée d'exécution de la requête : 
mysql> SELECT BENCHMARK(1000000,ENCODE("bonjour","au revoir"));
+--------------------------------------------------+
| BENCHMARK(1000000,ENCODE("bonjour","au revoir")) |
+--------------------------------------------------+
|                                                0 |
+--------------------------------------------------+
1 row in set (4.74 sec)
Le temps affiché est le temps côté client, et non pas les ressources processeurs consommées. il est conseillé d'utiliser BENCHMARK() plusieurs fois de suite pour interpréter un résultat, en dehors de charges ponctuelles sur le serveur.
INET_NTOA(expr)
Retourne l'adresse réseau (4 ou 8 octets), de l'expression numérique exp : 
mysql> SELECT INET_NTOA(3520061480);
       ->  "209.207.224.40"
INET_ATON(expr)
Retourne un entier qui représente l'expression numérique de l'adresse réseau. Les adresses peuvent être des entiers de 4 ou 8 octets. 
mysql> SELECT INET_ATON("209.207.224.40");
       ->  3520061480
Le nombre généré est toujours dans l'ordre des octets réseau ; par exemple, le nombre précédent est calculé comme ceci : 209*256^3 + 207*256^2 + 224*256 +40.
MASTER_POS_WAIT(log_name, log_pos)
Bloque le maître jusqu'à ce que l'esclave atteigne une position donnée dans le fichier d'historique principal, durant une replication. Si l'historique principal n'est pas initialisé, retourne NULL. Si l'esclave n'est pas démarré, le maître restera bloqué jusqu'à ce que l'esclave soit démarré et ai atteint la position demandée. Si l'esclave a déjà dépassé cette position, la fonction se termine immédiatement. La valeur retournée est le nombre d'évènements qui a du être traité pour atteindre la position demandée, ou NULL en cas d'erreur. Cette fonction est très utile pour contrôler la synchronisation maître-esclave, mais elle a été initialement écrite pour faciliter les tests de replications.
FOUND_ROWS()
Retourne le nombre de lignes que la dernière commande SELECT SQL_CALC_FOUND_ROWS ... a retourné, si aucune limite n'a été défini par LIMIT. 
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM nom_de_table
       WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();
Le second SELECT retourne un nombre indiquant combien de lignes le premier SELECT aurait retourné si il n'avait pas été écrit avec une clauseLIMIT. Notez que si vous utilisez SELECT SQL_CALC_FOUND_ROWS ..., MySQL calcule toutes les lignes dans la liste des résultats. Ainsi, c'est plus rapide si vous n'utilisez pas de clause LIMIT et que la liste des résultats n'a pas besoin d'être envoyée au client. SQL_CALC_FOUND_ROWS est utilisable à partir de la version 4.0.0 de MySQL.

6.3.7 Fonctions avec la clause GROUP BY

Si vous utilisez une fonction de groupement avec une commande qui n'utilise pas la clause GROUP BY, cela revient à regrouper toutes les lignes ensembles.

COUNT(expr)
Retourne le nombre de valeurs non-nulles (NULL) dans les lignes lues par la commande SELECT : 
mysql> SELECT etudiant.nom_etudiant,COUNT(*)
    ->        FROM etudiant,course
    ->        WHERE etudiant.id_etudiant=course.id_etudiant
    ->        GROUP BY nom_etudiant;
COUNT(*) est légèrement différent car il retourne le nombre de lignes lues, qu'elles contiennent ou pas la valeur NULL. COUNT(*) est optimisé pour retourner très rapidement le résultat si la commande SELECT n'opère que sur une table, qu'aucune autre valeur n'est calculée en même temps, et qu'il n'y a pas de clause WHERE. Par exemple : 
mysql> SELECT COUNT(*) FROM etudiant;
COUNT(DISTINCT expr,[expr...])
Retourne le nombre de lignes ayant des valeurs distinctes non-nulles (NULL) : 
mysql> SELECT COUNT(DISTINCT resultat) FROM etudiant;
Avec MySQL, vos pouvez connaître le nombre de combinaison d'expressions distinctes qui ne contiennent pas la valeur NULL en donnant une liste d'expressions. En ANSI SQL, vous devriez faire la concaténation de toutes les expressions de clause COUNT(DISTINCT ...).
AVG(expr)
Retourne la valeur moyenne de la colonne expr : 
mysql> SELECT nom_etudiant, AVG(test_score)
    ->        FROM etudiant
    ->        GROUP BY nom_etudiant;
MIN(expr)
MAX(expr)
Retourne le minimum ou le maximum de l'expression expr. MIN() et MAX() acceptent aussi les chaînes comme argument ; dans ce cas, elles retournent le minimum ou le maximum de la valeur de la chaîne. See section 5.4.3 Comment MySQL utilise les index. 
mysql> SELECT nom_etudiant, MIN(test_score), MAX(test_score)
    ->        FROM etudiant
    ->        GROUP BY nom_etudiant;
Dans MIN(), MAX() et les autres fonctions d'agrégat, MySQL compare les colonnes de type ENUM et SET par la valeur de leur chaîne au lieu de la position relative de la chaîne dans la liste. Cela sera rectifié.
SUM(expr)
Retourne la somme de l'expression expr. Notez que si aucune ligne n'est sélectionnée, la fonction retournera NULL !
VARIANCE(expr)
Retourne la variance standard de expr. C'est une extension à la norme ANSI SQL. (disponible à partir de la version 4.1).
STD(expr)
STDDEV(expr)
Retourne la déviation standard de l'expression expr. C'est une extension à la norme ANSI SQL. La fonction STDDEV() est fournie pour assurer la compatiblité avec Oracle.
BIT_OR(expr)
Retourne le OR bit-à-bit de l'expression expr. Les calculs sont effectués avec une précision de 64 bits (BIGINT).
BIT_AND(expr)
Retourne le AND bit-à-bit de l'expression expr. Les calculs sont effectués avec une précisioon de 64 bits (BIGINT).

MySQL a étendu l'utilisation de la clause GROUP BY. Vous pouvez utiliser des noms de colonnes ou des expressions qui n'apparaissent pas dans la clause GROUP BY. Elles prennent alors n'importe quelle valeur possible dans ce groupe. Vous pouvez les utiliser pour améliorer les performances, en évitant de trier et regrouper des éléments non critiques. Par exemple, vous n'avez pas besoin de faire de groupement par client.nom dans la requête suivante : 

mysql> SELECT order.custid,client.nom,MAX(payments)
    ->        FROM order,client
    ->        WHERE order.custid = client.custid
    ->        GROUP BY order.custid;

En ANSI SQL, vous devez ajouter client.nom à la clause GROUP BY. En MySQL, le nom est redondant si vous n'utilisez par le mode ANSI. N'utilisez surtout pas cette caractéristique si les colonnes que vous omettez n'ont pas de valeur unique au sein du groupe! Vous obtiendriez des résultas incohérents. Dans certains cas, vous pouvez utiliser MIN() et MAX() pour obtenir une valeur d'une colonne spécifique, même si elle n'est pas unique. La fonction suivante calcule la valeur de column dans la ligne contenant la plus petite valeur de la colonne sort : 

SUBSTR(MIN(CONCAT(RPAD(sort,6,' '),column)),7)

See section 3.5.4 La ligne contenant la plus grande valeur d'un certain champ par rapport à un groupe.

Notez que si vous utilisez la version 3.22 de MySQL (ou plus récent), ou si vous essayez d'utiliser le mode ANSI SQL, vous ne pouvez pas utiliser d'expressions dans les clauses GROUP BY et ORDER BY. Vous pouvez contourner ces limitations en utilisant un alias d'expression : 

mysql> SELECT id,FLOOR(value/100) AS val FROM nom_de_table
    ->        GROUP BY id,val ORDER BY val;

En MySQL version 3.23, vous pouvez faire : 

mysql> SELECT id,FLOOR(value/100) FROM nom_de_table ORDER BY RAND();

6.4 Manipulation de données : SELECT, INSERT, UPDATE, DELETE

6.4.1 Syntaxe de SELECT

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'nom_fichier' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], ...
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] lignes]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT est utilisé pour obtenir des enregistrements venant d'une ou plusieurs tables. select_expression indique les champs que vous voulez obtenir. SELECT peut aussi être utilisé pour obtenir des enregistrements calculés sans aucune référence à une table. Par exemple : 

mysql> SELECT 1 + 1;
         -> 2

Tous les mots-clés utilisés doivent être donnés exactement dans le même ordre que ci-dessus. Par exemple, une clause HAVING doit être placée après toute clause GROUP BY et avant toute clause ORDER BY.

6.4.1.1 Syntaxe de JOIN

MySQL supporte les syntaxes suivantes de JOIN pour une utilisation dans les SELECT : 

reference_table, reference_table
reference_table [CROSS] JOIN reference_table
reference_table INNER JOIN reference_table condition_jointure
reference_table STRAIGHT_JOIN reference_table
reference_table LEFT [OUTER] JOIN reference_table condition_jointure
reference_table LEFT [OUTER] JOIN reference_table
reference_table NATURAL [LEFT [OUTER]] JOIN reference_table
{ OJ reference_table LEFT OUTER JOIN reference_table ON expr_conditionnelle }
reference_table RIGHT [OUTER] JOIN reference_table condition_jointure
reference_table RIGHT [OUTER] JOIN reference_table
reference_table NATURAL [RIGHT [OUTER]] JOIN reference_table

où reference_table est définie de la manière suivante : 

nom_de_table [[AS] alias] [USE INDEX (liste_de_clefs)] [IGNORE INDEX (liste_de_clefs)]

et condition_jointure est définie comme suit : 

ON expr_conditionnelle |
USING (column_list)

Généralement, vous ne devez avoir aucune condition, dans la partie ON, qui soit utilisée pour spécifier les lignes que vous voulez obtenir en résultat. (il y a des exceptions à cette règle). Si vous voulez restreindre les lignes résultantes, vous devez le faire dans la clause WHERE.

Notez que dans les versions antérieures à la 3.23.17, INNER JOIN ne prenait pas en compte condition_jointure !

La dernière syntaxe de LEFT OUTER JOIN vue plus haut, n'existe que pour assurer la compatibilité avec ODBC :

Quelques exemples : 

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See section 5.2.6 Comment MySQL optimise LEFT JOIN et RIGHT JOIN.

6.4.1.2 Syntaxe de UNION

SELECT ...
UNION [ALL]
SELECT ...
  [UNION
   SELECT ...]

UNION est implémentée en MySQL 4.0.0.

UNION est utilisé pour combiner le résultat de plusieurs requêtes SELECT en un seul résultat.

Les colonnes listées dans la partie select_expression du SELECT doivent être du même type. Les noms de colonnes utilisés dans le premier SELECT seront utilisé comme nom de champs pour les résultats retournés.

Les commandes SELECT sont des select normaux, mais avec les restrictions suivantes :

Si vous n'utilisez pas le mot clef ALL pour l'UNION, toutes les lignes retournées seront uniques, comme si vous aviez fait un DISTINCT pour l'ensemble du résultat. Si vous spécifiez ALL, vous aurez alors tout les résultats retournés par toutes les commandes SELECT.

Si vous voulez utiliser un ORDER BY pour le résultat final de UNION, vous devez utiliser des parenthèses : 

(SELECT a FROM nom_de_table WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM nom_de_table WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

6.4.2 Syntaxe de HANDLER

HANDLER nom_de_table OPEN [ AS alias ]
HANDLER nom_de_table READ nom_index { = | >= | <= | < } (value1,value2,...)
    [ WHERE ... ] [LIMIT ... ]
HANDLER nom_de_table READ nom_index { FIRST | NEXT | PREV | LAST }
    [ WHERE ... ] [LIMIT ... ]
HANDLER nom_de_table READ { FIRST | NEXT }
    [ WHERE ... ] [LIMIT ... ]
HANDLER nom_de_table CLOSE

La commande HANDLER fournit un accès direct à l'interface de gestion de la table MyISAM.

La première forme de HANDLER ouvre la table, la rendant accessible via la requête HANDLER ... READ qui la suit. Cette objet table n'est pas partagé par les autres threads et ne sera refermé que si le thread appelle HANDLER nom_de_table CLOSE ou que celui ci se termine.

La seconde forme récupère une ligne (ou plus, à spécifier dans la clause LIMIT) où l'index spécifié remplit les conditions et où la clause WHERE est répondue. Si l'index se compose de plusieurs parties, (s'étend sur plusieurs colonnes) les valeurs sont spécifiées dans une liste séparée par des virgules, fournir des valeurs pour quelques premières colonnes est possible.

La troisième forme récupère une ligne (ou plus, à spécifier dans la clause LIMIT) de la table dans l'ordre de l'index, qui répond à la clause WHERE.

La quatrième forme (sans spécifications relatives à l'index) récupère une ligne (ou plus, à spécifier dans la clause LIMIT) de la table dans un ordre naturel des lignes (comme stocké dans le fichier de données) qui correspond à la condition WHERE. C'est plus rapide que HANDLER nom_de_table READ nom_index quand une lecture entière de la table est requise.

HANDLER ... CLOSE ferme une table qui a été ouverte avec HANDLER ... OPEN.

HANDLER est en quelque sorte une commande bas-niveau. Par exemple, elle ne propose pas de consistance. En clair, HANDLER ... OPEN ne se base PAS sur une image de la table, et ne verrouille PAS la table. Cela signifie qu'après l'exécution d'une requête HANDLER ... OPEN, les données de la table peuvent être modifiées (par ce ou un autre thread) et ces modifications peuvent apparaître partiellement dans les lectures de HANDLER ... NEXT ou HANDLER ... PREV.

Les raisons d'utiliser cette interface plutôt que les commandes MySQL usuelles sont :

6.4.3 Syntaxe de INSERT

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] nom_de_table [(nom_colonne,...)]
    VALUES ((expression | DEFAULT),...),(...),...
    ou  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] nom_de_table [(nom_colonne,...)]
    SELECT ...
    ou  INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] nom_de_table
:    SET nom_colonne=(expression | DEFAULT), ...

INSERT insère une nouvelle ligne dans une table existante. La syntaxe INSERT ... VALUES insère une ligne à partir de valeurs explicitement fournies. La syntaxe INSERT ... SELECT insère des valeurs à partir d'une autre table. La syntaxe INSERT ... VALUES avec plusieurs valeurs est supportées à partir de MySQL Version 3.22.5 ou supérieure. la syntaxe nom_colonne=expression est supportée à partir de la version 3.22.10 de MySQL.

nom_de_table est le nom de la table dans laquelle les valeurs seront insérées. La liste de noms de colonne ou la clause SET indiquent les colonnes qui seront affectées:

Si on fait une opération INSERT ... SELECT ou INSERT ... VALUES avec plusieurs listes de valeurs, la fonction mysql_info() de l'API C permet d'obtenir des informations relatives à la requête. Le format de la chaîne d'information est la suivante: 

Records: 100 Duplicates: 0 Warnings: 0

Duplicates indique le nombre de lignes qui n'ont pas peut être insérées pour cause de conflit avec une clé unique existante. Warnings indique le nombre de tentatives d'inserts de valeurs dans une colonne qui ont généré des problèmes. Les Warnings peuvent apparaître dans les conditions suivantes:

6.4.3.1 Syntaxe de INSERT ... SELECT

INSERT [LOW_PRIORITY] [IGNORE] [INTO] nom_de_la_table [(liste des colonnes)] SELECT ...

La requête INSERT ... SELECT permet de rapidement insérer dans une table un grand nombre de lignes d'une ou plusieurs autres tables. 

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

Les conditions suivantes s'appliquent à la requête INSERT ... SELECT:

Il est bien sûr possible d'utiliser REPLACE à la place de INSERT pour remplacer les anciennes lignes.

6.4.4 Syntaxe de INSERT DELAYED

INSERT DELAYED ...

L'option DELAYED de la commande INSERT est une option spécifique à MySQL très utile si vos clients ne peuvent pas attendre que INSERT se termine. C'est un problème fréquent quand on utilise MySQL pour des logs, mais aussi quand on utilise souvent des commandes SELECT ou UPDATE qui prennent beaucoup de temps. DELAYED a été ajouté à MySQL dans la version 3.22.15. C'est une extension de MySQL au ANSI SQL 92.

INSERT DELAYED fonctionne avec les tables ISAM et MyISAM. On peut noter que, les tables MyISAM supportant les SELECT et les INSERT concurrents, s'il n'y a pas de blocs libres au milieu du fichier de données il est vraiment conseillé d'utiliser INSERT DELAYED avec les tables MyISAM. See section 7.1 Tables MyISAM.

En utilisant INSERT DELAYED, le client reçoit immédiatement un aquitement, et la ligne sera insérée quand la table ne sera plus utilisée par un autre thread.

Un autre avantage de INSERT DELAYED est que les insertions des clients sont regroupés, et écrits d'un seul bloc. C'est beaucoup plus rapide que de faire des insertions séparés.

Actuellement, les lignes en attente sont uniquement stockées en mémoire tant qu'elle ne sont pas insérées dans la table. Cela signifie que si on tue mysqld violemment, (kill -9) ou si mysqld meurt accidentellement, toutes les lignes en attente qui n'auront pas été écrites sur le disque seront perdues !

Les paragraphes suivants décrivent en détail ce qu'il se passe quand on utilise l'option DELAYED dans une requête INSERT ou REPLACE. Dans cette description, ``thread'' est un thread qui reçoit une commande INSERT DELAYED ans ``handler'' est un thread qui gère toutes les opérations de INSERT DELAYED pour une table donnée.

Il faut noter que INSERT DELAYED est plus lent qu'un INSERT normal si la table n'est pas utilisée. L'utilisation d'un thread de gestion séparé pour chaque table sur lesquelles on utilise INSERT DELAYED rajoute également une surcharge au serveur. Ce qui signifie qu'il vaut mieux utiliser INSERT DELAYED uniquement quand c'est vraiment nécessaire!

6.4.5 Syntaxe de UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] nom_de_table
    SET nom_colonne1=expr1 [, nom_colonne2=expr2, ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT #]

UPDATE met à jour des enregistrements dans une tables avec de nouvelles valeurs. La clause SET indique les colonnes à modifier et les valeurs à leur donner. La clause WHERE, si fournie, spécifie les enregistrements à mettre à jour. Sinon, tous les enregistrements sont mis à jour. Si la clause ORDER BY est fournie, les enregistrements seront mis à jour dans l'ordre spécifié.

Si vous spécifiez le mot clef LOW_PRIORITY, l'exécution de l'UPDATE sera repoussé jusqu'à ce que aucun client ne lise plus de la table.

Si vous spécifiez le mot clef IGNORE, la mise à jour ne s'interrompra pas même si on rencontre des problèmes d'unicité de clefs durant l'opération. Les enregistrements posant problèmes ne seront pas mis à jour.

Si vous accédez à une colonne d'une table dans une expression, UPDATE utilisera la valeur courante de la colonne. Par exemple, la requête suivante ajoute une année à l'âge actuel de tout le monde : 

mysql> UPDATE persondata SET age=age+1;

Les requêtes UPDATE sont évaluées de gauche à droite. Par exemple, la requête suivante double la valeur de la colonnes âge, puis l'incrémente : 

mysql> UPDATE persondata SET age=age*2, age=age+1;

Si vous changez la valeur d'une colonne en lui spécifiant sa valeur actuelle, MySQL s'en aperçoit et ne fait pas la mise à jour.

UPDATE retourne le nombre d'enregistrements ayant changé. Depuis la version 3.22 de MySQL, la fonction mysql_info() de l'API C retourne le nombre de colonnes qui correspondaient, le nombre de colonnes mises à jour et le nombre d'erreurs générées pendant l'UPDATE.

Dans la version 3.23 de MySQL, vous pouvez utilisez le code LIMIT # pour vous assurer que seul un nombre d'enregistrements bien précis est changé.

A partir de la version 4.0.4 de MySQL, vous pouvez aussi effectuer un UPDATE qui se base sur plusieurs tables : 

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

6.4.6 Syntaxe de DELETE

DELETE [LOW_PRIORITY] [QUICK] FROM nom_de_table
       [WHERE clause_where]
       [ORDER BY ...]
       [LIMIT lignes]

ou

DELETE [LOW_PRIORITY] [QUICK] nom_de_table[.*] [,nom_de_table[.*] ...]
       FROM table-references
       [WHERE clause_where]

ou

DELETE [LOW_PRIORITY] [QUICK]
       FROM nom_de_table[.*], [nom_de_table[.*] ...]
       USING table-references
       [WHERE clause_where]

DELETE efface les enregistrements de nom_de_table qui satisfont la condition donnée par clause_where, et retourne le nombre d'enregistrements effacés.

Si vous exécutez un DELETE sans clause WHERE, tous les enregistrements sont effacés. Si vous le faites en mode AUTOCOMMIT cela aura le même effet qu'un TRUNCATE. See section 6.4.7 Syntaxe de TRUNCATE. Avec MySQL 3.23, DELETE sans clause WHERE retournera zéro comme nombre d'enregistrements affectés.

Si vous voulez vraiment savoir combien d'enregistrements ont été effacés quand vous videz une table, et que vous êtes prêts à souffrir d'un léger ralentissement, vous pouvez utiliser une requête DELETE de ce genre : 

mysql> DELETE FROM nom_de_table WHERE 1>0;

Notez que c'est plus lent que DELETE FROM nom_de_table sans clause WHERE, parce que cela efface un enregistrement à la fois.

Si vous spécifiez le mot clef LOW_PRIORITY, l'exécution du DELETE sera repoussée jusqu'à ce qu'aucun client ne lise plus de la table.

Si vous spécifiez le mot QUICK, le gestionnaire de la table ne fusionnera pas les blancs dans les index durant la suppréssion, ce qui peut accélérer certain types de suppressions.

Dans les tables de type MyISAM, les enregistrements effacés sont maintenus dans une liste liée et les requêtes INSERT suivantes réutilisent les vieux emplacements. Pour recouvrir l'espace inutilisé ou réduire la taille des fichiers, utilisez la commande OPTIMIZE TABLE ou l'utilitaire myisamchk pour réorganiser les tables. OPTIMIZE TABLE est plus simple, mais myisamchk est plus rapide. Voyez section 4.5.1 Syntaxe de OPTIMIZE TABLE et section 4.4.6.10 Optimisation de tables.

Le premier format de suppression multi-tables est supporté à partir de MySQL 4.0.0. Le second format de suppression multi-tables est supporté à partir de MySQL 4.0.2.

L'idée est que seul les lignes concordante dans les tables énumérées avant le FROM ou avant la clause USING sont effacés. Le but est de pouvoir effacer des lignes de plusieurs tables en même temps tout en ayant d'autres tables pour les recherches.

Le code .* après les noms de tables n'est présent que pour assurer la compatibilité avec Access: 

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

ou

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

Dans les cas précédents, nous n'avons supprimé les lignes correspondantes que dans les tables t1 et t2.

ORDER BY et l'utilisation de plusieurs tables dans une requête DELETE est supporté en MySQL 4.0.

Si une clause ORDER BY est utilisée, les enregistrements seront effacés dans cet ordre. Ceci n'est vraiment intéressant qu'en conjonction avec LIMIT. Par exemple: 

DELETE FROM unlog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

Cela effacera la plus vielle entrée (en se basant sur timestamp) où les enregistrements satisfont la clause WHERE.

L'option LIMIT lignes spécifique à MySQL pour DELETE donne au serveur le nombre maximal de lignes à être effacé avant que le contrôle ne revienne au client. Cela peut être utilisé pour s'assurer qu'une commande DELETE spécifique ne prenne pas trop de temps. Vous pouvez répéter la commande jusqu'à ce que le nombre de lignes affectées soit inférieur à la valeur passée à l'option LIMIT .

6.4.7 Syntaxe de TRUNCATE

TRUNCATE TABLE nom_de_table

Dans la version 3.23, TRUNCATE TABLE est équivalent à COMMIT ; DELETE FROM nom_de_table. See section 6.4.6 Syntaxe de DELETE.

TRUNCATE TABLE diffère de DELETE FROM ... des façons suivantes :

TRUNCATE est une extension Oracle SQL.

6.4.8 Syntaxe de REPLACE

    REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] nom_de_table [(nom_de_colonne,...)]
        VALUES (expression,...),(...),...
ou  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] nom_de_table [(nom_de_colonne,...)]
        SELECT ...
ou  REPLACE [LOW_PRIORITY | DELAYED]
        [INTO] nom_de_table
        SET col_name=expression, nom_de_colonne=expression,...

REPLACE fonctionne exactement comme INSERT, sauf que si une vieille ligne dans la table à la même valeur qu'une nouvelle pour un index UNIQUE ou une PRIMARY KEY, la vielle ligne sera effacée avant que la nouvelle ne soit insérée. See section 6.4.3 Syntaxe de INSERT.

En d'autres termes, vous ne pouvez pas accéder aux valeurs de l'ancienne ligne à partir d'une requête REPLACE. Dans quelques vieilles versions de MySQL, il apparaît que c'était possible, mais c'etait un dysfonctionnement qui a été corrigé depuis.

Pour utiliser REPLACE vous devez avoir les privilèges INSERT et DELETE sur la table.

Quand vous utilisez une commande REPLACE, mysql_affected_rows() retournera 2 si une nouvelle ligne en remplace une existante, et cela parce qu'il y aura eu une insertion puis une suppression.

Cela aide à savoir si REPLACE a ajouté ou a remplacé une ligne : Testez si le nombre de lignes affectées est égal à 1 (ajout) ou s'il est égal à 2 (remplacement).

Notez que si vous n'utilisez pas un index UNIQUE ou une PRIMARY KEY, utiliser un REPLACE n'a pas de sens vu que cela revient à utiliser un INSERT.

6.4.9 Syntaxe de LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'nom_de_fichier.txt'
    [REPLACE | IGNORE]
    INTO TABLE nom_de_table
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES TERMINATED BY '\n']
    [IGNORE nombre LINES]
    [(nom_de_colonne,...)]

La commande LOAD DATA INFILE lit les lignes dans un fichier texte et les insère à très grande vitesse. Si le mot clef LOCAL est spécifié, le fichier sera lu sur la machine du client. Si le mot clef LOCAL n'est pas spécifié, le fichier doit se trouver sur le serveur (LOCAL est disponible à partir de la version 3.22.6 de MySQL).

Pour des raisons de sécurité, lorsque les fichiers sont lus sur le serveur, ils doivent se trouver dans le répertoire de la base de données courante, ou bien être lisible par tous. Pour utiliser la commande LOAD DATA INFILE sur des fichiers du serveur, vous devez avoir le droit de FILE sur le serveur. See section 4.2.7 Droits fournis par MySQL.

Dans les versions 3.23.49 et 4.0.2 de MySQL, LOCAL ne fonctionnera que si vous n'avez pas démarré mysqld avec l'option --local-infile=0 ou si vous n'avez pas activé le support de LOCAL pour votre client. See section 4.2.4 Problèmes de sécurité avec LOAD DATA LOCAL.

Si vous spécifiez le mot clef LOW_PRIORITY, l'exécution de la commande LOAD DATA est ajournée jusqu'à ce qu'aucun client ne lise plus de la table.

Si vous spécifiez le mot clef CONCURRENT avec un table au format MyISAM, les autres threads pourront accéder à la table durant l'exécution de la commande LOAD DATA. L'utilisation de cette option ralentira un peu les performances de LOAD DATA même si aucun thread n'utilise la table en même si aucun autre thread n'accède à la table en même temps.

L'utilisation de LOCAL sera un peu plus lente que laisser le serveur accéder directement au fichiers, car le contenu du fichier devra transiter du client jusqu'au serveur. D'un autre côté, vous n'aurez pas besoin du droit FILE pour charger les fichiers locaux.

Si vous utilisez une version de MySQL antérieure à la 3.23.24 vous ne pouvez lire à partir d'un FIFO avec LOAD DATA INFILE. Si vous avez besoin de lire à partir d'un FIFO (par exemple la sortie de gunzip), utilisez LOAD DATA LOCAL INFILE.

Vous pouvez aussi charger des fichiers de données en utilisant l'utilitaire mysqlimport; Il opère en envoyant la commande LOAD DATA INFILE au serveur. L'option --local fais que mysqlimport lit les fichiers de données chez le client. Vous pouvez spécifier l'option --compress pour avoir de meilleurs performances avec les connexions lentes si le client et le serveur supportent le protocole compressé.

Lorsque les fichiers de données sont sur le serveur, celui çi utilise les règles suivantes :

Notez que ces règles font qu'un fichier tel que `./myfile.txt' est lu dans le dossier de données du serveur, alors que s'il est nommé `myfile.txt', il sera lu dans le dossier de base de données courante. Par exemple, la commande LOAD DATA suivante lit le fichier `donnees.txt' dans le dossier de la base db1 car db1 est la base de données courante, même si la commande charge explicitement le fichier dans la base de données db2 : 

mysql> USE db1;
mysql> LOAD DATA INFILE "donnees.txt" INTO TABLE db2.ma_table;

Les mots réservés REPLACE et IGNORE contrôlent la méthode d'insertion de lignes lorsque des doublons apparaissent pour les clés uniques. Si vous spécifiez REPLACE, les nouvelles lignes remplaceront les anciennes. Si vous spécifiez IGNORE, les nouvelles lignes seront ignorées. Si vous ne spécifiez pas cette option, une erreur sera générée à chaque doublon, et le reste du fichier sera ignoré.

Si vous chargez un fichier sur votre machine client avec l'option LOCAL, le serveur ne peut pas interrompre la transmission du fichier au milieu de l'opération : par défaut, il utilisera l'option IGNORE.

Si vous utilisez LOAD DATA INFILE sur une table vide de type MyISAM, tous les index non-uniques seront créés dans un processus séparé (tout comme REPAIR). Cela rend LOAD DATA INFILE beaucoup plus rapide si vous avez plusieurs index.

LOAD DATA INFILE est le complémentaire de SELECT ... INTO OUTFILE. See section 6.4.1 Syntaxe de SELECT. Pour écrire des données depuis une table dans un fichier, utilisez SELECT ... INTO OUTFILE. Pour lire les données dans la table, utilisez LOAD DATA INFILE. La syntaxe des clauses FIELDS et LINES est la même pour les deux commandes. Ces deux clauses sont optionnelles, mais FIELDS doit précéder LINES, si les deux sont specifiées.

Si vous spécifiez la clause FIELDS, les sous-clauses TERMINATED BY, [OPTIONALLY] ENCLOSED BY, et ESCAPED BY sont aussi optionnelles, mais vous devez en spécifier au moins une.

Si vous ne spécifiez par de clause FIELDS, les valeurs par défaut sont : 

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Si vous ne spécifiez par de clause LINES, les valeurs par défaut sont : 

LINES TERMINATED BY '\n'

En d'autres termes, les valeurs par défaut font que LOAD DATA INFILE lit les données comme suit :

A l'inverse, les valeurs par défaut font que SELECT ... INTO OUTFILE écrit les données comme ceci :

Notez que pour utiliser FIELDS ESCAPED BY '\\', vous devez spécifier deux antislashs pour que cette valeur soit interprétée comme un antislash simple.

L'option IGNORE nombre LINES sert à ignorer une en-tête de fichier, telle que des noms de colonnes, qui débutent parfois un fichier à charger : 

mysql> LOAD DATA INFILE "/tmp/nom_fichier" INTO TABLE test IGNORE 1 LINES;

Lorsque vous utilisez SELECT ... INTO OUTFILE conjointement avec LOAD DATA INFILE pour écrire des données dans un fichier et les relire dans une table, les options de FIELDS et LINES doivent être identiques. Sinon, LOAD DATA INFILE ne pourra pas interpréter le contenu du fichier correctement. Supposez que la commande SELECT ... INTO OUTFILE ait écrit un fichier délimité par des virgules : 

mysql> SELECT * INTO OUTFILE 'donnees.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

Pour lire ce fichier, la commande correcte serait : 

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

Si au contraire, vous essayez de lire le fichier avec la commande ci-dessous, cela ne fonctionnera pas, car la commande LOAD DATA INFILE essaie de lire des tabulations entre les champs : 

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

Il est probable que chaque ligne d'entrée sera interprétée que comme un seul champ.

La commande LOAD DATA INFILE peut être utilisée pour lire des données issues d'autres sources. Par exemple, un fichier au format dBASE présente des champs séparés par des virgules, et entourés de guillemets doubles. Si les lignes sont terminées par de nouvelles lignes, la commande ci-dessous illustre la relecture d'un tel fichier avec MySQL : 

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE nom_de_table
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Les clauses FIELDS et LINES peuvent prendre des chaînes vides comme valeur. S'il la chaîne n'est pas vide, FIELDS [OPTIONALLY] ENCLOSED BY et FIELDS ESCAPED BY ne doivent avoir qu'un seul caractère. Les valeurs de FIELDS TERMINATED BY et LINES TERMINATED BY peuvent avoir plus d'un caractère. Par exemple, pour écrire des lignes terminées par le couple retour chariot-nouvelle ligne, ou pour lire un tel fichier, spécifiez la clause LINES TERMINATED BY '\r\n'.

Par exemple, pour charger un fichier de blagues, qui sont séparées par une ligne de %%, dans une table vous pouvez faire : 

CREATE TABLE blagues (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   blague TEXT NOT NULL
   );
LOAD DATA INFILE "/tmp/blagues.txt" INTO TABLE blagues FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (blague);

FIELDS [OPTIONALLY] ENCLOSED BY contrôle la mise entre guillemets des champs. Pour l'écriture de fichier (SELECT ... INTO OUTFILE), si vous omettez le mot OPTIONALLY, tous les champs seront entourés par le caractère spécifié dans la clause ENCLOSED BY. Par exemple, si la virgule est utilisée comme séparateur de champs : 

"1","une chaîne","100.20"
"2","une chaîne contenant une , virgule","102.20"
"3","une chaîne contenant un \" guillemet","102.20"
"4","une chaîne contenant un \", guillemet et une virgule","102.20"

Si vous spécifiez OPTIONALLY, le caractère ENCLOSED BY n'est utilisé que pour protéger les colonnes de types CHAR et VARCHAR : 

1,"une chaîne",100.20
2,"une chaîne contenant une , virgule",102.20
3,"une chaîne contenant un \" guillemet",102.20
4,"une chaîne contenant un \", guillemet et une virgule",102.20

Notez que les occurrences du caractère ENCLOSED BY dans un champs sont échappée en les préfixant avec le caractère ESCAPED BY. Notez aussi que si vous spécifiez un caractère d'échappement vide, il n'est pas possible de garantir que les champs seront correctement relus par LOAD DATA INFILE. Par exemple, l'exemple ci-dessus apparaîtra comme montré ci-dessous. Notez que le second champ de la quatrième ligne comporte une virgule suivant un guillemet qui semble (mais c'est faux) terminer la ligne : 

1,"une chaîne",100.20
2,"une chaîne contenant une , virgule",102.20
3,"une chaîne contenant un " guillemet",102.20
4,"une chaîne contenant un ", guillemet et une virgule",102.20

Lors des lectures, le caractère ENCLOSED BY, s'il est présent, est supprimé des extrémités de la valeur du champ. (ce qui est vrai, qu'il y ait l'option OPTIONALLY ou pas). Les occurences du caractère ENCLOSED BY, précédées par le caractère ESCAPED BY sont interprétées comme faisant partie de la valeur du champ. Les caractères ENCLOSED BY doublées, apparaissant dans la chaîne, sont interprétés comme le caractère ENCLOSED BY lui-même. Par exemple, si ENCLOSED BY '"' est spécifié, les guillemets sont gérés comme ceci : 

"Le ""GRAND"" chef"  -> Le "GRAND" chef
Le "GRAND" chef      -> Le "GRAND" chef
Le ""GRAND"" chef    -> Le ""GRAND"" chef

FIELDS ESCAPED BY contrôle les caractères spéciaux. Si le caractère FIELDS ESCAPED BY n'est pas vide, il est utilisé pour préfixer les caractères suivants en écriture :

Si le caractère FIELDS ESCAPED BY est vide, aucun caractère ne sera échappé. Ce n'est probablement pas une bonne idée de spécifier un caractère d'échappement vide, en particulier si les valeurs dans vos champs risquent d'utiliser l'un des caractères de la liste ci-dessus.

En lecture, si le caractère FIELDS ESCAPED BY n'est pas vide, les occurrences de ce caractère sont supprimées, et le caractère suivant est lu littéralement. Les exceptions à cette règle sont `0' ou `N' (par exemple, 0 ou \N si le caractère d'échappement est `\'). Ces séquences sont interprétées comme l'octet nul (ASCII 0) et la valeur NULL. Voyez plus bas pour la gestion des valeurs NULL.

Pour plus d'informations sur la syntaxe avec les caractères d'échappement `\', consultez section 6.1.1 Literals: Comment écrire les chaînes et les nombres.

Dans certains cas, les options de FIELDS et LINES interfèrent entre elles :

La gestion des valeurs NULL depend des options FIELDS et LINES que vous utilisez :

Certains cas ne sont pas supportés par LOAD DATA INFILE:

L'exemple suivant charge toutes les colonnes de la table persondata : 

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Aucun champ n'est spécifié, ce qui fait que LOAD DATA INFILE s'attend à ce que les lignes lues contiennent le bon nombre de champs. Les valeurs par défaut de FIELDS et LINES sont utilisées.

Si vous voulez charger uniquement quelques colonnes dans une table, spécifiez la liste des champs : 

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

Vous devez aussi spécifier les champs si l'ordre dans lequel ils seront lus diffère de l'ordre des colonnes dans la table. Sinon, MySQL ne pourra pas savoir à quelle colonne correspond une valeur.

Si une ligne a trop peu de champs, les colonnes omises prendront leur valeur par défaut. Les affectations de valeurs par défaut sont décrites dans section 6.5.3 Syntaxe de CREATE TABLE.

Une valeur de champs vide et un champ manquant ne seront pas interprétés de la même façon :

Notez que vous obtiendrez le même résultat en assignant à ces différents types de champs la chaîne vide dans une commande INSERT ou UPDATE.

Les colonnes TIMESTAMP prendront la date et l'heure courante uniquement si on leur affecte la valeur NULL, ou (pour la première colonne TIMESTAMP seulement) si la colonne TIMESTAMP est ignorée de la liste des colonnes spécifiée.

Si une ligne d'entrée comporte trop de colonnes, les champs en trop sont ignorés, et le nombre d'alertes est incrémenté.

LOAD DATA INFILE considère toutes les valeurs lues comme des chaînes de caractères : vous ne pourrez donc pas utiliser la forme numérique des colonnes ENUM ou SET, comme d'habitude. Toutes les colonnes ENUM et SET doivent être spécifiée comme des chaînes ! Si vous utilisez l'API C, vous pouvez obtenir des informations à propos de la requête en utilisant la fonction mysql_info() quand LOAD DATA INFILE se termine. Le format de la chaîne d'informations est le suivant : 

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Les alertes sont générées dans les mêmes circonstances que pour la commande INSERT (see section 6.4.3 Syntaxe de INSERT), excepté que LOAD DATA INFILE génère aussi des alertes s'il y a trop peu ou trop de champs dans une ligne. Les alertes ne sont pas stockées; le nombre d'alertes est la seule indication. Si vous recevez des alertes et vous voulez savoir exactement ce qui s'est passé, exécutez une commande SELECT ... INTO OUTFILE dans un autre fichier et comparez le avec le fichier original.

Si vous voulez faire lire LOAD DATA à partir d'un pipe, vous pouvez utiliser l'astuce suivante : 

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /nt/mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Si vous utilisez une version de MySQL supérieure à 3.23.25 vous ne pouvez que faire ce qui précède avec LOAD DATA LOCAL INFILE.

Pour plus d'informations sur les performances de INSERT comparées à LOAD DATA INFILE et accélerer LOAD DATA INFILE : See section 5.2.9 Vitesse des requêtes INSERT.

6.4.10 Syntaxe de DO

DO expression, [expression, ...]

Exécute l'expression mais ne retourne aucun résultat. C'est un alias de SELECT expression, expression, mais il a l'avantage d'être plus rapide quand on n'a pas besoin du résultat.

Cela s'avère très utile avec les fonctions qui ont des effets secondaires, comme RELEASE_LOCK.

6.5 Définition de données : CREATE, DROP, ALTER

6.5.1 Syntaxe de CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name

CREATE DATABASE crée une base de données avec le nom donné. La syntaxe des noms des bases de données est donnée ici : section 6.1.2 Noms de bases, tables, index, colonnes et alias. Une erreur survient si la base de données existe déjà et que vous n'avez pas spécifié IF NOT EXISTS.

Les bases de données MySQL sont implémentées comme des répertoires contenant des fichiers qui correspondent aux tables dans les bases de données. Puisqu'il n'y a pas de tables dans une base de données lors de sa création, la requête CREATE DATABASE créera seulement le dossier dans le répertoire de données de MySQL.

Vous pouvez aussi créer des bases de données avec mysqladmin. See section 4.8 MySQL Scripts clients et utilitaires.

6.5.2 Syntaxe de DROP DATABASE

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE détruit toutes les tables dans la base de données et l'efface elle même. Si vous utilisez la commande DROP DATABASE sur un lien symbolique pointant sur la base de données, le lien et la base seront effacés. Soyez très prudents avec cette commande!

DROP DATABASE retourne le nombre de fichiers qui ont été effacés du dossier de la base de données. Normalement, c'est égal à trois fois le nombre de tables, car chaque table, correspond normalement à un fichier `.MYD', un fichier `.MYI' et un fichier `.frm'.

La commande DROP DATABASE efface tous les fichiers du dossier de la base de données ayant les extensions suivantes :

Ext Ext Ext Ext
.BAK .DAT .HSH .ISD
.ISM .ISM .MRG .MYD
.MYI .db .frm

Tous les sous-dossiers qui consistent de 2 chiffres (dossiers RAID) sont aussi supprimés.

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot clef IF EXISTS pour éviter l'affichage d'erreurs si la base n'existe pas.

Vous pouvez aussi supprimer des bases de données avec mysqladmin. See section 4.8 MySQL Scripts clients et utilitaires.

6.5.3 Syntaxe de CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nom_de_table [(definition_de_create,...)]
[options_de_table] [select_statement]

definition_de_create:
  nom_de_colonne type [NOT NULL | NULL] [DEFAULT valeur_par_defaut] [AUTO_INCREMENT]
            [PRIMARY KEY] [definition_de_reference]
  ou    PRIMARY KEY (index_col_name,...)
  ou    KEY [nom_index] (index_col_name,...)
  ou    INDEX [nom_index] (index_col_name,...)
  ou    UNIQUE [INDEX] [nom_index] (index_col_name,...)
  ou    FULLTEXT [INDEX] [nom_index] (index_col_name,...)
  ou    [CONSTRAINT symbol] FOREIGN KEY [nom_index] (index_col_name,...)
            [reference_definition]
  ou    CHECK (expr)

type:
        TINYINT[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    SMALLINT[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    MEDIUMINT[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    INT[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    INTEGER[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    BIGINT[(longueur)] [UNSIGNED] [ZEROFILL]
  ou    REAL[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
  ou    DOUBLE[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
  ou    FLOAT[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
  ou    DECIMAL(longueur,décimales) [UNSIGNED] [ZEROFILL]
  ou    NUMERIC(longueur,décimales) [UNSIGNED] [ZEROFILL]
  ou    CHAR(longueur) [BINARY]
  ou    VARCHAR(longueur) [BINARY]
  ou    DATE
  ou    TIME
  ou    TIMESTAMP
  ou    DATETIME
  ou    TINYBLOB
  ou    BLOB
  ou    MEDIUMBLOB
  ou    LONGBLOB
  ou    TINYTEXT
  ou    TEXT
  ou    MEDIUMTEXT
  ou    LONGTEXT
  ou    ENUM(valeur1,valeur2,valeur3,...)
  ou    SET(valeur1,valeur2,valeur3,...)

index_nom_de_colonne:
        nom_de_colonne [(longueur)]

definition_de_reference:
        REFERENCES nom_de_table [(index_nom_de_colonne,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE option_de_reference]
                   [ON UPDATE option_de_reference]

option_de_reference:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

options_de_table:
	TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
ou	AUTO_INCREMENT = #
ou	AVG_ROW_LENGTH = #
ou	CHECKSUM = {0 | 1}
ou	COMMENT = "phrase"
ou	MAX_ROWS = #
ou	MIN_ROWS = #
ou	PACK_KEYS = {0 | 1 | DEFAULT}
ou	PASSWORD = "mot"
ou	DELAY_KEY_WRITE = {0 | 1}
ou      ROW_FORMAT= { default | dynamic | fixed | compressed }
ou	RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
ou	UNION = (nom_de_table,[nom_de_table...])
ou	INSERT_METHOD= {NO | FIRST | LAST }
ou      DATA DIRECTORY="chemin absolu vers dossier"
ou      INDEX DIRECTORY="chemin absolu vers dossier"

select_statement:
	[IGNORE | REPLACE] SELECT ...  (une clause de séléction valide)

CREATE TABLE Crée une table avec le nom donné, dans la base de données courante. Les règles de nommage des tables sont disponibles dans section 6.1.2 Noms de bases, tables, index, colonnes et alias. Une erreur est affichée s'il n'y a pas de base courante, ou si la table existe déjà.

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot réservé TEMPORARY lorsque vous créez une table. Une table temporaire sera immédiatement effacée dès que la connexion se termine. Cela signifie que vous pouvez utiliser le même nom de table temporaire depuis deux connexions différentes sans risque de conflit entre les connexions. Vous pouvez aussi utiliser une table temporaire qui a le même nom qu'une table existante (la table existante est alors cachée tant que dure la table temporaire). En MySQL version 4.0.2 ou plus récent, vous avez juste à avoir le privilège CREATE TEMPORARY TABLES pour créer des tables temporaires.

Depuis la version 3.23 de MySQL, vous pouvez utiliser le mot réservé IF NOT EXISTS, de façon à ce qu'aucune erreur ne soit affiché si la table que vous essayez de créer existe déjà. Notez qu'il n'y a pas de comparaisons entre les structures de table lors du test d'existence.

En MySQL 4.1, vous pouvez utiliser LIKE pour crˇer une table basˇe sur la dˇfinition d'une autre table. En MySQL 4.1, vous pouvez aussi spˇcifier le nom de la colonne gˇnˇrˇe : 

CREATE TABLE foo (a tinyint not null) SELECT b+1 AS 'a' FROM bar;

Chaque table nom_de_table est representée par des fichiers dans le dossier de la base de données. Dans le cas des tables de type MyISAM, ce sont les fichiers suivants :

Fichier Rôle
nom_de_table.frm Fichier de définition de la table
nom_de_table.MYD Fichier de données
nom_de_table.MYI Fichier d'index

Pour plus d'informations sur les propriétés des différentes colonnes et de leurs types, voyez section 6.2 Types de colonnes:

6.5.3.1 Modification automatique du type de colonnes

Dans certains cas, MySQL change automatiquement la spécification d'une colonne fournie dans la commande CREATE TABLE. (Cela peut aussi arriver avec ALTER TABLE) :

Si vous voulez voir si MySQL a utilisé un autre type que celui que vous avez spécifié, utilisez la commande DESCRIBE nom_de_table, après votre création ou modification de structure de table.

Certain types de colonnes peuvent être modifiés si vous compressez une table en utilisant l'utilitaire myisampack.

6.5.4 Syntaxe de ALTER TABLE

ALTER [IGNORE] TABLE nom_de_table alter_spec [, alter_spec ...]

alter_specification:
        ADD [COLUMN] create_definition [FIRST | AFTER nom_colonne ]
  ou    ADD [COLUMN] (create_definition, create_definition,...)
  ou    ADD INDEX [nom_index] (index_nom_colonne,...)
  ou    ADD PRIMARY KEY (index_nom_colonne,...)
  ou    ADD UNIQUE [nom_index] (index_nom_colonne,...)
  ou    ADD FULLTEXT [nom_index] (index_nom_colonne,...)
  ou	ADD [CONSTRAINT symbol] FOREIGN KEY [nom_index] (nom_colonne_index,...)
            [reference_definition]
  ou    ALTER [COLUMN] nom_colonne {SET DEFAULT literal | DROP DEFAULT}
  ou    CHANGE [COLUMN] ancien_nom_colonne create_definition
               [FIRST | AFTER nom_de_colonne]
  ou    MODIFY [COLUMN] create_definition [FIRST | AFTER nom_colonne]
  ou    DROP [COLUMN] nom_colonne
  ou    DROP PRIMARY KEY
  ou    DROP INDEX nom_index
  ou    DISABLE KEYS
  ou    ENABLE KEYS
  ou    RENAME [TO] nouveau_nom_de_table
  ou    ORDER BY col
  ou    table_options

ALTER TABLE vous permet de changer la structure d'une table existante. Par exemple, vous pouvez ajouter ou supprimer des colonnes, des index, changer le type des colonnes existantes, renommer ces colonnes, ou la table elle-même. Vous pouvez de même changer le commentaire sur la table, ou le type de celle-ci. See section 6.5.3 Syntaxe de CREATE TABLE.

Si vous utilisez ALTER TABLE pour modifier les spécifications d'une colonne mais que DESCRIBE nom_de_table vous indique que cette colonne n'a pas été modifiée, il est possible que MySQL ait ignoré vos modifications pour une des raisons décrite dans section 6.5.3.1 Modification automatique du type de colonnes. Par exemple, si vous essayez de changer une colonne de type VARCHAR en CHAR, MySQL continuera d'utiliser VARCHAR si la table contient d'autres colonnes de taille variable.

ALTER TABLE effectue une copie temporaire de la table originale. Les modifications sont faites sur cette copie, puis l'original est effacée, et enfin la copie est renommée pour remplacer l'originale. Cette méthode permet de rediriger toutes les commandes automatiquement vers la nouvelle table sans pertes. Durant l'exécution de ALTER TABLE, la table originale est lisible par d'autres clients. Les modifications et insertions sont reportées jusqu'à ce que la nouvelle table soit prête.

Notez que si vous utilisez une autre option que RENAME avec ALTER TABLE, MySQL créera toujours une table temporaire, même si les données n'ont pas besoin d'être copiées (comme quand vous changez le nom d'une colonne). Nous avons prévu de corriger cela dans les versions suivantes, mais comme la commande ALTER TABLE n'est pas utilisée très souvent, cette correction ne fait pas partie de nos priorités.

Pour les tables MyISAM, vous pouvez accélérer la recréation de l'index (qui est la plus lente de tout le processus) en choisissant une valeur élevée pour la variable myisam_sort_buffer_size.

Voilà un exemple qui montre quelques utilisations de ALTER TABLE. On commence par une table t1 créée comme suit : 

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Pour renommer la table de t1 à t2 : 

mysql> ALTER TABLE t1 RENAME t2;

Pour changer une colonne a de INTEGER en TINYINT NOT NULL (en laissant le même nom), et pour changer une colonne b de CHAR(10) à CHAR(20) et la renommant de b en c : 

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

Pour ajouter une nouvelle colonne TIMESTAMP nommée d : 

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Pour ajouter un index sur une colonne d, et rendre la colonne a la clef primaire : 

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Pour effacer la colonne c : 

mysql> ALTER TABLE t2 DROP COLUMN c;

Pour ajouter une nouvelle colonne AUTO_INCREMENT nommée c : 

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Notez que nous avons indexé c, car les colonnes AUTO_INCREMENT doivent être indexées, et que nous définissons aussi c en tant que NOT NULL, car les colonnes indexées ne peuvent être NULL.

Quand vous ajoutez une colonne AUTO_INCREMENT, les valeurs de la colonne sont remplies automatiquement pour vous. Vous pouvez choisir la valeur de départ pour l'indexation en utilisant SET INSERT_ID=# avant ALTER TABLE ou en utilisant l'option AUTO_INCREMENT = # de la table. See section 5.5.6 Syntaxe de SET.

Avec les tables de type MyISAM, si vous ne changez pas la colonne AUTO_INCREMENT, l'indice d'auto-incrémentation ne sera pas affecté. Si vous effacez une colonne AUTO_INCREMENT puis en ajoutez une autre, l'indexation recommencera à partir de 1.

See section A.6.1 Problèmes avec ALTER TABLE..

6.5.5 Syntaxe de RENAME TABLE

RENAME TABLE nom_de_table TO nouveau_nom_de_table[, nom_de_table2 TO nouveau_nom_de_table2,...]

Le changement de nom se fait atomiquement ce qui signifie qu'aucun autre processus ne peut accéder la table tant que l'opération est en cours. Cela rend possible de remplacer une vielle table avec une table vide : 

CREATE TABLE nouvelle_table (...);
RENAME TABLE ancienne_table TO backup_table, nouvelle_table TO ancienne_table;

L'opération s'effectue de gauche à droite ce qui signifie que si vous voulez échanger deux noms de tables, vous devez : 

RENAME TABLE ancienne_table    TO backup_table,
             nouvelle_table    TO ancienne_table,
             backup_table TO nouvelle_table;

Si les deux bases de données sont sur le même disque, vous pouvez renommer à travers les bases : 

RENAME TABLE bdd_courante.nom_de_table TO autre_bdd.nom_de_table;

Quand vous exécutez RENAME, vous ne pouvez avoir aucune transaction active ou une table protégée en mode écriture. Vous devez avoir les privilèges ALTER et DROP sur l'ancienne table, et les privilèges CREATE et INSERT sur la nouvelle.

Si MySQL rencontre des erreurs dans un renommage multiple, il remettra les noms changés à leurs valeurs d'origine pour revenir à l'état d'origine.

RENAME TABLE a été ajouté à la version 3.23.23 de MySQL.

6.5.6 Syntaxe de DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS] nom_de_table [, nom_de_table2,...] [RESTRICT | CASCADE]

DROP TABLE supprime une ou plusieurs tables. Toutes les données et la structure de la tables sont perdues, alors soyez prudents avec cette commande !

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot réservé IF EXISTS pour éviter l'affichage des erreurs pour les tables qui n'existent pas. See section 4.5.6.9 SHOW WARNINGS | ERRORS.

RESTRICT et CASCADE sont autorisés pour faciliter le port. Pour le moment, elles ne font rien.

Note : DROP TABLE va automatiquement valider les transactions actives (hormis si vous utilisez la version 4.1 et le mot clˇ TEMPORARY).

L'option TEMPORARY est ignorˇe en 4.0. En 4.1, cette option fonctionne comme suit :

TEMPORARY est pour le moment ignoré; Dans un futur proche, il servira à s'assurer qu'on efface vraiment une table temporaire.

6.5.7 Syntaxe de CREATE INDEX

CREATE [UNIQUE|FULLTEXT] INDEX index_name
       ON tbl_name (col_name[(length)],... )

La requête CREATE INDEX n'effectue aucune action sur les versions de MySQL antérieures à la version 3.22. Dans les versions 3.22 et supérieures, CREATE INDEX est équivalent à une requête ALTER TABLE pour créer des index. See section 6.5.4 Syntaxe de ALTER TABLE.

Normalement, tous les index sont créés en même temps que la table elle même avec CREATE TABLE. See section 6.5.3 Syntaxe de CREATE TABLE. CREATE INDEX permet d'ajouter des index à une table existante.

Une liste de colonnes de la forme (col1,col2,...) crée un index multi-colonnes. Les valeurs de l'index sont créées en concaténant la valeur dex colonnes données.

Pour les colonnes CHAR et VARCHAR, les index peut être créés sur uniquement une partie de la colonne, avec la syntax col_name(length).(Pour les colonnes BLOB et TEXT la longueur d'index est obligatoire.) La requête suivante crée un index en utilisant les 10 premiers caractères de la colonne name: 

mysql> CREATE INDEX part_of_name ON customer (name(10));

Comme la plupart des noms ont en général des différences dans les 10 premiers caractères, l'index ne devrait pas être plus lent qu'un index créé à partir de la colonne name en entier. Ainsi, en n'utilisant qu'une partie de la colonne pour les index, on peut réduire la taille du fichier d'index, ce qui peut permettre d'économiser beaucoup d'espace disque, et peut aussi accélérer les opérations INSERT!

Il est important de savoir qu'on peut indexer une colonne qui peut avoir la valeur NULL ou une colonne BLOB/TEXT que si on utilise une version 3.23.2 ou supérieure de MySQL et en utilisant le type MyISAM.

Pour plus d'informations à propos de l'utilisation des index dans MySQL, voir section 5.4.3 Comment MySQL utilise les index.

Les index FULLTEXT ne peuvent indexer que des colonnes VARCHAR ou TEXT, et seulement dans les tables MyISAM. Les index FULLTEXT sont disponibles dans les versions 3.23.23 et supérieures de MySQL. section 6.8 Recherche en Texte-entier (Full-text) dans MySQL.

6.5.8 Syntaxe de DROP INDEX

DROP INDEX nom_de_l_index ON nom_de_table

DROP INDEX supprime l'index nommé nom_de_l_index de la table nom_de_table. DROP INDEX ne fait rien avec la version 3.22 et les précédentes. Depuis cette version, DROP INDEX est un alias d'ALTER TABLE supprimant l'index.

See section 6.5.4 Syntaxe de ALTER TABLE.

6.6 Commandes de bases de l'utilisateur de MySQL

6.6.1 Syntaxe de USE

USE db_name

La commande USE db_name spécifie à MySQL d'utiliser la base db_name comme base par défaut pour les requêtes ne les mentionnant pas. La base choisie reste la même jusqu'à la fermeture de la session ou un nouvel appel à USE : 

mysql> USE db1;
mysql> SELECT COUNT(*) FROM ma_table;      # sélectionne à partir de db1.ma_table
mysql> USE db2;
mysql> SELECT COUNT(*) FROM ma_table;      # sélectionne à partir de db2.ma_table

Rendre une base de données la base courante (en utilisant USE) ne vous interdit pas l'accès à d'autres tables dans d'autres bases. L'exemple suivant accède à la table author de la base db1 et à la table editor de la base db2 : 

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
    ->        WHERE author.editor_id = db2.editor.editor_id;

La commande USE est fournie pour assurer la compatibilité Sybase.

6.6.2 Syntaxe de DESCRIBE (obtenir des informations sur les colonnes)

{DESCRIBE | DESC} nom_de_table [nom_de_colonne | wild]

DESCRIBE est un raccourçi de SHOW COLUMNS FROM. See section 4.5.6.1 Obtenir des informations sur les bases, tables, colonnes et index.

DESCRIBE fournit des informations à propos des colonnes de la table. nom_de_colonne peut être le nom d'une colonne ou une chaîne contenant les caractères spéciaux SQL `%' et `_'.

Si le type de colonne est différent de celui que vous pensiez avoir définit lors du CREATE TABLE, nottez que MySQL change le type des colonnes de temps en temps. See section 6.5.3.1 Modification automatique du type de colonnes.

Cette instruction est fournie pour une meilleure compatibilité avec Oracle.

L'instruction SHOW renvoit les mêmes informations. See section 4.5.6 Syntaxe de SHOW.

6.7 Commandes relatives aux verrous et aux transactions

6.7.1 Syntaxe de BEGIN/COMMIT/ROLLBACK

Par défaut, MySQL est lancé en mode autocommit. Cela signifie que chaque modification effectué est enregistré immédiatement sur le disque par MySQL.

Si vous utilisez des tables supportant les transactions (comme InnoDB, BDB), vous pouvez configurer MySQL en mode non-autocommit grâce à la commande: 

SET AUTOCOMMIT=0

A partir de là, vous devez utiliser COMMIT pour enregistrer les modifications sur le disque ou ROLLBACK pour ignorer les modifications apportées depuis le début de la transaction.

Si vous souhaitez sortir du mode AUTOCOMMIT pour une série d'opérations, vous pouvez utiliser les commandes BEGIN ou BEGIN WORK : 

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

Vous devez savoir que si vous utilisez des tables ne supportant pas les transactions, les modifications seront écrites immédiatement, indépendamment de l'état du mode de autocommit

Si vous faites un ROLLBACK après avoir modifié une table non transactionnelle, vous obtiendrez (ER_WARNING_NOT_COMPLETE_ROLLBACK) comme message d'alerte. Toutes les tables supportant les transactions seront restaurées, mais aucune des autres tables ne changera.

Si vous utilisez BEGIN ou SET AUTOCOMMIT=0, il est recommandé d'utiliser les "binary log" de MySQL à la place des anciens logs d'update pour les sauvegardes. Les transactions sont stockées dans les logs binaires en un seul bloc, après COMMIT, pour être sû que les transactions qui ont été annulées ne soient pas enregistrées. See section 4.9.4 Le log binaire.

Les commandes suivantes déclenchent la fin des transactions (comme si vous aviez utilisé COMMIT avant d'exécuter la commande):

Command Command Command
ALTER TABLE BEGIN CREATE INDEX
DROP DATABASE DROP TABLE RENAME TABLE
TRUNCATE

Vous pouvez changer le niveau d'isolation des transactions avec SET TRANSACTION ISOLATION LEVEL .... See section 6.7.3 Syntaxe de SET TRANSACTION.

6.7.2 Syntaxe de LOCK TABLES/UNLOCK TABLES

LOCK TABLES nom_de_table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
            [, nom_de_table [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES verrouille une table pour le thread courant. UNLOCK TABLES déverrouillera automatiquement tous les verrous posés par le thread courant. Toutes les tables verrouillées par le thread courant sont automatiquement déverrouillées quand ce thread utilise à nouveau LOCK TABLES, ou quand la connexion au serveur est perdue.

L'utilisation de LOCK TABLES dans MySQL 4.0.2 nécessite le privilège LOCK TABLES global et un privilège de SELECT sur les tables impliquées. Dans MySQL 3.23, il faut les privilèges SELECT, INSERT, DELETE et UPDATE sur les tables.

Les principales raisons d'utiliser LOCK TABLES sont l'émulation de transactions ou l'accélération des processus de modification de tables. Cela sera détaillé plus loin.

Si un thread obtient un verrouillage READ sur une table, ce thread (et tous les autres threads) peuvent uniquement accéder a cette table en lecture. Si un thread obtient un verrouillage WRITE sur une table, alors seul le thread qui a posé le verrou peut lire ou écrire sur cette table. Tous les autres threads sont bloqués.

La différence entre READ LOCAL et READ est que READ LOCAL autorise des requêtes INSERT non-conflictuelles à être exécutées alors que le verrou est posé. Ceci ne peut cependant pas être utilisé si vous souhaitez modifier les fichiers de la base de données en dehors de MySQL pendant que le verrou est posé.

Quand vous utilisez LOCK TABLES, vous devez verrouiller toutes les tables que vous allez utiliser, et vous devez utiliser les mêmes alias sur ce que vous utiliserez dans vos requêtes ! Si vous utilisez une table a plusieurs reprises dans une requête (avec des alias), vous devez verrouiller chacun des alias !

Les verrous WRITE ont normalement des priorités supérieures aux verrous READ, afin de s'assurer que les updates sont exécutés au plus vite. Cela signifie que si un thread demande un verrou READ et qu'un autre thread demande un verrou WRITE, la demande de verrou READ attendra que le thread WRITE ait abouti pour libérer le verrou. Vous pouvez utiliser le verrou LOW_PRIORITY WRITE pour permettre à d'autres threads d'obtenir des verrous READ pendant que le thread attend le verrou WRITE. Vous ne devriez utiliser les verrous LOW_PRIORITY WRITE que si vous êtes sûr qu'il y aura effectivement un moment où aucun thread ne posera de verrou READ.

LOCK TABLES fonctionne de la manière suivante :

  1. Trie toutes les tables à verrouiller dans un ordre défini par MySQL (l'utilisateur ne définit pas d'ordre).
  2. Si une table est verrouillée avec un verrou read et un verrou write, il pose le verrou write avant le read.
  3. Verrouille une table à la fois jusqu'à ce que le thread ait tous ses verrous.

Cette politique garantit le bon verrouillage des tables. Il faut cependant connaitre certaines choses sur ce schema :

Si vous utilisez un verrou LOW_PRIORITY WRITE pour une table, cela signifie seulement que MySQL attendra, pour poser ce verrou, qu'aucun autre thread ne réclame de verrou READ. Quand le thread aura le verrou WRITE et qu'il attendra que les verrous soient posés sur les autres tables de la liste, tous les autres threads attendront que le verrou WRITE soit libéré. Si cela devient un problème grave pour votre application, il est conseillé de convertir des tables en tables supportant les transactions.

Vous pouvez terminer un thread attendant un verrouillage de table en toute sécurité avec KILL. See section 4.5.5 Syntaxe de KILL.

Il est déconseillé de verrouiller des tables utilisées avec INSERT DELAYED, car, dans ce cas, la requête INSERT est exécutée dans un autre thread.

Normalement, vous n'avez pas besoin de verrouiller les tables puisque chaque requête UPDATE est atomique : aucun autre thread ne peut interférer avec une autre requête active. Il existe cependant quelques cas où vous aurez besoin de verrouiller les tables :

L'utilisation de modifications incrémentales (UPDATE customer SET value=value+nouvelle_valeur) ou de la fonction LAST_INSERT_ID() permet de se passer de LOCK TABLES en de nombreuses occasions.

Il est aussi possible de résoudre de nombreux cas en utilisant un verrou utilisateur, avec les fonctions GET_LOCK() et RELEASE_LOCK(). Ces verrous sont stockés dans une table de hashage dans le serveur et utilisent les fonctions pthread_mutex_lock() et pthread_mutex_unlock() pour plus de vitesse. See section 6.3.6.2 Fonctions diverses.

Voir section 5.3.1 Comment MySQL verrouille les tables pour plus de détails.

Il est possible de verrouiller tous les tables de toutes les bases avec la commande FLUSH TABLES WITH READ LOCK.

See section 4.5.3 Syntaxe de FLUSH. C'est une méthode très pratique pour effectuer des sauvegardes si vous utilisez un système de fichiers qui, comme Veritas, permet de créer des instantanés.

NOTE: LOCK TABLES ne fonctionne pas avec les transactions et validera automatiquement toutes les transactions actives avant de poser verrouiller la table.

6.7.3 Syntaxe de SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Configuration du niveau d'isolation des transactions en général, pour la totalité de la session, ou pour la prochaine transaction.

Le comportement par défaut est de configurer le niveau d'isolation pour la transaction suivante (qui n'a pas encore été commencée) En utilisant le paramètre GLOBAL, on configure le niveau par défaut global pour toutes les nouvelles connections. Cette commande requiert les privilèges SUPER. En utilisant le paramètre SESSION, on configure le niveau par défaut pour toutes les prochaines transactions effectuées durant la session actuelle.

On peut configurer le niveau d'isolation global des transactions pour mysqld avec --transaction-isolation=.... See section 4.1.1 Options de ligne de commande de mysqld.

6.8 Recherche en Texte-entier (Full-text) dans MySQL

Comme dans la version 3.23.23, MySQL propose l'indexation et la recherche sur l'ensemble d'un champ "text" (full-text). Les index sur le texte en entier de MySQL sont des index de type FULLTEXT. Les index FULLTEXT sont utilisés avec les tables MyISAM et peuvent être créés depuis des colonnes de types CHAR, VARCHAR, ou TEXT au moment de CREATE TABLE ou plus tard avec ALTER TABLE ou CREATE INDEX. Pour les enregistrements les plus grands, il sera plus rapide de charger les donnés dans une table qui n'a pas d'index FULLTEXT, et ensuite de créer l'index avec ALTER TABLE (ou CREATE INDEX). L'enregistrement de données dans une table qui a déjà des index FULLTEXT sera plus lent.

La recherche sur un texte en entier est effectuée par la fonction MATCH(). 

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles VALUES
    -> (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
    -> (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
    -> (NULL,'Optimising MySQL','In this tutorial we will show ...'),
    -> (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
    -> (NULL,'MySQL Security', 'When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    ->          WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

La fonction MATCH() effectue la recherche d'une chaîne de caractères dans une liste de textes (et dans un groupe d'une ou plusieurs colonnes utilisées pour l'index FULLTEXT). La chaîne recherchée est donnée en argument à AGAINST(). La recherche est sans distinguer les majuscules des minuscules. Pour chaque ligne de la table, MATCH() retourne une valeur de pertinence, qui est une mesure de la ressemblance entre le chaîne recherchée et le texte de la ligne dans le colonne donnée dans la liste de MATCH().

Quand MATCH() est utilisé comme condition de WHERE (voir l'exemple suivant) les lignes retournées sont automatiquement organisées avec la pertinence la plus élevé en premier. Ces pertinences sont des nombres non négatifs en virgule flottante. Une pertinence de zéro signifie qu'il n'y a pas de similarité. La pertinence est calculé en fonction du nombre de mots dans la ligne, du nombre de mots uniques dans cette ligne, du nombre total de mots dans la liste, et du nombre de documents (lignes) qui contiennent un mot en particulier.

Il est aussi possible d'exécuter une recherche en mode booléen. Ceci est décrit dans une section ultérieure.

L'exemple précédent est une illustration élémentaire qui montre comment on utilise la fonction MATCH(). Les lignes sont retournées par ordre décroissant de pertinence.

L'exemple suivant montre comment récupérer la valeur de pertinence explicitement. Comme il n'y a pas de condition WHERE ni de condition ORDER BY les lignes retournées ne sont pas ordonnées. 

mysql> SELECT id,MATCH (title,body) AGAINST ('Tutorial') FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.64840710366884 |
|  2 |                                       0 |
|  3 |                        0.66266459031789 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

L'exemple suivant est plus complexe. La requête retourne la valeur de pertinence et organise les lignes par ordre décroissant de pertinence. Pour obtenir ce résultat, il faut spécifier MATCH() deux fois. Cela ne cause pas de surcharge car l'optimisateur de MySQL remarquera que les deux appels à MATCH() sont identiques et appellent le code de recherche sur texte plein une seule fois. 

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5055546709332 |
|  6 | When configured properly, MySQL ... |   1.31140957288 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

MySQL utilise un filtre très simple pour séparer le texte en mots. Un "mot" est n'importe quelle chaîne de caractères constituée de lettres, chiffres, `'' et `_'. Tout "mot" présent dans la liste des mots à ignorés ou qui est trop court (3 caractères ou moins) est ignoré.

Tous les mots corrects dans la liste et dans la requête sont pondérés en fonction de leur importance dans la liste ou la requête. de cette façon, un mot présent dans de nombreux documents aura un poids faible (et peut être même un poids nul), car il a peut d'importance dans cette requête particuliète. Au contraire, si le mot est rare, il recevra un poids fort. Le poids des mots sont alors rassemblés pour calculer la pertinence de la ligne.

Une telle technique fonctionne plus efficacement sur de grands volumes de données (en fait, elle est optimisée pour cela). Avec des toutes petites tables, la distribution des mots ne reflète par correctement leur valeur sémantique et ce modèle peut parfois produire des résultats étranges. 

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('MySQL');
Empty set (0.00 sec)

La recherche du mot MySQL ne donne aucun résultat dans l'exemple précédent, car il est présent dans plus de la moitié des lignes. Ainsi, il est considéré comme un mot à ignorer (un mot avec une valeur sémantique nulle). C'est le comportement le plus optimal -- un langage de requêtes ne doit pas retourner chaque ligne d'une table de 1 GB.

Un mot qui est trouvé dans la moitié des enregistrements d'une table n'est pas efficace pour trouver les document appropriés. En fait, il trouvera sûrement beaucoup de documents inappropriés à la recherche. On sait tous que cela arrive souvent lorsqu'on recherche quelque chose sur internet en utilisant un moteur de recherche. C'est en suivant ce raisonnement que ces lignes se sont vues attribuer une valeur sémantique très basse dans ce cas particulier.

Avec la version 4.0.1, MySQL peut aussi performer des recherches booléennes en utilisant le modificateur IN BOOLEAN MODE. 

mysql> SELECT * FROM articles WHERE MATCH (title,body)
    ->     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  1 | MySQL Tutorial               | DBMS stands for DataBase ...        |
|  2 | How To Use MySQL Efficiently | After you went through a ...        |
|  3 | Optimising MySQL             | In this tutorial we will show ...   |
|  4 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
|  6 | MySQL Security               | When configured properly, MySQL ... |
+----+------------------------------+-------------------------------------+

Cette requête a retourné toutes les lignes qui contiennent le mot MySQL (note : la barrière des 50% n'est pas utilisée), mais ne contiennent pas le mot YourSQL. Notez qu'une recherche en mode booléen ne trie pas automatiquement les lignes par ordre de pertinence. Vous pouvez le voir dans le résultat de la requête précédente, où la ligne avec la plus haute pertinence (celui qui contient MySQL deux fois) est listé en dernier. Une recherche booléenne peu aussi fonctionner sans un index FULLTEXT, même si cela risque de ralentir le processus.

La recherche booléenne supporte les opérateurs suivants :

+
Un signe plus avant un mot indique que celui-ci doit être présent dans chaque ligne retournée.
-
Un signe moins avant un mot indique que celui-ci ne doit pas être présent dans chaque ligne retournée.
Par défaut (quand ni moins ni plus n'est spécifié) le mot est optionnel, mais la ligne qui le contient sera mise en valeur. Ce fonctionnement est celui de MATCH() ... AGAINST() dans le paramètre IN BOOLEAN MODE.
< >
Ces deux opérateurs servent à faire varier la contribution d'un mot à la pertinence associée à une ligne. L'opérateur < diminue la contribution alors que > l'augmente. Voir l'exemple ci-dessous.
( )
Les parenthèses sont utilisées pour grouper des mots dans une sous-expression.
~
Un tilde avant un mot opère comme un opérateur de négation, ce qui rend la contribution du mot à la pertinence négative. C'est utile pour se débarrasser des mots inutiles. Une ligne contenant un mot comme cela sera moins mise en avant que les autres, mais ne sera pas exclue, ce qui serait le cas avec l'opérateur - .
*
C'est l'opérateur de tronquage. Contrairement aux autres opérateurs, il doit suivre le mot, non le précéder.
"
La phrase, protégée par des guillemets ", ne trouvera que les lignes contenant cette phrase littéralement, comme elle a été entrée.

et voici quelques exemples :

apple banana
trouve les lignes qui contiennent au moins l'un de ces mots.
+apple +juice
... les deux mots.
+apple macintosh
... le mot ``apple'', mais elles sont plus valorisées si elles contiennent aussi ``macintosh''.
+apple -macintosh
... le mot ``apple'' mais pas le mot ``macintosh''.
+apple +(>pie <strudel)
... ``apple'' et ``pie'', ou ``apple'' et ``strudel'' (l'ordre n'importe pas), mais valorise plus ``apple pie'' que ``apple strudel''.
apple*
... ``apple'', ``apples'', ``applesauce'', et ``applet''.
"some words"
... ``some words of wisdom'', mais pas ``some noise words''.

6.8.1 Restrictions avec full-text

6.8.2 Paramétrage précis de la recherche Full-text de MySQL

La recherche sur texte entier n'a malheureusement pas encore beaucoup de paramètres modifiables par l'utilisateur, même si l'ajout de certains apparaît très haut dans le TODO. Si vous utilisez MySQL depuis les sources (see section 2.3 Installer MySQL à partir des sources), vous pouvez mieux contrôler le fonctionnement de la recherche sur texte entier.

La recherche sur texte entier a été paramétrée pour une efficacité de recherche maximale. La modification du comportement par défaut ne fera généralement que diminuer la qualité des résultats des recherches. Il ne faut pas modifier les sources de MySQL sans savoir précisément ce qu'on fait.

Pour les modifications qui nécessitent une reconstruction des index FULLTEXT, la méthode la plus simple pour les tables MyISAM est l'opération suivante, qui reconstruit les fichiers d'index : 

mysql> REPAIR TABLE nom_de_table USE_FRM;

6.8.3 A faire dans la recherche Full-text

6.9 Cache de requêtes MySQL

Depuis la version 4.0.1, le MySQL server bénéficie d'un cache de requêtes. En fait, le cache sauvegrade le texte d'une requête SELECT avec le résultat qui a été envoyé au client. Si une requête identique est appelée par la suite, le serveur retournera le résultat à partir du cache plutôt que d'analyser puis exécuter la requête à nouveau.

NOTE : Le cache de requêtes ne retourne pas de données périmées. A chaques fois que les données sont modifiées, les entrées correspondantes dans le cache sont effacées.

La cache de requêtes est très utile dans des applications où (quelques) tables ne changent pas souvent et que vous avez les mêmes requêtes qui s'y répétent. C'est une solution typique pour les webmestres dont les sites utilisent du contenu dynamique.

Voici quelques performances du cache de requêtes. (Ces résultats on été générés en utilisant la suite benchmark MySQL sur un Linux Alpha 2 x 500 MHz avec 2GB RAM et un cache de requêtes de 64MB) :

6.9.1 Comment fonctionne le cache de requêtes

Les requêtes sont comparées avant d'être analysées : 

SELECT * FROM table

et 

Select * from table

sont considérées comme des requêtes différentes par le cache, ce qui fait que les requêtes doivent être les mêmes (caractère à caractère) pour être considérées comme identiques. De plus, les requêtes peuvent être considérées comme différentes si par exemple, un client utilise un nouveau format de protocole de communication ou un jeu de caractères différent d'un autre client.

Les requêtes qui utilisent différentes bases de données, différentes versions de protocole ou différents jeux de caractères par défaut sont considérées comme différentes et mises en cache séparément.

Le cache ne fonctionne pas pour les requêtes de type SELECT CALC_ROWS ... et SELECT FOUND_ROWS() ... car le nombre de lignes retournées est aussi mis en cache.

Si une table change (INSERT, UPDATE, DELETE, TRUNCATE, ALTER ou DROP TABLE|DATABASE), alors toutes les requêtes mises en cache qui utilisaient cette table deviennent obsolètes et en sont retirées.

Les tables transactionnelles InnoDB qui ont été modifiées seront rendues obsolètes lorsqu'un COMMIT sera exécuté.

Une requête ne peut être mise en cache si elle contient l'une des fonctions suivantes :
Fonction Fonction Fonction
Fonctions définies par l'utilisateur CONNECTION_ID FOUND_ROWS
GET_LOCK RELEASE_LOCK LOAD_FILE
MASTER_POS_WAIT NOW SYSDATE
CURRENT_TIMESTAMP CURDATE CURRENT_DATE
CURTIME CURRENT_TIME DATABASE
ENCRYPT (avec un seul paramètre) LAST_INSERT_ID RAND
UNIX_TIMESTAMP (sans paramètres) USER BENCHMARK

Une requête ne peut être mise en cache non plus, si elle utilise une variable définie par l'utilisateur, si elle opère sur les tables système de MySQL, est de la forme SELECT ... IN SHARE MODE ou de la forme SELECT * FROM AUTOINCREMENT_FIELD IS NULL (pour obtenir l'index du champ auto-increment - utilisé par ODBC).

Par contre, FOUND ROWS() retournera une valeur correcte, même si la requête précédente à utilisé le cache.

Dans les cas où la requête n'utilise aucune table, en utilise une temporaire, ou que l'utilisateur à un droit particulier sur l'une des tables concernées, celle ci ne sera pas mise en cache.

Avant chaque lecture à partir du cache, MySQL vérifie que l'utilisateur à le droit de lecture (SELECT) sur toutes les bases et les tables concernées. Si ce n'est pas le cas, le cache ne sera pas utilisé.

6.9.2 Configuration du cache de requêtes

Le cache de requête ajoute quelques variables système MySQL liées à mysqld qui peuvent être spécifiées dans un fichier de configuration, en ligne de commande lors du démarrage de mysqld.

La configuratoin du cache de requête peut être changée durant la connexion. La syntaxe est la suivante :

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

Option Description
0 ou OFF Ne pas utiliser le cache.
1 ou ON mettre en cache tous les résultats à part les requêtes SELECT SQL_NO_CACHE ....
2 ou DEMAND mettre en cache seulement les résultat des requêtes SELECT SQL_CACHE ....

6.9.3 Options relatives au cache de requêtes dans un SELECT

Il y a deux options relatives au cache de requêtes qui peuvent être utilisées dans une requête SELECT :

Option Description
SQL_CACHE Si QUERY_CACHE_TYPE est DEMAND, permet à la requête d'être mise en cache. Si QUERY_CACHE_TYPE est ON, ceci est la valeur par défaut. Si QUERY_CACHE_TYPE est OFF, ne fait rien.
SQL_NO_CACHE Rend cette requête non-cachable, ne permet pas à cette requête d'être mise en cache.

6.9.4 Status du cache de requêtes et maintenance

Avec la commande FLUSH QUERY CACHE vous pouvez défragmenter le cache de requêtes pour mieux en utiliser la mémoire. Cette commande n'effacera aucune requête du cache. FLUSH TABLES défragmente aussi le cache de requêtes.

La commande RESET QUERY CACHE efface tous les résultats de requêtes du cache.

Vous pouvez visualiser les performances du cache de requêtes avec SHOW STATUS:

Variable Description
Qcache_queries_in_cache Nombre de requêtes mises en cache.
Qcache_inserts Nombre de requêtes ajoutées au cache.
Qcache_hits Nombre d'appel au cache.
Qcache_lowmem_prunes Nombre de requêtes ôtées du cache pour cause de mémoire insuffisante.
Qcache_not_cached Nombre de requêtes qui n'ont pas été mises en cache. (not cachable, or due to QUERY_CACHE_TYPE).
Qcache_free_memory Quantité de mémoire libre pour le cache de requêtes.
Qcache_total_blocks Nombre total de blocs dans le cache de requêtes.
Qcache_free_blocks Nombre de blocs à mémoire libre dans le cache de requêtes.

Nombre total de requêtes = Qcache_inserts + Qcache_hits + Qcache_not_cached.

Le cache de requêtes utilise des blocs de longueur variable, ce qui fait que Qcache_total_blocks et Qcache_free_blocks peuvent indiquer une fragmentation de la mémoire du cache. Après un appel à FLUSH QUERY CACHE un seul (grand) bloc libre subsiste.

Note : Chaque requête a besoin au minimum de deux blocs (un pour le texte de la requête et un autre, ou plus, pour le résultat). De même, chaque table utilisée par une requête a besoin d'un bloc, mais si deux ou plusieurs requêtes utilisent la même table, seul un bloc a besoin d'être alloué.

Vous pouvez aussi utiliser la variable Qcache_lowmem_prunes pour ajuster la taille du cache de requêtes.

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