MySQL/Fonctions
Syntaxe
modifierÀ l'instar des mots réservés SQL, les noms des fonctions ne sont pas sensibles à la casse :
SELECT database() -- ok
SELECT DataBase() -- ok
SELECT DATABASE() -- ok
Si le SQL_MODE IGNORE_SPACE SQL_MODE n'est pas défini, il est impossible de placer un espace entre le no de la fonction et la première parenthèse, sous peine de voir une erreur 1064. IGNORE_SPACE est généralement à 0, car cela accélère le parseur. Donc :
SELECT DATABASE () -- déconseillé
SELECT DATABASE() -- recommandé
Toutefois, cette restriction ne s'applique qu'aux fonctions natives de MySQL (pas aux procédures stockées).
Fonctions générales
modifierFonctions qui dépendent du type.
BENCHMARK(nombre, expression)
modifierExécute l'expression n fois et retourne toujours zéro[1], le chiffre pertinent est donc le temps pris par cette opération de simulation. Utile pour trouver les goulots d'étranglement des expressions SQL :
SELECT BENCHMARK(10000, 'Bonjour'); -- Traitement en 0.0010 sec
CAST(valeur AS type)
modifierRenvoie la valeur convertie en chaine de caractères, comme les apostrophes.
SELECT CAST(20130101 AS date); -- 2013-01-01
Exemple avec encodage[2] :
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
CHARSET(chaine)
modifierRenvoie le type de caractères de la chaine :
SELECT CHARSET(20130101); -- binary
SHOW CHARACTER SET; -- montre tous les CHARACTER SET installés
COALESCE(valeur, ...)
modifierRenvoie le premier paramètre non nul. S'ils sont tous nuls, renvoie NULL
.
SELECT COALESCE(null, 'Bonjour', null); -- bonjour
COERCIBILITY(chaine)
modifierRenvoie la coercibility d'une chaine (entre 0 et 5) :
SELECT COERCIBILITY('bonjour'); -- 4
Coercibility[3] | Signification | Exemple |
---|---|---|
0 | Explicit collation | Value with COLLATE clause |
1 | No collation | Concatenation of strings with different collations |
2 | Implicit collation | Column value |
3 | System constant | USER() return value |
4 | Coercible | Literal string |
5 | Ignorable | NULL or an expression derived from NULL |
COLLATION(chaine)
modifierRenvoie la collation d'une chaine :
SELECT COLLATION('bonjour'); -- utf8_general_ci
Pour obtenir celle par défaut d'une base :
SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'maBase1'
CONCAT()
modifierConcaténation sans séparateur :
SELECT CONCAT('Hello', 'World');
# HelloWorld
CONCAT_WS()
modifierConcaténation avec séparateur de son choix :
SELECT CONCAT_WS(', ', 'Hello', 'World');
# Hello, World
CONNECTION_ID()
modifierRenvoie l'identifiant du thread courant :
SELECT CONNECTION_ID(); -- 31
CONVERT(valeur, type)
modifierTout comme CAST()
, retourne la valeur convertie dans le type mentionné :
SELECT CONVERT (20130101, date); -- 2013-01-01
CONVERT(chaine USING charset)
modifierConvertit la chaine string passée dans le CHARACTER SET spécifié :
SELECT CONVERT ('Voici une écriture' USING utf8); -- Voici une écriture
SELECT CONVERT ('Voici une écriture' USING ASCII); -- Voici une ?criture
CURRENT_USER()
modifierRetourne les noms de l'utilisateur et de l'hôte courants :
SELECT CURRENT_USER(); -- root@localhost
DATABASE()
modifierRetourne le nom de la base de données courante :
SELECT DATABASE(); -- wiki1
FOUND_ROWS()
modifierAprès un SELECT
avec une LIMIT
et le mot clé SQL_CALC_FOUND_ROWS
, il est possible de lancer un autre SELECT
avec FOUND_ROWS()
. En effet il renvoie le nombre de ligne de la clause précédente, sans la limite :
SELECT FOUND_ROWS() AS n; -- 0
SELECT SQL_CALC_FOUND_ROWS * FROM wiki1_page ORDER BY page_id LIMIT 10 OFFSET 2; -- deux lignes
SELECT FOUND_ROWS() AS n; -- 1
GREATEST(valeur1, valeur2, ...)
modifierRenvoie la plus grande valeur des paramètres :
SELECT GREATEST(1, 2, 21, 3); -- 21
INTERVAL(valeur1, valeur2, valeur3, ...)
modifierRenvoie l'emplacement du premier argument supérieur au premier, en partant du zéro dans la liste des entiers en paramètres :
SELECT INTERVAL(10, 20, 9, 8, 7); -- 0
SELECT INTERVAL(10, 9, 20, 8, 7); -- 1
SELECT INTERVAL(10, 9, 8, 20, 7); -- 2
SELECT INTERVAL(10, 9, 8, 7, 20); -- 3
IF(valeur1, valeur2, valeur3)
modifierIf valeur1 est vraie, renvoie valeur2, sinon (fausse ou nulle) renvoie valeur3.
select if(1=2, 'irréel', 'réel'); -- réel
IFNULL(valeur1, valeur2)
modifierSi valeur1 est nulle, renvoie valeur2, sinon valeur1.
SELECT IFNULL('variable1', 'défaut'); -- variable1
ISNULL(valeur)
modifierSi la valeur passée est nulle, renvoie 1, sinon 0.
SELECT ISNULL('variable1'); -- 0
NULLIF(valeur1, valeur2)
modifierRenvoie NULL
si valeur1 = valeur2, sinon valeur1.
SELECT NULLIF(10, 20); -- 10
LAST_INSERT_ID()
modifierRenvoie le dernier ID en AUTO_INCREMENT inséré dans la base, ce qui évite un SELECT lorsque l'on a besoin d'insérer deux enregistrements dont la deuxième avec une clé étrangère vers la première.
LEAST(valeur1, valeur2, ...)
modifierRenvoie la plus petite valeur dans la liste des paramètres passés :
SELECT LEAST(1, 2, 21, 3, -1); -- -1
LENGTH(chaine)
modifierAffiche la taille d'une chaine en octets.
Pour avoir le nombre de caractères, utiliser CHAR_LENGTH
.
REPLACE(chaine, 'sous-chaine à remplacer', 'par')
modifierSELECT REPLACE('helloworld', 'o', ''); -- hellwrld
SUBSTR(chaine, début, taille)
modifierDécoupe une chaine de caractère :
SELECT SUBSTR('Hello World!', 7, 5); -- World
Son alias "substring()" fonctionne aussi.
SUBSTRING_INDEX
modifierDécoupe une chaine selon un séparateur. Le troisième paramètre désigne la énième occurrence de ce dernier :
SELECT SUBSTRING_INDEX('Hello World!', ' ', 1);
# Hello
SELECT SUBSTRING_INDEX('Hello World!', ' ', 2);
# Hello World!
Fonctions JSON
modifierMySQL (et MariaDB[4]) offrent plusieurs fonctions de manipulation de champ varchar contenant du JSON.
JSON_VALID
modifierRenvoie vrai si le champ est un JSON valide, faux sinon.
JSON_UNQUOTE
modifierRetire les guillemets au début et à la fin d'une chaine (pour l'utiliser dans un JSON sans qu'ils soient interprétés). Ex :
SELECT JSON_UNQUOTE('"1"');
-- 1
JSON_KEYS
modifierRenvoie toutes les clés du JSON en paramètre.
JSON_EXTRACT
modifierExtrait un morceau du JSON par sa clé. Ex :
SET @json = '{"1": "2", "3": "4"}';
SELECT JSON_EXTRACT(@json, '$.1');
-- "2"
Cette fonction est combinable avec JSON_UNQUOTE pour les recherches dans des données JSON[5].. Ex :
SELECT JSON_UNQUOTE(JSON_EXTRACT(varchar_avec_json,'$.ma_clé')) AS ma_clé FROM users;
JSON_SEARCH
modifierRetourne les clés correspondant à la valeur recherchée. Son deuxième paramètre peut être "one" pour la première clé, ou "all" pour toutes. Ex :
SET @json = '{"1": "2", "3": "4"}';
SELECT JSON_SEARCH(@json, 'one', '4');
-- "$.3"
JSON_VALUE
modifierRetourne la valeur à partir de la clé en paramètre.
JSON_CONTAINS
modifierRenvoie 1 si la JSON contient la valeur en paramètre, ou 0 sinon.
JSON_REMOVE
modifierRetire la paire clé/valeur de la clé passée en paramètre.
Date et heure
modifierIl existe des dizaines de fonctions liées aux dates[6].
Pour trouver la date de l'an dernier :
SELECT CURDATE() - INTERVAL 1 YEAR
Sélectionner toutes les pages du wiki non lues depuis plus un an :
SELECT * FROM wiki1_page
WHERE page_touched <= (CURDATE() - INTERVAL 1 YEAR);
Autres exemples de sélections :
SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
DATE_FORMAT(CURDATE(), '%Y-%m-15'),
DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM table;
SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10')
SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE())
SELECT columns FROM table
WHERE start_time >= '2004-06-01 10:00:00' AND end_time <= '2004-06-03 18:00:00'
SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'
SELECT Start_time, End_time FROM Table
WHERE Start_time >= NOW() - INTERVAL 4 HOUR
SELECT NOW() + INTERVAL 60 SECOND
SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00
convert('17/02/2016 15:49:03',datetime)
ou convert('17-02-2016 15:49:03',datetime)
donne null, donc une requête d'insertion le remplace par le même résultat que now()
. La syntaxe doit être convert('2016-02-17 15:49:03',datetime)
ou convert('2016/02/17 15:49:03',datetime)
.
DATE_ADD()
modifierPour additionner deux dates. Par exemple pour calculer le jour d'une livraison prenant 48 h :
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY)
Pour la date d'hier :
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
Les unités à additionner ou soustraire les plus courantes sont[7] :
SECOND MINUTE HOUR DAY WEEK MONTH YEAR
DATEDIFF()
modifierPour soustraire une date à une autre. Par exemple pour calculer un âge :
SELECT DATEDIFF(NOW(), birthday_date) / 365 FROM user WHERE ISNULL(birthday_date) = 0 AND birthday_date != '0000-00-00'
STR_TO_DATE()
modifierUn BETWEEN ou ">" de champ date avec une chaine de caractères ne marche pas. Il faut donc convertir la chaine en date :
SELECT * FROM my_table WHERE start_date > STR_TO_DATE('2024-08-10', '%Y-%m-%d')
Fonctions d'agrégation
modifierCOUNT(champ)
modifierSi le paramètre est "*" au lieu d'un nom de colonne, COUNT()
renvoie les nombre de lignes total de la requête. Cela peut permettre de savoir combien de lignes possède une table, par exemple le nombre de pages d'un wiki :
SELECT COUNT(*) FROM `wiki1_page`;
Si le mot DISTINCT
est employé, cela ignore les doublons :
SELECT COUNT(DISTINCT id) FROM `wiki1_page`;
Si le nom d'un champ est précisé, cela renvoie le nombre de valeurs non nulles :
SELECT COUNT(`user_real_name`) FROM `wiki1_user`;
SELECT COUNT(DISTINCT `user_real_name`) FROM `wiki1_user`;
Cela fonctionne aussi pour des expressions, des combinaisons de champs :
SELECT COUNT(`user_name` + `user_real_name`) FROM `wiki1_user`;
Pour afficher le décompte de plusieurs tables non jointes :
SELECT
(SELECT COUNT(*) FROM maTable1) as t1,
(SELECT COUNT(*) FROM maTable2) as t2
MAX(champ)
modifierMAX()
renvoie la valeur maximum d'une expression issue du résultat d'une requête, ou NULL
s'il n'y en a pas :
SELECT MAX(`user_editcount`) FROM `wiki1_user`;
SELECT MAX(LENGTH(CONCAT(`user_name`, ' ', `user_real_name`))) FROM `wiki1_user`;
Alternatives
modifierSelon le contexte, la fonction MAX()
n'est pas toujours la meilleure option pour obtenir un maximum. Par exemple en cas de sous-requêtes ou sans agrégation possible :
SELECT `user_editcount` FROM `wiki1_user` ORDER BY user_editcount DESC LIMIT 1;
SELECT `user_editcount` FROM `wiki1_user` wu1 LEFT JOIN `wiki1_user` wu2 ON wu1.user_editcount > wu2.user_editcount WHERE wu2.user_editcount is null;
MIN(champ)
modifierMIN() renvoie la valeur minimum d'une expression issue du résultat d'une requête, ou NULL
s'il n'y en a pas :
SELECT MIN(`user_editcount`) FROM `wiki1_user`;
SELECT MIN(LENGTH(CONCAT(`user_name`, ' ', `user_real_name`))) FROM `wiki1_user`;
AVG(champ)
modifierAVG()
renvoie la valeur moyenne d'une expression, ou NULL
s'il n'y en a pas :
- Moyenne des valeurs d'un champ entier :
SELECT AVG(`user_editcount`) FROM `wiki1_user`;
- Moyenne des valeurs d'un champ date :
SELECT from_unixtime(avg(unix_timestamp(`user_registration`))) FROM `wiki1_user`;
- Moyenne d'un total obtenu par COUNT :
SELECT AVG(p.nb) as moyenne FROM ( SELECT count(*) as nb FROM wiki1_user where user_editcount > 1000 group by user_country ) as p;
SUM(champ)
modifierSUM()
dresse la somme des valeurs d'une expression, ou NULL
s'il n'y en a pas.
Si SUM(DISTINCT expression)
est utilisé, les valeurs identiques ne sont ajoutées qu'une seule fois. Il a été ajouté après MySQL 5.1.
SELECT SUM( DISTINCT user_editcount )
FROM wiki1_user
Cette fonction est impactée quand on ajoute des LEFT JOIN
dans la même requête. Il faut alors les séparer dans des sous-requêtes.
GROUP_CONCAT(champ)
modifierGROUP_CONCAT()
concatène les valeurs de tous les enregistrements d'un groupe dans une seule chaine séparée par une virgule par défaut. En effet, le deuxième paramètre facultatif permet de définir un autre séparateur.
CREATE TEMPORARY TABLE product (
id INTEGER, product_type VARCHAR(10), product_name VARCHAR(50)
);
INSERT INTO product VALUES
(1, 'mp3', 'iPod'),
(2, 'mp3', 'Zune'),
(3, 'mp3', 'ZEN'),
(4, 'notebook', 'Acer Eee PC'),
(4, 'notebook', 'Everex CloudBook');
SELECT * FROM product;
SELECT product_type, group_concat(product_name), group_concat(' ', product_name)
FROM product
GROUP BY product_type;
/*
# product_type, group_concat(product_name), group_concat(' ', product_name)
'mp3', 'iPod,Zune,ZEN', ' iPod, Zune, ZEN'
'notebook', 'Acer Eee PC,Everex CloudBook', ' Acer Eee PC, Everex CloudBook'
*/
Fonctions d'agrégation de bit
modifierSyntaxe générale :
FUNCTION_NAME(expression)
Ces fonctions bit à bit calculent expression pour chaque ligne du résultat et entre les expressions. La précision est de 64 bit.
AND
modifier SELECT BIT_AND(ip) FROM log
OR
modifier SELECT BIT_OR(ip) FROM log
(retourne 0 s'il n'y a aucun résultat)
XOR
modifier SELECT BIT_XOR(ip) FROM log
(retourne 0 s'il n'y a aucun résultat)
Références
modifier- ↑ http://dev.mysql.com/doc/refman/5.0/fr/information-functions.html
- ↑ https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html
- ↑ http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_coercibility
- ↑ https://mariadb.com/kb/en/json_search/
- ↑ https://www.startutorial.com/articles/view/how-to-search-json-data-in-mysql
- ↑ https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
- ↑ https://www.w3schools.com/sql/func_mysql_date_add.asp