10. Fonctions SQL

Ce chapitre décrit toutes les fonctions et tous les opérateurs SQL intégrés de MIRAJ en langage MIRAJ : leur signature, leur comportement précis, leur type de retour, leur comportement face à NULL et un exemple d'appel avec son résultat. Les résultats sont ceux affichés par la console miraj-cli (les chaînes sont écrites ici entre apostrophes pour les distinguer des nombres).

10.0 Généralités#

• Noms insensibles à la casse. CONCAT, Concat et concat désignent la même fonction. Ce chapitre les écrit en majuscules par convention.
• Propagation de NULL. Sauf mention contraire explicite dans la description d'une fonction, tout argument NULL rend le résultat NULL. Les exceptions notables sont COALESCE, IFNULL/NVL, CONCAT_WS, IF, ISNULL, ELT, FIELD, MAKE_SET, QUOTE, SFORMAT (pour les arguments autres que le format) et les agrégats (qui ignorent les NULL de leur argument plutôt que de propager NULL pour toute la ligne).
• Positions et longueurs de chaînes sont comptées en caractères Unicode (pas en octets), sauf LENGTH et BIT_LENGTH qui comptent des octets UTF-8. ASCII renvoie l'octet de tête de l'encodage UTF-8 du premier caractère ; ORD combine les octets UTF-8 du premier caractère en gros-boutiste (ORD('é') = 50089, alors que é a le point de code Unicode 233).
• CASE WHEN n'est pas une fonction mais une construction du langage SQL (voir le chapitre 9. Requêtes SELECT) ; son comportement de typage de résultat suit les mêmes règles que COALESCE/IF décrites en 10.4.
• Ce chapitre documente l'intégralité des fonctions scalaires du moteur, les fonctions d'agrégation et de fenêtrage, et les opérateurs (10.19). Un nom inconnu rend l'erreur 1305 (FUNCTION nom does not exist), un mauvais nombre d'arguments l'erreur 1582.

Table des matières#

• 10.1 Fonctions de chaînes de caractères
  • CONCAT · CONCAT_WS · UPPER / UCASE · LOWER / LCASE · LENGTH / OCTET_LENGTH / LENGTHB · BIT_LENGTH · CHAR_LENGTH / CHARACTER_LENGTH · TRIM · LTRIM · RTRIM · SUBSTRING / SUBSTR / MID · LEFT · RIGHT · REPLACE · LOCATE · INSTR · LPAD · RPAD · REVERSE · REPEAT · SPACE · ASCII · ORD · CHAR · CHR · STRCMP · FORMAT · INSERT · ELT · FIELD · FIND_IN_SET · SUBSTRING_INDEX · QUOTE · BIN · OCT · CONV · MAKE_SET · EXPORT_SET · SOUNDEX · NATURAL_SORT_KEY · WEIGHT_STRING · LOAD_FILE · CONVERT (jeu de caractères) · FORMAT_BYTES · FORMAT_PICO_TIME
• 10.2 Fonctions numériques
  • ABS · SIGN · ROUND · TRUNCATE · FLOOR · CEILING / CEIL · MOD · POWER / POW · SQRT · EXP · LN · LOG · LOG10 · LOG2 · PI · RAND · SIN, COS, TAN · COT · ASIN · ACOS · ATAN / ATAN2 · DEGREES · RADIANS · GREATEST · LEAST · CRC32 / CRC32C · BIT_COUNT · TO_NUMBER
• 10.3 Fonctions de date et heure
  • NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP · SYSDATE · CURDATE / CURRENT_DATE · CURTIME / CURRENT_TIME · UTC_TIMESTAMP, UTC_DATE, UTC_TIME · DATE · TIME · TIMESTAMP · YEAR · MONTH · DAY / DAYOFMONTH · HOUR · MINUTE · SECOND · MICROSECOND · DAYOFWEEK · WEEKDAY · DAYOFYEAR · QUARTER · WEEK · WEEKOFYEAR · YEARWEEK · DAYNAME · MONTHNAME · LAST_DAY · DATE_FORMAT / TIME_FORMAT · TO_CHAR · STR_TO_DATE · DATE_ADD / ADDDATE · DATE_SUB / SUBDATE · ADD_MONTHS · MONTHS_BETWEEN · TIMESTAMPADD · DATEDIFF · TIMEDIFF · TIMESTAMPDIFF · ADDTIME · SUBTIME · UNIX_TIMESTAMP · FROM_UNIXTIME · TO_DAYS · FROM_DAYS · MAKEDATE · MAKETIME · TIME_TO_SEC · SEC_TO_TIME · EXTRACT · TO_SECONDS · PERIOD_ADD · PERIOD_DIFF · GET_FORMAT · CONVERT_TZ
• 10.4 Fonctions de contrôle et logique
  • IF · IFNULL / NVL · COALESCE · NULLIF · ISNULL · NVL2 · INTERVAL (rang) · DEFAULT · NAME_CONST
• 10.5 Fonctions d'agrégation
  • COUNT · SUM · AVG · MIN / MAX · GROUP_CONCAT · STD / STDDEV / STDDEV_POP · STDDEV_SAMP · VARIANCE / VAR_POP · VAR_SAMP · BIT_AND, BIT_OR, BIT_XOR · ANY_VALUE · GROUPING · JSON_ARRAYAGG · JSON_OBJECTAGG
• 10.6 Expressions régulières
  • REGEXP_LIKE · REGEXP_INSTR · REGEXP_SUBSTR · REGEXP_REPLACE
• 10.7 Fonctions de hachage, chiffrement et encodage
  • MD5 · SHA1 / SHA · SHA2 · PASSWORD · HEX · UNHEX · TO_BASE64 · FROM_BASE64 · AES_ENCRYPT · AES_DECRYPT · COMPRESS · UNCOMPRESS · UNCOMPRESSED_LENGTH · OLD_PASSWORD · ENCODE / DECODE · DES_ENCRYPT / DES_DECRYPT · ENCRYPT · KDF
• 10.8 Fonctions réseau (IP)
  • INET_ATON · INET_NTOA · INET6_ATON · INET6_NTOA · IS_IPV4 · IS_IPV6 · IS_IPV4_COMPAT · IS_IPV4_MAPPED
• 10.9 Fonctions système, session et verrous
  • DATABASE / SCHEMA · VERSION · CHARSET · COLLATION · COERCIBILITY · BENCHMARK · LAST_INSERT_ID · ROW_COUNT · FOUND_ROWS · CONNECTION_ID · USER / SESSION_USER / SYSTEM_USER · CURRENT_USER · CURRENT_ROLE · SLEEP · UUID / UUID_V4 · UUID_V7 · SYS_GUID · RANDOM_BYTES · UUID_SHORT · GET_LOCK · RELEASE_LOCK · IS_FREE_LOCK · IS_USED_LOCK · RELEASE_ALL_LOCKS · MIRAJ_NOTIFY · MIRAJ_CHANGE_SEQ · BINLOG_GTID_POS · MASTER_GTID_WAIT · MASTER_POS_WAIT · DECODE_HISTOGRAM · Variables système @@nom
• 10.10 Fonctions JSON
  • JSON_OBJECT · JSON_ARRAY · JSON_MERGE_PRESERVE / JSON_MERGE · JSON_MERGE_PATCH · JSON_QUOTE · JSON_VALID · Chemins JSON · Opérateurs -> et ->> · JSON_EXTRACT · JSON_UNQUOTE · JSON_VALUE · JSON_SET / JSON_INSERT / JSON_REPLACE · JSON_REMOVE · JSON_ARRAY_APPEND / JSON_ARRAY_INSERT · JSON_CONTAINS · JSON_CONTAINS_PATH · JSON_KEYS · JSON_LENGTH · JSON_DEPTH · JSON_TYPE · JSON_SEARCH · JSON_OVERLAPS · MEMBER OF · JSON_PRETTY · JSON_QUERY · JSON_EXISTS · JSON_EQUALS · JSON_NORMALIZE · JSON_COMPACT / JSON_LOOSE / JSON_DETAILED · JSON_ARRAY_INTERSECT · JSON_OBJECT_FILTER_KEYS · JSON_OBJECT_TO_ARRAY · JSON_KEY_VALUE · JSON_SCHEMA_VALID / JSON_SCHEMA_VALIDATION_REPORT · JSON_TABLE
• 10.11 Fonctions vectorielles (VECTOR(n))
  • VEC_FROMTEXT · VEC_TOTEXT · VEC_DISTANCE_EUCLIDEAN · VEC_DISTANCE_COSINE · VEC_DISTANCE · STRING_TO_VECTOR / TO_VECTOR · VECTOR_TO_STRING / FROM_VECTOR · VECTOR_DIM · DISTANCE · L2_DISTANCE · COSINE_DISTANCE · INNER_PRODUCT · VECTOR_NEGATIVE_INNER_PRODUCT · L1_DISTANCE · VECTOR_DIMS · VECTOR_NORM · L2_NORMALIZE · SUBVECTOR · VECTOR_ADD · VECTOR_SUB · VECTOR_MUL
• 10.12 Mise en forme avancée
  • SFORMAT
• 10.13 Fonctions de fenêtrage
  • ROW_NUMBER · RANK · DENSE_RANK · PERCENT_RANK · CUME_DIST · NTILE · LAG / LEAD · FIRST_VALUE / LAST_VALUE · NTH_VALUE · MEDIAN / PERCENTILE_CONT / PERCENTILE_DISC · Agrégats en fenêtre
• 10.14 Colonnes dynamiques
  • COLUMN_CREATE · COLUMN_ADD · COLUMN_DELETE · COLUMN_GET · COLUMN_EXISTS · COLUMN_LIST · COLUMN_JSON · COLUMN_CHECK
• 10.15 Fonctions de séquence
• 10.16 Recherche plein texte (MATCH … AGAINST)
• 10.17 Fonctions spatiales
  • Conversions · Constructeurs · Accesseurs · Prédicats · Mesures et constructions · ST_Union / ST_Intersection / ST_Difference / ST_SymDifference · ST_Buffer / Buffer · Geohash · ST_Collect · Index spatial
• 10.18 Fonctions XML
  • EXTRACTVALUE · UPDATEXML
• 10.19 Opérateurs
  • Arithmétiques · Comparaison · Logiques · Bit à bit · Motifs · Autres · Conversion de type · Priorité

10.1 Fonctions de chaînes de caractères#

Récapitulatif :

CONCAT#

CONCAT(chaine1, chaine2, ...)

Concatène de un à un nombre illimité d'arguments, convertis en texte si besoin. NULL propage : si un seul argument est NULL, le résultat entier est NULL (contrairement à CONCAT_WS). Si l'un des arguments est une chaîne binaire (VARBINARY, BLOB, X'…', résultat de COMPRESS…), le résultat en est une : les octets de chaque argument sont mis bout à bout tels quels.

Retour : VARCHAR/texte.

SELECT CONCAT('MIRAJ', ' ', 'DB');   -- 'MIRAJ DB'
SELECT CONCAT('a', NULL, 'b');       -- NULL

CONCAT_WS#

CONCAT_WS(separateur, chaine1, chaine2, ...)

« Concat With Separator » : concatène chaine1, chaine2, ... en insérant separateur entre chaque paire d'arguments non NULL ; un argument NULL est simplement ignoré (pas d'insertion vide). Si separateur lui-même est NULL, le résultat est NULL.

Retour : texte.

SELECT CONCAT_WS(',', 'a', NULL, 'b');  -- 'a,b'
SELECT CONCAT_WS(NULL, 'a', 'b');       -- NULL

UPPER / UCASE#

UPPER(chaine)

Met chaine en majuscules (règles Unicode, pas seulement ASCII). UCASE est un alias strict.

Retour : texte. NULL → NULL.

SELECT UPPER('café');  -- 'CAFÉ'

LOWER / LCASE#

LOWER(chaine)

Met chaine en minuscules (règles Unicode). LCASE est un alias strict.

SELECT LOWER('CAFÉ');  -- 'café'

LENGTH / OCTET_LENGTH / LENGTHB#

LENGTH(chaine)

Longueur de chaine en octets de son encodage UTF-8 (pas en caractères) ; pour une valeur binaire (VARBINARY/BLOB), longueur en octets bruts, sans passer par un décodage UTF-8. OCTET_LENGTH et LENGTHB sont des alias stricts.

Retour : BIGINT.

SELECT LENGTH('café');  -- 5 (le é occupe deux octets en UTF-8)

BIT_LENGTH#

BIT_LENGTH(chaine)

Longueur en bits, soit huit fois LENGTH(chaine).

SELECT BIT_LENGTH('a');  -- 8

CHAR_LENGTH / CHARACTER_LENGTH#

CHAR_LENGTH(chaine)

Longueur de chaine en caractères Unicode (pas en octets) ; pour une valeur binaire, un octet compte pour un caractère. CHARACTER_LENGTH est un alias strict.

SELECT CHAR_LENGTH('café');  -- 4

TRIM#

TRIM([BOTH | LEADING | TRAILING] [caracteres] FROM chaine)
TRIM(chaine)
TRIM(caracteres, chaine)

Retire les occurrences répétées de caracteres (l'espace ' ' par défaut, avec la forme à un argument) en tête, en fin, ou des deux côtés (BOTH, valeur par défaut) de chaine. Si caracteres est une chaîne vide, chaine est rendue inchangée.

Retour : texte.

SELECT TRIM('  x  ');                 -- 'x'
SELECT TRIM(LEADING '0' FROM '007');  -- '7'
SELECT TRIM(BOTH 'xy' FROM 'xyzxy');  -- 'z'

LTRIM#

LTRIM(chaine)

Retire les espaces (' ') en tête de chaine uniquement.

SELECT LTRIM('  x  ');  -- 'x  '

RTRIM#

RTRIM(chaine)

Retire les espaces (' ') en fin de chaine uniquement.

SELECT RTRIM('  x  ');  -- '  x'

SUBSTRING / SUBSTR / MID#

SUBSTRING(chaine, position [, longueur])

Sous-chaîne de chaine à partir du caractère position (1 = premier caractère). Une position négative compte depuis la fin (-1 = dernier caractère). Sans longueur, va jusqu'à la fin ; avec longueur < 1 ou position = 0 ou position hors de la chaîne, rend une chaîne vide (pas NULL). SUBSTR et MID sont des alias stricts. Sur une chaîne binaire, les positions et la longueur comptent des octets et le résultat est une chaîne binaire (de même pour LEFT et RIGHT).

Retour : texte.

SELECT SUBSTRING('MIRAJ DB', 7);      -- 'DB'
SELECT SUBSTRING('MIRAJ DB', 1, 5);   -- 'MIRAJ'
SELECT SUBSTRING('MIRAJ DB', -2);     -- 'DB'

LEFT#

LEFT(chaine, n)

Les n premiers caractères de chaine (n négatif traité comme 0 ; n au-delà de la longueur rend chaine entière).

SELECT LEFT('MIRAJ', 3);  -- 'MIR'

RIGHT#

RIGHT(chaine, n)

Les n derniers caractères de chaine.

SELECT RIGHT('MIRAJ', 3);  -- 'RAJ'

REPLACE#

REPLACE(chaine, recherche, remplacement)

Remplace toutes les occurrences non chevauchantes de recherche par remplacement dans chaine, en respectant la casse. Si recherche est une chaîne vide, chaine est rendue inchangée (pas de boucle infinie).

SELECT REPLACE('a-b-c', '-', '/');  -- 'a/b/c'

LOCATE#

LOCATE(sous_chaine, chaine [, position_depart])

Position (en caractères, base 1) de la première occurrence de sous_chaine dans chaine, recherche selon l'interclassement résolu : en utf8mb4_general_ci (défaut) insensible à la casse mais sensible aux accents (LOCATE('eme', 'Crème') rend 0) ; en utf8mb4_unicode_ci et utf8mb4_bin, chaque fenêtre de même longueur en octets que sous_chaine est comparée sous l'interclassement (LOCATE('ss', 'Straße' COLLATE utf8mb4_unicode_ci) rend 5, LOCATE('a', 'A' COLLATE utf8mb4_bin) rend 0 ; voir Interclassements), à partir de position_depart (1 par défaut). Rend 0 si sous_chaine n'est pas trouvée ou si position_depart < 1.

SELECT LOCATE('DB', 'MIRAJ DB');     -- 7
SELECT LOCATE('xyz', 'MIRAJ DB');    -- 0

INSTR#

INSTR(chaine, sous_chaine)

Équivalent à LOCATE(sous_chaine, chaine) (ordre des arguments inversé), recherche depuis le début, selon l'interclassement comme LOCATE. La forme normalisée POSITION(sous_chaine IN chaine) est un autre synonyme.

SELECT INSTR('MIRAJ DB', 'DB');            -- 7
SELECT POSITION('DB' IN 'MIRAJ DB');       -- 7

LPAD#

LPAD(chaine, longueur, remplissage)

Complète chaine à gauche avec des répétitions de remplissage jusqu'à longueur caractères ; si chaine compte déjà longueur caractères ou plus, elle est tronquée à longueur caractères. Rend NULL si longueur < 0 ou dépasse un million de caractères ; rend une chaîne vide si remplissage est vide et qu'il faudrait compléter.

SELECT LPAD('7', 3, '0');   -- '007'
SELECT LPAD('12345', 3, '0');  -- '123' (tronqué)

RPAD#

RPAD(chaine, longueur, remplissage)

Comme LPAD mais complète à droite.

SELECT RPAD('7', 3, '0');  -- '700'

REVERSE#

REVERSE(chaine)

Inverse l'ordre des caractères (Unicode) de chaine.

SELECT REVERSE('MIRAJ');  -- 'JARIM'

REPEAT#

REPEAT(chaine, n)

Répète chaine n fois bout à bout (n négatif traité comme 0). Rend NULL si n dépasse un million, ou si le résultat dépasserait 64 Mio (valeur par défaut de max_allowed_packet).

SELECT REPEAT('ab', 3);  -- 'ababab'

SPACE#

SPACE(n)

Chaîne de n espaces (n négatif traité comme 0). Rend NULL si n dépasse un million.

SELECT SPACE(3);  -- '   '

ASCII#

ASCII(chaine)

Octet de tête de l'encodage UTF-8 du premier caractère de chaine (donc le code ASCII pour un caractère ASCII ; pour un caractère multi-octets, seul l'octet de tête est rendu, pas le point de code Unicode). 0 si chaine est vide.

Retour : BIGINT.

SELECT ASCII('A');  -- 65

ORD#

ORD(chaine)

Code numérique du premier caractère de chaine, calculé en combinant ses octets UTF-8 en gros-boutiste. Pour un caractère ASCII, identique à ASCII. 0 si chaine est vide.

SELECT ORD('A');    -- 65
SELECT ORD('é');    -- 50089 (0xC3 0xA9 combinés)

CHAR#

CHAR(code1 [, code2, ...])

Construit une chaîne à partir de points de code (chaque argument devient un caractère). Un argument NULL est ignoré (ne casse pas la concaténation, contrairement à la plupart des fonctions de chaînes). Chaque code est d'abord interprété comme les octets significatifs d'un caractère multi-octets ; si le résultat n'est pas de l'UTF-8 valide, chaque code est relu comme un point de code Unicode direct.

SELECT CHAR(77, 73, 82, 65, 74);  -- 'MIRAJ'

CHR#

CHR(code)

Le caractère dont code est le code dans le jeu de caractères de la base (utf8mb4) : équivaut à CHAR(code USING utf8mb4) à un seul argument. code est lu comme les octets significatifs du caractère (CHR(65) vaut 'A', CHR(50089) vaut 'é', dont l'encodage est C3 A9), et non comme un point de code Unicode. Si ces octets ne forment pas de l'UTF-8 valide (CHR(233)), le résultat est NULL avec l'avertissement 1300.

Retour : texte.

SELECT CHR(65), CHR(50089);  -- 'A', 'é'

STRCMP#

STRCMP(chaine1, chaine2)

Compare deux chaînes selon l'interclassement résolu de ses arguments (STRCMP('a' COLLATE utf8mb4_bin, 'A') rend 1) : 0 si égales, un nombre négatif si chaine1 < chaine2, positif sinon.

Retour : BIGINT.

SELECT STRCMP('a', 'b');  -- -1
SELECT STRCMP('a', 'a');  -- 0

FORMAT#

FORMAT(nombre, decimales [, locale])

Met nombre en forme avec decimales chiffres après la virgule (arrondi à la valeur la plus proche, moitié éloignée de zéro ; decimales borné à 0–38) et un séparateur de milliers. Pour un flottant (DOUBLE, PI(), littéral en notation scientifique), nombre × 10^decimales est arrondi à l'entier pair le plus proche (FORMAT(2.5e0, 0) vaut '2') et le résultat s'écrit avec ses chiffres significatifs les plus courts, complétés de zéros (FORMAT(PI(), 38) vaut '3.14159265358979300000000000000000000000') ; un flottant négatif arrondi à zéro garde son signe (FORMAT(-0.004e0, 2) vaut '-0.00'). Sans locale, utilise les conventions anglo-américaines (, pour les milliers, . pour la décimale). Avec une locale reconnue (par ex. 'fr_FR', 'de_DE'), les séparateurs et le groupement des chiffres suivent cette locale ; une locale inconnue ou NULL équivaut à 'en_US'. NULL si nombre ou decimales est NULL.

Retour : texte.

SELECT FORMAT(1234567.891, 2);              -- '1,234,567.89'
SELECT FORMAT(1234567.891, 2, 'fr_FR');     -- '1234567,89' (fr_FR ne groupe pas les milliers)
SELECT FORMAT(1234567.891, 2, 'de_DE');     -- '1.234.567,89'

INSERT#

INSERT(chaine, position, longueur, remplacement)

Remplace longueur caractères de chaine à partir de position (base 1) par remplacement. Si position est hors de chaine (< 1 ou > longueur(chaine)), chaine est rendue inchangée. Une longueur négative va jusqu'à la fin de chaine.

SELECT INSERT('MIRAJ DB', 7, 2, 'Serveur');  -- 'MIRAJ Serveur'

ELT#

ELT(n, chaine1, chaine2, ...)

Rend la n-ième chaîne (base 1) parmi chaine1, chaine2, .... Rend NULL si n est NULL, hors de portée (< 1 ou > le nombre de chaînes) ou si la chaîne choisie est NULL — mais les autres NULL de la liste n'affectent pas le résultat s'ils ne sont pas choisis.

SELECT ELT(2, 'a', 'b', 'c');  -- 'b'
SELECT ELT(5, 'a', 'b', 'c');  -- NULL

FIELD#

FIELD(chaine, valeur1, valeur2, ...)

Position (base 1) de chaine dans la liste valeur1, valeur2, ... (comparaison insensible à la casse), 0 si non trouvée. Rend 0 (pas NULL) si chaine est NULL ; une valeurN NULL est ignorée sans jamais correspondre.

SELECT FIELD('b', 'a', 'b', 'c');  -- 2
SELECT FIELD('x', 'a', 'b', 'c');  -- 0

FIND_IN_SET#

FIND_IN_SET(chaine, liste)

Position (base 1) de chaine dans liste, une chaîne de valeurs séparées par des virgules ; 0 si non trouvée ou si liste est vide.

SELECT FIND_IN_SET('b', 'a,b,c');  -- 2

SUBSTRING_INDEX#

SUBSTRING_INDEX(chaine, delimiteur, n)

Si n > 0, tout ce qui précède la n-ième occurrence de delimiteur depuis le début ; si n < 0, tout ce qui suit la |n|-ième occurrence depuis la fin. Rend chaine entière si delimiteur n'apparaît pas assez de fois. Rend une chaîne vide si delimiteur est vide ou n vaut 0.

SELECT SUBSTRING_INDEX('a.b.c.d', '.', 2);   -- 'a.b'
SELECT SUBSTRING_INDEX('a.b.c.d', '.', -2);  -- 'c.d'

QUOTE#

QUOTE(chaine)

Entoure chaine d'apostrophes et échappe les apostrophes et antislashs qu'elle contient, pour produire un littéral SQL réutilisable directement dans une instruction. Ne propage pas NULL : QUOTE(NULL) rend la chaîne littérale 'NULL' (quatre lettres, sans guillemets), le mot-clé SQL tel qu'il apparaîtrait dans une instruction.

SELECT QUOTE("l'ami");  -- 'l\'ami'  (apostrophes comprises dans le texte rendu)
SELECT QUOTE(NULL);     -- 'NULL'  (le texte de quatre lettres, pas la valeur SQL NULL)

BIN#

BIN(n)

Représentation binaire de n (entier signé sur 64 bits, lu en complément à deux si négatif).

SELECT BIN(5);   -- '101'
SELECT BIN(-1);  -- '1111111111111111111111111111111111111111111111111111111111111111' (64 uns)

OCT#

OCT(n)

Représentation octale de n, équivalente à CONV(n, 10, 8). Un argument texte est lu comme un préfixe entier décimal (lecture arrêtée au premier caractère non numérique, dépassement saturé) ; une chaîne vide rend NULL.

SELECT OCT(8);  -- '10'

CONV#

CONV(nombre, base_origine, base_cible)

Convertit la représentation texte de nombre (lu dans base_origine) vers base_cible (bases de 2 à 36, valeur absolue). La lecture s'arrête au premier chiffre invalide pour la base d'origine ; les espaces de tête sont ignorés. NULL si l'une des bases est hors de 2–36.

Retour : texte.

SELECT CONV('FF', 16, 10);  -- '255'
SELECT CONV(255, 10, 16);   -- 'FF'

MAKE_SET#

MAKE_SET(masque, chaine1, chaine2, ...)

Liste séparée par des virgules des chaineN dont le bit N-1 de masque (entier) vaut 1 ; les bits au-delà du nombre de chaînes fournies sont ignorés. Une chaineN NULL est ignorée même si son bit est à 1. NULL si masque est NULL.

SELECT MAKE_SET(5, 'a', 'b', 'c');  -- 'a,c' (bits 0 et 2 à 1 : 5 = 0b101)

EXPORT_SET#

EXPORT_SET(masque, on, off [, separateur [, nb_bits]])

Pour chacun des nb_bits bits de masque (64 par défaut, borné à 0–64), rend on si le bit vaut 1, off sinon, du bit de poids faible vers le haut, joints par separateur (, par défaut).

SELECT EXPORT_SET(5, 'Y', 'N', ',', 4);  -- 'Y,N,Y,N'

SOUNDEX#

SOUNDEX(chaine)

Code phonétique Soundex de chaine : la première lettre est recopiée (majuscule), puis un chiffre par consonne suivante selon la table Soundex classique (voyelles, H, W, Y ignorés sans séparer deux chiffres identiques), complété par des zéros jusqu'à 4 caractères. Chaîne vide si chaine ne contient aucune lettre.

SELECT SOUNDEX('Robert');  -- 'R163'
SELECT SOUNDEX('Rupert');  -- 'R163'

NATURAL_SORT_KEY#

NATURAL_SORT_KEY(chaine)

Produit une clé de texte telle qu'un tri lexicographique de ces clés équivaut à un « tri naturel » de chaine (les nombres inclus dans le texte triés par valeur, pas caractère par caractère : 'a2' avant 'a10'). Utile en ORDER BY NATURAL_SORT_KEY(colonne).

SELECT NATURAL_SORT_KEY('a2'), NATURAL_SORT_KEY('a10');
-- 'a02', 'a110' (la deuxième trie après la première)

WEIGHT_STRING#

WEIGHT_STRING(chaine)
WEIGHT_STRING(chaine AS CHAR(n))
WEIGHT_STRING(chaine AS BINARY(n))

Rend les octets de poids de tri de chaine selon son interclassement. En utf8mb4_unicode_ci : unités de 16 bits gros-boutistes (zéro, une ou plusieurs par caractère, 'ß' → 0FEA0FEA), AS CHAR(n) coupant à n unités puis complétant par 0209 ; en utf8mb4_bin : 3 octets par caractère (point de code), AS CHAR(n) complétant par 000020. En utf8mb4_general_ci (défaut) : un poids de 16 bits gros-boutiste par caractère, espaces finaux compris (HEX(WEIGHT_STRING('Crème')) rend 004300520045004D0045, comme pour 'creme' ; un caractère hors BMP pèse FFFD). AS CHAR(n) ramène d'abord le texte à n caractères (tronqué ou complété par des espaces), ce qui rend le même résultat pour deux chaînes égales selon = ; AS BINARY(n) ramène les octets bruts à n (tronqués ou complétés par des zéros). Une valeur numérique rend NULL (pas de poids de tri défini pour un nombre par cette fonction) ; une date ou une heure utilise les octets de son texte.

Retour : binaire (VARBINARY).

SELECT HEX(WEIGHT_STRING('Crème'));             -- '004300520045004D0045'
SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));     -- '0041004200200020'
SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));   -- '61620000'

LOAD_FILE#

LOAD_FILE(chemin)

Contenu d'un fichier du serveur, en binaire. Le fichier doit se trouver dans le dossier autorisé par la variable serveur secure_file_priv, et l'utilisateur doit avoir le privilège FILE ; un chemin relatif part de ce dossier, et un chemin qui en sortirait (via ..) est refusé. Rend NULL, sans erreur, si aucun dossier n'est autorisé pour la session, si le fichier est absent, illisible, n'est pas un fichier ordinaire, ou dépasse max_allowed_packet.

Retour : binaire.

-- secure_file_priv désigne un dossier qui contient note.txt (texte « bonjour »)
SELECT LOAD_FILE('note.txt');     -- 'bonjour'
SELECT LOAD_FILE('absent.txt');   -- NULL
SELECT LOAD_FILE('../x');         -- NULL (hors du dossier autorisé)

CONVERT (jeu de caractères)#

CONVERT(expr USING jeu_de_caracteres)
CAST(expr AS CHAR CHARACTER SET jeu_de_caracteres)

Convertit expr vers le jeu de caractères nommé. Vers binary, rend les octets UTF-8 bruts du texte (type binaire) ; vers un autre jeu, rend un texte où les caractères non représentables dans ce jeu sont remplacés par ?. Cette forme de CONVERT (avec un jeu de caractères) est distincte de CONVERT(expr, type) qui effectue un changement de type SQL, décrit au chapitre 6. Types de données.

SELECT HEX(CONVERT('é' USING latin1));  -- 'E9'

FORMAT_BYTES#

FORMAT_BYTES(octets)

Écrit une taille en octets dans l'unité binaire la plus lisible : en dessous de 1024, N bytes (partie entière) ; au-delà, la valeur est divisée par 1024 tant qu'elle reste au moins égale à 1024 et s'écrit avec deux décimales suivie de KiB, MiB, GiB, TiB, PiB ou EiB. Une valeur négative garde son signe.

Retour : texte.

SELECT FORMAT_BYTES(512), FORMAT_BYTES(1536), FORMAT_BYTES(1073741824);  -- '512 bytes', '1.50 KiB', '1.00 GiB'
SELECT FORMAT_BYTES(-2048);                                              -- '-2.00 KiB'

FORMAT_PICO_TIME#

FORMAT_PICO_TIME(picosecondes)

Écrit une durée exprimée en picosecondes dans l'unité la plus lisible : en dessous de 1000, N ps ; au-delà, ns, us, ms, s (pas de 1000), puis min, h et d (pas de 60, 60 et 24), avec deux décimales. Une valeur négative garde son signe.

Retour : texte.

SELECT FORMAT_PICO_TIME(999), FORMAT_PICO_TIME(2500), FORMAT_PICO_TIME(1500000000);  -- '999 ps', '2.50 ns', '1.50 ms'
SELECT FORMAT_PICO_TIME(90000000000000), FORMAT_PICO_TIME(86400000000000000);      -- '1.50 min', '1.00 d'

10.2 Fonctions numériques#

Les calculs sur DECIMAL restent exacts (arithmétique entière mise à l'échelle) ; une division, un modulo par zéro ou un domaine invalide (logarithme d'un nombre négatif, racine carrée d'un négatif...) rendent NULL plutôt que de lever une erreur.

Récapitulatif :

ABS#

ABS(nombre)

Valeur absolue. Un entier reste entier, un DECIMAL reste DECIMAL (même échelle), les autres deviennent DOUBLE. NULL en cas de dépassement (valeur minimale d'un BIGINT signé, dont l'opposé ne tient pas dans un BIGINT).

SELECT ABS(-5);    -- 5
SELECT ABS(-5.25); -- 5.25

SIGN#

SIGN(nombre)

1 si nombre > 0, -1 si nombre < 0, 0 sinon.

Retour : BIGINT.

SELECT SIGN(-12.5), SIGN(0), SIGN(3);  -- -1, 0, 1

ROUND#

ROUND(nombre [, decimales])

Arrondit nombre à decimales décimales (0 par défaut ; négatif pour arrondir à des dizaines, centaines...). Un entier reste entier (arrondi à des dizaines si decimales < 0) ; un BIGINT UNSIGNED arrondi à des dizaines par un nombre écrit rend un DECIMAL, l'arrondi pouvant dépasser 2⁶⁴ − 1 (ROUND(18446744073709551615, -1) = 18446744073709551620) ; un DECIMAL est arrondi au plus loin de zéro pour un demi (ROUND(0.5, 0) = 1) et le résultat garde le nombre de décimales demandé, complété de zéros si besoin (ROUND(2.567, 5) = 2.56700) ; un flottant (FLOAT/DOUBLE) est arrondi au plus proche pair (mode IEEE 754 par défaut : ROUND(2.5e0) = 2, ROUND(1.5e0) = 2, ROUND(0.5e0) = 0), comme pour tout calcul en virgule flottante.

SELECT ROUND(1250, -2);   -- 1300 (entier)
SELECT ROUND(2.5);        -- 3   (DECIMAL littéral : arrondi au plus loin de zéro)
SELECT ROUND(1.005, 2);   -- 1.01 (DECIMAL exact)
SELECT ROUND(2.5e0), ROUND(1.5e0), ROUND(0.5e0);  -- 2.0, 2.0, 0.0 (DOUBLE : au plus proche pair)

TRUNCATE#

TRUNCATE(nombre, decimales)

Tronque nombre à decimales décimales sans arrondir (troncature vers zéro). decimales est obligatoire, contrairement à ROUND. TRUNC(nombre, decimales) en est un synonyme, aux mêmes règles (décimales négatives, NULL, deux arguments).

SELECT TRUNCATE(1.999, 2);  -- 1.99
SELECT TRUNCATE(1999, -2);  -- 1900
SELECT TRUNC(1.999, 2);     -- 1.99

FLOOR#

FLOOR(nombre)

Plus grand entier inférieur ou égal à nombre. NULL si hors de la plage d'un BIGINT signé.

Retour : BIGINT.

SELECT FLOOR(1.9);   -- 1
SELECT FLOOR(-1.1);  -- -2

CEILING / CEIL#

CEILING(nombre)

Plus petit entier supérieur ou égal à nombre. CEIL est un alias strict.

SELECT CEILING(1.1);  -- 2
SELECT CEILING(-1.9); -- -1

MOD#

MOD(a, b)

Reste de la division entière de a par b (même signe que a, comme l'opérateur %). NULL si b vaut 0. Un entier divisé par un entier rend un entier ; sinon le calcul passe par une division flottante mais le résultat garde la représentation numérique du premier argument (entier, DECIMAL ou DOUBLE).

SELECT MOD(10, 3);   -- 1
SELECT MOD(-10, 3);  -- -1
SELECT MOD(10, 0);   -- NULL

POWER / POW#

POWER(base, exposant)

base élevé à la puissance exposant. POW est un alias strict.

Retour : DOUBLE.

SELECT POWER(2, 10), POW(2, -1);  -- 1024.0, 0.5

SQRT#

SQRT(nombre)

Racine carrée. NULL si nombre < 0.

SELECT SQRT(16);  -- 4.0
SELECT SQRT(-1);  -- NULL

EXP#

EXP(nombre)

e élevé à la puissance nombre.

SELECT EXP(1);  -- 2.718281828459045

LN#

LN(nombre)

Logarithme népérien. NULL si nombre <= 0.

SELECT LN(1), LN(EXP(2)), LN(0);  -- 0.0, 2.0, NULL

LOG#

LOG(nombre)
LOG(base, nombre)

Sans base, logarithme népérien (identique à LN). Avec base, logarithme de nombre en base base. NULL si nombre <= 0, base <= 0 ou base = 1.

SELECT LOG(2, 8);  -- 3.0

LOG10#

LOG10(nombre)

Logarithme décimal. NULL si nombre <= 0.

SELECT LOG10(1000), LOG10(-1);  -- 3.0, NULL

LOG2#

LOG2(nombre)

Logarithme en base 2. NULL si nombre <= 0.

SELECT LOG2(8);  -- 3.0

PI#

PI()

La constante π (3.141592653589793), sans argument.

Retour : DOUBLE. Fonction constante (évaluée une fois par instruction).

SELECT PI(), ROUND(PI(), 4);  -- 3.141592653589793, 3.1416

RAND#

RAND([graine])

Nombre flottant pseudo-aléatoire dans [0, 1). Sans argument, une nouvelle valeur à chaque appel. Avec graine (un entier constant ou une expression), le générateur est réamorcé avec cette graine : deux appels avec la même graine rendent la même valeur. graine égale à NULL équivaut à 0.

SELECT RAND();              -- par exemple 0.02946782614786525 (change à chaque appel)
SELECT RAND(42), RAND(42);   -- 0.5265574090027738, 0.5265574090027738

SIN, COS, TAN#

SIN(angle_radians)
COS(angle_radians)
TAN(angle_radians)

Fonctions trigonométriques usuelles, argument en radians.

Retour : DOUBLE.

SELECT SIN(PI()/2), COS(0), TAN(PI()/4);  -- 1.0, 1.0, 0.9999999999999999

COT#

COT(angle_radians)

Cotangente (1 / TAN(angle_radians)). NULL si TAN(angle_radians) = 0.

SELECT COT(1), COT(0);  -- 0.6420926159343308, NULL

ASIN#

ASIN(nombre)

Arc sinus, en radians. NULL si nombre hors de [-1, 1].

SELECT ASIN(1), ASIN(2);  -- 1.5707963267948966, NULL

ACOS#

ACOS(nombre)

Arc cosinus, en radians. NULL si nombre hors de [-1, 1].

SELECT ACOS(1), ACOS(0);  -- 0.0, 1.5707963267948966

ATAN / ATAN2#

ATAN(nombre)
ATAN(y, x)
ATAN2(y, x)

ATAN(nombre) : arc tangente en radians. ATAN(y, x) (ou ATAN2(y, x)) : angle du point (x, y), tenant compte du quadrant.

SELECT ATAN(1), ATAN(1, -1), ATAN2(-1, -1);  -- 0.7853981633974483, 2.356194490192345, -2.356194490192345

DEGREES#

DEGREES(angle_radians)

Convertit des radians en degrés.

SELECT DEGREES(PI());  -- 180.0

RADIANS#

RADIANS(angle_degres)

Convertit des degrés en radians.

SELECT RADIANS(180);  -- 3.141592653589793

GREATEST#

GREATEST(expr1, expr2, ...)

La plus grande des expressions, comparées selon un ordre commun déduit de leurs types (voir le tableau de typage de COALESCE en 10.4). NULL si l'une des expressions est NULL.

SELECT GREATEST(3, 7, 2);  -- 7

LEAST#

LEAST(expr1, expr2, ...)

La plus petite des expressions, mêmes règles que GREATEST.

SELECT LEAST(3, 7, 2);  -- 2

CRC32 / CRC32C#

CRC32(expr)
CRC32([graine,] expr)
CRC32C(expr)
CRC32C([graine,] expr)

Somme de contrôle CRC-32 (polynôme standard pour CRC32, Castagnoli pour CRC32C) des octets de expr (octets bruts pour une valeur binaire, texte UTF-8 sinon). Avec une graine (résultat d'un appel précédent), enchaîne le calcul : CRC32C(CRC32C('a'), 'b') équivaut à CRC32C('ab').

Retour : BIGINT (valeur non signée sur 32 bits, représentée comme un entier positif).

SELECT CRC32('MIRAJ'), CRC32C('MIRAJ');            -- 2416857158, 3509818374
SELECT CRC32C(CRC32C('a'), 'b') = CRC32C('ab');    -- 1

BIT_COUNT#

BIT_COUNT(n)

Nombre de bits à 1 de l'entier n sur 64 bits (BIT_COUNT(-1) : 64) ; NULL si n est NULL.

Retour : BIGINT.

SELECT BIT_COUNT(7);  -- 3

TO_NUMBER#

TO_NUMBER(expr)

Nombre lu dans le texte expr, comme CAST(expr AS DECIMAL) sans perte de décimales ; un nombre est rendu tel quel. Un texte qui n'est pas entièrement un nombre vaut son préfixe numérique (0 s'il n'y en a pas), avec l'avertissement 1292 (Truncated incorrect DECIMAL value).

Retour : BIGINT pour un entier constant, DECIMAL (échelle du texte) pour un décimal constant, DOUBLE sinon.

SELECT TO_NUMBER('123.45'), TO_NUMBER('42'), TO_NUMBER(7.5);  -- 123.45, 42, 7.5
SELECT TO_NUMBER('12abc');                                    -- 12 (avertissement 1292)

10.3 Fonctions de date et heure#

Les fonctions ci-dessous acceptent une valeur DATE, TIME, DATETIME ou TIMESTAMP, ou un texte qui se lit comme telle ('2026-09-13', '2026-09-13 08:09:10.5'). Les fonctions de l'heure courante acceptent une précision fractionnaire facultative de 0 à 6 chiffres (NOW(3), CURTIME(6)).

Récapitulatif :

NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP#

NOW([precision])

Date et heure courantes dans le fuseau de la session (variable time_zone, voir Fuseau horaire de la session), figées au début de l'instruction : tous les appels à NOW() dans une même instruction (y compris pour plusieurs lignes) rendent la même valeur. precision (0 à 6) fixe le nombre de chiffres des fractions de seconde. CURRENT_TIMESTAMP, LOCALTIME et LOCALTIMESTAMP sont des alias stricts, qui s'écrivent aussi sans parenthèses.

Retour : DATETIME.

SELECT NOW();                         -- '2026-10-19 03:25:54' (instant de l'exécution)
SELECT NOW(3);                        -- '2026-10-19 03:27:45.989'
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP() = NOW();  -- '2026-10-19 03:27:45', 1

SYSDATE#

SYSDATE()

Comme NOW(), mais lit l'horloge à chaque appel (non figée pour l'instruction) : deux appels à SYSDATE() dans la même instruction peuvent différer.

SELECT SYSDATE() >= NOW(), SYSDATE(2);  -- 1, '2026-10-19 03:27:45.98'

CURDATE / CURRENT_DATE#

CURDATE()

Date courante (sans heure), figée pour l'instruction. CURRENT_DATE est un alias strict (avec ou sans parenthèses).

Retour : DATE.

SELECT CURDATE(), CURRENT_DATE;  -- '2026-10-19', '2026-10-19'

CURTIME / CURRENT_TIME#

CURTIME([precision])

Heure courante (sans date), figée pour l'instruction. CURRENT_TIME est un alias strict (avec ou sans parenthèses).

Retour : TIME.

SELECT CURTIME(), CURTIME(6);  -- '03:27:45', '03:27:45.989207'

UTC_TIMESTAMP, UTC_DATE, UTC_TIME#

UTC_TIMESTAMP([precision])
UTC_DATE()
UTC_TIME([precision])

Équivalents de NOW(), CURDATE() et CURTIME() en heure UTC plutôt que locale, tous figés pour l'instruction.

-- session dans le fuseau Europe/Paris (heure d'été)
SELECT NOW(), UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();
-- '2026-10-19 03:27:45', '2026-10-19 02:27:45', '2026-10-19', '02:27:45'

DATE#

DATE(expr)

Partie date d'une expression date/heure (heure mise à zéro).

Retour : DATE.

SELECT DATE('2026-09-13 08:09:10');  -- '2026-09-13'

TIME#

TIME(expr)

Partie heure d'une expression date/heure.

Retour : TIME.

SELECT TIME('2026-09-13 08:09:10');  -- '08:09:10'

TIMESTAMP#

TIMESTAMP(expr)
TIMESTAMP(date_expr, heure_expr)

Avec un argument, convertit expr en DATETIME. Avec deux arguments, ajoute la durée heure_expr à date_expr (le résultat peut passer au jour suivant).

Retour : DATETIME.

SELECT TIMESTAMP('2026-09-13');                         -- '2026-09-13 00:00:00'
SELECT TIMESTAMP('2026-09-13', '08:30:00');             -- '2026-09-13 08:30:00'
SELECT TIMESTAMP('2026-09-13 23:00:00', '02:00:00');    -- '2026-09-14 01:00:00'

YEAR#

YEAR(expr)

Année (BIGINT).

SELECT YEAR('2026-09-13');  -- 2026

MONTH#

MONTH(expr)

Mois, de 1 à 12 (BIGINT).

SELECT MONTH('2026-09-13');  -- 9

DAY / DAYOFMONTH#

DAY(expr)

Jour du mois, de 1 à 31 (BIGINT). DAYOFMONTH est un alias strict.

SELECT DAY('2026-09-13'), DAYOFMONTH('2026-09-13');  -- 13, 13

HOUR#

HOUR(expr)

Heure, de 0 à 23 pour une date/heure ; pour une valeur TIME, peut dépasser 23 (une durée TIME peut représenter plus de 24 heures), et une durée négative donne son heure sans le signe. Un texte est lu d'abord comme une durée [-]H:MM:SS, puis, s'il n'en est pas une, comme une date/heure.

SELECT HOUR('2026-09-13 08:09:10'), HOUR(TIME '125:30:00');  -- 8, 125
SELECT HOUR('125:30:00'), HOUR('-125:30:00');                -- 125, 125

MINUTE#

MINUTE(expr)

Minute, de 0 à 59. Un texte est lu comme pour HOUR : d'abord comme une durée, puis comme une date/heure.

SELECT MINUTE('2026-09-13 08:09:10'), MINUTE('125:30:15');  -- 9, 30

SECOND#

SECOND(expr)

Seconde, de 0 à 59. Un texte est lu comme pour HOUR.

SELECT SECOND('2026-09-13 08:09:10'), SECOND('125:30:15');  -- 10, 15

MICROSECOND#

MICROSECOND(expr)

Microsecondes de la partie fractionnaire (0 à 999999).

SELECT MICROSECOND('2026-09-13 08:09:10.123456'), MICROSECOND('10:00:00.5');  -- 123456, 500000

DAYOFWEEK#

DAYOFWEEK(expr)

Jour de la semaine selon la convention usuelle : 1 = dimanche, ..., 7 = samedi.

SELECT DAYOFWEEK('2026-09-13'), DAYOFWEEK('2026-09-19');  -- 1, 7 (dimanche, samedi)

WEEKDAY#

WEEKDAY(expr)

Jour de la semaine selon la convention ISO : 0 = lundi, ..., 6 = dimanche.

SELECT DAYOFWEEK('2026-09-13'), WEEKDAY('2026-09-13');  -- 1, 6 (dimanche)

DAYOFYEAR#

DAYOFYEAR(expr)

Rang du jour dans l'année (1 à 366).

SELECT DAYOFYEAR('2026-09-13');  -- 256

QUARTER#

QUARTER(expr)

Trimestre de l'année, de 1 à 4.

SELECT QUARTER('2026-09-13');  -- 3

WEEK#

WEEK(expr [, mode])

Numéro de semaine dans l'année (0 par défaut sans mode). mode (0 à 7) combine trois choix indépendants par ses bits :

SELECT WEEK('2026-01-01', 0);  -- 0 (le 1er janvier 2026 est un jeudi, avant le premier dimanche)
SELECT WEEK('2026-01-01', 1);  -- 1
SELECT WEEK('2026-09-13');     -- 37

WEEKOFYEAR#

WEEKOFYEAR(expr)

Équivalent à WEEK(expr, 3) (semaines ISO 8601 : lundi, numérotées 1 à 53, semaine 1 = celle contenant le premier jeudi de l'année).

SELECT WEEKOFYEAR('2026-09-13');  -- 37

YEARWEEK#

YEARWEEK(expr [, mode])

Année et semaine combinées en un seul entier AAAASS (mode par défaut 0, mêmes bits que WEEK, avec le bit « semaines 1–53 » toujours actif).

SELECT YEARWEEK('2026-09-13');  -- 202637

DAYNAME#

DAYNAME(expr)

Nom complet du jour de la semaine, en anglais ('Sunday', 'Monday', ...).

SELECT DAYNAME('2026-09-13');  -- 'Sunday'

MONTHNAME#

MONTHNAME(expr)

Nom complet du mois, en anglais ('January', ...).

SELECT MONTHNAME('2026-09-13');  -- 'September'

LAST_DAY#

LAST_DAY(expr)

Date du dernier jour du mois de expr.

Retour : DATE.

SELECT LAST_DAY('2026-02-10');  -- '2026-02-28'

DATE_FORMAT / TIME_FORMAT#

DATE_FORMAT(expr, format)

Met expr en forme selon format, une chaîne avec des spécificateurs %X :

TIME_FORMAT est un alias strict (utile en documentation avec un format sans partie date).

Retour : texte.

SELECT DATE_FORMAT('2026-09-13 08:09:10', '%W %d %M %Y à %H:%i');
-- 'Sunday 13 September 2026 à 08:09'
SELECT DATE_FORMAT('2026-09-01', '%D %b %y, %a %j');   -- '1st Sep 26, Tue 244'
SELECT TIME_FORMAT('08:09:10', '%Hh%i');               -- '08h09'

TO_CHAR#

TO_CHAR(expr [, modele])

Met en forme une date, une heure ou une date-heure (ou un texte lu comme une date-heure) selon un modèle à la Oracle. Sans modele, 'YYYY-MM-DD HH24:MI:SS' est employé quel que soit le type de expr (une date s'écrit donc avec 00:00:00, une heure avec 0000-00-00). Les champs se lisent sans égard à la casse :

La ponctuation et les espaces sont recopiés ; un texte entre guillemets doubles l'est aussi, sans ses guillemets ('YYYY"abc"MM'). Un | seul sépare deux champs sans rien écrire ; de plusieurs | à la suite, le premier seul est omis. Les noms de mois et de jours sont en anglais et s'écrivent January, Jan, Monday, Mon quelle que soit la casse du modèle. Pour une heure (TIME), l'année, le mois et le jour valent zéro (MON, DY : 00) et HH24 peut dépasser 23.

NULL si un argument est NULL, si le modèle est vide ou si un texte ne se lit pas comme une date (avertissement 1292). Toute autre lettre, un chiffre ou un caractère non ASCII hors guillemets est refusé : erreur 3047 (Invalid argument error: date format not recognized at … in function to_char.) pour un modèle constant, NULL et avertissement 3047 pour un modèle lu dans une colonne. Un nombre en premier argument est refusé (3047) : TO_CHAR ne met pas en forme les nombres (voir FORMAT). Le modèle compte au plus 144 octets.

Retour : texte sous l'interclassement de la connexion (@@collation_connection), quel que soit celui d'un premier argument texte ; avec un modèle constant, sa longueur maximale est connue (VARCHAR(10) pour 'YYYY-MM-DD', 27 caractères comptés pour MONTH et DAY).

SELECT TO_CHAR('2026-09-13 08:09:10', 'DD/MM/YYYY HH24:MI');      -- '13/09/2026 08:09'
SELECT TO_CHAR('2026-09-13 20:09:10', 'DY DD MON YYYY, HH:MI PM'); -- 'Sun 13 Sep 2026, 08:09 PM'
SELECT TO_CHAR('2026-09-13', 'YYYY"-T"MM');                         -- '2026-T09'
SELECT TO_CHAR(DATE'2026-09-13');                                   -- '2026-09-13 00:00:00'

En langage PostgreSQL, to_char est la fonction de PostgreSQL (modèles de dates et de nombres, voir le chapitre 30).

STR_TO_DATE#

STR_TO_DATE(chaine, format)

Analyse chaine selon format (mêmes spécificateurs que DATE_FORMAT, plus %p et %T en lecture) et rend la date/heure correspondante. NULL si chaine ne correspond pas exactement au format ou décrit une date invalide (par ex. '2026-02-30'). Le type de retour (DATE, TIME ou DATETIME) est déduit des spécificateurs présents dans format quand celui-ci est une constante.

SELECT STR_TO_DATE('13/09/2026', '%d/%m/%Y');              -- '2026-09-13' (DATE)
SELECT STR_TO_DATE('13/09/2026 08:30', '%d/%m/%Y %H:%i');  -- '2026-09-13 08:30:00' (DATETIME)
SELECT STR_TO_DATE('08:30 PM', '%h:%i %p');                 -- '20:30:00' (TIME)
SELECT STR_TO_DATE('31/02/2026', '%d/%m/%Y');              -- NULL (jour invalide)

DATE_ADD / ADDDATE#

DATE_ADD(expr, INTERVAL n unite)

Ajoute n unités unite à expr. Unités simples : YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND. Unités composites acceptées avec un intervalle textuel (INTERVAL '1:30' HOUR_MINUTE, INTERVAL '2-3' YEAR_MONTH, etc.) : YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND. Un ajout de mois qui dépasse le dernier jour du mois cible le ramène à ce dernier jour ('2026-01-31' + INTERVAL 1 MONTH = '2026-02-28'). NULL si le résultat sort de la plage 0001-01-01 à 9999-12-31, avec l'avertissement 1441 (Datetime function: datetime field overflow). ADDDATE est un alias strict, acceptant aussi la forme à trois arguments ADDDATE(expr, n, 'unite').

Retour : DATE si expr est une date et l'unité vaut un jour ou plus, TIME si expr est une heure, DATETIME sinon.

SELECT DATE_ADD('2026-01-31', INTERVAL 1 MONTH);        -- '2026-02-28'
SELECT DATE_ADD('2026-01-01 10:00', INTERVAL '1:30' HOUR_MINUTE);  -- '2026-01-01 11:30:00'
SELECT ADDDATE('2026-09-13', 10), ADDDATE('2026-09-13', INTERVAL 2 WEEK);  -- '2026-09-23', '2026-09-27'

DATE_SUB / SUBDATE#

DATE_SUB(expr, INTERVAL n unite)

Comme DATE_ADD, mais soustrait. SUBDATE est un alias strict, qui accepte aussi un nombre de jours en second argument.

SELECT DATE_SUB('2026-03-01', INTERVAL 1 DAY);  -- '2026-02-28'
SELECT SUBDATE('2026-09-13', 10);               -- '2026-09-03'

ADD_MONTHS#

ADD_MONTHS(expr, n)

Ajoute n mois à expr (les retranche si n est négatif) : équivalent de DATE_ADD(expr, INTERVAL n MONTH). n est arrondi à l'entier. Si le jour n'existe pas dans le mois d'arrivée, le dernier jour de ce mois est retenu ; le dernier jour d'un mois court n'est pas reporté sur le dernier jour du mois d'arrivée (ADD_MONTHS('2024-02-29', 1) = '2024-03-29'). NULL si un argument est NULL, si le résultat sort des années 1 à 9999 (avertissement 1441, comme DATE_ADD) ou si un texte ne se lit pas comme une date (avertissement 1292).

Retour : comme DATE_ADD avec une unité MONTH : DATE pour une date (ou une chaîne constante sans heure), DATETIME sinon (heure et fraction de seconde gardées).

SELECT ADD_MONTHS('2024-01-31', 1);            -- '2024-02-29'
SELECT ADD_MONTHS('2024-03-31', -1);           -- '2024-02-29'
SELECT ADD_MONTHS('2024-01-31 10:20:30', 13);  -- '2025-02-28 10:20:30'

MONTHS_BETWEEN#

MONTHS_BETWEEN(expr1, expr2)

Nombre de mois de expr2 à expr1 (expr1 - expr2), négatif si expr1 précède expr2. Le résultat est entier quand les deux dates tombent le même jour du mois ou sont toutes deux des derniers jours de mois ; sinon la partie fractionnaire compte la différence de jours (et d'heures) sur la base d'un mois de 31 jours.

Retour : DOUBLE.

SELECT MONTHS_BETWEEN('2026-09-13', '2026-06-13');   -- 3.0
SELECT MONTHS_BETWEEN('2026-03-31', '2026-02-28');   -- 1.0 (deux fins de mois)
SELECT MONTHS_BETWEEN('2026-02-15', '2026-01-01');   -- 1.4516129032258065
SELECT MONTHS_BETWEEN('2024-01-01', '2024-03-15');   -- -2.4516129032258065

TIMESTAMPADD#

TIMESTAMPADD(unite, n, expr)

Ajoute n unités unite (mêmes unités simples que DATE_ADD) à expr ; ordre des arguments différent de DATE_ADD.

Retour : DATETIME.

SELECT TIMESTAMPADD(DAY, 7, '2026-09-13');  -- '2026-09-20 00:00:00'

DATEDIFF#

DATEDIFF(expr1, expr2)

Différence en nombre entier de jours entre les parties date de expr1 et expr2 (expr1 - expr2), sans tenir compte de l'heure.

Retour : BIGINT.

SELECT DATEDIFF('2026-09-20', '2026-09-13');  -- 7

TIMEDIFF#

TIMEDIFF(expr1, expr2)

Différence expr1 - expr2 sous forme de durée (négative si expr1 précède expr2) ; les deux arguments doivent être du même genre (deux dates-heures ou deux heures).

Retour : TIME.

SELECT TIMEDIFF('2026-09-13 10:00:00', '2026-09-12 08:30:00');  -- '25:30:00'
SELECT TIMEDIFF('08:00', '09:30');                              -- '-01:30:00'

TIMESTAMPDIFF#

TIMESTAMPDIFF(unite, expr1, expr2)

Différence entière expr2 - expr1 exprimée en unite (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND). Pour YEAR, QUARTER et MONTH, le calcul est calendaire exact : il compte les mois pleins réellement écoulés (en tenant compte du jour et de l'heure), pas une approximation par division d'un nombre de jours moyen. Par exemple, entre deux 15 janvier consécutifs, la différence en années vaut toujours exactement 1, quelle que soit la présence d'une année bissextile entre les deux — un calcul par 365.25 jours donnerait parfois 0 à tort.

Retour : BIGINT.

SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-04-10');  -- 2 (pas encore 3 mois pleins)
SELECT TIMESTAMPDIFF(YEAR, '2001-01-01', '2002-01-01');   -- 1 (exact, calendaire)

ADDTIME#

ADDTIME(expr, duree)

Ajoute duree (une heure, ou un texte d'intervalle de temps) à expr.

Retour : DATETIME si expr porte une date, TIME sinon.

SELECT ADDTIME('2026-09-13 23:00:00', '02:30:00');  -- '2026-09-14 01:30:00'
SELECT ADDTIME('10:00:00', '1:15:30');              -- '11:15:30'

SUBTIME#

SUBTIME(expr, duree)

Comme ADDTIME, mais soustrait.

SELECT SUBTIME('2026-09-13 01:00:00', '02:00:00');  -- '2026-09-12 23:00:00'

UNIX_TIMESTAMP#

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(expr)

Sans argument, secondes écoulées depuis l'epoch Unix (1er janvier 1970 UTC) jusqu'à l'instant de début de l'instruction. Avec expr (heure locale du fuseau de la session, time_zone), secondes Unix correspondantes.

Retour : BIGINT.

SET time_zone = '+00:00';
SELECT UNIX_TIMESTAMP('1970-01-02 00:00:00'), UNIX_TIMESTAMP('2026-09-13 00:00:00');  -- 86400, 1789257600

FROM_UNIXTIME#

FROM_UNIXTIME(secondes [, format])

Date-heure, dans le fuseau de la session (time_zone), correspondant à secondes (secondes Unix, fractions acceptées à la milliseconde près). Avec format, rend directement le texte mis en forme (comme DATE_FORMAT).

Retour : DATETIME, ou texte si format est fourni.

SET time_zone = '+00:00';
SELECT FROM_UNIXTIME(1767323045);               -- '2026-01-02 03:04:05'
SELECT FROM_UNIXTIME(1767323045.5);             -- '2026-01-02 03:04:05.5'
SELECT FROM_UNIXTIME(1789257600, '%d/%m/%Y');   -- '13/09/2026'

TO_DAYS#

TO_DAYS(expr)

Nombre de jours écoulés depuis le jour 0 du calendrier grégorien proleptique, pour la partie date de expr.

Retour : BIGINT.

SELECT TO_DAYS('2026-09-13');  -- 740237

FROM_DAYS#

FROM_DAYS(n)

Date correspondant au nombre de jours n depuis le jour 0 (inverse de TO_DAYS). Ne tient pas compte du calendrier grégorien avant son adoption ; à réserver aux calculs de différences entre dates plutôt qu'à des dates historiques réelles.

Retour : DATE.

SELECT FROM_DAYS(740237);  -- '2026-09-13'

MAKEDATE#

MAKEDATE(annee, jour_de_lannee)

Date du jour_de_lannee-ième jour de annee (le 1er janvier = jour 1). NULL si jour_de_lannee < 1.

SELECT MAKEDATE(2026, 1);   -- '2026-01-01'
SELECT MAKEDATE(2026, 60);  -- '2026-03-01'

MAKETIME#

MAKETIME(heure, minute, seconde)

Construit une heure à partir de ses composantes (seconde accepte une fraction).

Retour : TIME.

SELECT MAKETIME(8, 30, 15);  -- '08:30:15'

TIME_TO_SEC#

TIME_TO_SEC(expr)

Nombre entier de secondes correspondant à la partie heure de expr (ou à expr directement s'il s'agit déjà d'une heure).

Retour : BIGINT.

SELECT TIME_TO_SEC('01:01:01'), TIME_TO_SEC('2026-09-13 10:00:00');  -- 3661, 36000

SEC_TO_TIME#

SEC_TO_TIME(secondes)

Heure correspondant à secondes (fractions de seconde acceptées).

Retour : TIME.

SELECT SEC_TO_TIME(3661);  -- '01:01:01'

EXTRACT#

EXTRACT(unite FROM expr)

Extrait un champ de expr : YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, ou une unité composite (YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND) qui combine plusieurs champs en un seul entier (par ex. YEAR_MONTH donne AAAAMM).

Retour : BIGINT.

SELECT EXTRACT(YEAR_MONTH FROM '2026-09-13');           -- 202609
SELECT EXTRACT(DAY_MINUTE FROM '2026-09-13 08:09:10');  -- 130809
SELECT EXTRACT(WEEK FROM '2026-09-13');                 -- 37

TO_SECONDS#

TO_SECONDS(expr)

Nombre de secondes écoulées depuis le jour 0 de l'an 0 jusqu'à expr (combine TO_DAYS et l'heure du jour).

Retour : BIGINT.

SELECT TO_SECONDS('2026-09-13 00:00:01');  -- 63956476801

PERIOD_ADD#

PERIOD_ADD(periode, n)

Ajoute n mois à periode, une période au format AAMM ou AAAAMM (les années à deux chiffres inférieures à 70 sont lues comme 20xx, sinon 19xx). 0 reste 0 quel que soit n.

Retour : BIGINT au format AAAAMM.

SELECT PERIOD_ADD(202609, 3);  -- 202612

PERIOD_DIFF#

PERIOD_DIFF(periode1, periode2)

Différence en mois entre deux périodes AAMM/AAAAMM.

SELECT PERIOD_DIFF(202612, 202609);  -- 3

GET_FORMAT#

GET_FORMAT({DATE | TIME | DATETIME | TIMESTAMP}, {'EUR' | 'USA' | 'JIS' | 'ISO' | 'INTERNAL'})

Rend la chaîne de format (compatible DATE_FORMAT) correspondant à une convention régionale nommée, par exemple pour l'utiliser ensuite avec STR_TO_DATE ou DATE_FORMAT. NULL si la combinaison n'est pas définie.

SELECT GET_FORMAT(DATE, 'EUR');  -- '%d.%m.%Y'
SELECT STR_TO_DATE('13.09.2026', GET_FORMAT(DATE, 'EUR'));  -- '2026-09-13'
SELECT GET_FORMAT(DATETIME, 'ISO'), GET_FORMAT(TIME, 'USA');  -- '%Y-%m-%d %H:%i:%s', '%h:%i:%s %p'

Fuseau horaire de la session#

SET [GLOBAL | SESSION] time_zone = 'SYSTEM' | '+02:00' | 'Europe/Paris' | DEFAULT;
SELECT @@time_zone, @@global.time_zone, @@system_time_zone;

time_zone vaut SYSTEM au départ (fuseau de la machine, avec ses passages à l'heure d'été). Les autres valeurs sont un décalage fixe de -13:59 à +14:00 ('+02:00') ou un nom de la base IANA ('Europe/Paris', 'UTC', casse ignorée), fournie avec MIRAJ : aucun fichier système à installer, sous Windows, macOS et Linux. Un fuseau inconnu ou un décalage hors plage rend l'erreur 1298. SET time_zone ne change que la session ; SET GLOBAL time_zone fixe la valeur que reçoivent les sessions ouvertes ensuite (elle n'est pas conservée après un redémarrage du serveur) et DEFAULT reprend la valeur globale.

Le fuseau de la session détermine :

• NOW() et ses synonymes, SYSDATE(), CURDATE(), CURTIME(), UNIX_TIMESTAMP() et FROM_UNIXTIME() ; UTC_TIMESTAMP(), UTC_DATE() et UTC_TIME() restent en UTC ;
• les colonnes TIMESTAMP : la valeur désigne un instant, rangé en UTC (fichiers, index, journaux, réplication, sauvegardes), que chaque session lit et écrit dans son propre fuseau (DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP comprises). Changer le fuseau de la machine ne déplace pas les valeurs rangées (une session en SYSTEM les montre dans le nouveau fuseau), et deux instants de l'heure répétée au passage à l'heure d'hiver restent distincts une fois rangés (clés, index uniques, ordre d'un index), même si une session de ce fuseau les lit à la même heure civile. Les colonnes générées et les valeurs des événements de changement voient une colonne TIMESTAMP à l'heure du fuseau de la machine. Les colonnes DATETIME, DATE, TIME et YEAR gardent l'heure écrite, sans conversion.

SET time_zone = '+00:00';
INSERT INTO t (ts, dt) VALUES ('2026-06-01 12:00:00', '2026-06-01 12:00:00');
SET time_zone = '+05:00';
SELECT ts, dt FROM t;   -- '2026-06-01 17:00:00' | '2026-06-01 12:00:00'

CAST(expr AS YEAR) convertit un nombre ou une date en année (CAST(69 AS YEAR) : 2069, CAST(70 AS YEAR) : 1970), NULL hors de 1901 à 2155 (0 reste 0) ; un entier de forme AAAAMMJJ[hhmmss] se convertit en DATE ou DATETIME par CAST, et un texte de forme libre ('200101', '2020.01.01', '20200101100000') aussi (voir le chapitre 6). Un DATE ou un DATETIME utilisé dans un calcul prend sa forme numérique (AAAAMMJJhhmmss) : CAST('2020-01-01 10:00:00' AS DATETIME) + 0 vaut 20200101100000.

CONVERT_TZ#

CONVERT_TZ(expr, fuseau_origine, fuseau_cible)

Convertit expr du fuseau fuseau_origine vers fuseau_cible. Chaque fuseau est soit 'SYSTEM' (heure locale du serveur), soit un décalage fixe ('+01:00', de -13:59 à +14:00), soit un nom de fuseau IANA ('Europe/Paris', 'UTC', ...). NULL si un fuseau est invalide ou si le résultat sort de la plage des années 1 à 9999 (jamais d'erreur 1298, réservée à SET time_zone). CONVERT_TZ ne dépend pas du time_zone de la session.

Retour : DATETIME.

SELECT CONVERT_TZ('2026-09-13 12:00:00', 'UTC', 'Europe/Paris');  -- '2026-09-13 14:00:00'

10.4 Fonctions de contrôle et logique#

Récapitulatif :

IF#

IF(condition, valeur_si_vrai, valeur_si_faux)

Rend valeur_si_vrai si condition est vraie (non nulle et différente de zéro), sinon valeur_si_faux. Une condition NULL compte comme fausse. Le type de retour est le type commun de valeur_si_vrai et valeur_si_faux (voir le tableau de COALESCE ci-dessous) ; la branche non prise n'est pas évaluée pour son type d'erreur mais compte pour le typage du résultat.

SELECT IF(1 > 0, 'oui', 'non');  -- 'oui'
SELECT IF(NULL, 'oui', 'non');   -- 'non'

IFNULL / NVL#

IFNULL(expr1, expr2)

Rend expr1 s'il n'est pas NULL, sinon expr2. NVL est un alias strict.

SELECT IFNULL(NULL, 'valeur par défaut');  -- 'valeur par défaut'
SELECT IFNULL('x', 'y');                   -- 'x'

COALESCE#

COALESCE(expr1, expr2, ...)

Rend la première expression non NULL de la liste, ou NULL si toutes le sont.

Type de retour : déduit de tous les arguments — tous entiers → BIGINT ; tous numériques avec un flottant → DOUBLE ; tous numériques sans flottant → DECIMAL (à l'échelle la plus large) ; tous temporels avec un DATETIME → DATETIME, sinon le type du premier argument ; tous VECTOR → VECTOR ; sinon texte. Ces mêmes règles s'appliquent à IF, IFNULL, GREATEST, LEAST et à CASE WHEN.

SELECT COALESCE(NULL, NULL, 'trouvé', 'ignoré');  -- 'trouvé'

NULLIF#

NULLIF(expr1, expr2)

Rend NULL si expr1 = expr2, sinon expr1 (avec son type et son échelle d'origine, y compris DECIMAL).

SELECT NULLIF(5, 5);  -- NULL
SELECT NULLIF(5, 6);  -- 5

ISNULL#

ISNULL(expr)

1 si expr est NULL, 0 sinon. Contrairement à la plupart des fonctions, ne rend jamais NULL elle-même.

Retour : BIGINT.

SELECT ISNULL(NULL);  -- 1
SELECT ISNULL(0);     -- 0

NVL2#

NVL2(expr, si_non_null, si_null)

Rend si_non_null si expr n'est pas NULL, si_null sinon. Type du résultat comme IF.

SELECT NVL2(NULL, 'a', 'b'), NVL2(0, 'a', 'b');  -- 'b', 'a'

INTERVAL (rang)#

INTERVAL(n, n1, n2, ...)

Rang de n dans la liste croissante n1 < n2 < ... : 0 si n < n1, 1 si n1 <= n < n2, et ainsi de suite ; -1 si n est NULL (les éléments NULL de la liste sont ignorés). Deux arguments au moins (sinon erreur de syntaxe 1064). Ne pas confondre avec l'INTERVAL n unité des calculs de dates, qui reste inchangé.

SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);  -- 3

DEFAULT#

DEFAULT(colonne)

Valeur par défaut déclarée de la colonne (NULL si elle n'en a pas et accepte NULL). Une colonne NOT NULL sans défaut rend l'erreur 1364, une colonne inconnue 1054, une expression qui n'est pas une colonne de table 1210. S'emploie dans une liste de sélection, un WHERE ou un UPDATE (SET a = DEFAULT(a) + 1). VALUES(colonne) hors d'un ON DUPLICATE KEY UPDATE rend NULL.

CREATE TABLE td (id INT PRIMARY KEY, n INT DEFAULT 10, s VARCHAR(5) DEFAULT 'abc');
INSERT INTO td (id) VALUES (1);
SELECT DEFAULT(n), DEFAULT(s) FROM td;   -- 10, 'abc'

NAME_CONST#

NAME_CONST(nom, valeur)

Rend valeur ; nom n'intervient pas dans le calcul (la forme sert aux instructions rejouées par la réplication).

SELECT NAME_CONST('total', 42);   -- 42

Les comparaisons a IS [NOT] DISTINCT FROM b (équivalent de NOT (a <=> b), NULL comparé comme une valeur) et x IS [NOT] UNKNOWN (test d'un résultat booléen NULL) complètent ces fonctions (voir 10.19).

10.5 Fonctions d'agrégation#

Les fonctions d'agrégation résument les valeurs d'un groupe de lignes (GROUP BY, ou toute la table sans GROUP BY) en une seule valeur. Contrairement aux fonctions scalaires, elles ignorent silencieusement les NULL de leur argument plutôt que de faire échouer tout le calcul.

Ces fonctions, à l'exception de GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_*, ANY_VALUE, STDDEV_SAMP, VAR_POP et VAR_SAMP, peuvent aussi s'utiliser comme fonctions de fenêtrage avec OVER (...) : voir 10.13.

Les exemples de cette section utilisent les tables suivantes :

CREATE TABLE clients (id INT PRIMARY KEY, nom VARCHAR(20), ville VARCHAR(20));
INSERT INTO clients VALUES (1,'Ali','Lyon'), (2,'Bob','Paris'), (3,'Chloé','Lyon');
CREATE TABLE commandes (id INT PRIMARY KEY, client_id INT, article_id INT, montant DECIMAL(8,2));
INSERT INTO commandes VALUES (1,1,10,25.00), (2,1,11,40.50), (3,2,10,12.00), (4,3,12,NULL), (5,1,10,30.00);
CREATE TABLE mesures (x INT, b INT);
INSERT INTO mesures VALUES (2,12), (4,10), (4,6), (6,NULL);
CREATE TABLE ventes (region VARCHAR(10), qte INT);
INSERT INTO ventes VALUES ('Nord',10), ('Nord',5), ('Sud',7);
CREATE TABLE parametres (code VARCHAR(10), valeur VARCHAR(10));
INSERT INTO parametres VALUES ('SEUIL','20'), ('JOUR',NULL);

Récapitulatif :

COUNT#

COUNT(*)
COUNT([DISTINCT] expr)
COUNT(DISTINCT expr, expr2 [, ...])

COUNT(*) compte toutes les lignes du groupe. COUNT(expr) compte les lignes où expr n'est pas NULL. COUNT(DISTINCT expr) compte les valeurs distinctes non NULL de expr. COUNT(DISTINCT expr, expr2, ...) compte les combinaisons distinctes de valeurs dont aucune n'est NULL ; chaque texte est comparé selon son interclassement ('a' et 'A' ne font qu'une valeur en utf8mb4_general_ci). Plusieurs expressions ne s'écrivent qu'avec DISTINCT (COUNT(a, b) et COUNT() : erreur de syntaxe 1064), et pas dans une fonction de fenêtrage.

Retour : BIGINT.

SELECT COUNT(*), COUNT(montant) FROM commandes;                        -- 5, 4
SELECT COUNT(DISTINCT client_id) FROM commandes;                       -- 3
SELECT COUNT(DISTINCT client_id, article_id) FROM commandes;           -- 4

SUM#

SUM([DISTINCT] expr)

Somme des valeurs non NULL de expr. NULL si le groupe ne contient aucune valeur non NULL. Sur une colonne VECTOR(n), effectue une somme élément par élément (résultat VECTOR(n)).

Retour : BIGINT si expr est entier, DECIMAL (même échelle) si expr est DECIMAL, DOUBLE sinon.

SELECT SUM(montant), SUM(DISTINCT client_id), SUM(id) FROM commandes;   -- 107.50, 6, 15

AVG#

AVG([DISTINCT] expr)

Moyenne des valeurs non NULL de expr. Sur VECTOR(n), moyenne élément par élément.

Retour : DECIMAL avec 4 décimales de plus que expr si expr est entier ou DECIMAL, DOUBLE sinon.

SELECT AVG(montant), AVG(id), AVG(montant * 1e0) FROM commandes;   -- 26.875000, 3.0000, 26.875

MIN / MAX#

MIN(expr)
MAX(expr)

Plus petite / plus grande valeur non NULL de expr selon l'ordre naturel du type (l'interclassement pour un texte). NULL si le groupe ne contient aucune valeur non NULL.

Retour : le type de expr.

SELECT MIN(montant), MAX(montant) FROM commandes;   -- 12.00, 40.50
SELECT MIN(nom), MAX(nom) FROM clients;             -- 'Ali', 'Chloé'

GROUP_CONCAT#

GROUP_CONCAT([DISTINCT] expr [, expr2, ...]
             [ORDER BY cle1 [ASC|DESC], ...]
             [SEPARATOR chaine]
             [LIMIT n | LIMIT m, n | LIMIT n OFFSET m])

Concatène les valeurs non NULL de expr du groupe (converties en texte) en une seule chaîne. Avec plusieurs expressions (expr, expr2, ...), chacune est concaténée directement (sans séparateur entre elles) pour chaque ligne, et la ligne entière est ignorée si l'une de ces expressions vaut NULL. DISTINCT élimine les doublons avant concaténation. ORDER BY (avec un numéro de position ou une expression) fixe l'ordre des valeurs dans le résultat — la clé de tri peut être une expression qui n'est pas elle-même concaténée. SEPARATOR fixe le séparateur entre valeurs (',' par défaut) ; SEPARATOR '' ne met aucun séparateur. LIMIT ne garde que n valeurs après en avoir passé m (aucune par défaut), une fois les doublons éliminés et les valeurs triées (dans l'ordre de lecture sans ORDER BY) ; n et m sont des entiers sans signe, des paramètres ? ou des variables d'une routine. Une limite qui ne garde aucune valeur rend un texte vide, et non NULL, pour un groupe qui en a. Les clauses s'écrivent dans cet ordre : ORDER BY, SEPARATOR, LIMIT.

La longueur du résultat est bornée par la variable group_concat_max_len (1 048 576 octets par défaut, de 4 à 4 294 967 295 ; option de démarrage --group-concat-max-len) : au-delà, le texte est tronqué à cette longueur en octets, sans jamais couper un caractère UTF-8, et l'instruction reçoit l'avertissement 1260 (N line(s) were cut by GROUP_CONCAT(), N étant le nombre de groupes tronqués), visible par SHOW WARNINGS. Le texte au-delà de la limite n'est jamais gardé en mémoire, y compris dans une lecture parallèle. SET [SESSION] group_concat_max_len = n change la limite de la session, SET GLOBAL celle des sessions ouvertes ensuite, SET STATEMENT group_concat_max_len = n FOR … celle d'une seule instruction.

Retour : texte.

SELECT GROUP_CONCAT(nom ORDER BY nom SEPARATOR ' ; ') FROM clients;
-- 'Ali ; Bob ; Chloé'
SELECT GROUP_CONCAT(DISTINCT ville) FROM clients;
-- 'Lyon,Paris'
SELECT GROUP_CONCAT(nom ORDER BY nom LIMIT 2 OFFSET 1) FROM clients;
-- 'Bob,Chloé'

STD / STDDEV / STDDEV_POP#

STD(expr)
STDDEV(expr)
STDDEV_POP(expr)

Écart-type de population des valeurs non NULL de expr (diviseur = nombre de valeurs). Les trois noms sont strictement équivalents. NULL si le groupe est vide.

Retour : DOUBLE.

SELECT STD(x), STDDEV(x), STDDEV_POP(x) FROM mesures;   -- 1.4142135623730951, 1.4142135623730951, 1.4142135623730951

STDDEV_SAMP#

STDDEV_SAMP(expr)

Écart-type corrigé (d'échantillon), diviseur = nombre de valeurs moins un. NULL si le groupe compte moins de deux valeurs.

SELECT STDDEV_SAMP(x) FROM mesures;   -- 1.632993161855452

VARIANCE / VAR_POP#

VARIANCE(expr)
VAR_POP(expr)

Variance de population (carré de STD). Les deux noms sont équivalents.

SELECT VARIANCE(x), VAR_POP(x) FROM mesures;   -- 2.0, 2.0

VAR_SAMP#

VAR_SAMP(expr)

Variance corrigée (carré de STDDEV_SAMP). NULL si le groupe compte moins de deux valeurs.

SELECT VAR_SAMP(x) FROM mesures;   -- 2.6666666666666665

BIT_AND, BIT_OR, BIT_XOR#

BIT_AND(expr)
BIT_OR(expr)
BIT_XOR(expr)

ET, OU et OU exclusif bit à bit de toutes les valeurs entières non NULL de expr (BIT_AND d'un groupe vide vaut tous les bits à 1, BIT_OR et BIT_XOR d'un groupe vide valent 0).

Retour : entier non signé sur 64 bits.

SELECT BIT_AND(b), BIT_OR(b), BIT_XOR(b) FROM mesures;     -- 0, 14, 0   (12, 10 et 6)
SELECT BIT_AND(b), BIT_OR(b) FROM mesures WHERE x > 100;   -- 18446744073709551615, 0

ANY_VALUE#

ANY_VALUE(expr)

Une valeur de expr prise dans le groupe (NULL pour un groupe vide), sans garantie sur laquelle. Sert à citer une colonne non agrégée et hors GROUP BY quand sql_mode contient ONLY_FULL_GROUP_BY, qui la refuserait nue (§10.5).

SELECT ville, ANY_VALUE(nom), COUNT(*) FROM clients GROUP BY ville ORDER BY ville;
-- 'Lyon', 'Ali', 2
-- 'Paris', 'Bob', 1

GROUPING#

GROUPING(expr [, expr2, ...])

Pour un GROUP BY ... WITH ROLLUP, ROLLUP(...), CUBE(...) ou GROUPING SETS (...) (chapitre 9, §9.17) : 1 si la ligne est une ligne de sous-total qui résume expr (la colonne y vaut NULL), 0 si NULL est une vraie valeur ou si la colonne est regroupée. Avec plusieurs arguments, un masque de bits dont le premier argument est le bit de poids fort (GROUPING(a, b) vaut 3 sur le total général). Chaque argument doit être une expression du GROUP BY (erreur 3580) ; GROUPING() sans argument rend 1582, et sa présence dans un WHERE ou sans regroupement 1111. Sans ROLLUP, vaut toujours 0.

Retour : BIGINT.

SELECT IF(GROUPING(region), 'total', IFNULL(region, '?')) AS region, SUM(qte)
FROM ventes GROUP BY region WITH ROLLUP;
-- 'Nord', 15
-- 'Sud', 7
-- 'total', 22

JSON_ARRAYAGG#

JSON_ARRAYAGG([DISTINCT] expr
              [ORDER BY cle1 [ASC|DESC], ...]
              [LIMIT n | LIMIT m, n | LIMIT n OFFSET m])

Construit un tableau JSON ([...]) à partir des valeurs de expr du groupe (dans l'ordre de lecture, ou celui de ORDER BY), chaque valeur étant convertie en document JSON scalaire (nombre tel quel, texte entre guillemets, NULL en null) sauf si expr produit déjà un document JSON. DISTINCT élimine les doublons, jugés sur la valeur de expr sous son interclassement comme dans GROUP_CONCAT ('a', 'A' et 'a ' confondus en utf8mb4_general_ci), pas sur son texte JSON ; LIMIT ne garde que n éléments après en avoir passé m, une fois les doublons éliminés et les éléments triés, comme dans GROUP_CONCAT (un NULL reste un élément, trié avant les autres valeurs ; [] si la limite ne garde rien). Le contenu entre les crochets est borné par group_concat_max_len, comme GROUP_CONCAT (avertissement 1260 au-delà ; le tableau tronqué n'est alors plus un document JSON valide).

Retour : texte JSON.

SELECT JSON_ARRAYAGG(nom) FROM clients;  -- '["Ali", "Bob", "Chloé"]'
SELECT JSON_ARRAYAGG(nom ORDER BY nom DESC LIMIT 2) FROM clients;  -- '["Chloé", "Bob"]'

JSON_OBJECTAGG#

JSON_OBJECTAGG(cle, valeur)

Construit un objet JSON ({...}) à partir des paires du groupe : chaque cle devient un nom de membre, chaque valeur est convertie comme pour JSON_ARRAYAGG. Une clé répétée garde sa première place et sa dernière valeur ; une clé NULL lève l'erreur 3158. NULL pour un groupe vide. Le contenu est borné par group_concat_max_len, comme pour JSON_ARRAYAGG.

Retour : texte JSON.

SELECT JSON_OBJECTAGG(code, valeur) FROM parametres;  -- '{"SEUIL": "20", "JOUR": null}'

10.6 Expressions régulières#

MIRAJ implémente les expressions régulières compatibles PCRE (moteur en temps linéaire, sans référence arrière ni assertions de largeur nulle) ; c'est le même moteur que l'opérateur REGEXP/RLIKE. Par défaut, la recherche est insensible à la casse, sauf sous l'interclassement utf8mb4_bin ('ABC' COLLATE utf8mb4_bin REGEXP 'abc' vaut 0) ; le type c / i l'emporte toujours. Les poids ne s'appliquent pas ('é' REGEXP 'e' est faux). Le paramètre facultatif type (dernier argument de chaque fonction) accepte une combinaison des lettres :

Un motif invalide lève une erreur ; un quantificateur appliqué directement à ^ ou $ est refusé.

Récapitulatif :

REGEXP_LIKE#

REGEXP_LIKE(expr, motif [, type])

1 si motif correspond quelque part dans expr, 0 sinon.

Retour : BIGINT.

SELECT REGEXP_LIKE('MIRAJ-1.0', '^[A-Z]+-[0-9.]+$');  -- 1
SELECT REGEXP_LIKE('ABC', 'abc'), REGEXP_LIKE('ABC', 'abc', 'c');  -- 1, 0

REGEXP_INSTR#

REGEXP_INSTR(expr, motif [, position [, occurrence [, option [, type]]]])

Position (en caractères, base 1) du début de la occurrence-ième correspondance (1 par défaut) à partir de position (1 par défaut) ; 0 si elle n'existe pas. Si option vaut 1, rend la position après la fin de la correspondance plutôt que son début (0 par défaut).

Retour : BIGINT.

SELECT REGEXP_INSTR('abc123def456', '[0-9]+', 1, 2);     -- 10 (début du second groupe de chiffres)
SELECT REGEXP_INSTR('abc123def456', '[0-9]+', 1, 2, 1);  -- 13 (position après sa fin)

REGEXP_SUBSTR#

REGEXP_SUBSTR(expr, motif [, position [, occurrence [, type]]])

Texte de la occurrence-ième correspondance de motif dans expr à partir de position. Rend une chaîne vide (pas NULL) si aucune correspondance n'est trouvée.

SELECT REGEXP_SUBSTR('prix: 42.50 EUR', '[0-9]+\\.[0-9]+');  -- '42.50'
SELECT REGEXP_SUBSTR('abc', '[0-9]+');                        -- '' (chaîne vide)

REGEXP_REPLACE#

REGEXP_REPLACE(expr, motif, remplacement [, position [, occurrence [, type]]])

Remplace par remplacement toutes les correspondances de motif à partir de position (toutes, par défaut), ou seulement la occurrence-ième si elle est précisée (> 0). Dans remplacement, \N (N de 0 à 9) insère le texte du N-ième groupe capturé (\0 = correspondance entière) ; un \N sans groupe correspondant insère une chaîne vide.

SELECT REGEXP_REPLACE('2026-09-13', '([0-9]+)-([0-9]+)-([0-9]+)', '\\3/\\2/\\1');
-- '13/09/2026'
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '#', 1, 2);   -- 'a1b#c3' (deuxième correspondance seulement)

10.7 Fonctions de hachage, chiffrement et encodage#

Récapitulatif :

MD5#

MD5(expr)

Empreinte MD5 (RFC 1321) de expr, en hexadécimal minuscule (32 caractères).

Retour : texte.

SELECT MD5('MIRAJ');  -- '58c6d474b82f107c62de4154e2c75ee4'

Attention — pas pour des mots de passe : MD5, SHA1 et PASSWORD sont rapides, sans sel et, pour les deux premiers, cassés face aux collisions. Une table d'empreintes MD5(mot_de_passe) ou SHA1(mot_de_passe) se retrouve par dictionnaire ou force brute en quelques minutes sur une carte graphique. Pour les mots de passe d'une application, hachez-les dans l'application avec une fonction lente et salée (Argon2id, scrypt ou bcrypt) et ne stockez que le résultat. Ces fonctions restent utiles pour une somme de contrôle, une clé de déduplication ou la compatibilité.

SHA1 / SHA#

SHA1(expr)

Empreinte SHA-1 (FIPS 180-4), en hexadécimal minuscule (40 caractères). SHA est un alias strict.

SELECT SHA1('MIRAJ');  -- 'ed6f42df071e947ccc017ef765bff993f67c1b9c'

SHA2#

SHA2(expr, longueur_bits)

Empreinte de la famille SHA-2, en hexadécimal minuscule. longueur_bits doit valoir 224, 256, 384, 512, ou 0 (équivalent à 256) ; toute autre valeur rend NULL.

SELECT SHA2('MIRAJ', 256);  -- '2e1b6300e6c695f56eae36449ea80f4e35315b99cc1941f9fbb5852ee338e9e8'

PASSWORD#

PASSWORD(mot_de_passe)

Empreinte utilisée en interne pour authentication_string d'un compte (voir le chapitre 15. Comptes et privilèges). N'est normalement pas nécessaire dans des requêtes applicatives.

SELECT PASSWORD('secret');  -- '*14E65567ABDB5135D0CFD9A70B3032C179A49EE7'

Attention : PASSWORD() est un double SHA-1 sans sel (* suivi de 40 chiffres hexadécimaux), gardé pour la compatibilité du protocole. Ne vous en servez pas pour les mots de passe de votre application (voir l'encadré de MD5) ; et une empreinte PASSWORD() ne se communique pas : elle suffit à se connecter au compte avec un client modifié.

HEX#

HEX(expr)

Représentation hexadécimale (majuscules) de expr : pour un nombre, sa valeur entière sur 64 bits ; pour une valeur binaire, ses octets bruts ; pour un texte, ses octets UTF-8.

SELECT HEX(255);      -- 'FF'
SELECT HEX('AB');     -- '4142'

UNHEX#

UNHEX(chaine_hex)

Décode chaine_hex (un chiffre de tête implicite 0 est ajouté si sa longueur est impaire) en octets bruts. NULL si chaine_hex contient un caractère qui n'est pas un chiffre hexadécimal.

Retour : binaire (VARBINARY).

SELECT UNHEX('4142');  -- 'AB'

TO_BASE64#

TO_BASE64(expr)

Encode expr en base64 (alphabet standard, avec remplissage =), en insérant un retour à la ligne tous les 76 caractères.

Retour : texte.

SELECT TO_BASE64('MIRAJ'), TO_BASE64('é');            -- 'TUlSQUo=', 'w6k='
SELECT LOCATE('\n', TO_BASE64(REPEAT('a', 60)));        -- 77 (retour à la ligne après 76 caractères)

FROM_BASE64#

FROM_BASE64(chaine)

Décode chaine (base64 standard) en octets bruts ; les espaces, tabulations et retours à la ligne sont ignorés ; NULL si le texte n'est pas du base64 valide.

Retour : binaire.

SELECT FROM_BASE64(TO_BASE64('MIRAJ'));  -- 'MIRAJ'

AES_ENCRYPT#

AES_ENCRYPT(donnees, cle)

Chiffre donnees en AES-128-ECB (avec bourrage PKCS7), cle étant repliée par OU exclusif sur 16 octets quelle que soit sa longueur (mode par défaut, correspondant à block_encryption_mode = aes-128-ecb).

Retour : binaire.

SELECT HEX(AES_ENCRYPT('secret', 'ma_cle'));   -- '9E858F2C52FB8D1AE228315AB3C3B821'

Attention — compatibilité seulement, pas une protection des données : AES_ENCRYPT et AES_DECRYPT reproduisent un format historique, et ce format est faible :

• mode ECB, sans vecteur d'initialisation : deux blocs de 16 octets identiques donnent le même chiffré, si bien que des valeurs égales (ou des débuts de valeurs égaux) se repèrent sans la clé ;
• clé repliée sur 16 octets par OU exclusif, sans dérivation : une phrase de passe courte ou prévisible s'attaque directement, et deux clés différentes peuvent donner la même clé AES ;
• aucune authentification : un chiffré modifié se déchiffre souvent en données fausses sans erreur (le seul contrôle est le bourrage) ;
• la clé voyage dans le texte de la requête : MIRAJ la masque dans ses journaux et dans SHOW PROCESSLIST, mais tout ce qui voit la requête avant le serveur (application, traces côté client, réseau sans TLS) la voit en clair.

Utilisez-les pour relire des données déjà chiffrées ainsi. Pour protéger de nouvelles données, chiffrez-les dans l'application (AES-256-GCM ou ChaCha20-Poly1305, nonce aléatoire, clé gardée hors de la base) et stockez le résultat en VARBINARY.

AES_DECRYPT#

AES_DECRYPT(donnees_chiffrees, cle)

Inverse de AES_ENCRYPT avec la même dérivation de clé. NULL si le déchiffrement échoue (mauvaise clé, données corrompues).

SELECT AES_DECRYPT(AES_ENCRYPT('secret', 'ma_cle'), 'ma_cle');  -- 'secret'

COMPRESS#

COMPRESS(donnees)

Compresse donnees (texte ou binaire) : la longueur d'origine sur quatre octets, octet de poids faible en tête (30 bits utiles), suivie du flux zlib. Une chaîne vide reste une chaîne vide. Si le résultat se termine par une espace, un point . lui est ajouté, pour qu'une colonne CHAR ou VARCHAR ne la retire pas ; rangez de préférence le résultat dans une colonne VARBINARY ou BLOB.

Retour : binaire.

SELECT HEX(COMPRESS('a'));                         -- '01000000789C4B040000620062'
SELECT LENGTH(COMPRESS(REPEAT('a', 1000)));        -- 21

UNCOMPRESS#

UNCOMPRESS(donnees_compressees)

Inverse de COMPRESS. Une chaîne vide reste une chaîne vide. Une donnée illisible rend NULL avec un avertissement :

Une longueur annoncée plus grande que le contenu réel est sans effet : le contenu décompressé est rendu tel quel.

Retour : binaire.

SELECT UNCOMPRESS(COMPRESS('bonjour'));  -- 'bonjour'
SELECT UNCOMPRESS('abc');                -- NULL, avertissement 1259

UNCOMPRESSED_LENGTH#

UNCOMPRESSED_LENGTH(donnees_compressees)

Longueur d'origine d'une valeur produite par COMPRESS, lue dans son en-tête sans rien décompresser. 0 pour une chaîne vide ; NULL avec l'avertissement 1259 pour une valeur de quatre octets au plus.

Retour : entier.

SELECT UNCOMPRESSED_LENGTH(COMPRESS(REPEAT('a', 30)));  -- 30

OLD_PASSWORD#

OLD_PASSWORD(mot_de_passe)

Ancienne empreinte de mot de passe sur 64 bits : 16 chiffres hexadécimaux minuscules ; chaîne vide pour une chaîne vide. Gardée pour relire d'anciennes données, comme PASSWORD (voir l'encadré de MD5).

Retour : texte.

SELECT OLD_PASSWORD('secret');  -- '428567f408994404'

ENCODE / DECODE#

ENCODE(chaine, mot_de_passe)
DECODE(chiffre, mot_de_passe)

Brouillage historique symétrique : ENCODE rend une valeur binaire de même longueur que chaine, DECODE la remet en clair avec le même mot de passe. Un mot de passe vide rend la chaîne telle quelle. Aucune sécurité réelle : réservé à la relecture d'anciennes données.

Retour : binaire.

SELECT HEX(ENCODE('MIRAJ', 'cle'));                  -- 'AB64DE848C'
SELECT DECODE(ENCODE('MIRAJ', 'cle'), 'cle');        -- 'MIRAJ'

DES_ENCRYPT / DES_DECRYPT#

DES_ENCRYPT(chaine, cle)
DES_DECRYPT(chiffre, cle)

Chiffrement Triple DES (CBC) avec une clé texte : le résultat commence par l'octet FF (clé texte), suivi du chiffré complété jusqu'à un multiple de 8 octets. Seule la forme avec une clé texte est disponible : sans second argument, ou avec un numéro de clé, DES_ENCRYPT rend NULL avec l'avertissement 1105 (DES key file not loaded). DES_DECRYPT rend telle quelle une valeur qui ne porte pas cet en-tête, et NULL si la clé est mauvaise.

Retour : binaire.

SELECT HEX(DES_ENCRYPT('MIRAJ', 'cle'));                   -- 'FF3775F69B04E0A7CF'
SELECT DES_DECRYPT(DES_ENCRYPT('MIRAJ', 'cle'), 'cle');    -- 'MIRAJ'
SELECT DES_DECRYPT('texte clair', 'cle');                  -- 'texte clair'

ENCRYPT#

ENCRYPT(mot_de_passe [, sel])

Empreinte crypt d'un mot de passe : DES traditionnelle avec un sel de deux caractères (alphabet ./0-9A-Za-z), ou MD5 ($1$sel$), SHA-256 ($5$sel$) et SHA-512 ($6$sel$). Sans sel, un sel DES aléatoire est tiré. NULL si le sel est trop court, invalide ou d'un format inconnu.

Retour : texte.

SELECT ENCRYPT('secret', 'ab');         -- 'abNANd1rDfiNc'
SELECT ENCRYPT('secret', '$1$sel$');    -- '$1$sel$/HICHqRMepslFM4ftMnyL.'
SELECT ENCRYPT('secret', '$5$sel$');    -- '$5$sel$jk8lUuVUlIs9bmXSnJ3Fsqp/qMlaMJy6D0CSZCxTv2B'
SELECT ENCRYPT('secret', 'a');          -- NULL

KDF#

KDF(cle, sel [, {info | iterations} [, methode [, largeur_bits]]])

Dérive une clé binaire de largeur_bits / 8 octets (128 bits par défaut) à partir de cle et sel. methode vaut 'hkdf' (défaut, HKDF-SHA256 ; le troisième argument est alors le texte info) ou 'pbkdf2_hmac' (PBKDF2-HMAC-SHA256 ; le troisième argument est le nombre d'itérations, 1000 par défaut, de 1 à 10 000 000). Une méthode inconnue, une largeur nulle, négative, non multiple de 8 ou trop grande, ou un nombre d'itérations hors limites lèvent l'erreur 1210.

Retour : binaire.

SELECT HEX(KDF('motdepasse', 'sel'));                              -- 'DF033C95264D7D131F666FAF915665CE'
SELECT HEX(KDF('motdepasse', 'sel', 1000, 'pbkdf2_hmac'));         -- 'E45D453A16A4523E069E4A4D7DCBC3A4'
SELECT LENGTH(KDF('motdepasse', 'sel', 'contexte', 'hkdf', 256));  -- 32

10.8 Fonctions réseau (IP)#

Récapitulatif :

INET_ATON#

INET_ATON(adresse_ipv4)

Convertit une adresse IPv4 en notation décimale pointée stricte (quatre parties, sans forme abrégée) en son entier 32 bits. NULL si adresse_ipv4 n'est pas une IPv4 valide dans cette notation.

Retour : BIGINT.

SELECT INET_ATON('192.168.1.1');  -- 3232235777

INET_NTOA#

INET_NTOA(entier)

Inverse de INET_ATON. NULL si entier n'est pas dans [0, 4294967295].

SELECT INET_NTOA(3232235777);  -- '192.168.1.1'

INET6_ATON#

INET6_ATON(adresse)

Convertit une adresse IPv4 ou IPv6 (notation texte) en sa forme binaire (4 octets pour IPv4, 16 pour IPv6). NULL si adresse n'est ni l'une ni l'autre.

Retour : binaire.

SELECT HEX(INET6_ATON('2001:db8::1'));   -- '20010DB8000000000000000000000001'
SELECT HEX(INET6_ATON('10.0.0.1'));      -- '0A000001'

INET6_NTOA#

INET6_NTOA(binaire)

Inverse de INET6_ATON : forme texte (abrégée) d'une adresse binaire de 4 ou 16 octets. NULL pour toute autre longueur.

SELECT INET6_NTOA(INET6_ATON('2001:0db8:0000::0001'));   -- '2001:db8::1'
SELECT INET6_NTOA(UNHEX('0A000001'));                    -- '10.0.0.1'

IS_IPV4#

IS_IPV4(chaine)

1 si chaine est une adresse IPv4 valide, 0 sinon (jamais NULL pour une entrée non NULL).

Retour : BIGINT.

SELECT IS_IPV4('10.0.0.1'), IS_IPV4('10.0.0.256');   -- 1, 0

IS_IPV6#

IS_IPV6(chaine)

1 si chaine est une adresse IPv6 valide, 0 sinon.

SELECT IS_IPV6('::1'), IS_IPV6('10.0.0.1');   -- 1, 0

IS_IPV4_COMPAT#

IS_IPV4_COMPAT(binaire)

1 si binaire (16 octets, comme produit par INET6_ATON) est une adresse IPv4 compatible obsolète (::a.b.c.d : douze premiers octets à zéro), 0 sinon.

SELECT IS_IPV4_COMPAT(INET6_ATON('::10.0.0.1'));   -- 1

IS_IPV4_MAPPED#

IS_IPV4_MAPPED(binaire)

1 si binaire est une adresse IPv4 mappée (::ffff:a.b.c.d), 0 sinon.

SELECT IS_IPV4_MAPPED(INET6_ATON('::ffff:10.0.0.1')), IS_IPV4_MAPPED(INET6_ATON('::10.0.0.1'));   -- 1, 0

10.9 Fonctions système, session et verrous#

Récapitulatif :

DATABASE / SCHEMA#

DATABASE()

Nom de la base courante de la session, NULL si aucune base n'est sélectionnée. SCHEMA est un alias strict. Fonction constante (une seule évaluation par instruction).

USE test;
SELECT DATABASE(), SCHEMA();   -- 'test', 'test'

VERSION#

VERSION()

Numéro de version du serveur MIRAJ, au format compatible avec les connecteurs existants : 9.0.40-miraj-1.0.0, identique à @@version et à SHOW VARIABLES LIKE 'version'. Le numéro de tête, 9.0.40, est celui que le serveur annonce seul dans la poignée de main du protocole : les connecteurs et les applications le comparent aux numéros de version qu'ils attendent pour activer leurs fonctions (WordPress, par exemple, exige au moins 5.5.5 et n'installe pas sinon). Suivent le nom et la version du produit (voir le chapitre 1. Introduction pour les variables qui distinguent les éditions).

Retour : texte.

SELECT VERSION();   -- '8.0.40-miraj-1.0.0'

CHARSET#

CHARSET(expr)

Jeu de caractères de expr : utf8mb4 pour un texte (littéral, colonne), binary pour un nombre, une date, NULL ou une valeur binaire ; latin1 ou utf8mb3 pour CONVERT(... USING jeu). Une colonne inconnue rend 1054. CHARSET, COLLATION et COERCIBILITY prennent exactement un argument : CHARSET() ou CHARSET(a, b) est une erreur de syntaxe (1064), et non 1582.

SELECT CHARSET('a'), CHARSET(1), CHARSET(CONVERT('a' USING latin1));   -- 'utf8mb4', 'binary', 'latin1'

COLLATION#

COLLATION(expr)

Interclassement de expr : celui de la colonne ou de COLLATE (utf8mb4_general_ci par défaut d'un littéral, utf8mb4_unicode_ci, utf8mb4_bin, voir le chapitre 13), celui de l'opérande retenu pour CONCAT, UPPER, etc. ; binary pour un nombre, une date ou NULL.

SELECT COLLATION('a'), COLLATION('a' COLLATE utf8mb4_bin), COLLATION(1);   -- 'utf8mb4_general_ci', 'utf8mb4_bin', 'binary'

COERCIBILITY#

COERCIBILITY(expr)

Priorité d'interclassement de expr, qui sert à arbitrer entre deux textes d'interclassements différents : 0 pour un COLLATE explicite, 2 pour une colonne, 3 pour une fonction système (USER()), 4 pour un CAST(... AS CHAR), 6 pour un littéral texte, 7 pour un nombre, 8 pour NULL. Détail au chapitre 13.

SELECT COERCIBILITY('a' COLLATE utf8mb4_bin), COERCIBILITY(USER()), COERCIBILITY(CAST(1 AS CHAR)), COERCIBILITY('a');
-- 0, 3, 4, 6
SELECT COERCIBILITY(1), COERCIBILITY(NULL);   -- 7, 8

BENCHMARK#

BENCHMARK(n, expr)

Fonction de mesure : rend toujours 0 (MIRAJ n'évalue pas expr n fois). BENCHMARK(NULL, x) rend NULL, BENCHMARK(-1, x) aussi, avec l'avertissement 1411.

SELECT BENCHMARK(1000, MD5('a')), BENCHMARK(NULL, 1);   -- 0, NULL

LAST_INSERT_ID#

LAST_INSERT_ID()

Dernière valeur de colonne auto-incrémentée générée par une instruction INSERT de la session.

LAST_INSERT_ID(expr) fixe en plus cette valeur à expr (et la rend) : LAST_INSERT_ID(55) puis LAST_INSERT_ID() valent 55, comme @@last_insert_id. Avec NULL, la valeur devient 0. Les appels d'une même instruction s'évaluent dans l'ordre des lignes. Après un INSERT de plusieurs lignes, c'est la valeur générée pour la première ligne.

Retour : BIGINT.

CREATE TABLE ai (id INT AUTO_INCREMENT PRIMARY KEY, x INT);
INSERT INTO ai (x) VALUES (1), (2), (3);
SELECT LAST_INSERT_ID();                       -- 1
SELECT LAST_INSERT_ID(55), LAST_INSERT_ID();   -- 55, 55

ROW_COUNT#

ROW_COUNT()

Nombre de lignes affectées par la dernière instruction INSERT, UPDATE ou DELETE de la session.

UPDATE ai SET x = x + 1 WHERE id > 1;
SELECT ROW_COUNT();   -- 2

FOUND_ROWS#

FOUND_ROWS()

Nombre de lignes du dernier résultat lu (usage historique, propre à la connexion). Après un SELECT SQL_CALC_FOUND_ROWS ... LIMIT n, nombre de lignes que le SELECT aurait rendues sans son LIMIT : SELECT SQL_CALC_FOUND_ROWS id FROM t LIMIT 2; SELECT FOUND_ROWS(); rend le total de t.

SELECT SQL_CALC_FOUND_ROWS id FROM ai LIMIT 2;   -- 2 lignes
SELECT FOUND_ROWS();                             -- 3

CONNECTION_ID#

CONNECTION_ID()

Identifiant de connexion de la session courante, tel qu'affiché par SHOW PROCESSLIST.

SELECT CONNECTION_ID();   -- par exemple 12

USER / SESSION_USER / SYSTEM_USER#

USER()

Identité du client telle que fournie à la connexion, au format 'utilisateur@hôte' (hôte du client, pas celui du compte). SESSION_USER et SYSTEM_USER sont des alias stricts. Dans une routine SQL SECURITY DEFINER, USER() reste le compte de la connexion : seul CURRENT_USER() y rend le définisseur.

SELECT USER(), SESSION_USER(), SYSTEM_USER();   -- 'root@localhost', 'root@localhost', 'root@localhost'

CURRENT_USER#

CURRENT_USER()

Compte réellement utilisé pour les vérifications de privilèges, au format 'utilisateur@hôte' (l'hôte défini pour le compte, qui peut différer de celui de USER() si le compte a une entrée générique comme '%'). S'écrit aussi sans parenthèses (CURRENT_USER).

SELECT CURRENT_USER(), CURRENT_USER;   -- 'root@localhost', 'root@localhost'

CURRENT_ROLE#

CURRENT_ROLE()

Rôles actifs de la session, ou la chaîne 'NONE' si aucun rôle n'est actif (voir le chapitre 15. Comptes et privilèges).

SELECT CURRENT_ROLE();   -- 'NONE'

SLEEP#

SLEEP(secondes)

Suspend l'exécution pendant secondes (fractions acceptées), puis rend 0. Un argument NULL ou négatif ne fait pas attendre (rend 0 immédiatement). Un appel n'attend jamais plus d'une heure : une valeur plus grande est ramenée à 3 600 secondes, sans avertissement (un SLEEP démesuré, par erreur ou par malveillance, ne tient plus une session et un fil du serveur pendant des jours). L'attente est interruptible : KILL QUERY sur la session l'abrège et SLEEP rend 1 ; KILL CONNECTION ou le dépassement de max_statement_time lèvent une erreur plutôt que de rendre une valeur.

Retour : BIGINT.

SELECT SLEEP(0.5);  -- attend une demi-seconde, rend 0

UUID / UUID_V4#

UUID()
UUID_V4()

Identifiant UUID version 4 aléatoire, en minuscules (xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx). UUID_V4 est un alias strict.

Ses 122 bits aléatoires viennent d'un générateur cryptographique (ChaCha, amorcé et réamorcé par le système), distinct de celui de RAND() : un UUID ne se déduit ni des précédents ni des valeurs de RAND(). Il peut donc servir d'identifiant difficile à deviner (lien de partage, jeton). Pour un secret brut (sel, clé, jeton binaire), préférez RANDOM_BYTES.

Retour : texte.

SELECT UUID();     -- par exemple '7a6ba694-a7f0-45b8-af54-3fbdfb8ed614'
SELECT UUID_V4();  -- par exemple '65fe50fe-cd82-4979-96c2-f73a0e6256f8'

UUID_V7#

UUID_V7()

Identifiant UUID version 7, ordonné dans le temps : les 48 premiers bits portent l'instant en millisecondes, la suite est aléatoire. Deux valeurs successives se trient dans l'ordre de leur création, ce qui en fait une bonne clé primaire textuelle (insertions en fin d'index). Nouvelle valeur à chaque appel et à chaque ligne.

Retour : texte.

SELECT UUID_V7();  -- par exemple '01a151fb-ce7c-7000-a185-3b114b9e8c8c'

SYS_GUID#

SYS_GUID()

Le même identifiant que UUID, écrit sans tirets : 32 chiffres hexadécimaux en minuscules. Comme UUID(), chaque appel et chaque ligne donnent une nouvelle valeur ; la fonction est refusée dans une contrainte CHECK et une colonne générée, et admise en valeur par défaut (DEFAULT SYS_GUID()).

Retour : texte.

SELECT SYS_GUID();  -- par exemple '499fae041ce14b428ded02de5582f5c8'

RANDOM_BYTES#

RANDOM_BYTES(n)

n octets aléatoires tirés directement du générateur du système d'exploitation (qualité cryptographique), pour un sel, une clé, un vecteur d'initialisation ou un jeton. n va de 1 à 1024 ; hors de cet intervalle, erreur 1690 (length value is out of range in 'random_bytes'). NULL si n est NULL. Jamais évaluée comme une constante : chaque appel, chaque ligne, donne une nouvelle valeur.

Retour : binaire (VARBINARY).

SELECT HEX(RANDOM_BYTES(16));             -- par exemple '194E96E9343C758A83AC7012CC2E1502'
SELECT LENGTH(RANDOM_BYTES(32));          -- 32

UUID_SHORT#

UUID_SHORT()

Identifiant entier croissant, unique dans le temps : chaque appel rend la valeur précédente plus un.

Retour : BIGINT.

SELECT UUID_SHORT(), UUID_SHORT();   -- par exemple 102128687452782593, 102128687452782594

GET_LOCK#

GET_LOCK(nom, delai)

Tente de prendre un verrou nommé nom (64 caractères au plus) pour la session. Rend 1 si le verrou est pris (les prises répétées par la même session se cumulent), 0 si delai secondes (fraction permise ; NULL = 0) s'écoulent sans y parvenir, NULL si l'attente est interrompue par KILL QUERY ou hors de toute session. Un délai négatif (« attente illimitée ») ou supérieur à un an est ramené à un an, comme les autres délais du serveur. Lève une erreur si KILL CONNECTION survient, si max_statement_time est dépassé, ou si l'attente fermerait un cycle entre verrous nommés (interblocage détecté).

Une session tient au plus 1 024 verrous nommés distincts à la fois (les reprises d'un verrou qu'elle tient déjà ne comptent pas) : au-delà, GET_LOCK lève l'erreur 1210 (Arguments incorrects pour GET_LOCK (1024 verrous nommés distincts au plus par session)) sans rien prendre. RELEASE_LOCK ou RELEASE_ALL_LOCKS libèrent des places.

Retour : BIGINT.

SELECT GET_LOCK('verrou_import', 10);      -- 1
-- ... traitement protégé ...
SELECT RELEASE_LOCK('verrou_import');      -- 1

RELEASE_LOCK#

RELEASE_LOCK(nom)

Rend une prise du verrou nom détenue par la session. 1 si une prise a été rendue, 0 si le verrou est détenu par une autre session, NULL s'il n'est pas détenu du tout.

SELECT GET_LOCK('verrou', 1), RELEASE_LOCK('verrou'), RELEASE_LOCK('verrou');   -- 1, 1, NULL

IS_FREE_LOCK#

IS_FREE_LOCK(nom)

1 si personne ne détient le verrou nom, 0 sinon.

SELECT IS_FREE_LOCK('verrou'), GET_LOCK('verrou', 1), IS_FREE_LOCK('verrou');   -- 1, 1, 0

IS_USED_LOCK#

IS_USED_LOCK(nom)

Identifiant de connexion de la session qui détient le verrou nom, NULL s'il est libre.

SELECT GET_LOCK('verrou', 1), IS_USED_LOCK('verrou') = CONNECTION_ID(), IS_USED_LOCK('autre');   -- 1, 1, NULL

RELEASE_ALL_LOCKS#

RELEASE_ALL_LOCKS()

Rend toutes les prises de verrous nommés de la session courante ; rend leur nombre.

Retour : BIGINT.

SELECT GET_LOCK('a', 0), GET_LOCK('b', 0), RELEASE_ALL_LOCKS();   -- 1, 1, 2

MIRAJ_NOTIFY#

MIRAJ_NOTIFY(canal [, charge])

Forme fonction de NOTIFY canal, charge (chapitre 21, §21.4) : la notification est publiée aux abonnés du canal à la validation de la transaction de l'instruction, jamais si elle échoue ou est annulée. Rend 1. Un canal NULL ou vide lève l'erreur 1210, une charge trop longue (--change-events-max-payload) l'erreur 9010 ; éditions Entreprise et Cluster (9001 en édition Express).

Retour : BIGINT.

SELECT MIRAJ_NOTIFY('canal', 'charge');   -- 1

MIRAJ_CHANGE_SEQ#

MIRAJ_CHANGE_SEQ()

Dernier numéro d'événement de changement publié par le serveur (ou sa base de numérotation, tirée de l'horloge au démarrage, s'il n'en a publié aucun) : point de reprise d'un abonné qui veut tout relire (LISTEN … FROM, chapitre 21, §21.3). Éditions Entreprise et Cluster.

Retour : BIGINT.

SELECT MIRAJ_CHANGE_SEQ();   -- par exemple 1792376684321324

BINLOG_GTID_POS#

BINLOG_GTID_POS(fichier, position)

État GTID du journal binaire à la position donnée d'un de ses fichiers (voir le chapitre 17). NULL si un argument est NULL, si le fichier est inconnu ou si la position est au-delà de sa fin.

Retour : texte.

SELECT BINLOG_GTID_POS('absent.000001', 4);   -- NULL (fichier inconnu)

MASTER_GTID_WAIT#

MASTER_GTID_WAIT(position_gtid [, delai])

Sur un réplica, attend que la position GTID donnée ('domaine-serveur-numéro', plusieurs séparées par des virgules) ait été appliquée, au plus delai secondes (sans fin si omis). Rend 0 quand la position est atteinte, -1 si le délai s'écoule. Une position mal formée lève l'erreur 1210.

Retour : BIGINT.

SELECT MASTER_GTID_WAIT('0-1-1', 0);   -- -1 (rien n'a été appliqué sur ce serveur)

MASTER_POS_WAIT#

MASTER_POS_WAIT(fichier, position [, delai [, canal]])

Sur un réplica, attend que la source ait été appliquée jusqu'à la position donnée de son journal binaire. Rend le nombre d'événements attendus, -1 si le délai s'écoule, NULL si la réplication ne tourne pas.

Retour : BIGINT.

SELECT MASTER_POS_WAIT('miraj-bin.000002', 4, 0);   -- NULL (aucune réplication en cours)

DECODE_HISTOGRAM#

DECODE_HISTOGRAM(type, histogramme)

Écrit en clair un histogramme binaire de statistiques de colonne : 'SINGLE_PREC_HB' lit un octet par seau (valeur / 255), 'DOUBLE_PREC_HB' deux octets par seau, octet de poids faible en tête (valeur / 65535), chaque seau étant écrit avec trois décimales et séparé du suivant par une virgule ; 'JSON_HB' rend le texte tel quel. Un autre type rend NULL.

Retour : texte.

SELECT DECODE_HISTOGRAM('SINGLE_PREC_HB', UNHEX('3F7FBFFF'));   -- '0.247,0.498,0.749,1.000'
SELECT DECODE_HISTOGRAM('DOUBLE_PREC_HB', UNHEX('FF3FFF7F'));   -- '0.250,0.500'

Variables système @@nom#

En complément des fonctions ci-dessus, une expression peut lire une variable système de session ou globale par @@nom ou @@session.nom / @@local.nom / @@global.nom (par exemple @@version, @@miraj_edition, @@max_allowed_packet). Ces variables sont décrites au chapitre 16. Administration du serveur ; elles se manipulent avec SET et se consultent aussi par SHOW VARIABLES. Une variable utilisateur @nom (propre à la session, NULL tant qu'elle n'a rien reçu) se lit de même et s'affecte dans une expression par @nom := valeur (voir 10.19).

SELECT @@version, @@session.sql_mode, @@GLOBAL.max_connections, @@autocommit;
-- '8.0.40-miraj-1.0.0', 'STRICT_TRANS_TABLES', 151, 1

10.10 Fonctions JSON#

MIRAJ porte un document JSON comme une simple valeur texte, écrite sous la forme {"a": 1, "b": [1, 2]} (espace après : et après chaque virgule). L'ordre des membres d'un objet est conservé tel qu'écrit, et un nombre garde son écriture d'origine (1.50 reste 1.50).

Échappements des chaînes. Une chaîne d'un document (valeur ou nom de membre) garde le texte écrit entre ses guillemets, échappements compris ("\u00e9", "http:\/\/x.fr", "\x") :

• les fonctions qui rendent du JSON le rendent tel quel : JSON_EXTRACT (et ->), JSON_QUERY, JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE / JSON_ARRAY_APPEND / JSON_ARRAY_INSERT (le reste du document n'est pas réécrit : un document produit par PHP json_encode garde ses \u00e9 et \/), JSON_MERGE_PRESERVE / JSON_MERGE_PATCH, JSON_COMPACT / JSON_LOOSE / JSON_DETAILED / JSON_NORMALIZE / JSON_PRETTY, JSON_KEYS, JSON_OBJECT_TO_ARRAY, JSON_KEY_VALUE, JSON_UNQUOTE d'un objet ou d'un tableau, JSON_SEARCH (chemins rendus) et la colonne JSON de JSON_TABLE ;
• les fonctions qui rendent un texte SQL le décodent : JSON_UNQUOTE (et ->>) d'une chaîne, JSON_VALUE, les colonnes de JSON_TABLE ; JSON_SEARCH compare son motif au texte décodé ; une valeur extraite comparée à un texte SQL l'est sans guillemets, décodée (JSON_EXTRACT('["\u0041"]', '$[0]') = 'A' vaut 1) ;
• les comparaisons de documents portent sur le texte écrit : JSON_EQUALS, JSON_CONTAINS, JSON_OVERLAPS, JSON_ARRAY_INTERSECT, JSON_OBJECT_FILTER_KEYS et JSON_SCHEMA_VALID (enum, const, pattern, required, properties..., minLength / maxLength comptés en octets de ce texte) distinguent "\u0041" de "A" (JSON_CONTAINS('["\u0041"]', '"A"') vaut 0) ; seuls JSON_CONTAINS et JSON_OVERLAPS retrouvent un membre d'objet par son nom décodé (JSON_CONTAINS('{"\u0041": 1}', '{"A": 1}') vaut 1). MEMBER OF (extension MIRAJ) compare les textes décodés ;
• une clé de chemin désigne le membre de même nom décodé ($.A et $."\u0041" désignent le membre "\u0041") pour JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_SET & co., JSON_REMOVE, JSON_KEYS, JSON_LENGTH, JSON_CONTAINS, JSON_KEY_VALUE et les colonnes de JSON_TABLE ; le membre de même nom écrit pour JSON_EXTRACT (et ->), JSON_CONTAINS_PATH, les chemins de JSON_SEARCH et le chemin des lignes (et NESTED PATH) de JSON_TABLE (seul $."\u0041" désigne alors ce membre). Un membre ajouté par JSON_SET prend le nom écrit dans le chemin.

Une chaîne venue du SQL (JSON_OBJECT, JSON_ARRAY, JSON_QUOTE, valeur de JSON_SET) est écrite avec ", \ et les caractères de contrôle échappés, le reste tel quel (/ et é non échappés).

Les fonctions se répartissent en trois familles : construction et validation (JSON_OBJECT, JSON_ARRAY, fusions, JSON_QUOTE, JSON_VALID), lecture par chemin (JSON_EXTRACT et l'opérateur ->, JSON_UNQUOTE et ->>, JSON_VALUE, JSON_CONTAINS, JSON_SEARCH...) et modification (JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT). Les agrégats JSON_ARRAYAGG et JSON_OBJECTAGG sont décrits avec les fonctions d'agrégation.

Récapitulatif :

JSON_OBJECT#

JSON_OBJECT([cle1, valeur1, cle2, valeur2, ...])

Construit un objet JSON à partir de paires clé/valeur (nombre pair d'arguments obligatoire). Chaque cle est convertie en texte JSON ; chaque valeur est convertie en scalaire JSON (nombre tel quel, texte entre guillemets échappés, NULL en null) sauf si elle est déjà un document JSON (produit par une autre fonction JSON ou un CAST(... AS JSON)). Une clé NULL lève une erreur.

Retour : texte JSON ; chaîne binaire si une clé ou une valeur est une chaîne binaire (x'…', CAST(… AS BINARY), colonne VARBINARY / BLOB, ou CHAR(n) sans USING écrit en argument) : ses octets sont gardés tels quels (sauf ", \ et les caractères de contrôle, échappés), un texte y est écrit avec un octet par caractère jusqu'à U+00FF et en \uXXXX au-delà (JSON_ARRAY('é', CHAR(1)) rend les octets [", E9, ", "\u0001"]). Même règle pour JSON_ARRAY.

SELECT JSON_OBJECT('nom', 'MIRAJ', 'version', 1.0);
-- '{"nom": "MIRAJ", "version": 1.0}'

JSON_ARRAY#

JSON_ARRAY([valeur1, valeur2, ...])

Construit un tableau JSON à partir de ses arguments (mêmes règles de conversion que JSON_OBJECT).

SELECT JSON_ARRAY(1, 'deux', NULL);  -- '[1, "deux", null]'

JSON_MERGE_PRESERVE / JSON_MERGE#

JSON_MERGE_PRESERVE(document1, document2, ...)

Fusionne au moins deux documents JSON : deux tableaux sont mis bout à bout ; deux objets sont réunis (les valeurs d'une clé présente dans les deux sont elles-mêmes fusionnées récursivement) ; toute autre combinaison (par ex. un objet et un tableau) traite chaque valeur isolée comme un tableau à un élément puis les met bout à bout. JSON_MERGE est un alias strict.

SELECT JSON_MERGE_PRESERVE('{"a": 1}', '{"a": 2, "b": 3}');  -- '{"a": [1, 2], "b": 3}'
SELECT JSON_MERGE_PRESERVE('[1, 2]', '[true, false]');       -- '[1, 2, true, false]'

JSON_MERGE_PATCH#

JSON_MERGE_PATCH(document1, document2, ...)

Fusion selon la RFC 7396 : les membres du document suivant remplacent ceux du précédent, un membre valant null retire la clé correspondante ; un document qui n'est pas un objet remplace entièrement le précédent. NULL si un document est NULL (sauf s'il est suivi d'un document qui n'est pas un objet, qui l'emporte quand même).

SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"a": null, "c": 3}');  -- '{"b": 2, "c": 3}'

JSON_QUOTE#

JSON_QUOTE(chaine)

Entoure chaine de guillemets doubles et échappe les caractères spéciaux JSON (caractère de contrôle en \u001F, chiffres majuscules), pour produire une chaîne JSON valide à partir d'un texte SQL brut. Une chaîne binaire (x'e9', CHAR(233)) est lue en latin1, un caractère par octet ("é") ; de même pour la valeur placée par JSON_SET & co.

SELECT JSON_QUOTE('a "b" c');  -- '"a \"b\" c"'

JSON_VALID#

JSON_VALID(chaine)

1 si chaine est un document JSON syntaxiquement valide, 0 sinon.

Retour : BIGINT.

SELECT JSON_VALID('{"a": 1}'), JSON_VALID('{a: 1}'), JSON_VALID('[1');   -- 1, 0, 0

Chemins JSON#

Les fonctions de lecture et de modification désignent une partie d'un document par un chemin, écrit comme une chaîne :

Un indice appliqué à une valeur qui n'est pas un tableau la traite comme un tableau d'un seul élément : $.a[0] désigne $.a elle-même. Les jokers (.*, [*], **) et les plages ne sont permis que par les fonctions de lecture qui peuvent rendre plusieurs valeurs (JSON_EXTRACT, JSON_CONTAINS_PATH, JSON_SEARCH) ; ailleurs la fonction rend NULL avec l'avertissement 4044, sauf JSON_VALUE et JSON_QUERY, qui rendent NULL sans avertissement, et JSON_EXISTS, qui les accepte. Un chemin mal écrit ou un document invalide ne lève pas d'erreur : la fonction rend NULL avec un avertissement (SHOW WARNINGS) qui cite l'argument et la fonction — 4041 (chemin inachevé) ou 4042 (position du caractère fautif) pour un chemin, 4037 (texte inachevé), 4038 (position) ou 4036 (caractère de contrôle dans une chaîne, position) pour un document. Un échappement inconnu dans une chaîne (\x) vaut le caractère lui-même. Un argument numérique ou temporel passé comme document est lu sous sa forme texte : JSON_EXTRACT(12, '$') rend 12, une date lève l'avertissement 4038.

Sauf mention contraire, un argument NULL (document ou chemin) donne NULL.

SELECT JSON_EXTRACT('{"a": [1]}', '$.a[0]'), JSON_EXTRACT('[1, 2, 3, 4]', '$[last]'), JSON_EXTRACT('[1, 2, 3, 4]', '$[1 to 2]');
-- '1', '4', '[2, 3]'

Opérateurs -> et ->>#

colonne->'chemin'
colonne->>'chemin'

colonne->'$.a' équivaut à JSON_EXTRACT(colonne, '$.a') (valeur rendue comme document : une chaîne garde ses guillemets) ; colonne->>'$.a' équivaut à JSON_UNQUOTE(JSON_EXTRACT(colonne, '$.a')) (texte sans guillemets). Le chemin doit être une chaîne écrite. Les opérateurs s'utilisent partout où une expression est permise : liste de SELECT, WHERE, ORDER BY, GROUP BY, arguments de fonctions.