MySQL/Opérateurs

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.

NULL

Fonctions

Opérateurs d'assignation

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

Égalité

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

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 vaisseau spatial : <=>, 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

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

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éfini 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é 	Ã©tÃ© 	├®t├® 	0 	0

Le meilleur interclassement pour les slugs est utf8_bin^[1], car insensible à la casse.

BETWEEN

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

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

Booléens logiques

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

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

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

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

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

Addition

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

Soustraction

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

Multiplication

 SELECT 1 * 1 -- 1

Divisions

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

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

ROUND()

Pour arrondit en définissant le nombre de chiffres après la virgule. Ex :

 SELECT ROUND(10 / 3, 2) # 3.33

Opérateurs de texte

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

L'opérateur LIKE si la chaîne 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).

À 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^[2] :

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

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.

Opérateur bit à bit

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

-- Non logique bit à bit :
SELECT ~0  -- 18446744073709551615
SELECT ~1  -- 18446744073709551614

-- Et logique bit à bit :
SELECT 1 & 1  -- 1
SELECT 1 & 3  -- 1
SELECT 2 & 3  -- 2

-- Ou logique bit à bit :
SELECT 1 | 0  -- 1
SELECT 3 | 0  -- 3
SELECT 4 | 2  -- 6

-- Ou exclusif bit à bit :
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

IF

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^[3] : IF(condition, siVraie, siFausse);.

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

Exemple avec plusieurs conditions (switch)^[4]^[5] :

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

CASE

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^[6] :

   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

Précédence des opérateurs

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

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

[1] ttps://www.bennadel.com/blog/3918-performing-a-case-sensitive-search-on-a-case-insensitive-column-using-collate-in-mysql-5-6-49.htm

[2] ttps://docs.oracle.com/cd/E17952_01/mysql-5.0-en/string-comparison-functions.html

[3] ttp://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

[4] ttps://dev.mysql.com/doc/refman/5.7/en/if.html

[5] ttps://dev.mysql.com/doc/refman/5.7/en/case.html

[6] ttps://dev.mysql.com/doc/refman/5.7/en/case.html

[1]

[2]

[3]

[4]

[5]

[6]