MySQL/Opérateurs

Un livre de Wikilivres.
Sauter à la navigation Sauter à la recherche


MySQL propose plus que les standards des opérateurs SQL. Ils peuvent être utilisés pour rédiger des expressions contenant des constantes, variables, valeurs contenues dans des champs ou autres expressions.

Opérateurs d'assignation[modifier | modifier le wikicode]

L'opérateur = peut assigner une valeur à une colonne :

 UPDATE `table1` SET `champ1`=0

Par contre pour assigner une valeur à une variable, l'opérateur est :=, car = est déjà utilisé pour la comparaison.

 SELECT @variable1 := 1

SELECT INTO peut aussi remplir les variables.

 SELECT 1 INTO @variable1

Opérateurs de comparaison[modifier | modifier le wikicode]

Égalité[modifier | modifier le wikicode]

Pour vérifier si deux valeurs sont égales, utiliser = :

 SELECT True = True  -- 1
 SELECT True = False -- 0

Pour vérifier si deux valeurs sont différentes, c'est <> ou != :

 SELECT True <> False -- 1
 SELECT True != True  -- 0


Comparaison IS NULL[modifier | modifier le wikicode]

Pour savoir si une valeur est nulle, utiliser IS :

 SELECT (NULL = NULL)  -- NULL
 SELECT (NULL IS NULL) -- 1
 SELECT (1 IS NULL)    -- 0
 SELECT (True IS True) -- erreur

Pour savoir si une valeur n'est pas nulle :

 SELECT (True IS NOT NULL) -- 1

Il existe par ailleurs l'opérateur <=> qui considère NULL comme une valeur normale :

 SELECT NULL <=> NULL -- 1
 SELECT True <=> True -- 1
 SELECT col1 <=> col2 FROM table1

Comparaison IS booléen[modifier | modifier le wikicode]

IS et IS NOT fonctionnent aussi avec TRUE, FALSE et UNKNOWN (qui est purement un synonyme de NULL).

 SELECT 1 IS TRUE      -- 1
 SELECT 1 IS NOT TRUE  -- 0
 SELECT 1 IS FALSE     -- 0
 SELECT (NULL IS NOT FALSE)    -- 1 : unknown n'est pas false
 SELECT (NULL IS UNKOWN)       -- 1
 SELECT (NULL IS NOT UNKNOWN)  -- 0

Plus grand et plus petit que[modifier | modifier le wikicode]

Avec des nombres :

 SELECT 100 > 0  -- 1
 SELECT 4 > 5    -- 0

 SELECT 1 < 2    -- 1
 SELECT 2 < 2    -- 0

Avec du texte dans l'ordre alphabétique :

 SELECT 'a' < 'b'  -- 1
 SELECT `a` >= `b` FROM `table1`
 SELECT NOT (`a` < `b`) FROM `table1`
 SELECT `a` <= `b` FROM `table1`
 SELECT * FROM `table1` WHERE `a` >= `b`

Cet ordre alphabétique est définit par COLLATION (l'interclassement), pour un CHARACTER SET donné. Par exemple, une COLLATION peut être sensible à la casse ou pas (suffixe utf8_general_cs = case sensitive, utf8_general_ci = case insensitive).

Exemple :

SELECT _latin1'été', _utf8'été', _cp850'été', (_latin1'été' = _utf8'été'), (_latin1'été' LIKE _utf8'été')
-- Résultat :
é 	é© 	├®t├® 	0 	0

BETWEEN[modifier | modifier le wikicode]

l’opérateur BETWEEN ... AND ... permet de vérifier si une valeur appartient à une plage (bornes incluses) :

 SELECT 2 BETWEEN 10 AND 100    -- 0
 SELECT 10 BETWEEN 10 AND 100   -- 1
 SELECT 20 BETWEEN 10 AND 100   -- 1

 SELECT 8 NOT BETWEEN 5 AND 10  -- 0

IN[modifier | modifier le wikicode]

IN permet de s'assurer si une valeur est dans une liste :

 SELECT 5 IN (5, 6, 7)  -- 1
 SELECT 1 IN (5, 6, 7)  -- 0
 SELECT 1 NOT IN (1, 2, 3)  -- 0

Attention : si la liste contient des nombres et des chaines, il faut tout mettre entre apostrophe pour obtenir le résultat escompté.

 SELECT 4 IN ('a', 'z', '5')

Il n'y a aucune limite théorique au nombre de valeurs de la liste.

Opérateurs logiques[modifier | modifier le wikicode]

Booléens logiques[modifier | modifier le wikicode]

MySQL n'a pas vraiment de type BOOLEAN.

FALSE est un synonyme de 0. Les chaines vides sont considérées FALSE.

TRUE est un synonyme de 1. Tout ce qui n'est ni FALSE, ni NULL est considéré TRUE.

UNKNOWN est un synonyme de NULL. La date spéciale 0/0/0 est nulle.

NOT[modifier | modifier le wikicode]

NOT est le seul opérateur qui n'a qu'une seule opérande. Il renvoie 0 si l'opérande est TRUE, 1 si elle est FALSE, et NULL si elle est NULL.

 SELECT NOT 1       -- 0
 SELECT NOT FALSE   -- 1
 SELECT NOT NULL    -- NULL
 SELECT NOT UNKNOWN -- NULL

! est synonyme de NOT.

AND[modifier | modifier le wikicode]

AND renvoie 1 si les deux opérandes sont TRUE, sinon 0 ; si au moins l'une des deux opérandes est nulle, il renvoie NULL.

 SELECT 1 AND 1      -- 1
 SELECT 1 AND ''     -- 0
 SELECT '' AND NULL  -- NULL

&& est synonyme de AND.

OR[modifier | modifier le wikicode]

OR renvoie TRUE si au moins une des opérandes est TRUE, sinon FALSE ; si les deux opérandes sont nulles, il renvoie NULL.

 SELECT TRUE OR FALSE   -- 1
 SELECT 1 OR 1          -- 1
 SELECT FALSE OR FALSE  -- 0
 SELECT NULL OR TRUE    -- NULL

|| est un synonyme de OR.

XOR[modifier | modifier le wikicode]

XOR (ou exclusif) renvoie :

  • 1 si une seule des deux opérandes est TRUE et l'autre FALSE.
  • 0 si les deux sont TRUE ou FALSE.
  • NULL si au moins l'une des deux est NULL.
 SELECT 1 XOR 0         -- 1
 SELECT FALSE XOR TRUE  -- 1
 SELECT 1 XOR TRUE      -- 0
 SELECT 0 XOR FALSE     -- 0
 SELECT NULL XOR 1      -- NULL

Opérateurs arithmétiques[modifier | modifier le wikicode]

Addition[modifier | modifier le wikicode]

 SELECT +1 -- 1
 SELECT 1 + 1 -- 2

Soustraction[modifier | modifier le wikicode]

 SELECT -1 -- -1
 SELECT -+1 -- -1
 SELECT --1 -- 1
 SELECT True - 1 -- 0

Multiplication[modifier | modifier le wikicode]

 SELECT 1 * 1 -- 1

Divisions[modifier | modifier le wikicode]

Renvoie un nombre de type FLOAT :

 SELECT 10 / 2  -- 5,0000
 SELECT 1 / 1   -- 1,0000
 SELECT 1 / 0   -- NULL

Pour retourner la valeur entière du résultat d'une division sous forme de type INTEGER, utiliser DIV :

 SELECT 10 DIV 3 -- 3

Le reste de la division (modulo) se trouve avec '%' ou MOD :

 SELECT 10 MOD 3 -- 1

Utiliser + pour convertir des données[modifier | modifier le wikicode]

Pour convertir un INTEGER en FLOAT :

 SELECT 1 + 0.0       -- 1.0
 SELECT 1 + 0.000     -- 1.000
 SELECT TRUE + 0.000  -- 1.000

Il est impossible de convertir une valeur FLOAT en ajoutant 0.0, mais on peut forcer le type en INTEGER :

 SELECT '1' + 0       -- 1
 SELECT '1' + FALSE   -- 1
 SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki>  -- 0

Opérateurs de texte[modifier | modifier le wikicode]

Il n'y a pas d'opérateurs de concaténation en MySQL. Les opérateurs arithmétiques convertissent les valeurs en nombres et pour leurs opérations, donc la concaténation avec + est impossible.

La fonction CONCAT() pallie à cela.

LIKE[modifier | modifier le wikicode]

L'opérateur LIKE si la chaine recherchée est inclue dans une colonne :

 SELECT * FROM articles WHERE titre LIKE 'hello world'

Généralement cette chaine est sensible à la casse, mais il y a deux exceptions, quand :

  • une comparaison LIKE touche une colonne déclarée en BINARY ;
  • l'expression contient une clause BINARY :
 SELECT * 'test' LIKE BINARY 'TEST'  -- 0

Les comparaisons LIKE acceptent deux caractères spéciaux :

  • _ : n'importe quel caractère (un seul, ni zéro ni deux).
  • % : n'importe quel séquence de caractères (par exemple zéro ou mille).

A noter que dans les expressions LIKE, \ est aussi le caractère d'échappement pour ', et son comportement ne peut pas être changé par la clause ESCAPE. Il peut aussi échapper d'autres caractères, mais pas lui-même.

Utilisations courantes de LIKE :

  • Trouver tous les titres commençant par "hello" :
 SELECT * FROM articles WHERE titre LIKE 'hello%'
  • Trouver tous les titres finissant par "world" :
 SELECT * FROM articles WHERE titre LIKE '%world'
  • Trouver tous les titres contenant la chaine "gnu" :
 SELECT * FROM articles WHERE titre LIKE '%gnu%'

Ces caractères spéciaux peuvent être contenus dans le pattern lui-même. Par exemple, pour rechercher les symboles _ ou % dans la base :

 SELECT * FROM articles WHERE titre LIKE '\_%'
 SELECT * FROM articles WHERE titre LIKE '\%%'

/ peut-être une alternative à \ si on le précise :

 SELECT * FROM articles WHERE titre LIKE '/_%' ESCAPE '/'

Quand on utilise l'opérateur =, les espaces des chaines sont ignorés, mais avec LIKE ils sont reconnus :

 SELECT 'word' = 'word ';     -- 1
 SELECT 'word' LIKE 'word ';  -- 0

De même, contrairement à "=", "LIKE" compare uniquement les caractères, même si leurs règles d'interclassement les regroupent[1] :

SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;     -- 1
SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;  -- 0

LIKE fonctionne aussi avec les nombres :

 SELECT 123 LIKE '%2%'  -- 1

Pour tester si un pattern ne fonctionne pas alors qu'il devrait, utiliser NOT LIKE :

 SELECT 'a' NOT LIKE 'b' -- 1

SOUNDS LIKE[modifier | modifier le wikicode]

SOUNDS LIKE permet de vérifier si deux textes se prononcent pareils. Il utilise l'algorithme SOUNDEX, basé sur les règles de l'anglais, et peut s'avérer assez approximatif :

 SELECT `word1` SOUNDS LIKE `word2` FROM `wordList`          -- forme courte
 SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList`  -- forme longue

SOUNDS LIKE est une extension apparue depuis MySQL 4.1.

Expressions régulières[modifier | modifier le wikicode]

Expressions rationnelles courantes
Caractère Type Explication
. Point n'importe quel caractère
[...] crochets classe de caractères : tous les caractères énumérés dans la classe
[^...] crochets et circonflexe classe complémentée : tous les caractères sauf ceux énumérés
^ circonflexe marque le début de la chaine, la ligne...
$ dollar marque la fin d'une chaine, ligne...
| barre verticale alternative - ou reconnaît l'un ou l'autre
(...) parenthèses groupe de capture : utilisée pour limiter la portée d'un masque ou de l'alternative
* astérisque 0, 1 ou plusieurs occurrences
+ le plus 1 ou plusieurs occurrences
? interrogation 0 ou 1 occurrence
Classes de caractères POSIX[2]
Classe Signification
[[:alpha:]] n'importe quelle lettre
[[:digit:]] n'importe quel chiffre
[[:xdigit:]] caractères hexadécimaux
[[:alnum:]] n'importe quelle lettre ou chiffre
[[:space:]] n'importe quel espace blanc
[[:punct:]] n'importe quel signe de ponctuation
[[:lower:]] n'importe quelle lettre en minuscule
[[:upper:]] n'importe quelle lettre capitale
[[:blank:]] espace ou tabulation
[[:graph:]] caractères affichables et imprimables
[[:cntrl:]] caractères d'échappement
[[:print:]] caractères imprimables exceptés ceux de contrôle
Expressions rationnelles Unicode[3]
Expression Signification
\A Début de chaine
\b Caractère de début ou fin de mot
\d Chiffre
\D Non chiffre
\s Caractères espace
\S Non caractères espace
\w Lettre, chiffre ou underscore
\W Caractère qui n'est pas lettre, chiffre ou underscore
\X Caractère Unicode
\z Fin de chaine

Débogueur : https://regex101.com/

En MySQL 5.1, les expressions régulières fonctionnent sur des textes en octets et peuvent donc donner des résultats inattendus avec des textes en Unicode[4].

Syntaxe :

 SELECT 'string' REGEXP 'pattern'

RLIKE est synonyme de REGEXP.

L'antislash fait office de caractère d'échappement.

Exemple, est-ce que la sélection est différente des lettres de A à Z :

 SELECT 'a' REGEXP '^[a-z]';  -- 1
 SELECT 'A' REGEXP '^[a-z]';  -- 1
 SELECT '1' REGEXP '^[a-z]';  -- 0


Got error 'invalid character range'[modifier | modifier le wikicode]

L'utilisation de \- est parfois proscrite.

Opérateur bit à bit[modifier | modifier le wikicode]

Il existe des opérateurs pour les opérations bit à bit.

Bit-NOT :

 SELECT ~0  -- 18446744073709551615
 SELECT ~1  -- 18446744073709551614

Bit-AND :

 SELECT 1 & 1  -- 1
 SELECT 1 & 3  -- 1
 SELECT 2 & 3  -- 2

Bit-OR :

 SELECT 1 | 0  -- 1
 SELECT 3 | 0  -- 3
 SELECT 4 | 2  -- 6

Bit-XOR :

 SELECT 1 ^ 0  -- 1
 SELECT 1 ^ 1  -- 0
 SELECT 3 ^ 1  -- 2

Décalage de bit à gauche :

 SELECT 1 << 2  -- 4

Décalage de bit à droite :

 SELECT 1 >> 2  -- 0

Conditions[modifier | modifier le wikicode]

IF[modifier | modifier le wikicode]

La structure IF ... THEN ... ELSE ... END IF; ne fonctionne que dans les procédures stockées (contenant plusieurs requêtes). Pour gérer une condition en dehors d'elles, on peut utiliser[5] : IF(condition, siVraie, siFausse);.

Exemple : SELECT IF(-1 < 0, 0, 1); renvoie 0.

Exemple avec plusieurs conditions (switch)[6][7] :

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

CASE[modifier | modifier le wikicode]

SELECT CASE WHEN condition THEN siVraie ELSE siFausse END;

Exemple : SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; renvoie 0.

Exemple avec plusieurs conditions[8] :

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;

Dans une seule requête :

SELECT CASE v
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    WHEN 4 THEN 'd'
    ELSE 0
    END as value

Précédence[modifier | modifier le wikicode]

Précédence des opérateurs[modifier | modifier le wikicode]

Du plus au moins prioritaire :

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

Modificateurs :

  • PIPES_AS_CONCAT : si activé, || est prioritaire sur ^, mais - et ~ le reste sur ||.
  • HIGH_NOT_PRECEDENCE : si activé, NOT est au niveau de !.

Utilisation des parenthèses[modifier | modifier le wikicode]

Tout comme en mathématiques, les parenthèses permettent d'évaluer des sous-expressions avant d'autres :

 SELECT 1 + 1 * 5    -- = 6
 SELECT (1 + 1) * 5  -- = 10

Cela peut aussi se faire pour rendre les requêtes plus lisibles aux humains :

 SELECT 1 + (2 * 5)

Références[modifier | modifier le wikicode]