« MySQL/Fonctions » : différence entre les versions
Contenu supprimé Contenu ajouté
Aucun résumé des modifications |
m <source> -> <syntaxhighlight> (phab:T237267) |
||
Ligne 3 :
== Syntaxe ==
A 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
</syntaxhighlight>
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é
</syntaxhighlight>
Toutefois, cette restriction ne s'applique qu'aux fonctions natives de MySQL (pas aux procédures stockées).
Ligne 21 :
=== BENCHMARK(nombre, expression) ===
Exécute l'expression n fois et retourne toujours zéro<ref>http://dev.mysql.com/doc/refman/5.0/fr/information-functions.html</ref>, 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
</syntaxhighlight>
=== CAST(valeur AS type) ===
Renvoie la valeur convertie en chaine de caractères, comme les apostrophes.
<
SELECT CAST(20130101 AS date); -- 2013-01-01
</syntaxhighlight>
Exemple avec encodage<ref>https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html</ref> :
<
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
</syntaxhighlight>
=== CHARSET(chaine) ===
Renvoie le type de caractères de la chaine :
<
SELECT CHARSET(20130101); -- binary
SHOW CHARACTER SET; -- montre tous les CHARACTER SET installés
</syntaxhighlight>
=== COALESCE(valeur, ...) ===
Renvoie le premier paramètre non nul. S'ils sont tous nuls, renvoie <code>NULL</code>.
<
SELECT COALESCE(null, 'Bonjour', null); -- bonjour
</syntaxhighlight>
=== COERCIBILITY(chaine) ===
Renvoie la ''{{wt|coercibility}}'' d'une chaine (entre 0 et 5) :
<
SELECT COERCIBILITY('bonjour'); -- 4
</syntaxhighlight>
{|class="wikitable"
Ligne 73 :
=== COLLATION(chaine) ===
Renvoie la ''{{wt|collation}}'' d'une chaine :
<
SELECT COLLATION('bonjour'); -- utf8_general_ci
</syntaxhighlight>
Pour obtenir celle par défaut d'une base :
<
SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'maBase1'
</syntaxhighlight>
=== CONCAT() ===
Concaténation sans séparateur :
<
SELECT CONCAT('Hello', 'World');
# HelloWorld
</syntaxhighlight>
=== CONCAT_WS() ===
Concaténation avec séparateur de son choix :
<
SELECT CONCAT_WS(', ', 'Hello', 'World');
# Hello, World
</syntaxhighlight>
=== CONNECTION_ID() ===
Renvoie l'identifiant du thread courant :
<
SELECT CONNECTION_ID(); -- 31
</syntaxhighlight>
=== CONVERT(valeur, type) ===
Tout comme <code>CAST()</code>, retourne la valeur convertie dans le type mentionné :
<
SELECT CONVERT (20130101, date); -- 2013-01-01
</syntaxhighlight>
=== CONVERT(chaine USING charset) ===
Convertit 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
</syntaxhighlight>
=== CURRENT_USER() ===
Retourne les noms de l'utilisateur et de l'hôte courants :
<
SELECT CURRENT_USER(); -- root@localhost
</syntaxhighlight>
=== DATABASE() ===
Retourne le nom de la base de données courante :
<
SELECT DATABASE(); -- wiki1
</syntaxhighlight>
=== FOUND_ROWS() ===
Après un <code>SELECT</code> avec une <code>LIMIT</code> et le mot clé <code>SQL_CALC_FOUND_ROWS</code>, il est possible de lancer un autre <code>SELECT</code> avec <code>FOUND_ROWS()</code>. 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
</syntaxhighlight>
=== GREATEST(valeur1, valeur2, ...) ===
Renvoie la plus grande valeur des paramètres :
<
SELECT GREATEST(1, 2, 21, 3); -- 21
</syntaxhighlight>
=== INTERVAL(valeur1, valeur2, valeur3, ...) ===
Renvoie 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
</syntaxhighlight>
=== IF(valeur1, valeur2, valeur3) ===
If ''valeur1'' est vraie, renvoie ''valeur2'', sinon (fausse ou nulle) renvoie ''valeur3''.
<
select if(1=2, 'irréel', 'réel'); -- réel
</syntaxhighlight>
=== IFNULL(valeur1, valeur2) ===
Si ''valeur1'' est nulle, renvoie ''valeur2'', sinon ''valeur1''.
<
SELECT IFNULL('variable1', 'défaut'); -- variable1
</syntaxhighlight>
=== ISNULL(valeur) ===
Si la valeur passée est nulle, renvoie 1, sinon 0.
<
SELECT ISNULL('variable1'); -- 0
</syntaxhighlight>
=== NULLIF(valeur1, valeur2) ===
Renvoie <code>NULL</code> si valeur1 = valeur2, sinon valeur1.
<
SELECT NULLIF(10, 20); -- 10
</syntaxhighlight>
=== LAST_INSERT_ID() ===
Ligne 180 :
=== LEAST(valeur1, valeur2, ...) ===
Renvoie la plus petite valeur dans la liste des paramètres passés :
<
SELECT LEAST(1, 2, 21, 3, -1); -- -1
</syntaxhighlight>
=== LENGTH(chaine) ===
Ligne 191 :
=== SUBSTR(chaine, début, taille) ===
Découpe une chaine de caractère :
<
SELECT SUBSTR('Hello World!', 7, 5); -- World
</syntaxhighlight>
=== SUBSTRING_INDEX ===
Dé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!
</syntaxhighlight>
=== JSON_UNQUOTE et JSON_EXTRACT ===
Ligne 211 :
Pour trouver la date de l'an dernier :
<
SELECT CURDATE() - INTERVAL 1 YEAR
</syntaxhighlight>
Sélectionner toutes les pages du wiki non lues depuis plus un an :
<
SELECT * FROM wiki1_page
WHERE page_touched <= (CURDATE() - INTERVAL 1 YEAR);
</syntaxhighlight>
Autres exemples de sélections :
<
SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
DATE_FORMAT(CURDATE(), '%Y-%m-15'),
Ligne 245 :
SELECT UNIX_TIMESTAMP('2007-05-01'); -- 1177970400
SELECT FROM_UNIXTIME(1177970400); -- 2007-05-01 00:00:00
</syntaxhighlight>
{{Attention|<code>convert('17/02/2016 15:49:03',datetime)</code> ou <code>convert('17-02-2016 15:49:03',datetime)</code> donne ''null'', donc une requête d'insertion le remplace par le même résultat que <code>now()</code>. La syntaxe doit être <code>convert('2016-02-17 15:49:03',datetime)</code> ou <code>convert('2016/02/17 15:49:03',datetime)</code>.}}
Ligne 251 :
=== DATE_ADD() ===
Pour additionner deux dates. Par exemple pour calculer le jour d'une livraison prenant 48 h :
<
SELECT DATE_ADD(NOW(), INTERVAL 2 DAY)
</syntaxhighlight>
Pour la date d'hier :
<
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY)
</syntaxhighlight>
Les unités à additionner ou soustraire les plus courantes sont<ref>https://www.w3schools.com/sql/func_mysql_date_add.asp</ref> :
Ligne 271 :
=== DATEDIFF() ===
Pour 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'
</syntaxhighlight>
== Fonctions d'agrégation ==
===COUNT(champ)===
Si le paramètre est "*" au lieu d'un nom de colonne, <code>COUNT()</code> 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`;
</syntaxhighlight>
Si le mot <code>DISTINCT</code> est employé, cela ignore les doublons :
<
SELECT COUNT(DISTINCT id) FROM `wiki1_page`;
</syntaxhighlight>
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`;
</syntaxhighlight>
Cela fonctionne aussi pour des expressions, des combinaisons de champs :
<
SELECT COUNT(`user_name` + `user_real_name`) FROM `wiki1_user`;
</syntaxhighlight>
Pour afficher le décompte de plusieurs tables non jointes :
<
SELECT
(SELECT COUNT(*) FROM maTable1) as t1,
(SELECT COUNT(*) FROM maTable2) as t2
</syntaxhighlight>
=== MAX(champ) ===
<code>MAX()</code> renvoie la valeur maximum d'une expression issue du résultat d'une requête, ou <code>NULL</code> 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`;
</syntaxhighlight>
==== Alternatives ====
Selon le contexte, la fonction <code>MAX()</code> n'est pas toujours la meilleure option pour obtenir un maximum. Par exemple en cas de sous-requêtes ou sans agrégation possible :
* <code>SELECT `user_editcount` FROM `wiki1_user` ORDER BY user_editcount DESC LIMIT 1;</code>
* <
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;
</syntaxhighlight>
===MIN(champ)===
MIN() renvoie la valeur minimum d'une expression issue du résultat d'une requête, ou <code>NULL</code> 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`;
</syntaxhighlight>
===AVG(champ)===
<code>AVG()</code> renvoie la valeur moyenne d'une expression, ou <code>NULL</code> s'il n'y en a pas :
<
SELECT AVG(`user_editcount`) FROM `wiki1_user`;
</syntaxhighlight>
===SUM(champ)===
Ligne 344 :
Si <code>SUM(DISTINCT expression)</code> 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
</syntaxhighlight>
{{Attention|Cette fonction est impactée quand on ajoute des <code>LEFT JOIN</code> dans la même requête. Il faut alors les séparer dans des sous-requêtes.}}
Ligne 354 :
<code>GROUP_CONCAT()</code> 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)
Ligne 377 :
'notebook', 'Acer Eee PC,Everex CloudBook', ' Acer Eee PC, Everex CloudBook'
*/
</syntaxhighlight>
=== Fonctions d'agrégation de bit ===
Syntaxe générale :
<
FUNCTION_NAME(''expression'')
</syntaxhighlight>
Ces fonctions bit à bit calculent ''expression'' pour chaque ligne du résultat et entre les ''expression''s. La précision est de 64 bit.
==== AND ====
<
SELECT BIT_AND(ip) FROM log
</syntaxhighlight>
==== OR ====
<
SELECT BIT_OR(ip) FROM log
</syntaxhighlight>
(retourne 0 s'il n'y a aucun résultat)
==== XOR ====
<
SELECT BIT_XOR(ip) FROM log
</syntaxhighlight>
(retourne 0 s'il n'y a aucun résultat)
|