Chapitre 12 Fonctions à utiliser dans les clauses SELECT et WHERE

Table des matières

12.1 Opérateurs et fonctions tous types
12.1.1 Précédence des opérateurs
12.1.2 Parenthèses
12.1.3 Opérateurs de comparaison
12.1.4 Opérateurs logiques
12.2 Les fonctions de contrôle
12.3 Fonctions de chaînes de caractères
12.3.1 Opérateurs de comparaison pour les chaînes de caractères
12.4 Fonctions numériques
12.4.1 Opérations arithmétiques
12.4.2 Fonctions mathématiques
12.5 Fonctions de dates et d'heures
12.6 Recherche en texte intégral (Full-text) dans MySQL
12.6.1 Booléens de recherches en texte intégral
12.6.2 Recherche en texte intégral avec extension de requête
12.6.3 Restrictions avec la recherche en texte intégral
12.6.4 Paramétrage précis de la recherche en text intégral de MySQL
12.6.5 A faire dans la recherche Full-text
12.7 Fonctions de transtypage
12.8 Autres fonctions
12.8.1 Fonctions sur les bits
12.8.2 Fonctions de chiffrements
12.8.3 Fonctions d'informations
12.8.4 Fonctions diverses
12.9 Fonctions et options à utiliser dans les clauses GROUP BY
12.9.1 Fonctions avec GROUP BY
12.9.2 Options de GROUP BY
12.9.3 GROUP BY avec les champs cachés

Les expressions peuvent être utilisées en différents endroits des requêtes SQL, comme dans les clauses ORDER BY et HAVING des commandes SELECT, dans les clauses WHERE de SELECT, DELETE et UPDATE, ou dans les commandes SET. Les expressions peuvent contenir des valeurs littérales, des noms de colonnes, la valeur NULL, des fonctions et des opérateurs. Ce chapitre décrit les fonctions et opérateurs qui sont autorisés pour écrire une expression avec MySQL.

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 l'analyseur 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.5.3, « 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

12.1 Opérateurs et fonctions tous types

12.1.1 Précédence des opérateurs

La priorité des opérateurs est présentée dans la liste suivante, depuis la priorité la plus basse à la plus haute. Les opérateurs sur la même ligne ont la même priorité.

:=
||, OR, XOR
&&, AND
BETWEEN, CASE, WHEN, THEN, ELSE
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
|
&
<<, >>
-, +
*, /, DIV, %, MOD
^
- (unary minus), ~ (unary bit inversion)
NOT, !
BINARY, COLLATE

12.1.2 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
    

12.1.3 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 :

  • Si l'un ou les deux arguments sont NULL, le résultat de la comparaison est NULL, exception faite pour l'opérateur <=>.

  • Si les deux arguments de la comparaison sont des chaînes, ils seront comparés en tant que chaînes.

  • Si les deux arguments sont des entiers, ils sont comparés en tant qu'entiers.

  • Les valeurs hexadécimales sont traitées en tant que chaînes binaires si elles ne sont pas comparées à un nombre.

  • Si l'un des arguments est une colonne de type TIMESTAMP ou DATETIME et que l'autre est une constante, celle ci est convertie en timestamp avant que la comparaison ne s'opère. Cela est fait pour être mieux compatible avec ODBC.

  • Dans tous les autres cas, les arguments sont comparés en tant que nombres à décimale flottante. (réels)

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

Si vous comparez des chaînes insensibles à la chasse, avec les opérateurs standards (=, <>..., mais pas avec LIKE) les espaces terminaux seront ignorés (espaces, tabulations et nouvelles lignes).

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

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
    
  • <=>

    Comparaison compatible avec NULL. Cet opérateur fait une comparaison d'égalité comme l'opérateur =, mais retourne 1 plutôt que NULL si les deux opérandes sont NULL, et 0 plutôt que NULL si un opérande est NULL.

    mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
            -> 1, 1, 0
    mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
            -> 1, NULL, NULL
    

    <=> a été ajouté en MySQL 3.23.0.

  • <>, != 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
    
  • 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 :

    • Vous pouvez trouver le dernier enregistrement inséré en utilisant :

      SELECT * FROM nom_de_table WHERE auto_col IS NULL
      

      Cela peut être interdit en mettant SQL_AUTO_IS_NULL=0. See Section 13.5.2.8, « Syntaxe de SET ».

    • Pour les colonnes NOT NULL DATE et DATETIME, vous pouvez sélectionner lignes ayant la date spéciale 0000-00-00 avec :

      SELECT * FROM nom_de_table WHERE date_column IS NULL
      

      C'est une fonctionnalité nécessaire pour que certaines applications ODBC fonctionnent (car ODBC ne supporte pas les dates 0000-00-00)

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

  • 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
    
  • GREATEST(value1,value2,...)

    Avec deux ou plusieurs arguments, retourne la valeur la plus grande. Les arguments sont comparés en utilisant les mêmes règles que pour 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'
    

    Avant MySQL 3.22.5, vous pouvez utiliser MAX() au lieu de GREATEST().

  • 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
    

    Depuis MySQL version 4.1, une clause IN() peut aussi contenir une sous-requête. See Section 13.1.8.3, « Sous-requêtes avec les clauses ANY, IN et SOME ».

  • 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 !

  • 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
    
  • LEAST(value1,value2,...)

    Avec deux arguments ou plus, retourne la plus petite valeur. Les arguments sont comparés avec les règles suivantes :

    • Si la valeur retournée est utilisée dans un contexte INTEGER ou que tous les arguments sont des entiers, ils sont comparés comme des entiers.

    • Si la valeur retournée est utilisée dans un contexte REAL ou que tous les arguments sont des entiers, ils sont comparés comme des entiers.

    • Si un des arguments est une chaîne sensible à la casse, les arguments sont comparés comme des chaînes sensibles à la casse.

    • Dans les autres cas, les arguments sont comparés comme des chaînes insensibles à la casse.

    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'
    

    Avant MySQL 3.22.5, vous pouvez utiliser MIN() au lieu de LEAST().

    Notez que les conversions précédentes peuvent produire des résultats étranges dans certains cas limites :

    mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
            -> -9223372036854775808
    

    Cela arrive parce que MySQL lit 9223372036854775808.0 dans un contexte d'entier. La représentation entière n'est pas suffisante pour contenir la valeur, alors elle est transformée en entier signé.

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

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

    mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
    mysql> DESCRIBE tmp;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | test  | varbinary(4) | NO   |     |         |       |
    +-------+--------------+------+-----+---------+-------+
    

    Le type de la colonne test est VARBINARY(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'argument 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 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 :

    ExpressionValeur retournée
    expr2 ou expr3 retourne une chaînechaîne
    expr2 ou expr3 retourne un nombre à virgulenombre à virgule
    expr2 ou expr3 retourne un entierentier

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

12.3 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 7.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. ASCII() fonctionne avec des valeurs numériques entre 0 et 255.

    mysql> SELECT ASCII('2');
            -> 50
    mysql> SELECT ASCII(2);
            -> 50
    mysql> SELECT ASCII('dx');
            -> 100
    

    Voir aussi la fonction ORD().

  • 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'
    
  • BIT_LENGTH(str)

    Retourne le nombre de bits de la chaîne de caractères str.

    mysql> SELECT BIT_LENGTH('text');
            -> 32
    

    BIT_LENGTH() a été ajouté en MySQL 4.0.2.

  • 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'
    
  • CHAR_LENGTH(str)

    Retourne le nombre de caractères de la chaîne str: Un caractère multi-octets compte comme un seul caractère. Cela signifie que pour une chaîne contenant 5 caractères de 2 octets, LENGTH() retournera 10, alors que CHAR_LENGTH() retournera 5.

  • CHARACTER_LENGTH(str)

    CHARACTER_LENGTH() est un synonyme de CHAR_LENGTH().

  • COMPRESS(string_to_compress)

    Compresse une chaîne. Cette fonction requiert la présence de la bibliothèque zlib. Sinon, la valeur retournée sera toujours NULL.

    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',1000)));
            -> 21
    mysql> SELECT LENGTH(COMPRESS(''));
            -> 0
    mysql> SELECT LENGTH(COMPRESS('a'));
            -> 13
    mysql> SELECT LENGTH(COMPRESS(REPEAT('a',16)));
            -> 15
    

    La chaîne compressée est stockée de cette manière :

    • Les chaînes vides sont stockées comme des chaînes vides.

    • Les chaînes non-vides sont stockées avec 4 octets de plus, indiquant la taille de la chaîne non compressée, suivie de la chaîne compressée. Si la chaîne se termine avec des espaces, un point supplémentaire ‘.’ est ajouté, pour éviter que les espaces terminaux soient supprimés de la chaîne. N'utilisez pas les types CHAR ou VARCHAR pour stocker des chaînes compressée. Il est mieux d'utiliser un type BLOB.

    COMPRESS() a été ajouté en MySQL 4.1.1.

  • 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'
    
  • 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'
    
  • 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'
    
  • 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
    
  • 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
    
  • 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"
    
  • 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.

  • INSTR(str,substr)

    Retourne la position de la première occurrence 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.

  • LCASE(str)

    LCASE() est un synonyme de LOWER().

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

  • LENGTH(str)

    Retourne la taille de la chaîne str, mesurée en octets. Un caractère multi-octets compte comme un seul caractère. Cela signifie que pour une chaîne contenant 5 caractères de 2 octets, LENGTH() retournera 10, alors que CHAR_LENGTH() retournera 5.

    mysql> SELECT LENGTH('text');
            -> 4
    
  • 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 devez avoir 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 bibliothèque MySQL++, peut être trouvée à http://www.mysql.com/documentation/mysql++/mysql++-examples.html.

  • LOCATE(substr,str), 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. 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
    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.

  • LOWER(str)

    Retourne la chaîne str avec tous les caractères en minuscules, en fonction du jeu de caractères courant (par défaut, c'est le jeu ISO-8859-1 Latin1) :

    mysql> SELECT LOWER('QUADRATIQUE');
            -> 'quadratique'
    

    Cette fonction gère les caractères multi-octets.

  • 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'
    
  • LTRIM(str)

    Retourne la chaîne de caractères str sans les espaces initiaux :

    mysql> SELECT LTRIM('  barbar');
            -> 'barbar'
    
  • 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 chaînes 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');
            -> ''
    
  • MID(str,pos,len)

    MID(str,pos,len) est un synonyme de SUBSTRING(str,pos,len).

  • 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'
    
  • OCTET_LENGTH(str)

    OCTET_LENGTH() est un synonyme de LENGTH().

  • 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 :

      (1st octet * 256)
    + (2nd octet * 256^2)
    + (3rd octet * 256^3) ...
    

    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
    
  • POSITION(substr IN str)

    POSITION(substr IN str) est un synonyme de LOCATE(substr,str).

  • 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 guillemet simple (‘'’), l'anti-slash (‘\’), ASCII NUL, et le Contrôle-Z. Si l'argument vaut NULL, la valeur retournée sera le mot ``NULL'' sans les guillemets 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
    
  • 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'
    
  • REPLACE(str,from_str,to_str)

    Retourne une chaîne de caractères str dont toutes les occurrences 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.

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

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

  • 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???'
    
  • 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.

  • 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 standard 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 chaîne Soundex standard. 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'
    

    Note : cette fonction implémente l'algorithme soundex original, et non la version populaire améliorée (aussi décrite par D. Knuth). La différence est que la version originale supprime les voyelles, puis les doublons, alors que la version améliorée supprime les doublons d'abord, et ensuite, les voyelles.

  • expr1 SOUNDS LIKE expr2

    Identique à SOUNDEX(expr1)=SOUNDEX(expr2) (disponible depuis la version 4.1).

  • SPACE(N)

    Retourne une chaîne constituée de N espaces :

    mysql> SELECT SPACE(6);
            -> '      '
    
  • SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

    Retourne une chaîne de len caractères de long de la chaîne str, à partir de la position pos. La syntaxe ANSI SQL92 utilise une variante de la fonction FROM :

    mysql> SELECT SUBSTRING('Quadratically',5);
            -> 'ratically'
    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
            -> 'barbar'
    mysql> SELECT SUBSTRING('Quadratically',5,6);
            -> 'ratica'
    

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

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

  • UCASE(str)

    UCASE() est un synonyme de UPPER().

  • UNCOMPRESS(string_to_uncompress)

    Décompresse une chaîne compressée avec COMPRESS(). Si l'argument n'est pas une valeur compressée, le résultat est NULL. Cette fonction requiert la bibliothèque zlib. Sinon, la valeur retournée est toujours NULL.

    mysql> SELECT UNCOMPRESS(COMPRESS('any string'));
            -> 'any string'
    mysql> SELECT UNCOMPRESS('any string');
            -> NULL
    

    UNCOMPRESS() a été ajoutée en MySQL 4.1.1.

  • UNCOMPRESSED_LENGTH(compressed_string)

    Retourne la taille de la chaîne avant compression.

    mysql> SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a',30)));
            -> 30
    

    UNCOMPRESSED_LENGTH() a été ajoutée en MySQL 4.1.1.

  • UNHEX(str)

    Le contraire de HEX(string). C'est à dire, chaque pair de chiffres hexadécimaux sont interprétées comme des nombres, et sont convertis en un caractère représenté par le nombre. Le résultat est retournée sous forme de chaîne binaire.

    mysql> SELECT UNHEX('4D7953514C');
            -> 'MySQL'
    mysql> SELECT 0x4D7953514C;
            -> 'MySQL'
    mysql> SELECT UNHEX(HEX('string'));
            -> 'string'
    mysql> SELECT HEX(UNHEX('1267'));
            -> '1267'
    

    UNHEX() a été ajoutée en MySQL 4.1.2.

  • UPPER(str)

    Retourne la chaîne str en majuscules, en fonction du jeu de caractères courant. Par défaut, c'est le jeu ISO-8859-1 Latin1 :

    mysql> SELECT UPPER('Hey');
            -> 'HEY'
    

    Cette fonction gère les caractères multi-octets.

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

MySQL convertit automatiquement les nombres en chaînes et et vice-versa :

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

Si vous devez convertir explicitement un nombre en chaîne, passez-le en argument de la fonction CONCAT().

mysql> SELECT 38.8, CAST(38.8 AS CHAR);
        -> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
        -> 38.8, '38.8'

CAST() est recommandée, mais elle a été ajoutée en MySQL 4.0.2.

Si une fonction de chaîne de caractères est donnée comme chaîne binaire dans un argument d'une autre fonction, le résultat sera aussi une chaîne binaire. Les nombres convertis en chaînes sont traités comme des chaînes binaires. Cela affecte les comparaisons.

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 :

    CharDescription
    %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é :

    StringDescription
    \%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 slash ‘\’ 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-slash 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).

    Note : actuellement, LIKE n'est pas compatible avec les caractères multi-octets. La comparaison est faîte caractère par caractère.

  • expr NOT LIKE pat [ESCAPE 'escape-char']

    Equivalent à NOT (expr LIKE pat [ESCAPE 'escape-char']).

  • expr NOT REGEXP pat, expr NOT RLIKE pat

    Equivalent à NOT (expr REGEXP pat).

  • 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 Annexe 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 compatibilité 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-slash ‘\’ 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
    
  • 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
    

    Depuis MySQL 4.0, STRCMP() utilise le jeu de caractères courant pour effectuer des comparaisons. Cela fait que le comportement par défaut est la comparaison insensible à la casse, à moins que l'un des deux opérandes soient une chaîne binaire. Avant MySQL 4.0, STRCMP() était sensible à la casse.

12.4 Fonctions numériques

12.4.1 Opérations arithmétiques

Les opérateurs arithmétiques usuels sont disponibles. Notez que dans le cas de ‘-’, ‘+’ et ‘*’, 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 12.7, « Fonctions de transtypage ».

  • +

    Addition :

    mysql> SELECT 3+5;
            -> 8
    
  • -

    Soustraction :

    mysql> SELECT 3-5;
            -> -2
    
  • -

    Moins unaire. Change le signe de l'argument.

    mysql> SELECT - 2;
            -> -2
    

    Notez que si cet opérateur est utilisé avec un BIGINT, la valeur retournée est un BIGINT! Cela signifie que vous devez éviter d'utiliser - sur des entiers qui peuvent avoir une valeur de -2^63!

  • *

    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.

  • DIV

    Division entière. Similaire à FLOOR() mais compatible avec les valeurs BIGINT.

    mysql> SELECT 5 DIV 2;
            -> 2
    

    DIV a été ajouté en MySQL 4.1.0.

12.4.2 Fonctions mathématiques

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

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

  • 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'arctangente 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
    
  • CEILING(X), CEIL(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!

  • COS(X)

    Retourne le cosinus de X, où X est donné en radians.

    mysql> SELECT COS(PI());
            -> -1.000000
    
  • COT(X)

    Retourne la cotangente de X.

    mysql> SELECT COT(12);
            -> -1.57267341
    mysql> SELECT COT(0);
            -> NULL
    
  • CRC32(expr)

    Calcule la somme de contrôle et retourne un entier 32 bits non-signé. Le résultat est la valeur NULL si l'argument est NULL. L'argument attendu est une chaîne, et sera traité comme une chaîne s'il n'est pas du bon type.

    mysql> SELECT CRC32('MySQL');
            -> 3259397556
    

    CRC32() est disponible en MySQL 4.1.0.

  • DEGREES(X)

    Retourne l'argument X, convertit de radians en degrés.

    mysql> SELECT DEGREES(PI());
            -> 180.000000
    
  • 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
    
  • 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!

  • 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
    
  • MOD(N,M), N % M, N MOD 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.

  • 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
    
  • 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
    
  • RADIANS(X)

    Retourne l'argument X, converti de degrés en radians.

    mysql> SELECT RADIANS(90);
            -> 1.570796
    
  • RAND(), RAND(N)

    Retourne un nombre aléatoire à virgule flottante compris 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 :

    mysql> SELECT * FROM tbl_name ORDER BY RAND();
    

    Cette syntaxe est très pratique pour faire une sélection aléatoire de lignes :

    mysql> 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 parfait de nombres aléatoires, mais reste une manière rapide de produire des nombres aléatoires portables selon les différentes plates-formes pour une même version de MySQL.

  • ROUND(X), ROUND(X,D)

    Retourne l'argument X, arrondi à un nombre à D décimales. Avec deux arguments, la valeur est arrondie avec 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.23);
            -> -1
    mysql> SELECT ROUND(-1.58);
            -> -2
    mysql> SELECT ROUND(1.58);
            -> 2
    mysql> SELECT ROUND(1.298, 1);
            -> 1.3
    mysql> SELECT ROUND(1.298, 0);
            -> 1
    mysql> SELECT ROUND(23.298, -1);
            -> 20
    

    Notez que le comportement de l'opérateur ROUND(), lorsque l'argument est exactement entre deux entiers, dépend de la bibliothèque 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().

  • 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
    
  • SIN(X)

    Retourne le sinus de X, où X est donné en radians.

    mysql> SELECT SIN(PI());
            -> 0.000000
    
  • SQRT(X)

    Retourne la racine carrée de X.

    mysql> SELECT SQRT(4);
            -> 2.000000
    mysql> SELECT SQRT(20);
            -> 4.472136
    
  • TAN(X)

    Retourne la tangente de X, où X est donné en radians.

    mysql> SELECT TAN(PI()+1);
            -> 1.557408
    
  • 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.

    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.

12.5 Fonctions de dates et d'heures

Cette section décrit les fonctions qui peuvent être utilisées pour manipuler les valeurs temporelles. Voir Section 11.3, « 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;

Notez que cette requête va aussi sélectionner des lignes dont les dates sont dans le futur.

Les fonctions qui utilisent des valeurs de date acceptent les valeurs de type DATETIME et ignore la partie horaire. Les fonctions qui attendent des heures acceptent les valeurs littérales et ignorent la partie de date.

Les fonctions qui retourne la date ou l'heure courante sont évaluées une fois par requête, tout au début. Cela signifie que des références multiples à la fonction NOW() dans une même requête produiront toujours le même résultat. Ce principe s'applique aussi à CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), et leurs synonymes.

Les intervalles de valeurs de retour des fonctions suivantes s'appliquent aux dates complètes. Si une date est une valeur ``zéro'' ou une date incomplète, comme '2001-11-00', les fonctions qui extraient une partie d'une date retourneront 0. Par exemple, DAYOFMONTH('2001-11-00') retourne 0.

  • ADDDATE(date,INTERVAL expr type), ADDDATE(expr,days)

    Lorsqu'elle est utilisée avec la forme INTERVAL, ADDDATE() est un synonyme de DATE_ADD(). La fonction complémentaire SUBDATE() est un synonyme DATE_SUB().

    mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
            -> '1998-02-02'
    mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
            -> '1998-02-02'
    

    Depuis MySQL 4.1.1, la seconde syntaxe est utilisée si expr est une expression de type DATE ou DATETIME, et que days est un nombre de jour à ajouter à expr.

    mysql> SELECT ADDDATE('1998-01-02', 31);
            -> '1998-02-02'
    
  • ADDTIME(expr,expr2)

    ADDTIME() ajoute expr2 à expr et retourne le résultat. expr est une expression de type DATE ou DATETIME, et expr2 est une expression de type TIME.

    mysql> SELECT ADDTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
            -> '1998-01-02 01:01:01.000001'
    mysql> SELECT ADDTIME("01:00:00.999999", "02:00:00.999998");
            -> '03:00:01.999997'
    

    ADDTIME() a été ajouté en MySQL 4.1.1.

  • 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
    
  • CURRENT_DATE, CURRENT_DATE()

    CURRENT_DATE et CURRENT_DATE() sont synonymes de CURDATE().

  • CURTIME()

    Retourne l'heure courante au format 'HH:MM:SS' or HHMMSS suivant le contexte numérique ou chaîne :

    mysql> SELECT CURTIME();
            -> '23:50:26'
    mysql> SELECT CURTIME() + 0;
            -> 235026
    
  • CURRENT_TIME, CURRENT_TIME()

    CURRENT_TIME et CURRENT_TIME() sont synonymes de CURTIME().

  • CURRENT_TIMESTAMP, CURRENT_TIMESTAMP()

    CURRENT_TIMESTAMP et CURRENT_TIMESTAMP() sont synonymes de NOW().

  • DATE(expr)

    Extrait la partie date de l'expression expr de type DATE ou DATETIME.

    mysql> SELECT DATE('2003-12-31 01:02:03');
            -> '2003-12-31'
    

    DATE() est disponible depuis MySQL 4.1.1.

  • DATEDIFF(expr,expr2)

    DATEDIFF() retourne le nombre de jours entre la date de début expr et la date de fin expr2. expr et expr2 sont des expressions de type DATE ou DATETIME. Seule la partie DATE est utilisée dans le calcul.

    mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
            -> 1
    mysql> SELECT DATEDIFF('1997-11-31 23:59:59','1997-12-31');
            -> -30
    

    DATEDIFF() est disponible depuis MySQL 4.1.1.

  • DATE_ADD(date,INTERVAL expr type), DATE_SUB(date,INTERVAL expr type)

    Ces fonctions effectuent des calculs arithmétiques sur les dates.

    Depuis MySQL 3.23, INTERVAL expr type est autorisé des deux cotés de l'opérateur + si l'expression de l'autre coté est de type DATE ou DATETIME. Pour l'opérateur -, INTERVAL expr type est autorisé uniquement du coté droit, car on ne peut pas soustraire une date d'un intervalle (voir les exemples ci-dessous).

    date est une valeur de type DATETIME ou DATE qui spécifie la date de début. expr est une expression qui spécifie une valeur d'intervalle à ajouter ou soustraire de la date initiale. expr est une chaîne : elle peut commencer avec ‘-’ pour les intervalles négatifs. type est un mot-clé, indiquant comment l'expression doit être interprétée.

    La table suivante indique la signification des arguments type et expr :

    type ValeurAttendue expr Format
    MICROSECONDMICROSECONDS
    SECONDSECONDS
    MINUTEMINUTES
    HOURHOURS
    DAYDAYS
    WEEKWEEKS
    MONTHMONTHS
    QUARTERQUARTERS
    YEARYEARS
    SECOND_MICROSECOND'SECONDS.MICROSECONDS'
    MINUTE_MICROSECOND'MINUTES.MICROSECONDS'
    MINUTE_SECOND'MINUTES:SECONDS'
    HOUR_MICROSECOND'HOURS.MICROSECONDS'
    HOUR_SECOND'HOURS:MINUTES:SECONDS'
    HOUR_MINUTE'HOURS:MINUTES'
    DAY_MICROSECOND'DAYS.MICROSECONDS'
    DAY_SECOND'DAYS HOURS:MINUTES:SECONDS'
    DAY_MINUTE'DAYS HOURS:MINUTES'
    DAY_HOUR'DAYS HOURS'
    YEAR_MONTH'YEARS-MONTHS'

    Les valeurs de type DAY_MICROSECOND, HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND, et MICROSECOND ont été ajoutés en MySQL 4.1.1. Les valeurs QUARTER et WEEK sont disponibles depuis MySQL 5.0.0.

    MySQL autorise tous les signes de ponctuation, comme délimiteur dans le format de expr. Ceux qui sont affichés dans la table sont des suggestions. Si l'argument date est une valeur DATE et que vos calculs impliquent des parties YEAR, MONTH et DAY (c'est à dire, sans partie horaire), le résultat sera de type DATE. Sinon, le résultat est 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'
    mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
        ->                 INTERVAL '1.999999' SECOND_MICROSECOND);
            -> '1993-01-01 00:00:01.000001'
    

    Si vous spécifiez un intervalle qui est trop court (il n'inclut pas toutes les parties d'intervalle attendues par type), MySQL suppose que vous avez omis les valeurs de gauche. Par exemple, si vous spécifiez un type type de DAY_SECOND, la valeur expr devrait contenir des jours, heures, minutes et secondes. Si vous fournissez une valeur de la forme '1:10', MySQL suppose que les jours et heures manquent, et que la valeur représente des minutes et secondes. En d'autres termes, '1:10' DAY_SECOND est interprété comme '1:10' MINUTE_SECOND. C'est similaire au comportement de MySQL avec les valeurs de type TIME, qui représente des durées plutôt que des horaires.

    Notez que si vous ajoutez ou soustrayez à une valeur de type DATE des horaires, le résultat sera automatiquement au format 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 malformées, le résultat sera NULL. Si vous ajoutez des MONTH, YEAR_MONTH ou YEAR, et que le résultat a un jour du mois qui est au-delà de ce qui est possible dans le mois, le jour sera adapté au plus grand jour possible du mois. Par exemple :

    mysql> SELECT DATE_ADD('1998-01-30', interval 1 month);
            -> '1998-02-28'
    

    Notez que dans l'exemple précédent, le mot clé INTERVAL et le spécificateur type sont insensibles à la casse.

  • DATE_FORMAT(date,format)

    Formate la date date avec le format format. Les spécificateurs suivants peuvent être utilisé dans la chaîne format :

    OptionDescription
    %%Un signe pourcentage littéral ‘%’.
    %aNom du jour de la semaine, en abrégé et en anglais (Sun..Sat)
    %bNom du mois, en abrégé et en anglais (Jan..Dec)
    %cMois, au format numérique (1..12)
    %dJour du mois, au format numérique (00..31)
    %DJour du mois, avec un suffixe anglais (1st, 2nd, 3rd, etc.)
    %eJour du mois, au format numérique (0..31)
    %fMicrosecondes (000000..999999)
    %HHeure (00..23)
    %hHeure (01..12)
    %IHeure (01..12)
    %iMinutes, au format numérique (00..59)
    %jJour de l'année (001..366)
    %kHeure (0..23)
    %lHeure (1..12)
    %mMois, au format numérique (01..12)
    %MNom du mois (January..December)
    %pAM ou PM
    %rHeures, au format 12 heures (hh:mm:ss [AP]M)
    %sSecondes (00..59)
    %SSecondes (00..59)
    %THeures, au format 24 heures (hh:mm:ss)
    %UNuméro de la semaine (00..53), où Dimanche est le premier jour de la semaine
    %uNuméro de la semaine (00..53), où Lundi est le premier jour de la semaine
    %VNuméro de la semaine (01..53), où Dimanche est le premier jour de la semaine, utilisé avec '%X'
    %vNuméro de la semaine (01..53), où Lundi est le premier jour de la semaine, utilisé avec '%x'
    %WNom du jour de la semaine (Sunday..Saturday)
    %wNuméro du jour de la semaine (0=Sunday..6=Saturday)
    %XAnnée, pour les semaines qui commencent le Dimanche, au format numérique, sur 4 chiffres, utilisé avec '%V'
    %xAnnée, pour les semaines qui commencent le Lundi, au format numérique, sur 4 chiffres, utilisé avec '%v'
    %yAnnée, au format numérique, sur 2 chiffres
    %YAnnée, au format numérique, sur 4 chiffres

    Tous les autres caractères sont simplement copiés dans le résultat sans interprétation:

    Le format %f est disponible depuis MySQL 4.1.1.

    Depuis MySQL version 3.23, le caractère ‘%’ est requis devant les caractères de format. Dans les versions antérieures de MySQL, ‘%’ était optionnel.

    La raison qui fait que les intervalles de mois et de jours commencent avec zéro est que MySQL autorise les dates incomplètes comme '2004-00-00', depuis MySQL 3.23.

    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'
    
  • DAY(date)

    DAY() est un synonyme de DAYOFMONTH(). Cette fonction est disponible depuis MySQL 4.1.1.

  • DAYNAME(date)

    Retourne le nom du jour de la semaine de date :

    mysql> SELECT DAYNAME('1998-02-05');
            -> 'Thursday'
    
  • DAYOFMONTH(date)

    Retourne le jour de la date date, dans un intervalle de 1 à 31 :

    mysql> SELECT DAYOFMONTH('1998-02-03');
            -> 3
    
  • DAYOFWEEK(date)

    Retourne l'index du jour de la semaine : pour date (1 = Dimanche, 2 = Lundi, ... 7 = Samedi). Ces index correspondent au standard ODBC :

    mysql> SELECT DAYOFWEEK('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
    
  • 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
    mysql> SELECT EXTRACT(MICROSECOND FROM "2003-01-02 10:30:00.00123");
            -> 123
    
  • 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.

  • 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
    

    Si format est donné, le résultat est formaté en fonction de la chaîne format. format peut contenir les mêmes options de format que celles utilisées par DATE_FORMAT() :

    mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),
        ->                      '%Y %D %M %h:%i:%s %x');
            -> '2003 6th August 06:22:58 2003'
    
  • GET_FORMAT(DATE | TIME | TIMESTAMP, 'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL')

    Retourne une chaîne de format. Cette fonction est pratique lorsqu'elle est utilisée avec les fonctions DATE_FORMAT() et STR_TO_DATE().

    Les trois valeurs possibles pour le premier argument, et les cinq valeurs possible pour le second argument donnent 15 formats d'affichage (pour les options utilisées, voyez la table de la fonction DATE_FORMAT()) :

    Appel fonctionRésultat
    GET_FORMAT(DATE,'USA')'%m.%d.%Y'
    GET_FORMAT(DATE,'JIS')'%Y-%m-%d'
    GET_FORMAT(DATE,'ISO')'%Y-%m-%d'
    GET_FORMAT(DATE,'EUR')'%d.%m.%Y'
    GET_FORMAT(DATE,'INTERNAL')'%Y%m%d'
    GET_FORMAT(TIMESTAMP,'USA')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'JIS')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'ISO')'%Y-%m-%d %H:%i:%s'
    GET_FORMAT(TIMESTAMP,'EUR')'%Y-%m-%d-%H.%i.%s'
    GET_FORMAT(TIMESTAMP,'INTERNAL')'%Y%m%d%H%i%s'
    GET_FORMAT(TIME,'USA')'%h:%i:%s %p'
    GET_FORMAT(TIME,'JIS')'%H:%i:%s'
    GET_FORMAT(TIME,'ISO')'%H:%i:%s'
    GET_FORMAT(TIME,'EUR')'%H.%i.%S'
    GET_FORMAT(TIME,'INTERNAL')'%H%i%s'

    Le format ISO est le format ISO 9075, et non ISO 8601.

    mysql> SELECT DATE_FORMAT('2003-10-03', GET_FORMAT(DATE, 'EUR')
            -> '03.10.2003'
    mysql> SELECT STR_TO_DATE('10.31.2003', GET_FORMAT(DATE, 'USA'))
            -> 2003-10-31
    
    
    
    

    GET_FORMAT() est disponible depuis MySQL 4.1.1. Voyez See Section 13.5.2.8, « Syntaxe de SET ».

  • HOUR(time)

    Retourne le nombre d'heures pour l'heure time, dans un intervalle de 0 à 23 :

    mysql> SELECT HOUR('10:05:03');
            -> 10
    

    Cependant, l'intervalle des valeurs TIME est bien plus grand, et donc, HOUR peut retourner des valeurs plus grandes que 23 :

    
    mysql> SELECT HOUR('272:59:59');
            -> 272
    
  • LAST_DAY(date)

    Prend une valeur de format DATE ou DATETIME, et retourne le dernier jour du mois correspondant. Retourne NULL si l'argument est invalide.

    mysql> SELECT LAST_DAY('2003-02-05'), LAST_DAY('2004-02-05');
            -> '2003-02-28', '2004-02-29'
    mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
            -> '2004-01-31'
    mysql> SELECT LAST_DAY('2003-03-32');
            -> NULL
    

    LAST_DAY() est disponible depuis MySQL 4.1.1.

  • LOCALTIME, LOCALTIME()

    LOCALTIME et LOCALTIME() sont synonymes de NOW().

  • LOCALTIMESTAMP, LOCALTIMESTAMP()

    LOCALTIMESTAMP et LOCALTIMESTAMP() sont synonymes de NOW().

  • MAKEDATE(year,dayofyear)

    Retourne une valeur de format DATE, à partir d'une année et du numéro de jour. dayofyear doit être plus grand que 0 ou le résultat sera NULL.

    mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
            -> '2001-01-31', '2001-02-01'
    mysql> SELECT MAKEDATE(2001,365), MAKEDATE(2004,365);
            -> '2001-12-31', '2004-12-30'
    mysql> SELECT MAKEDATE(2001,0);
            -> NULL
    

    MAKEDATE() est disponible depuis MySQL 4.1.1.

  • MAKETIME(hour,minute,second)

    Retourne une valeur de format TIME, calculée à partir des arguments hour, minute et second.

    mysql> SELECT MAKETIME(12,15,30);
            -> '12:15:30'
    

    MAKETIME() est disponible depuis MySQL 4.1.1.

  • MICROSECOND(expr)

    Retourne le nombre de microsecondes dans l'expression de type TIME ou DATETIME expr, sous la forme d'un nombre entre 0 et 999999.

    mysql> SELECT MICROSECOND('12:00:00.123456');
            -> 123456
    mysql> SELECT MICROSECOND('1997-12-31 23:59:59.000010');
            -> 10
    

    MICROSECOND() est disponible depuis MySQL 4.1.1.

  • MINUTE(time)

    Retourne le nombre de minutes pour l'heure time, dans un intervalle de 0 à 59 :

    mysql> SELECT MINUTE('98-02-03 10:05:03');
            -> 5
    
  • 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
    
  • MONTHNAME(date)

    Retourne le nom du mois de la date date :

    mysql> SELECT MONTHNAME("1998-02-05");
            -> 'February'
    
  • NOW()

    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
    
  • 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
    
  • 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
    
  • SECOND(time)

    Retourne le nombre de secondes pour l'heure time, dans un intervalle de 0 à 59 :

    mysql> SELECT SECOND('10:05:03');
            -> 3
    
  • 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
    
  • STR_TO_DATE(str,format)

    Cette fonction est l'inverse de la fonction DATE_FORMAT(). Elle prend la chaîne str, et une chaîne de format format, puis retourne une valeur DATETIME.

    Les valeurs de type DATE, TIME ou DATETIME contenues dans la chaîne str doivent être au format format. Pour les options qui sont utilisables dans la chaîne format, voyez la table dans la description de la fonction DATE_FORMAT(). Tous les autres caractères sont utilisés littéralement, et ne seront pas interprétés. Si str contient une valeur illégale, STR_TO_DATE() retourne NULL.

    
    
    
    mysql> SELECT STR_TO_DATE('03.10.2003 09.20', '%d.%m.%Y %H.%i')
            -> 2003-10-03 09:20:00
    mysql> SELECT STR_TO_DATE('10rap', '%crap')
            -> 0000-10-00 00:00:00
    mysql> SELECT STR_TO_DATE('2003-15-10 00:00:00', '%Y-%m-%d %H:%i:%s')
            -> NULL
    

    STR_TO_DATE() est disponible depuis MySQL 4.1.1.

  • SUBDATE(date,INTERVAL expr type), SUBDATE(expr,days)

    Lorsqu'elle est utilisée avec la forme INTERVAL du second argument, SUBDATE() est synonyme DATE_SUB().

    mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
            -> '1997-12-02'
    

    Depuis MySQL 4.1.1, la seconde syntaxe est autorisée, où expr est une expression de type DATE ou DATETIME et days est le nombre de jour à soustraire de l'expression expr.

    mysql> SELECT SUBDATE('1998-01-02 12:00:00', 31);
            -> '1997-12-02 12:00:00'
    
  • SUBTIME(expr,expr2)

    SUBTIME() soustrait expr2 de expr et retourne le résultat. expr est une expression de format DATE ou DATETIME et expr2 est une expression de type TIME.

    mysql> SELECT SUBTIME("1997-12-31 23:59:59.999999", "1 1:1:1.000002");
            -> '1997-12-30 22:58:58.999997'
    mysql> SELECT SUBTIME("01:00:00.999999", "02:00:00.999998");
            -> '-00:59:59.999999'
    

    SUBTIME() a été ajoutée en MySQL 4.1.1.

  • SYSDATE()

    SYSDATE() est un synonyme de NOW().

  • TIME(expr)

    Extrait la partie horaire de l'expression expr, de type TIME ou DATETIME.

    mysql> SELECT TIME('2003-12-31 01:02:03');
            -> '01:02:03'
    mysql> SELECT TIME('2003-12-31 01:02:03.000123');
            -> '01:02:03.000123'
    

    TIME() a été ajoutée en MySQL 4.1.1.

  • TIMEDIFF(expr,expr2)

    TIMEDIFF() retourne la durée entre l'heure de début expr et l'heure de fin expr2. expr et expr2 sont des expressions de type TIME ou DATETIME, et doivent être de même type.

    mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
            -> '-00:00:00.000001'
    mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001','1997-12-30 01:01:01.000002');
            -> '46:58:57.999999'
    

    TIMEDIFF() a été ajoutée en MySQL 4.1.1.

  • TIMESTAMP(expr), TIMESTAMP(expr,expr2)

    Avec un seul argument, retourne l'expression expr de type DATE ou DATETIME sous la forme d'une valeur DATETIME. Avec deux arguments, ajouter l'expression expr2 à l'expression expr et retourne le résultat au format DATETIME.

    mysql> SELECT TIMESTAMP('2003-12-31');
            -> '2003-12-31 00:00:00'
    mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
            -> '2004-01-01 00:00:00'
    

    TIMESTAMP() a été ajoutée en MySQL 4.1.1.

  • TIMESTAMPADD(interval,int_expr,datetime_expr)

    Ajoute l'expression entière int_expr à l'expression datetime_expr au format DATE ou DATETIME. L'unité de int_expr est donnée avec l'argument interval, qui peut être l'une des valeurs suivantes : FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, ou YEAR.

    La valeur interval peut être spécifiée, en utilisant un des mots-clé cités, ou avec le préfixe SQL_TSI_. Par exemple, DAY et SQL_TSI_DAY sont tous les deux valides.

    mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
            -> '2003-01-02 00:01:00'
    mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
            -> '2003-01-09'
    

    TIMESTAMPADD() a été ajoutée en MySQL 5.0.0.

  • TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

    Retourne la différence entière entre les expressions datetime_expr1 et datetime_expr2, de format DATE et DATETIME. L'unité du résultat est donné par l'argument interval. Les valeurs légales de interval sont les mêmes que pour la fonction TIMESTAMPADD().

    mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
            -> 3
    mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
            -> -1
    

    TIMESTAMPDIFF() a été ajoutée en MySQL 5.0.0.

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

    Si la valeur time contient une valeur d'heure qui est plus grande que 23, les formats %H et %k produiront une valeur qui est hors de l'intervalle 0..23. L'autre format d'heure produira une heure modulo 12 :

    mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
            -> '100 100 04 04 4'
    
  • 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
    
  • 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.

    N'oubliez pas que MySQL convertit les années représentées sur deux chiffres en dates à quatre chiffres, en utilisant les règles de la section Section 11.3, « Les types date et heure ». Par exemple, '1997-10-07' et '97-10-07' sont identiques :

    mysql> SELECT TO_DAYS('1997-10-07'), TO_DAYS('97-10-07');
            -> 729669, 729669
    

    Pour les dates antérieures à 1582, les résultats sont indéfinis.

  • 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 12.7, « Fonctions de transtypage ».

  • UTC_DATE, UTC_DATE()

    Retourne la date UTC courante au format 'YYYY-MM-DD' ou YYYYMMDD suivant le contexte numérique ou chaîne :

    mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
            -> '2003-08-14', 20030814
    

    UTC_DATE() est disponible depuis MySQL 4.1.1.

  • UTC_TIME, UTC_TIME()

    Retourne l'heure UTC courante au format 'HH:MM:SS' or HHMMSS suivant le contexte numérique ou chaîne :

    mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
            -> '18:07:53', 180753
    

    UTC_TIME() est disponible depuis MySQL 4.1.1.

  • UTC_TIMESTAMP, UTC_TIMESTAMP()

    Retourne l'heure et la date UTC courante au format 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS suivant le contexte numérique ou chaîne :

    mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
            -> '2003-08-14 18:08:04', 20030814180804
    

    UTC_TIMESTAMP() est disponible depuis MySQL 4.1.1.

  • WEEK(date [,mode])

    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. Lorsque l'argument mode est omis, la valeur de la variable default_week_format (ou 0 en MySQL 4.0 ou plus ancien) est utilisé. See Section 13.5.2.8, « Syntaxe de SET ».

    Voici un tableau explicatif sur le fonctionnement du second argument :

    ValeurSignification
    0La semaine commence le Sunday;l'intervalle de valeur de retour va de 0 à !2; la semaine 1 est la première semaine de l'année
    1La semaine commence le Monday;l'intervalle de valeur de retour va de 0 à !2; la semaine 1 est la première semaine de l'année qui a plus de trois jours
    2La semaine commence le Sunday;l'intervalle de valeur de retour va de 1 à !2; la semaine 1 est la première semaine de l'année
    3La semaine commence le Monday;l'intervalle de valeur de retour va de 1 à !2; la semaine 1 est la première semaine de l'année qui a plus de trois jours
    4La semaine commence le Sunday;l'intervalle de valeur de retour va de 0 à !2; la semaine 1 est la première semaine de l'année qui a plus de trois jours
    5La semaine commence le Monday;l'intervalle de valeur de retour va de 0 à !2; la semaine 1 est la première semaine de l'année
    6La semaine commence le Sunday;l'intervalle de valeur de retour va de 1 à !2; la semaine 1 est la première semaine de l'année qui a plus de trois jours
    7La semaine commence le Monday;l'intervalle de valeur de retour va de 1 à !2; la semaine 1 est la première semaine de l'année

    Le mode 3 est disponible depuis MySQL 4.0.5. Le mode 4 est disponible depuis MySQL 4.0.17.

    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 : en version 4.0, WEEK(date,0) a été modifiée pour correspondre au système calendaire des USA. Avant cela, WEEK() était calculé incorrectement, pour des dates américaines : en effet, WEEK(date) et WEEK(date,0) étaient incorrects.

    Si vous préférez que le résultat soit calculé en fonction de l'année qui contient le premier jour de la semaine de la date utilisée en argument, vous devriez utiliser les valeurs 2, 3, 6, or 7 de l'argument mode.

    mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
            -> 2000, 0
    mysql> SELECT WEEK('2000-01-01',2);
            -> 52
    

    Alternativement, utilisez la fonction YEARWEEK() :

    mysql> SELECT YEARWEEK('2000-01-01');
            -> 199952
    mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
            -> '52'
    
  • WEEKDAY(date)

    Retourne l'index du jour de la semaine, avec la conversion 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
    
  • WEEKOFYEAR(date)

    Retourne le numéro de semaine dans l'année, sous forme d'un nombre compris entre 1 et 53.

    mysql> SELECT WEEKOFYEAR('1998-02-20');
            -> 8
    

    WEEKOFYEAR() est disponible depuis MySQL 4.1.1.

  • YEAR(date)

    Retourne l'année de la date date, dans un intervalle de 1000 à 9999:

    mysql> SELECT YEAR('98-02-03');
            -> 1998
    
    mysql> SELECT YEAR('98-02-03');
            -> 1998
    
  • YEARWEEK(date), YEARWEEK(date,start)

    Retourne l'année et la semaine d'une date. L'argument start fonctionne exactement comme l'argument start de la fonction WEEK(). Notez que l'année dans le résultat peut être différente de l'année passée en argument, pour 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 semaine est différent de celui que la fonction WEEK() retourne (0) pour les arguments optionnels 0 ou 1, comme WEEK() puis retourne la semaine dans le contexte de l'année.

12.6 Recherche en texte intégral (Full-text) dans MySQL

  • MATCH (col1,col2,...) AGAINST (expr [IN BOOLEAN MODE | WITH QUERY EXPANSION])

    Depuis la version 3.23.23, MySQL propose l'indexation et la recherche sur l'ensemble d'un champ TEXT (full-text). Les index en texte intégral 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.

    Les contraintes sur la recherche en texte intégral sont listées dans la section Section 12.6.3, « Restrictions avec la recherche en texte intégral ».

La recherche en texte intégral 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. La pertinence est un nombre décimal positif. La 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.

Pour les recherches en texte intégral et en langage naturel, la technique impose que les colonnes utilisées avec la fonction MATCH() doivent être les mêmes que les colonnes utilisées dans un index FULLTEXT. Dans la requête précédente, notez que les colonnes nommées dans la fonction MATCH() (title et body) sont les mêmes que celles de la définition de la table article et son index FULLTEXT. Si vous voulez rechercher le titre title ou le corps body séparément, vous devrez créer un index FULLTEXT pour chaque colonne.

Il est aussi possible d'exécuter une recherche en mode booléen. Ceci est décrit dans les sections Section 12.6.1, « Booléens de recherches en texte intégral » et Section 12.6.2, « Recherche en texte intégral avec extension de requête ».

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'optimiseur de MySQL remarquera que les deux appels à MATCH() sont identiques et appellent le code de recherche sur texte intégral 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é.

  • Un mot trop court est ignoré. La taille minimale pour un mot dans les recherches est de 4 lettres.

  • Les mots de la liste sont ignorés. Un mot banni est par exemple ``the'' ou ``some'', ``un'' or ``les'' en fran¸ais, qui sont considérés comme trop communs pour avoir une valeur intrinsèque. Il y a une liste de mots bannis en anglais par défaut.

La taille minimale des mots et la liste de mots à ignorer sont décrites dans la section Section 12.6.4, « Paramétrage précis de la recherche en text intégral de MySQL ».

Tous les mots corrects de la liste et de 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 nombreuses lignes aura un poids faible (et peut être même un poids nul), car il a peu d'importance dans cette requête particulière. 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 les 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 Go.

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.

Le seuil de 50% a un impact significatif lorsque vous commencez à comprendre comment fonctionne l'index : si vous créez une table et insérez une ou deux lignes, chaque mot apparaîtra dans 50% des lignes. Résultat, la recherche ne trouvera rien. Assurez-vous d'insérer au moins trois lignes, et même plus.

12.6.1 Booléens de recherches en texte intégral

Depuis la version 4.0.1, MySQL peut aussi effectuer des recherchez en texte intégral avec l'option 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 Well | After you went through a ...        |
|  3 | Optimizing 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 recherche toute les lignes qui contiennent le mot ``MySQL'', mais qui ne contient pas le mot ``YourSQL''.

Les recherches booléennes en texte intégral ont les caractéristiques suivantes :

  • Elle n'utilise pas le seuil de 50%.

  • Elles ne trie pas automatiquement les lignes par ordre de pertinence décroissante. Vous pouvez le voir dans l'exemple précédent : la ligne ayant la plus grande pertinence est celle qui contient ``MySQL'' deux fois, mais elle est listée en dernier.

  • Elles peuvent fonctionner sans l'index FULLTEXT, même si c'est particulièrement lent.

Les recherches booléenne en texte intégral supporte les opérateurs suivants :

  • +

    A signe + initial indique que le mot doit être présent dans la ligne retournée.

  • -

    Un signe - initial indique que le mot ne doit pas être présent dans la ligne retournée.

  • (pas d'opérateur)

    Par défaut, lorsque ni +, ni - n'est spécifié), le mot est optionnel, mais les lignes qui le contiennent seront mieux cotées. Cela imite le comportement de MATCH() ... AGAINST() sans l'option IN BOOLEAN MODE.

  • > <

    Ces deux opérateurs servent à changer la contribution d'un mot à la pertinence. L'opérateur > accroît la contribution, et l'opérateur < la décroît. Voir un exemple ci-dessous.

  • ( )

    Les parenthèses servent à grouper des mots en sous-expressions. Les groupes de parenthèses peuvent être imbriquées.

  • ~

    Un signe tilde initial marque la négation, et fait que la contribution du mot à la pertinence sera négative. Cet opérateur est pratique pour marquer les mots ambigus. Une ligne qui contient un tel mot sera classée bien plus bas, mais elle ne sera pas exclue, comme ce serait le cas avec -.

  • *

    Un astérisque est l'opérateur de troncature. Contrairement aux autres opérateurs, il doit être en suffixe et non pas en préfixe.

  • "

    Une phrase entre guillemets double (‘"’) est recherchée littéralement, telle qu'elle a été saisie.

Les exemples ci-dessous illustrent quelques résultats de chaînes de recherche avec les opérateurs :

  • 'pomme banane'

    Recherche les lignes qui contiennent au moins un de ces mots.

  • '+pomme +jus'

    Recherche les lignes qui contiennent ces deux mots.

  • '+pomme macintosh'

    Recherche les lignes qui contiennent le mot ``pomme'', mais classe plus haut les lignes qui contiennent aussi ``macintosh''.

  • '+pomme -macintosh'

    Recherche les lignes qui contiennent ``pomme'' mais pas ``macintosh''.

  • '+pomme +(>tatin <strudel)'

    Recherche les lignes qui contiennent les mots ``pomme'' et ``tatin'', ou ``pomme'' et ``strudel'' (dans n'importe quel ordre), mais classe ``pomme tatin'' plus haut que ``pomme strudel''.

  • 'pomm*'

    Trouve les lignes qui contiennent des mots tels que ``pomme'', ``pommes'', ``pommier'', ou ``pommeau''.

  • '"deux mots"'

    Recherche les lignes qui contiennent exactement la phrase ``deux mots'' (par exemple, les lignes qui contiennent ``deux mots d'amour'' mais pas ``le mot deux''). Notez que les caractères ‘"’ qui entourent la phrase délimitent la phrase. Ils ne délimitent pas la chaîne.

12.6.2 Recherche en texte intégral avec extension de requête

Depuis la version 4.1.1, la recherche en texte intégral supporte l'extension de requête (en particulier la variable dite ``extension aveugle''). C'est généralement utile lorsque la phrase de recherche est trop courte, ce qui signifie que l'utilisateur sous-entend des informations. Par exemple, un utilisateur qui recherche ``database'' peut en fait rechercher ``MySQL8'', ``Oracle'', ``DB2'' ou encore ``RDBMS'' : ce sont des solutions qui doivent être liées à ``databases'' et être retournée. C'est de l'information implicite.

L'extension de requête aveugle (dite aussi, pertinence automatique), fonctionne en faisant la même recherche 2 fois : la seconde fois, la recherche est complétée avec les mots les plus fréquents des premiers résultats. Par conséquent, si un de ces document contenait ``databases'' et ``MySQL'', la seconde recherche va rechercher les documents qui contiennent ``MySQL'' mais pas ``database''. L'exemple suivant illustre la différence :

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)

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' WITH QUERY EXPANSION);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  3 | Optimizing MySQL  | In this tutorial we will show ...        |
+----+-------------------+------------------------------------------+
3 rows in set (0.00 sec)

Un autre exemple est la recherche de livres de Georges Simenon, de la série Maigret, alors que l'utilisateur ne sais pas trop comment écrire ``Maigret''. Alors que la recherche de ``Megre and the reluctant witnesses'' ne conduit qu'à ``Maigret and the Reluctant Witnesses'' sans l'extension aveugle, la version avec extension aveugle va sortir la collection complète des livres avec le mot ``Maigret''.

Note : comme l'extension aveugle augmente le niveau de bruit, en retournant des documents sans rapport, elle n'est utile que si la phrase de recherche est courte.

12.6.3 Restrictions avec la recherche en texte intégral

  • La recherche en texte intégral n'est supportée que par les tables MyISAM.

  • Depuis MySQL 4.1.1, les recherches en texte plein peuvent être utilisées avec la plupart des jeux de caractères. L'exception est pour Unicode, le jeu de caractères utf8 peut être utilisé, mais pas ucs2.

  • Depuis MySQL 4.1, l'utilisation de jeux de caractères multiples dans une table est supportée. Cependant, toutes les colonnes dans un index FULLTEXT doivent avoir le même jeu de caractères et collation.

  • Les arguments de MATCH() doivent correspondre exactement à la liste de colonnes de certaines définitions d'index FULLTEXT pour la table, sauf si MATCH() est utilisé dans un contexte BOOLEAN.

  • L'argument de AGAINST() doit être une chaîne constante.

12.6.4 Paramétrage précis de la recherche en text intégral 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 la liste de tâches. Si vous utilisez MySQL depuis les sources (See Section 2.4, « Installation de MySQL avec une distribution source ».), 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.

  • La taille minimale des mots à indexer est définie dans la variable ft_min_word_len de MySQL. See Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ». Vous pouvez modifier cette valeur pour celle que vous préférez, puis reconstruire les index FULLTEXT. (Cette variable n'existe que pour la version 4.0 de MySQL) La valeur par défaut de cette option est de 4 caractères. Modifiez la, puis recompilez les index FULLTEXT. Par exemple, si vous souhaitez pouvoir rechercher des mots de 3 caractères, vous pouvez donner à cette variable la valeur suivante dans le fichier d'options :

    [mysqld]
    ft_min_word_len=3
    

    Puis, relancez le serveur et recompilez vos index FULLTEXT.

  • La liste des mots rejetés est définie dans la variable ft_stopword_file. See Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ». Modifiez le selon vos goûts, reconstruisez vos index FULLTEXT.

  • Le taux de 50% est déterminé par la méthode de pondération choisie. Pour le désactiver, il faut changer la ligne suivante dans myisam/ftdefs.h :

    #define GWS_IN_USE GWS_PROB
    

    Par la ligne:

    #define GWS_IN_USE GWS_FREQ
    

    Puis recompiler MySQL. Il n'est pas nécessaire de reconstruire les index dans ce cas. Note : en faisant ces modifications, vous diminuez énormément les capacités de MySQL à fournir des valeurs pertinentes pour la fonction MATCH(). Si vous avez réellement besoin de faire des recherches avec ces mots courants, il est préférable de rechercher EN MODE BOOLEEN, lequel ne respecte pas le taux de 50%.

  • Pour changer les opérateurs utilisés pour les recherches booléennes, modifiez la variable système ft_boolean_syntax (disponible depuis MySQL 4.0.1). La variable peut aussi être modifiée durant le fonctionnement du serveur, mais vous devez avoir les droits de SUPER. La recompilation des index n'est pas possible. Section 5.2.3, « Variables serveur système » décrit les règles de définition de cette variable.

Si vous modifiez des variables d'indexation de textes qui affectent les index (les variables (ft_min_word_len, ft_max_word_len et ft_stopword_file), vous devez reconstruire les index FULLTEXT après avoir faire les modifications et relancé le serveur. Pour reconstruire les index, il est suffisant de faire une réparation QUICK :

mysql> REPAIR TABLE tbl_name QUICK;

Si vous utilisez spécifiquement les fonctionnalités IN BOOLEAN MODE, si vous mettez à jour depuis MySQL 3.23 vers 4.0 ou plus récent, il est nécessaire de remplacer aussi les entêtes des index. Pour cela, utilisez l'opération de réparation USE_FRM :

mysql> REPAIR TABLE nom_de_table USE_FRM;

C'est nécessaire, car les recherches booléennes en texte plein requièrent une option dans l'entête qui n'était pas présente en MySQL en version 3.23, et elle n'est pas ajoutée si vous faites une réparation de type QUICK. Si vous tentez une recherche booléenne sans reconstruire l'index comme ceci, la recherche retournera des résultats incorrects.

Notez que si vous utilisez myisamchk pour effectuer une opération qui modifie les index de la table, pour une réparation ou une analyse, les index FULLTEXT sont reconstruits en utilisant la configuration par défaut des index en texte plein, à moins que vous ne les spécifiez autrement. Cela peut conduire à des requêtes qui échouent.

Le problème survient car les valeurs de cette configuration n'est connue que du serveur. Elles ne sont pas stockées dans les fichiers d'index MyISAM. Pour éviter ce problème si vous avez modifié la taille minimale ou maximale des mots, ou encore le fichier de mots interdits, spécifiez les options ft_min_word_len, ft_max_word_len et ft_stopword_file à myisamchk, en donnant les mêmes valeurs que pour mysqld. Par exemple, si vous avez donnez une taille minimale de mot de 3, vous pouvez réparer votre table avec myisamchk :

shell> myisamchk --recover --ft_min_word_len=3 tbl_name.MYI

Pour vous assurer que le serveur et myisamchk utilisent les mêmes valeurs pour les index, vous pouvez les placer dan s les sections [mysqld] et [myisamchk] du fichier d'options :

[mysqld]
ft_min_word_len=3

[myisamchk]
ft_min_word_len=3

Une alternative à l'utilisation de myisamchk est l'utilisation de REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE ou ALTER TABLE. Ces commandes sont effectuées par le serveur, qui connaît la configuration des index en texte plein.

12.6.5 A faire dans la recherche Full-text

  • Rendre toutes les opérations avec l'index FULLTEXT plus rapides.

  • Opérateurs de proximité

  • Support de listes de mots à toujours indexer ("always-index words"). Ceux ci pourraient être n'importe quelle chaîne de caractères que l'utilisateur voudrais traiter comme des mots : par exemple "C++", "AS/400", "TCP/IP", etc.

  • Support de la recherche full-text sur les tables MERGE.

  • Support des jeux de caractères multi-octets.

  • Rendre la liste des mots ignorés dépendante de la langue des données.

  • Stemming (dépendante de la langue des données, bien sûr).

  • Preprocesseur générique pour les UDF fournies par l'utilisateur.

  • Rendre le modèle plus flexible (en ajoutant des valeurs paramétrables pour FULLTEXT dans CREATE/ALTER TABLE).

12.7 Fonctions de transtypage

  • CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

    Les fonctions CAST() et CONVERT() peuvent être utilisées pour convertir une donnée d'un type en un autre. Leurs syntaxes sont :

    La valeur de type peut être l'une des suivantes :

    • BINARY

    • CHAR

    • DATE

    • DATETIME

    • SIGNED {INTEGER}

    • TIME

    • UNSIGNED {INTEGER}

    CAST() et CONVERT() sont disponibles depuis MySQL 4.0.2. La conversion de type CHAR est disponible depuis la version 4.0.6. La forme USING de CONVERT() est disponible depuis la version 4.1.0.

    CAST() et CONVERT(... USING ...) sont des syntaxes SQL-99. La forme sans USING de CONVERT() est une syntaxe ODBC.

    CONVERT() avec la clause USING sert à convertir des données entre différent jeux de caractères. Avec MySQL, les noms d'encodage sont les mêmes que les noms des jeux de caractères. Par exemple, cette commande converti la chaîne 'abc' depuis le jeu de caractères par défaut du serveur vers utf8 :

    SELECT CONVERT('abc' USING utf8);
    

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

Les fonctions peuvent aussi être utilisée pour trier des colonnes de type ENUM en ordre lexical. Normalement, le tri sur les colonnes ENUM est fait avec les valeurs numériques internes. Pour trier les valeurs avec l'ordre lexical CHAR :

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(string AS BINARY) est l'équivalent de BINARY string. CAST(expr AS CHAR) traite l'expression comme une chaîne, avec le jeu de caractères par défaut.

Note : en MySQL 4.0 le CAST() en DATE, DATETIME ou TIME ne fait que marquer la colonne comme étant du type indiqué, mais n'en change pas la valeur.

En MySQL 4.1.0, la valeur est convertie dans le type de colonne demandé, puis il est envoyé à l'utilisateur. Cette fonctionnalité est une nouveauté du protocole 4.1, qui envoie les données au client :

mysql> SELECT CAST(NOW() AS DATE);
       -> 2003-05-26

Dans les prochaines versions de MySQL (probablement 4.1.2 ou 5.0) nous allons corriger CAST pour qu'elle modifie le résultat si vous l'utilisez comme une partie d'une expression plus complexe, comme CONCAT("Date: ",CAST(NOW() AS DATE)).

N'utilisez pas CAST() pour extraire des données dans différents formats, mais utilisez plutôt LEFT ou EXTRACT(). See Section 12.5, « Fonctions de dates et d'heures ».

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.

L'affichage 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.

12.8 Autres fonctions

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

12.8.2 Fonctions de chiffrements

Les fonctions de cette section chiffrent et déchiffrent des valeurs. Si vous voulez stocker le résultat d'un chiffrement qui peut contenir des valeur arbitraires, vous devez utiliser une colonne BLOB plutôt que CHAR ou VARCHAR, afin d'éviter les problèmes potentiels de suppression d'espaces terminaux, qui corrompraient les valeurs.

  • AES_ENCRYPT(str,key_str), AES_DECRYPT(crypt_str,key_str)

    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 modifiant les sources. Nous avons choisi 128 bits parce que c'est plus rapide et suffisamment 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.

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

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

  • DES_DECRYPT(crypt_str[,key_str])

    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 5.6.7, « 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.

  • DES_ENCRYPT(str[,(key_num|key_str)])

    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 5.6.7, « Utilisation des connexions sécurisées ».

    La clé de hachage utilisée est choisie en suivant les recommandations suivantes :

    ArgumentDescription
    Un seul argumentLa 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îneLa 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 permettra 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");
    
  • 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.

  • MD5(str)

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

  • OLD_PASSWORD(str)

    OLD_PASSWORD() est disponible depuis MySQL 4.1, lorsque l'implémentation de la fonction PASSWORD() a été modifiée pour améliorer la sécurité. OLD_PASSWORD() retourne la valeur pre-4.1 de PASSWORD(). Section 5.5.9, « Hashage de mots de passe en MySQL 4.1 ».

  • PASSWORD(str)

    Calcule un mot de passe chiffré à partir de la chaîne str. C'est cette fonction qui est utilisé pour chiffrer 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 de chiffrage Unix. Voir ENCRYPT().

    Note : La fonction PASSWORD() est utilisée durant l'identification au serveur MYSQL. Il est recommandé de ne pas l'utiliser pour vos applications. Utilisez plutôt MD5() ou SHA1(). Voyez aussi RFC-2195 pour plus d'informations sur comment gérer les mots de passe et l'identification de votre système.

  • SHA1(str), SHA(str)

    Calcule la somme de vérification SHA1 160 bits 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().

12.8.3 Fonctions d'informations

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

  • CHARSET(str)

    Retourne le jeu de caractères de la chaîne argument.

    mysql> SELECT CHARSET('abc');
            -> 'latin1'
    mysql> SELECT CHARSET(CONVERT('abc' USING utf8));
            -> 'utf8'
    mysql> SELECT CHARSET(USER());
            -> 'utf8'
    

    CHARSET() a été ajouté en MySQL version 4.1.0.

  • COERCIBILITY(str)

    Retourne la coercibilité de la collation de la chaîne argument.

    mysql> SELECT COERCIBILITY('abc' COLLATE latin1_swedish_ci);
            -> 0
    mysql> SELECT COERCIBILITY('abc');
            -> 3
    mysql> SELECT COERCIBILITY(USER());
            -> 2
    

    Les valeurs retournées possibles sont :

    CoercibilitéSignification
    0Collation explicite
    1Par de collation
    2Collation implicite
    3Coercible

    Les valeurs les plus faibles ont la plus haute priorité.

    COERCIBILITY() a été ajouté en MySQL version 4.1.1.

  • COLLATION(str)

    Retourne la collation du jeu de caractères de la chaîne argument.

    mysql> SELECT COLLATION('abc');
            -> 'latin1_swedish_ci'
    mysql> SELECT COLLATION(_utf8'abc');
            -> 'utf8_general_ci'
    

    COLLATION() a été ajouté en MySQL version 4.1.0.

  • CONNECTION_ID()

    Retourne l'identifiant de connexion courant (thread_id). Chaque connexion a son propre identifiant unique.

    mysql> SELECT CONNECTION_ID();
            -> 23786
    

    CONNECTION_ID() a été ajouté en MySQL version 3.23.14.

  • CURRENT_USER()

    Retourne le nom d'utilisateur et le nom d'hôte de la session courante. Cette valeur correspond au compte qui a été utilisé durant l'identification auprès du serveur. Cela peut être différent des valeurs de USER().

    mysql> SELECT USER();
            -> 'davida@localhost'
    mysql> SELECT * FROM mysql.user;
    ERROR 1044: Access denied for user: '@localhost' to
    database 'mysql'
    mysql> SELECT CURRENT_USER();
            -> '@localhost'
    

    Cet exemple montre que même si le client a indiqué le nom d'utilisateur davida (comme mentionné par la fonction USER()), le serveur a identifié le client comme un utilisateur anonyme (comme indiqué par la fonction CURRENT_USER()). Une situation qui arrive s'il n'y a aucun compte de listé dans les tables de droits pour davida.

    CURRENT_USER() a été ajouté en MySQL version 4.0.6.

  • 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. A partir de la version 4.1.1, elle retourne NULL.

  • FOUND_ROWS()

    Une commande SELECT peut inclure une clause LIMIT pour restreindre le nombre de lignes qui sera retournée par le client. Dans certains cas, il est mieux de savoir combien de lignes une commande aurait retourné, sans la clause LIMIT, mais sans lancer à nouveau le calcul. Pour cela, ajoutez l'option SQL_CALC_FOUND_ROWS dans la commande SELECT, puis appelez FOUND_ROWS() après :

    mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
        -> WHERE id > 100 LIMIT 10;
    mysql> SELECT FOUND_ROWS();
    

    Le second SELECT retourne un nombre indiquant combien de lignes le premier SELECT aurait retourné s'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. Si la commande SELECT précédente n'inclut pas l'option SQL_CALC_FOUND_ROWS, alors FOUND_ROWS() pourrait retourner une valeur différente suivant que LIMIT est utilisé ou pas.

    SQL_CALC_FOUND_ROWS et FOUND_ROWS() peuvent être pratiques dans des situations où vous devez limiter le nombre de lignes que la requête retourne, mais que vous devez tout de même connaître le nombre de ligne total, sans exécuter une seconde requête. Un exemple classique est un script web qui présente des résultats de recherche. En utilisant FOUND_ROWS(), vous connaîtrez facilement le nombre de lignes de résultat.

    L'utilisation de SQL_CALC_FOUND_ROWS et FOUND_ROWS() est plus complexe pour les requêtes UNION que pour les commandes SELECT simples, car LIMIT peut intervenir plusieurs fois dans une commande UNION. Elle sera appliquée à différentes commandes SELECT de la commande UNION, ou globalement à l'UNION.

    Le but de SQL_CALC_FOUND_ROWS pour UNION est de retourner le nombre de lignes qui aurait été retourné sans la clause globale LIMIT. Les conditions d'utilisation de SQL_CALC_FOUND_ROWS avec UNION sont :

    • Le mot clé SQL_CALC_FOUND_ROWS doit apparaître dans le premier SELECT de l'UNION.

    • La valeur deFOUND_ROWS() est exactement la même que si UNION ALL était utilisé. Si UNION sans ALL est utilisé, des réductions de doublons surviendront, et la valeur de FOUND_ROWS() sera approximative.

    • Si aucune clause LIMIT n'est présente dans UNION, SQL_CALC_FOUND_ROWS est ignoré et retourne le nombre de lignes dans la table temporaire créé durant le traitement de l'UNION.

    SQL_CALC_FOUND_ROWS et FOUND_ROWS() sont disponibles à partir de la version 4.0.0 de MySQL.

  • LAST_INSERT_ID(), LAST_INSERT_ID(expr)

    Retourne le dernier identifiant automatiquement généré par une colonne AUTO_INCREMENT.

    mysql> SELECT LAST_INSERT_ID();
            -> 195
    

    Le dernier ID généré est conservé par le serveur pour chaque connexion. Un autre client ne la modifiera donc pas, même s'ils génèrent une autre valeur AUTO_INCREMENT de leur coté. Ce comportement permet de s'assurer que les actions des autres clients ne perturbe pas les actions du client en cours.

    La valeur de LAST_INSERT_ID() ne sera pas modifiée 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 à cela est que cela rend possible la reproduction facilement la même requête INSERT sur d'autres serveurs.

    Si vous utilisez une commande INSERT IGNORE et que la ligne est ignorée, le compteur AUTO_INCREMENT sera malgré tout incrémenté, et LAST_INSERT_ID() retournera une nouvelle valeur.

    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);
      
    • Utilisez cette table pour générer des séquences de nombre comme ceci :

      mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
      mysql> SELECT LAST_INSERT_ID();
      

      La commande UPDATE incrémente le compteur de séquence, et fait que le prochain appel à LAST_INSERT_ID() va retourner une valeur différente. La commande SELECT lit cette valeur. La fonction C mysql_insert_id() peut aussi être utilisée pour lire la valeur. See Section 24.2.3.33, « mysql_insert_id() ».

    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.

  • SESSION_USER()

    SESSION_USER() est un synonyme de USER().

  • SYSTEM_USER()

    SYSTEM_USER() est un synonyme de USER().

  • USER()

    Retourne le nom d'utilisateur et le nom d'hôte courant MySQL :

    mysql> SELECT USER();
            -> 'davida@localhost'
    

    La valeur indique le nom d'utilisateur qui a été spécifié lors de l'identification avec le serveur MySQL, et l'hôte client avec lequel il est connecté.

    Avant la version 3.22.11, la fonction ne retournait pas le nom d'hôte. Vous pouvez extraire le nom d'utilisateur sans l'hôte avec la commande suivante :

    mysql> SELECT SUBSTRING_INDEX(USER(),'@',1);
            -> 'davida'
    

    Depuis MySQL version 4.1, USER() retourne la valeur au format utf8. Assurez vous que la chaîne '@' est bien interprétée dans ce jeu de caractères :

    mysql> SELECT SUBSTRING_INDEX(USER(),_utf8'@',1);
            -> 'davida'
    
  • VERSION()

    Retourne une chaîne indiquant la version courante du serveur MySQL :

    mysql> SELECT VERSION();
            -> '4.1.2-alpha-log'
    

    Notez que si votre version se termine par -log, cela signifie que le système d'historique est actif.

12.8.4 Fonctions diverses

  • 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'
    
  • 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().

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

    Depuis MySQL 4.1.2, INET_ATON() comprend aussi les IP courtes :

    mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
            -> 2130706433, 2130706433
    

    INET_ATON() a été ajouté en MySQL 3.23.15.

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

  • IS_USED_LOCK(str)

    Vérifie si le verrou appelé str est actuellement posé ou pas. Si c'est le cas, la fonction retourne l'identifiant de connexion qui a le verrou. Sinon, elle retourne NULL.

    IS_USED_LOCK() a été ajouté en MySQL version 4.1.0.

  • 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 réplication. 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 réplications.

  • 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 13.1.2, « Syntaxe de DO ».

  • UUID()

    Retourne un Universal Unique Identifier (UUID) généré grâce à ``DCE 1.1: Remote Procedure Call'' (Appendix A) CAE (Common Applications Environment) Specifications, publié par le The Open Group en octobre 1997 (Document numéro C706).

    Un UUID est con¸u comme un numéro qui est globalement unique dans l'espace, et le temps. Deux appels à UUID() sont supposés générer deux valeurs différentes, même si ces appels sont faits sur deux ordinateurs séparés, qui ne sont pas connectés ensembles.

    Un UUID est un nombre de 128 bits, représenté par une chaîne de 5 nombres hexadécimaux, au format aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee :

    • Les trois premiers nombres sont générés à partir d'un timestamp.

    • Le quatrième nombre préserver l'unicité temporelle si le timestamp perd sa monotonie (par exemple, à cause du changement d'heure d'hiver/été).

    • Le cinquième nombre est un nombre IEEE 802 qui fournit l'unicité. Un nombre aléatoire est utilisé la si ce dernier n'est pas disponible (par exemple, comme l'hôte n'a pas de carte Ethernet, nous ne savons pas comment trouver une adresse matériel sur le système d'exploitation). Dans ce cas, l'unicité spatiale ne peut être garantie. Néanmoins, une collision aura une très faible propriété.

      Actuellement, l'adresse MAC est une interface utilisée sur FreeBSD et Linux. Sur les autres systèmes d'exploitation, MySQL génère un nombre aléatoire de 48 bits.

    mysql> SELECT UUID();
            -> '6ccd780c-baba-1026-9564-0040f4311e29'
    

    Notez que UUID() ne fonctionne pas encore avec la réplication.

    UUID() a été ajoutée en MySQL 4.1.2.

12.9 Fonctions et options à utiliser dans les clauses GROUP BY

12.9.1 Fonctions avec GROUP BY

Si vous utilisez les fonctions de groupement avec une requête ne contenant pas de clause GROUP BY, cela revient à grouper toutes les lignes.

  • AVG(expr)

    Retourne la moyenne de l'expression expr :

    mysql> SELECT student_name, AVG(test_score)
        ->        FROM student
        ->        GROUP BY student_name;
    
  • BIT_AND(expr)

    Retourne la combinaison AND bit à bit de expr. Le calcul est fait en précision de 64 bits (BIGINT).

    Depuis MySQL 4.0.17, cette fonction retourne 18446744073709551615 s'il n'y avait pas de lignes. (C'est un entier BIGINT non-signé, dont tous les bits sont à 1.) Avant 4.0.17, la fonction retournait -1 s'il n'y avait pas de ligne trouvées.

  • BIT_OR(expr)

    Retourne la combinaison OR bit à bit de expr. Le calcul est fait en précision de 64 bits (BIGINT).

    Cette fonction retourne 0 s'il n'y a pas de ligne à traiter.

  • BIT_XOR(expr)

    Retourne la combinaison XOR bit à bit de expr. Le calcul est fait en précision de 64 bits (BIGINT).

    Cette fonction retourne 0 s'il n'y a pas de ligne à traiter.

    Cette fonction est disponible depuis MySQL 4.1.1.

  • COUNT(expr)

    Retourne le nombre de valeurs non-NULL dans les lignes lues par la commande SELECT :

    mysql> SELECT student.student_name,COUNT(*)
        ->        FROM student,course
        ->        WHERE student.student_id=course.student_id
        ->        GROUP BY student_name;
    
    

    COUNT(*) est un peu différente dans son action, car elle retourne le nombre de lignes, même si elles contiennent NULL.

    COUNT(*) est optimisée pour retourner très rapidement un résultat si SELECT travaille sur une table, qu'aucune autre colonne n'est lue, et qu'il n'y a pas de clause WHERE. Par exemple :

    mysql> SELECT COUNT(*) FROM student;
    

    Cette optimisation s'applique uniquement pour les tables MyISAM et ISAM, car un compte exact du nombre de lignes est stocké pour ces types de tables, et il peut être lu très rapidement. Pour les moteurs de tables transactionnels, (InnodB, BDB), le stockage de cette valeur est plus problématique, car plusieurs transactions peuvent survenir en même temps, et affecter toutes ce compte.

  • COUNT(DISTINCT expr,[expr...])

    Retourne le nombre de valeurs non-NULL distinctes :

    mysql> SELECT COUNT(DISTINCT results) FROM student;
    

    Avec MySQL, vous pouvez lire le nombre d'expression distinctes qui ne contiennent pas NULL, en pla¸ant ici une liste d'expression. Avec SQL-99, vous devriez faire une concaténation de toutes les expressions dans COUNT(DISTINCT ...).

  • GROUP_CONCAT(expr)

    Syntaxe complète :

    GROUP_CONCAT([DISTINCT] expr [,expr ...]
                 [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
                 [SEPARATOR str_val])
    

    Cette fonction a été ajoutée en MySQL version 4.1. Elle retourne la chaîne résultant de la concaténation de toutes les valeurs du groupe :

    mysql> SELECT student_name,
        ->        GROUP_CONCAT(test_score)
        ->        FROM student 
        ->        GROUP BY student_name;
    

    ou :

    mysql> SELECT student_name,
        ->        GROUP_CONCAT(DISTINCT test_score
        ->                     ORDER BY test_score DESC SEPARATOR " ")
        ->        FROM student
        ->        GROUP BY student_name;
    

    Avec MySQL, vous pouvez obtenir la concaténation d'une série d'expressions. Vous pouvez éliminer les doublons en utilisant DISTINCT. Si vous voulez trier les valeurs du résultat, il faut utiliser ORDER BY. Pour trier en ordre inverse, ajoutez le mot clé DESC (descendant) au nom de la colonne que vous triez dans la clause ORDER BY. Par défaut, l'ordre est ascendant. Cela peut être spécifié explicitement avec le mot clé ASC. SEPARATOR est une chaîne qui sera insérée entre chaque valeur du résultat. La valeur par défaut est une virgule ‘","’. vous pouvez supprimer le séparateur en spécifiant la chaîne vide SEPARATOR "".

    Vous pouvez donner une taille maximale à la variable group_concat_max_len de votre configuration. La syntaxe pour faire cela durant l'exécution est :

    SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
    

    Si une taille maximale a été atteinte, le résultat sera tronqué à cette taille maximale.

    Note : il y a encore de petites limitations pour GROUP_CONCAT() lorsqu'il faut utiliser des valeurs DISTINCT avec ORDER BY et et en utilisant les valeurs BLOB. Voyez Section 1.5.7.4, « Bugs connus / limitations de MySQL ».

    GROUP_CONCAT() a été ajoutée en MySQL 4.1.

  • MIN(expr), MAX(expr)

    Retourne le minimum ou le maximum de expr. MIN() et MAX() peuvent prendre des chaînes comme argument : dans ce cas, elles retournent la valeur minimale ou maximale de la valeur de la chaîne. See Section 7.4.5, « Comment MySQL utilise les index ».

    mysql> SELECT student_name, MIN(test_score), MAX(test_score)
        ->        FROM student
        ->        GROUP BY student_name;
    

    Actuellement, MIN(), MAX() et d'autres fonctions d'agrégation MySQL, le serveur compare les valeurs de type ENUM et SET avec leur valeur de chaîne, et non pas leur position relative dans l'ensemble. Ce sera corrigé.à

  • STD(expr), STDDEV(expr)

    Retourne la déviation standard de expr (la racine carrée de la VARIANCE(). Ceci est une extension au standard SQL 99. La forme STDDEV() de cette fonction est fournie pour assurer la compatibilité Oracle.

  • SUM(expr)

    Retourne la somme de expr. Notez que si le résultat ne contient pas de ligne, cette fonction retournera NULL.

  • VARIANCE(expr)

    Retourne la variance standard de l'expression expr (en considérant que les lignes forment une population totale, et non pas un échantillon. Le nombre de ligne est le dénominateur. C'est une extension à la norme SQL-99 (disponible en version version 4.1 ou plus récent).

12.9.2 Options de GROUP BY

Depuis MySQL 4.1.1, la clause GROUP BY permet l'utilisation de l'option WITH ROLLUP qui fait que des lignes supplémentaires seront ajoutées lors de regroupements. Ces lignes représentent des regroupements de haut niveau (ou des super-agrégats). ROLLUP vous permet de répondre simultanément à plusieurs niveaux d'analyse avec une seule requête. Il peut être utilisée, par exemple, pour supporter des opérations OLAP (Online Analytical Processing).

Voici une illustration. Supposons que vous ayez une table de ventes sales, avec des colonnes pour l'année year, le pays country, le produit product et le profit profit :

CREATE TABLE sales
(
    year    INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(32) NOT NULL,
    profit  INT
);

Le contenu de cette table peut être agrégé par année avec la clause GROUP BY :

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
+------+-------------+

Cette requête affiche le profit par année, mais si vous voulez déterminer le profit total de toutes les années, vous devez ajouter ces valeurs vous-mêmes, ou faire une autre requête.

Ou alors, vous pouvez utiliser la clause ROLLUP, qui fournit les deux niveaux d'analyse dans la même requête. En ajoutant l'option WITH ROLLUP à la clause GROUP BY, la requête va produire une autre ligne, avec le grand total de toutes les années :

mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;
+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 |        4525 |
| 2001 |        3010 |
| NULL |        7535 |
+------+-------------+

La ligne du grand total est identifiée par la valeur NULL dans la colonne year.

ROLLUP a des effets plus complexes lorsqu'il y a plusieurs colonnes dans la clause GROUP BY. Dans ce cas, il a y un changement de valeur pour toutes sauf la dernière colonne de groupement, et la requête va produire les super-agrégats.

Par exemple, sans la clause ROLLUP, le résumé des ventes de la table sales basé sur l'année year, le pays country et le produit product peut ressembler à ceci :

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2001 | Finland | Phone      |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
+------+---------+------------+-------------+

Le résultat indique les valeurs résumées pour chaque triplet année/pays/produit. Si nous ajoutons la clause ROLLUP, la requête produit plusieurs nouvelles lignes :

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

Pour cette requête, ajouter ROLLUP fait que la requête ajoute les résumés de quatre niveaux d'analyse, et non pas un seul. Voici comment interpréter le résultat de la clause ROLLUP :

  • Après chaque jeu de ligne sur les produits, pour une année et un pays donnée, un résumé est ajouté, indiquant le total de tous les produits. Ces lignes voient leur colonne product contenir la valeur NULL.

  • Après chaque jeu de ligne couvrant une année particulière, une nouvelle ligne est ajoutée pour afficher le total de tous les pays et produits, pour cette année la. Ces lignes voient leurs colonnes country et products contenir NULL.

  • Finalement, suivant toutes les autres lignes, un résumé général est produit, avec le grand total de toutes les années, pays et produits. Cette ligne contient la valeur NULL pour toutes les colonnes year, country et products.

Autres considérations avec ROLLUP

Voici quelques comportements spécifiques de MySQL et son implémentation de ROLLUP:

Lorsque vous utilisez ROLLUP, vous ne pouvez pas utiliser de clause ORDER BY pour trier les résultats. En d'autres termes, ROLLUP et ORDER BY sont mutuellement exclusives. Toutefois, vous avec toujours le contrôle sur l'ordre de tri avec la clause GROUP BY. Vous pouvez utiliser explicitement les mots ASC et DESC avec les colonnes listées dans GROUP BY pour spécifier les ordres de tri des colonnes individuelles. Les lignes de résumés de ROLLUP apparaissent toujours après les lignes pour lesquelles ils sont calculés, quelque soit le tri.

La clause LIMIT peut être utilisée pour restreindre le nombre de lignes retournées au client. LIMIT s'applique après ROLLUP, et la limite s'appliquera aux lignes ajoutées par ROLLUP. Par exemple :

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;
+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

Notez qu'utiliser LIMIT avec ROLLUP peut conduire à des résultats plus difficiles à interpréter, car vous avez moins de contexte pour comprendre les résumés.

Les indicateurs NULL de chaque super-agrégat sont produits lorsque la ligne est envoyée au client. Le serveur recherche les colonnes citées dans la clause GROUP BY, en les prenant la plus à gauche, dont la valeur change. Toute colonne du jeu de résultat dont le nom ne correspond pas lexicalement à un de ces noms, verra sa valeur être NULL. Si vous spécifiez un groupement par numéro de colonne, le serveur identifiera aussi les colonnes qui devront recevoir NULL.

Comme les valeurs NULL des résumés sont placées dans le résultat aussi tard durant le traitement de la requête, nous ne pouvons pas les tester comme étant des valeurs NULL provenant de la requête elle-même. Par exemple, vous ne pourrez pas ajouter HAVING product IS NULL pour éliminer certains résumés qui ne vous intéressent pas.

D'un autre coté, les valeurs NULL apparaissent comme des valeurs NULL du coté du client, et peuvent être repérées en tant que telles par le client MySQL.

12.9.3 GROUP BY avec les champs cachés

MySQL a étendu l'utilisation de la clause GROUP BY. Vous pouvez utiliser des colonnes ou des calculs de l'expression SELECT qui n'apparaissent pas dans la clause GROUP BY. Cela se dit n'import quelle valeur pour ce groupe. Vous pouvez utiliser cela pour améliorer les performances en évitant les tris ou les regroupements inutiles de valeurs. Par exemple, vous n'avez pas besoin de faire un regroupement par nom de client customer.name dans la requête suivante :

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

En SQL standard, vous devriez ajouter la colonne customer.name à la clause GROUP BY. Avec MySQL, ce nom est redondant si vous n'utilisez pas le mode ANSI.

N'utilisez pas cette fonctionnalité si les colonnes que vous omettez dans la clause GROUP BY ne sont pas unique dans le groupe!! Vous auriez des résultats inattendus!

Dans certains cas, vous pouvez utiliser MIN() et MAX() pour obtenir une valeur spécifique d'une colonne, même si cette valeur n'est pas unique. L'exemple suivant donne la valeur de la colonne column issue de la ligne contenant la plus petit valeur de la colonne sort :

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

See Section 3.6.4, « La ligne contenant la plus grande valeur d'un certain champ par rapport à un groupe ».

Notez que si vous utilisez MySQL version 3.22 ou plus ancien, ou si vous essayez de suivre la norme SQL-99, vous ne pouvez pas utiliser les expressions dans GROUP BY ou ORDER BY. Vous pouvez contourner cette limitation en utilisant un alias pour l'expression :

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

En MySQL version 3.23, vous pouvez faire :

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