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.
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
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
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 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 enBINARY
; - 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[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
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.
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
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[2] : IF(condition, siVraie, siFausse);
.
Exemple : SELECT IF(-1 < 0, 0, 1);
renvoie 0.
Exemple avec plusieurs conditions (switch)[3][4] :
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[5] :
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
- ↑ https://docs.oracle.com/cd/E17952_01/mysql-5.0-en/string-comparison-functions.html
- ↑ http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
- ↑ https://dev.mysql.com/doc/refman/5.7/en/if.html
- ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html
- ↑ https://dev.mysql.com/doc/refman/5.7/en/case.html