9. Fonctions SQL

Ce chapitre décrit toutes les fonctions SQL intégrées de MIRAJ : leur signature, leur comportement précis (déduit du code du moteur), leur type de retour, leur comportement face à NULL et un exemple d'appel.

9.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 7. Requêtes SELECT) ; son comportement de typage de résultat suit les mêmes règles que COALESCE/IF décrites en 9.4.
• Le nombre exact de fonctions décrites dans ce chapitre dépasse largement celui annoncé ailleurs dans la documentation produit (152) : ce compte historique semble ne couvrir qu'un sous-ensemble des familles ci-dessous (par exemple sans les fonctions réseau, vectorielles ou de verrous nommés). Ce chapitre documente l'intégralité du registre de fonctions scalaires du moteur, plus les fonctions d'agrégation.

Table des matières#

• 9.1 Fonctions de chaînes de caractères
  • CONCAT · CONCAT_WS · UPPER / UCASE · LOWER / LCASE · LENGTH / OCTET_LENGTH · 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 · 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)
• 9.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
• 9.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 · STR_TO_DATE · DATE_ADD / ADDDATE · DATE_SUB / SUBDATE · 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
• 9.4 Fonctions de contrôle et logique
  • IF · IFNULL / NVL · COALESCE · NULLIF · ISNULL · NVL2 · INTERVAL (rang) · DEFAULT · NAME_CONST
• 9.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
• 9.6 Expressions régulières
  • REGEXP_LIKE · REGEXP_INSTR · REGEXP_SUBSTR · REGEXP_REPLACE
• 9.7 Fonctions de hachage, chiffrement et encodage
  • MD5 · SHA1 / SHA · SHA2 · PASSWORD · HEX · UNHEX · TO_BASE64 · FROM_BASE64 · AES_ENCRYPT · AES_DECRYPT
• 9.8 Fonctions réseau (IP)
  • INET_ATON · INET_NTOA · INET6_ATON · INET6_NTOA · IS_IPV4 · IS_IPV6 · IS_IPV4_COMPAT · IS_IPV4_MAPPED
• 9.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 · RANDOM_BYTES · UUID_SHORT · GET_LOCK · RELEASE_LOCK · IS_FREE_LOCK · IS_USED_LOCK · RELEASE_ALL_LOCKS · Variables système @@nom
• 9.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
• 9.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
• 9.12 Mise en forme avancée
  • SFORMAT
• 9.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
• 9.14 Colonnes dynamiques
  • COLUMN_CREATE · COLUMN_ADD · COLUMN_DELETE · COLUMN_GET · COLUMN_EXISTS · COLUMN_LIST · COLUMN_JSON · COLUMN_CHECK
• 9.15 Fonctions de séquence

9.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).

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#

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 est un alias strict.

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.

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, comme la référence) ; 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.

SELECT INSTR('MIRAJ DB', '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.

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 (compatible avec l'implémentation historique de la référence). 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'

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');  -- valeur négative
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–30) et un séparateur de milliers. 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'''  (littéral : 'l\'ami')
SELECT QUOTE(NULL);     -- NULL  (le texte, 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).

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.

SELECT LOAD_FILE('donnees/import.csv');

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 4. Types de données.

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

9.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.

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 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), conformément au comportement observé du serveur de référence pour ce type.

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)

TRUNCATE#

TRUNCATE(nombre, decimales)

Tronque nombre à decimales décimales sans arrondir (troncature vers zéro). decimales est obligatoire, contrairement à ROUND.

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

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);  -- 1024

SQRT#

SQRT(nombre)

Racine carrée. NULL si nombre < 0.

SELECT SQRT(16);  -- 4
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.

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

LOG10#

LOG10(nombre)

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

LOG2#

LOG2(nombre)

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

SELECT LOG2(8);  -- 3

PI#

PI()

La constante π (3.141592653589793), sans argument.

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

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();      -- ex. 0.7364...
SELECT RAND(42);    -- toujours la même valeur pour la graine 42

SIN, COS, TAN#

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

Fonctions trigonométriques usuelles, argument en radians.

COT#

COT(angle_radians)

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

ASIN#

ASIN(nombre)

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

ACOS#

ACOS(nombre)

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

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.

DEGREES#

DEGREES(angle_radians)

Convertit des radians en degrés.

RADIANS#

RADIANS(angle_degres)

Convertit des degrés en radians.

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 9.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');  -- un entier 32 bits

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

9.3 Fonctions de date et heure#

MIRAJ représente les dates comme un nombre à virgule flottante (partie entière = nombre de jours depuis le 30/12/1899, partie fractionnaire = fraction de journée), à la manière de TDateTime ; les fonctions ci-dessous en masquent la représentation interne.

Récapitulatif :

NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP#

NOW()

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. CURRENT_TIMESTAMP, LOCALTIME et LOCALTIMESTAMP sont des alias stricts.

Retour : DATETIME.

SELECT NOW();  -- '2026-09-23 14:05:12'

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.

CURDATE / CURRENT_DATE#

CURDATE()

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

Retour : DATE.

CURTIME / CURRENT_TIME#

CURTIME()

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

Retour : TIME.

UTC_TIMESTAMP, UTC_DATE, UTC_TIME#

UTC_TIMESTAMP()
UTC_DATE()
UTC_TIME()

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

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, combine la date de date_expr et l'heure (partie fractionnaire) de heure_expr.

Retour : DATETIME.

YEAR#

YEAR(expr)

Année (BIGINT).

MONTH#

MONTH(expr)

Mois, de 1 à 12 (BIGINT).

DAY / DAYOFMONTH#

DAY(expr)

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

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).

MINUTE#

MINUTE(expr)

Minute, de 0 à 59.

SECOND#

SECOND(expr)

Seconde, de 0 à 59.

MICROSECOND#

MICROSECOND(expr)

Microsecondes de la partie fractionnaire. MIRAJ ne conserve la précision qu'à la milliseconde ; le résultat est donc toujours un multiple de 1000.

DAYOFWEEK#

DAYOFWEEK(expr)

Jour de la semaine selon la convention usuelle : 1 = dimanche, ..., 7 = 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).

QUARTER#

QUARTER(expr)

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

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
SELECT WEEK('2026-01-01', 1);  -- dépend du jour de la semaine du 1er janvier

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).

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', ...).

MONTHNAME#

MONTHNAME(expr)

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

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'

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');  -- DATE '2026-09-13'
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. 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'

DATE_SUB / SUBDATE#

DATE_SUB(expr, INTERVAL n unite)

Comme DATE_ADD, mais soustrait. SUBDATE est un alias strict.

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

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.

Retour : TIME.

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.

SUBTIME#

SUBTIME(expr, duree)

Comme ADDTIME, mais soustrait.

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.

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.

SELECT FROM_UNIXTIME(1767323045);

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.

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.

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.

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

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.

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'

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, que chaque session lit et écrit dans son propre fuseau (DEFAULT CURRENT_TIMESTAMP et ON UPDATE CURRENT_TIMESTAMP comprises). 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. 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'

9.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. 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.

NAME_CONST#

NAME_CONST(nom, valeur)

Rend valeur ; nom n'est conservé que pour la forme des réplications d'un autre serveur. NAME_CONST('x', 5) vaut 5.

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.

9.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 9.13.

Récapitulatif :

COUNT#

COUNT(*)
COUNT([DISTINCT] expr)

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.

Retour : BIGINT.

SELECT COUNT(*) FROM commandes;
SELECT COUNT(DISTINCT client_id) FROM commandes;

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.

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.

MIN / MAX#

MIN(expr)
MAX(expr)

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

Retour : le type de expr.

GROUP_CONCAT#

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

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.

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;

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.

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.

VARIANCE / VAR_POP#

VARIANCE(expr)
VAR_POP(expr)

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

VAR_SAMP#

VAR_SAMP(expr)

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

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, comme la référence).

Retour : entier non signé sur 64 bits.

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 (§8.5).

GROUPING#

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

Pour un GROUP BY ... WITH ROLLUP, ROLLUP(...), CUBE(...) ou GROUPING SETS (...) (chapitre 8, §8.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;

JSON_ARRAYAGG#

JSON_ARRAYAGG(expr)

Construit un tableau JSON ([...]) à partir des valeurs de expr du groupe (dans l'ordre de lecture), 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. 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é"]'

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}'

9.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 (certains serveurs SQL répandus acceptent en pratique des constructions PCRE non supportées ici) ; 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

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);  -- position du second groupe de chiffres

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'

9.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');  -- une chaîne hexadécimale de 32 caractères

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.

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);  -- 64 caractères hexadécimaux

PASSWORD#

PASSWORD(mot_de_passe)

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

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 (une 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');  -- les octets de '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 comme la référence.

Retour : texte.

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'));  -- les octets de '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.

Attention — compatibilité seulement, pas une protection des données : AES_ENCRYPT et AES_DECRYPT reproduisent le format historique de la référence, 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'

9.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.

INET6_NTOA#

INET6_NTOA(binaire)

Inverse de INET6_ATON : forme texte d'une adresse binaire de 4 ou 16 octets. NULL pour toute autre longueur.

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.

IS_IPV6#

IS_IPV6(chaine)

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

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.

IS_IPV4_MAPPED#

IS_IPV4_MAPPED(binaire)

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

9.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).

VERSION#

VERSION()

Numéro de version du serveur MIRAJ, au format compatible avec les connecteurs existants : 8.0.40-miraj-1.0.0, identique à @@version et à SHOW VARIABLES LIKE 'version'. Le numéro de tête, 8.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 versions du serveur de référence 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.

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.

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 12), celui de l'opérande retenu pour CONCAT, UPPER, etc. ; binary pour un nombre, une date ou NULL.

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 12.

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.

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.

Retour : BIGINT.

ROW_COUNT#

ROW_COUNT()

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

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.

CONNECTION_ID#

CONNECTION_ID()

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

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.

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 '%').

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 10. Comptes et privilèges).

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()

Identifiant UUID version 4 aléatoire, en minuscules (xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx).

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.

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));         -- 32 chiffres hexadécimaux, différents à chaque appel
SELECT TO_BASE64(RANDOM_BYTES(32));   -- jeton de 256 bits

UUID_SHORT#

UUID_SHORT()

Identifiant entier croissant, unique dans le temps, au format compatible avec la référence.

Retour : BIGINT.

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);
-- ... traitement protégé ...
SELECT RELEASE_LOCK('verrou_import');

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.

IS_FREE_LOCK#

IS_FREE_LOCK(nom)

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

IS_USED_LOCK#

IS_USED_LOCK(nom)

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

RELEASE_ALL_LOCKS#

RELEASE_ALL_LOCKS()

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

Retour : BIGINT.

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 / @@global.nom (par exemple @@version, @@miraj_edition, @@max_allowed_packet). Ces variables sont décrites au chapitre 11. Administration du serveur ; elles se manipulent avec SET et se consultent aussi par SHOW VARIABLES.

9.10 Fonctions JSON#

MIRAJ porte un document JSON comme une simple valeur texte, écrite dans le même format que la référence ({"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).

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.

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, pour produire une chaîne JSON valide à partir d'un texte SQL brut.

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.

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 ils lèvent l'erreur 3149. Un chemin mal écrit lève l'erreur 3143 (avec la position du caractère fautif), un document invalide l'erreur 3141 (avec la raison et la position), un argument numérique ou temporel passé comme document l'erreur 3146.

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

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.

SELECT id, doc->>'$.nom' AS nom FROM fiches WHERE doc->'$.age' > 26 ORDER BY doc->>'$.nom';

Une valeur extraite comparée à un texte (doc->'$.nom' = 'Ali') est comparée sans ses guillemets, comme une chaîne JSON face à une chaîne SQL ; face à un nombre, elle est comparée comme un nombre.

JSON_EXTRACT#

JSON_EXTRACT(document, chemin [, chemin ...])

Valeur désignée par le chemin, écrite comme un document. Avec plusieurs chemins, ou un chemin comportant un joker ou une plage, les valeurs trouvées sont réunies dans un tableau (dans l'ordre des chemins puis du document). NULL si rien n'est trouvé.

Retour : texte JSON.

SELECT JSON_EXTRACT('{"a": 1, "b": [10, 20]}', '$.b[1]');      -- '20'
SELECT JSON_EXTRACT('{"a": 1, "b": [10, 20]}', '$.a', '$.b');  -- '[1, [10, 20]]'
SELECT JSON_EXTRACT('{"x": {"p": 1}, "y": {"p": 2}}', '$**.p'); -- '[1, 2]'

JSON_UNQUOTE#

JSON_UNQUOTE(texte)

Si texte est une chaîne JSON (entourée de guillemets doubles), la rend sans ses guillemets et avec ses échappements (\", \n, é...) résolus ; tout autre texte est rendu tel quel. Une chaîne JSON mal formée lève l'erreur 3141.

SELECT JSON_UNQUOTE('"été"'), JSON_UNQUOTE('[1, 2]');  -- été | [1, 2]

JSON_VALUE#

JSON_VALUE(document, chemin
           [RETURNING type]
           [{NULL | ERROR | DEFAULT valeur} ON EMPTY]
           [{NULL | ERROR | DEFAULT valeur} ON ERROR])

Valeur scalaire désignée par le chemin (sans joker), sans guillemets, convertie au type de RETURNING : SIGNED, UNSIGNED, DECIMAL(p, s), DOUBLE / FLOAT / REAL, DATE, TIME, DATETIME ou CHAR(n) (par défaut CHAR(512)). Un null JSON donne NULL.

• ON EMPTY s'applique quand le chemin ne désigne rien ;
• ON ERROR s'applique quand la valeur trouvée est un tableau ou un objet, ou ne se convertit pas au type demandé (texte non numérique pour SIGNED, trop de chiffres pour DECIMAL(p, s), texte plus long que n pour CHAR(n)...).

La réaction par défaut est NULL. ERROR ON EMPTY lève l'erreur 3966, ERROR ON ERROR l'erreur 3156. ON EMPTY s'écrit avant ON ERROR.

SELECT JSON_VALUE('{"prix": "12.5"}', '$.prix' RETURNING DECIMAL(6,2));           -- 12.50
SELECT JSON_VALUE('{"prix": 1}', '$.remise' DEFAULT 0 ON EMPTY);                  -- 0
SELECT JSON_VALUE('{"code": "A7"}', '$.code' RETURNING SIGNED DEFAULT -1 ON ERROR); -- -1

JSON_SET / JSON_INSERT / JSON_REPLACE#

JSON_SET(document, chemin, valeur [, chemin, valeur ...])
JSON_INSERT(document, chemin, valeur [, chemin, valeur ...])
JSON_REPLACE(document, chemin, valeur [, chemin, valeur ...])

Placent chaque valeur au chemin indiqué, les paires étant appliquées l'une après l'autre : JSON_SET remplace une valeur existante ou ajoute la valeur absente, JSON_INSERT ajoute seulement, JSON_REPLACE remplace seulement. Un membre absent est ajouté à la fin de l'objet ; un indice au-delà de la fin d'un tableau ajoute en fin ; un indice appliqué à une valeur qui n'est pas un tableau l'enveloppe (JSON_SET('{"a": 1}', '$.a[1]', 2) donne {"a": [1, 2]}). Un chemin dont le parent n'existe pas est ignoré. Les valeurs suivent les règles de conversion de JSON_OBJECT : texte entre guillemets, nombre tel quel, NULL en null, document (fonction JSON, CAST(... AS JSON), colonne JSON) imbriqué tel quel.

Retour : texte JSON (NULL si le document ou un chemin est NULL).

SELECT JSON_SET('{"a": 1}', '$.a', 2, '$.b', 'x');      -- '{"a": 2, "b": "x"}'
SELECT JSON_INSERT('{"a": 1}', '$.a', 2, '$.b', 3);     -- '{"a": 1, "b": 3}'
SELECT JSON_REPLACE('{"a": 1}', '$.a', 2, '$.b', 3);    -- '{"a": 2}'
UPDATE fiches SET doc = JSON_SET(doc, '$.age', doc->'$.age' + 1) WHERE id = 1;

JSON_REMOVE#

JSON_REMOVE(document, chemin [, chemin ...])

Retire le membre ou la case désignés (les chemins sont appliqués l'un après l'autre ; un chemin qui ne désigne rien est ignoré). Le chemin $ seul lève l'erreur 3153.

SELECT JSON_REMOVE('[1, 2, 3]', '$[1]');  -- '[1, 3]'

JSON_ARRAY_APPEND / JSON_ARRAY_INSERT#

JSON_ARRAY_APPEND(document, chemin, valeur [, chemin, valeur ...])
JSON_ARRAY_INSERT(document, chemin, valeur [, chemin, valeur ...])

JSON_ARRAY_APPEND ajoute la valeur à la fin du tableau désigné ; une valeur qui n'est pas un tableau devient un tableau [valeur, ajoutée]. JSON_ARRAY_INSERT insère la valeur à la case désignée, les suivantes étant décalées (en fin de tableau au-delà) ; son chemin doit se terminer par une case [n] (sinon erreur 3165).

SELECT JSON_ARRAY_APPEND('[1, [2]]', '$[1]', 3);  -- '[1, [2, 3]]'
SELECT JSON_ARRAY_INSERT('[1, 3]', '$[1]', 2);    -- '[1, 2, 3]'

JSON_CONTAINS#

JSON_CONTAINS(cible, candidat [, chemin])

1 si candidat est contenu dans cible (ou dans la valeur désignée par chemin), 0 sinon, NULL si le chemin ne désigne rien. Deux scalaires se contiennent s'ils sont égaux (1 et 1.0 sont égaux) ; un tableau contient un scalaire présent parmi ses éléments, et un tableau dont il contient chaque élément ; un objet contient un objet dont chaque membre existe chez lui avec une valeur qui contient la sienne.

SELECT JSON_CONTAINS('{"a": 1, "b": [1, 2]}', '[2]', '$.b');  -- 1

JSON_CONTAINS_PATH#

JSON_CONTAINS_PATH(document, 'one' | 'all', chemin [, chemin ...])

1 si au moins un chemin ('one') ou tous les chemins ('all') désignent une valeur. Un autre mode lève l'erreur 3154.

JSON_KEYS#

JSON_KEYS(document [, chemin])

Tableau JSON des noms de membres de l'objet (du document ou de la valeur désignée) ; NULL si ce n'est pas un objet.

SELECT JSON_KEYS('{"a": 1, "b": {"c": 2}}');          -- '["a", "b"]'

JSON_LENGTH#

JSON_LENGTH(document [, chemin])

Nombre d'éléments d'un tableau, de membres d'un objet, 1 pour un scalaire ; NULL si le chemin ne désigne rien.

JSON_DEPTH#

JSON_DEPTH(document)

Profondeur d'imbrication : 1 pour un scalaire, un tableau ou un objet vides, 1 + la profondeur du plus profond élément sinon.

JSON_TYPE#

JSON_TYPE(document)

Type de la valeur : OBJECT, ARRAY, STRING, INTEGER, UNSIGNED INTEGER (entier au-delà de BIGINT), DOUBLE (nombre écrit avec une partie décimale ou un exposant), BOOLEAN ou NULL. Un texte qui n'est pas du JSON (JSON_TYPE('abc')) lève l'erreur 3141.

SELECT JSON_TYPE(JSON_EXTRACT('{"a": [1]}', '$.a'));  -- ARRAY

JSON_SEARCH#

JSON_SEARCH(document, 'one' | 'all', motif [, echappement [, chemin ...]])

Chemin des chaînes du document (ou des valeurs désignées par les chemins) qui vérifient le motif LIKE (%, _, caractère d'échappement \ par défaut ou echappement s'il est donné ; comparaison de LIKE sous l'interclassement du document : binaire pour une colonne JSON (sensible à la casse), utf8mb4_general_ci pour un littéral (sans casse ni accents), celui de la colonne pour une colonne texte). 'one' rend le premier trouvé, 'all' tous : une chaîne JSON pour un résultat, un tableau pour plusieurs, NULL si rien n'est trouvé. Un caractère d'échappement de plus d'un caractère lève l'erreur 1210.

SELECT JSON_SEARCH('["abc", {"x": "abc"}]', 'all', 'ab%');  -- '["$[0]", "$[1].x"]'

JSON_OVERLAPS#

JSON_OVERLAPS(document1, document2)

1 si les deux documents ont une partie commune : un élément commun à deux tableaux, un membre commun (même nom, même valeur) à deux objets, deux scalaires égaux ; une valeur isolée face à un tableau est traitée comme l'un de ses éléments.

MEMBER OF#

valeur MEMBER OF (document)

1 si valeur (convertie comme un argument de JSON_ARRAY) est un élément du tableau document, ou égale au document s'il n'est pas un tableau. NULL si l'un des deux est NULL.

SELECT 2 MEMBER OF ('[1, 2]'), 'a' MEMBER OF ('["a", "b"]');  -- 1 | 1

JSON_PRETTY#

JSON_PRETTY(document)

Document écrit sur plusieurs lignes, un élément ou un membre par ligne, indenté de deux espaces par niveau (un tableau ou un objet vide reste [] / {}).

JSON_QUERY#

JSON_QUERY(document, chemin)

Objet ou tableau désigné par le chemin (le premier trouvé si le chemin contient un joker) ; NULL pour un scalaire ou un chemin qui ne désigne rien. Pendant de JSON_VALUE, qui ne rend que les scalaires.

SELECT JSON_QUERY('{"a": {"b": [1, 2]}, "c": 3}', '$.a'),   -- '{"b": [1, 2]}'
       JSON_QUERY('{"a": {"b": [1, 2]}, "c": 3}', '$.c');   -- NULL

JSON_EXISTS#

JSON_EXISTS(document, chemin)

1 si le chemin désigne une valeur (même un null JSON), 0 sinon.

JSON_EQUALS#

JSON_EQUALS(document1, document2)

1 si les deux documents sont égaux : nombres comparés par leur valeur (1 = 1.0), membres d'objets sans égard à leur ordre, éléments de tableaux dans leur ordre.

JSON_NORMALIZE#

JSON_NORMALIZE(document)

Forme canonique du document, pour comparer ou indexer : membres rangés par nom, nombres en notation scientifique, aucune espace. Deux documents égaux au sens de JSON_EQUALS ont la même forme normalisée.

SELECT JSON_NORMALIZE('{"b": 15, "a": [0.5]}');  -- '{"a":[5.0E-1],"b":1.5E1}'

JSON_COMPACT / JSON_LOOSE / JSON_DETAILED#

JSON_COMPACT(document)
JSON_LOOSE(document)
JSON_DETAILED(document [, indentation])

Réécriture du document : JSON_COMPACT sans aucune espace ({"a":[1,2]}), JSON_LOOSE avec une espace après chaque virgule et chaque deux-points ({"a": [1, 2]}, l'écriture habituelle de Miraj), JSON_DETAILED sur plusieurs lignes comme JSON_PRETTY, avec indentation espaces par niveau (4 par défaut, 32 au plus).

JSON_ARRAY_INTERSECT#

JSON_ARRAY_INTERSECT(tableau1, tableau2)

Éléments de tableau1 présents dans tableau2, dans l'ordre du premier ; chaque élément du second ne sert qu'une fois. NULL si l'un des documents n'est pas un tableau ou si rien n'est commun.

SELECT JSON_ARRAY_INTERSECT('[1, 2, 2, 3]', '[2, 3, 2]');  -- '[2, 2, 3]'

JSON_OBJECT_FILTER_KEYS#

JSON_OBJECT_FILTER_KEYS(objet, tableau_de_cles)

Membres de l'objet dont le nom figure dans le tableau de chaînes. NULL si le premier document n'est pas un objet, le second pas un tableau, ou si aucun nom n'est commun.

JSON_OBJECT_TO_ARRAY#

JSON_OBJECT_TO_ARRAY(objet)

L'objet en tableau de paires [nom, valeur] ; NULL pour un document qui n'est pas un objet.

SELECT JSON_OBJECT_TO_ARRAY('{"a": 1, "b": [2]}');  -- '[["a", 1], ["b", [2]]]'

JSON_KEY_VALUE#

JSON_KEY_VALUE(document, chemin)

L'objet désigné par le chemin en tableau de {"key": nom, "value": valeur}, pratique avec JSON_TABLE pour parcourir des membres de noms inconnus ; NULL si le chemin ne désigne pas un objet.

SELECT jt.k, jt.v
FROM JSON_TABLE(JSON_KEY_VALUE('{"a": 1, "b": 2}', '$'), '$[*]'
     COLUMNS (k VARCHAR(10) PATH '$.key', v INT PATH '$.value')) AS jt;

JSON_SCHEMA_VALID / JSON_SCHEMA_VALIDATION_REPORT#

JSON_SCHEMA_VALID(schema, document)
JSON_SCHEMA_VALIDATION_REPORT(schema, document)

Validation d'un document par un schéma JSON Schema (brouillon 4 et mots-clés compatibles des suivants). JSON_SCHEMA_VALID rend 1 si le document est conforme, 0 sinon ; JSON_SCHEMA_VALIDATION_REPORT rend {"valid": true} ou le premier manquement relevé : raison, emplacement dans le schéma et dans le document (pointeurs JSON #/...) et mot-clé non respecté.

Mots-clés pris en compte : type, enum, const, minimum, maximum, exclusiveMinimum, exclusiveMaximum, multipleOf, minLength, maxLength, pattern, items, additionalItems, minItems, maxItems, uniqueItems, contains, properties, patternProperties, additionalProperties, required, minProperties, maxProperties, dependencies, propertyNames, allOf, anyOf, oneOf, not, if / then / else, schémas true / false et $ref interne (#/definitions/nom). Les autres (format, title...) sont ignorés. Le schéma doit être un objet (erreur 3853) ; un pattern invalide lève 1139.

CREATE TABLE lieux (
  doc JSON,
  CHECK (JSON_SCHEMA_VALID('{"type": "object", "required": ["lat", "lon"],
    "properties": {"lat": {"type": "number", "minimum": -90, "maximum": 90}}}', doc))
);
SELECT JSON_SCHEMA_VALIDATION_REPORT('{"properties": {"lat": {"maximum": 90}}}', '{"lat": 91}');
-- '{"valid": false, "reason": "The JSON document location '#/lat' failed requirement 'maximum' at JSON Schema location '#/properties/lat'", ...}'

JSON_TABLE#

SELECT ... FROM JSON_TABLE(document, 'chemin' COLUMNS (
    nom FOR ORDINALITY,
    nom type PATH 'chemin' [reaction ON EMPTY] [reaction ON ERROR],
    nom type EXISTS PATH 'chemin',
    NESTED [PATH] 'chemin' COLUMNS (...)
)) [AS] alias
-- reaction : NULL | ERROR | DEFAULT 'texte JSON'

Source de table du FROM : une ligne par valeur que le chemin désigne dans le document ('$[*]' pour chaque élément d'un tableau). L'alias est obligatoire (erreur 3667 sans lui). Un document NULL, ou un chemin qui ne désigne rien, ne donne aucune ligne ; un texte qui n'est pas un document lève 3141, un chemin invalide 3143.

Le document peut être un littéral, une variable, un paramètre de routine ou une expression sur les colonnes des tables qui précèdent dans le FROM : la table est alors recalculée pour chaque ligne de ces tables (jointure latérale). Il s'écrit après une virgule, CROSS JOIN, [INNER] JOIN ... ON ou LEFT JOIN ... ON ; avec LEFT JOIN, une ligne dont le document ne donne aucune ligne (ou aucune qui satisfait ON) est gardée, complétée de NULL. Une référence latérale dans un RIGHT JOIN lève 3668, une colonne d'une table qui suit le JSON_TABLE 1054 ; le document ne peut contenir ni sous-requête ni agrégat (1210).

Colonnes (noms uniques, tous niveaux confondus, sinon 1060) :

• nom FOR ORDINALITY : rang de la ligne dans son niveau, à partir de 1 (INT UNSIGNED) ;
• nom type PATH 'chemin' : valeur désignée, relative à la ligne, convertie au type déclaré comme à l'écriture dans une colonne de ce type (VARCHAR(n), INT, DECIMAL(p,s), DATE...). Une colonne JSON reçoit le document désigné (plusieurs valeurs y sont réunies en tableau). Un null JSON donne NULL ;
• nom type EXISTS PATH 'chemin' : 1 si le chemin désigne une valeur, 0 sinon ;
• NESTED [PATH] 'chemin' COLUMNS (...) : une ligne par valeur désignée dans la valeur de la ligne parente, combinée avec les colonnes du parent ; sans aucune valeur, la ligne du parent reste, colonnes imbriquées à NULL. Deux NESTED au même niveau produisent leurs lignes l'un après l'autre, les colonnes de l'autre à NULL.

ON EMPTY s'applique quand le chemin ne désigne rien (ERROR : 3665) ; ON ERROR quand la valeur est un tableau ou un objet destiné à une colonne scalaire (ERROR : 3666), quand le chemin désigne plusieurs valeurs pour une colonne scalaire, ou quand elle ne se convertit pas au type (ERROR : 3669 hors limites, 1366 sinon). Les deux réagissent par NULL par défaut. La valeur de DEFAULT est un texte JSON ('"texte"', '12', '{"a": 1}' pour une colonne JSON), converti au type dès l'analyse : 1067 s'il n'est pas du JSON valide, ou s'il est un tableau ou un objet pour une colonne scalaire. ON ERROR peut précéder ON EMPTY.

SELECT c.id, l.art, l.q
FROM commandes c
LEFT JOIN JSON_TABLE(c.lignes, '$[*]' COLUMNS (
    n   FOR ORDINALITY,
    art VARCHAR(10) PATH '$.article',
    q   INT PATH '$.qte' DEFAULT '1' ON EMPTY,
    NESTED PATH '$.options[*]' COLUMNS (opt VARCHAR(20) PATH '$')
)) AS l ON TRUE
WHERE c.date_cmd >= '2026-01-01'
ORDER BY c.id, l.n;

JSON_TABLE s'emploie comme toute table : WHERE, GROUP BY, ORDER BY, table dérivée, sous-requête (corrélée ou non), vue (SHOW CREATE VIEW rend le texte écrit), INSERT ... SELECT, CREATE TABLE ... AS SELECT. EXPLAIN le montre comme une ligne Table function: json_table. La table est calculée en mémoire, sans index : filtrer d'abord les lignes des tables qui la précèdent.

9.11 Fonctions vectorielles (VECTOR(n))#

Ces fonctions opèrent sur le type VECTOR(n) (n flottants 32 bits), utilisé pour la recherche par similarité (embeddings). Elles se répartissent en trois familles aux conventions d'erreur différentes, héritées de compatibilités distinctes :

• Famille VEC_* : un argument incorrect (mauvais format, dimensions différentes) rend NULL plutôt que d'échouer.
• Famille STRING_TO_VECTOR / VECTOR_TO_STRING / VECTOR_DIM / DISTANCE : un argument incorrect lève une erreur.
• Famille L2_DISTANCE, COSINE_DISTANCE, VECTOR_ADD, etc. (et les opérateurs <->, <=>, <#>, <+>, +, -, * entre vecteurs, voir le chapitre 4. Types de données) : un argument incorrect lève une erreur ; les calculs se font en simple précision (32 bits).

Partout, un argument vecteur accepte aussi sa forme texte '[1, 2, 3]'.

Récapitulatif :

VEC_FROMTEXT#

VEC_FROMTEXT(chaine)

Convertit la forme texte '[1, 2, 3]' en vecteur binaire. NULL si chaine n'est pas un vecteur texte valide.

Retour : VECTOR(n).

VEC_TOTEXT#

VEC_TOTEXT(vecteur)

Forme texte d'un vecteur, avec six chiffres significatifs par composante.

Retour : texte.

VEC_DISTANCE_EUCLIDEAN#

VEC_DISTANCE_EUCLIDEAN(vecteur1, vecteur2)

Distance euclidienne (L2) entre deux vecteurs, calculée en double précision. NULL si un argument est invalide ou si les dimensions diffèrent.

Retour : DOUBLE.

VEC_DISTANCE_COSINE#

VEC_DISTANCE_COSINE(vecteur1, vecteur2)

Distance cosinus (1 - similarité cosinus) entre deux vecteurs, en double précision.

VEC_DISTANCE#

VEC_DISTANCE(vecteur1, vecteur2)

Distance de la métrique de l'index vectoriel que porte l'une des deux colonnes passées en argument (euclidienne, cosinus ou produit scalaire négé, voir le chapitre 10). Refusé à la compilation (erreur 4206) en dehors d'un contexte avec index vectoriel, la distance n'étant alors jamais définie sans indication de métrique.

STRING_TO_VECTOR / TO_VECTOR#

STRING_TO_VECTOR(chaine)

Convertit chaine (forme texte '[1, 2, 3]') en vecteur binaire ; lève une erreur si chaine n'est pas valide (au lieu de rendre NULL, contrairement à VEC_FROMTEXT). TO_VECTOR est un alias strict.

VECTOR_TO_STRING / FROM_VECTOR#

VECTOR_TO_STRING(vecteur)

Forme texte d'un vecteur (notation exponentielle, cinq chiffres significatifs). FROM_VECTOR est un alias strict.

VECTOR_DIM#

VECTOR_DIM(vecteur)

Nombre de composantes du vecteur.

Retour : BIGINT.

DISTANCE#

DISTANCE(vecteur1, vecteur2, metrique)

Distance entre deux vecteurs selon metrique : 'EUCLIDEAN', 'COSINE' ou 'DOT' (produit scalaire). Erreur si metrique n'est pas reconnue.

Retour : DOUBLE.

L2_DISTANCE#

L2_DISTANCE(vecteur1, vecteur2)

Distance euclidienne, calculs en simple précision (32 bits). Erreur si les dimensions diffèrent (message donnant les deux dimensions).

COSINE_DISTANCE#

COSINE_DISTANCE(vecteur1, vecteur2)

Distance cosinus, calculs en simple précision.

INNER_PRODUCT#

INNER_PRODUCT(vecteur1, vecteur2)

Produit scalaire des deux vecteurs.

VECTOR_NEGATIVE_INNER_PRODUCT#

VECTOR_NEGATIVE_INNER_PRODUCT(vecteur1, vecteur2)

Opposé du produit scalaire (utile pour trier par similarité décroissante avec un index qui minimise).

L1_DISTANCE#

L1_DISTANCE(vecteur1, vecteur2)

Distance de Manhattan (somme des valeurs absolues des différences composante par composante).

VECTOR_DIMS#

VECTOR_DIMS(vecteur)

Nombre de composantes (variante de VECTOR_DIM de la famille « opérateur », qui lève une erreur plutôt que de rendre NULL sur un argument incorrect).

VECTOR_NORM#

VECTOR_NORM(vecteur)

Norme euclidienne (longueur) du vecteur.

Retour : DOUBLE.

L2_NORMALIZE#

L2_NORMALIZE(vecteur)

Vecteur ramené à une norme de 1 (chaque composante divisée par la norme) ; un vecteur nul est rendu inchangé. Erreur en cas de dépassement de capacité d'un flottant 32 bits.

Retour : VECTOR(n).

SUBVECTOR#

SUBVECTOR(vecteur, position, nombre)

Sous-vecteur de nombre composantes à partir de position (base 1, comme SUBSTRING). Erreur si nombre < 1 ou si la plage demandée est vide.

VECTOR_ADD#

VECTOR_ADD(vecteur1, vecteur2)

Addition élément par élément. Erreur en cas de dépassement de capacité.

VECTOR_SUB#

VECTOR_SUB(vecteur1, vecteur2)

Soustraction élément par élément.

VECTOR_MUL#

VECTOR_MUL(vecteur1, vecteur2)

Multiplication élément par élément. Erreur aussi en cas de perte de précision par soupassement (résultat à zéro alors qu'aucun des deux facteurs ne l'était).

9.12 Mise en forme avancée#

Récapitulatif :

SFORMAT#

SFORMAT(format [, arg1, arg2, ...])

Met en forme arg1, arg2, ... selon format, à la manière de la bibliothèque C++ {fmt} (syntaxe proche de str.format() de Python). Les champs {} prennent les arguments dans l'ordre ; {n} prend l'argument d'indice n (base 0) — les deux numérotations ne peuvent pas être mélangées dans un même appel. {{ et }} produisent une accolade littérale.

Spécification après : (facultative) : [[remplissage]alignement][signe][#][0][largeur][.précision][L][type], où largeur et précision peuvent eux-mêmes venir d'un argument entier ({:{}}, {:.{2}}). Types acceptés selon la nature de l'argument : d x X o b B c pour un entier ; e E f F g G a A pour un nombre à virgule ; s ? pour un texte ; sans type, la forme la plus courte qui redonne le même nombre en le relisant. L groupe les chiffres de milliers par ,.

Comportement avec NULL : un argument numérique NULL est traité comme 0 ; un argument texte NULL, ou le format lui-même NULL, rendent NULL pour tout l'appel. Toute erreur de format (spécification invalide, argument absent, accolade non appariée, changement de numérotation en cours de format) rend aussi NULL, plutôt que de lever une erreur — de même qu'un résultat qui dépasserait max_allowed_packet.

Retour : texte.

SELECT SFORMAT('{} vaut {:.2f} ({:#x})', 'Pi', 3.14159, 255);
-- 'Pi vaut 3.14 (0xff)'

SELECT SFORMAT('{0} puis {1} puis {0}', 'A', 'B');
-- 'A puis B puis A'

SELECT SFORMAT('{:L}', 1234567);
-- '1,234,567' (le type 'L' groupe les milliers)

9.13 Fonctions de fenêtrage#

Une fonction de fenêtrage calcule une valeur pour chaque ligne en regardant les autres lignes de sa partition, sans les regrouper (contrairement à GROUP BY). Elle s'écrit :

fonction(...) OVER ([PARTITION BY expr, ...] [ORDER BY expr [ASC|DESC], ...] [cadre])

• PARTITION BY découpe les lignes en partitions indépendantes ; sans lui, toutes les lignes forment une seule partition.
• ORDER BY fixe l'ordre des lignes dans la partition. Les lignes qui ont la même valeur de clé sont des ex æquo (« peers »).
• Cadre : ROWS ou RANGE, sous la forme ROWS début ou ROWS BETWEEN début AND fin, avec les bornes UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. ROWS accepte toutes les bornes ; RANGE n'accepte que UNBOUNDED et CURRENT ROW (les ex æquo de la ligne courante font alors partie du cadre) ; RANGE avec un décalage n PRECEDING/n FOLLOWING rend l'erreur « non supporté » (1235). Un début UNBOUNDED FOLLOWING est une erreur de syntaxe (1064).
• Cadre par défaut (aucun cadre écrit) : avec ORDER BY, du début de la partition à la ligne courante et ses ex æquo (RANGE UNBOUNDED PRECEDING) ; sans ORDER BY, la partition entière.
• La fenêtre est évaluée après WHERE, GROUP BY et HAVING (elle peut donc porter sur des agrégats : SUM(SUM(x)) OVER (ORDER BY g)) et avant ORDER BY/LIMIT de la requête ; un filtre sur son résultat se fait dans une requête englobante (table dérivée). Elle peut aussi figurer dans un ORDER BY.
• Erreurs communes : fonction de fenêtrage dans un WHERE ou imbriquée dans une autre fonction de fenêtrage/agrégat (1111) ; nombre d'arguments incorrect (1582) ; fonction scalaire ordinaire suivie de OVER (par exemple UPPER(x) OVER (...)) ou DISTINCT dans une fonction de fenêtrage (1235, « non supporté »).

ROW_NUMBER#

ROW_NUMBER() OVER (...)

Numéro de la ligne dans sa partition, à partir de 1, selon l'ORDER BY de la fenêtre. Les ex æquo reçoivent des numéros distincts (dans leur ordre de lecture, non garanti). Aucun argument (sinon erreur 1582).

SELECT id, ROW_NUMBER() OVER (PARTITION BY g ORDER BY d) FROM T;

RANK#

RANK() OVER (...)

Rang de la ligne : 1 + nombre de lignes strictement précédentes. Les ex æquo partagent le même rang et le rang suivant est sauté (1, 2, 2, 4). Sans ORDER BY, toutes les lignes valent 1.

DENSE_RANK#

DENSE_RANK() OVER (...)

Comme RANK mais sans trou : 1, 2, 2, 3.

SELECT id, RANK() OVER (ORDER BY d), DENSE_RANK() OVER (ORDER BY d) FROM T;
-- d = 1, 2, 2, 3  ->  RANK = 1, 2, 2, 4 ; DENSE_RANK = 1, 2, 2, 3

PERCENT_RANK#

PERCENT_RANK() OVER (...)

(rang - 1) / (nombre de lignes de la partition - 1), entre 0 et 1. Vaut 0 pour une partition d'une seule ligne. Retour : DOUBLE.

CUME_DIST#

CUME_DIST() OVER (...)

Distribution cumulée : nombre de lignes jusqu'à la dernière ex æquo de la ligne courante, divisé par la taille de la partition (dans ]0, 1]). Retour : DOUBLE.

NTILE#

NTILE(n) OVER (...)

Répartit la partition ordonnée en n tranches de taille aussi égale que possible et rend le numéro (à partir de 1) de la tranche de la ligne ; quand la division ne tombe pas juste, les premières tranches reçoivent une ligne de plus. n doit être un entier constant supérieur ou égal à 1 : NTILE(0), NTILE(NULL) ou une valeur non constante (colonne) rendent l'erreur 1210.

SELECT id, NTILE(3) OVER (ORDER BY id) FROM T;   -- 7 lignes : 1,1,1,2,2,3,3

LAG / LEAD#

LAG(expr [, décalage [, défaut]]) OVER (...)
LEAD(expr [, décalage [, défaut]]) OVER (...)

Valeur de expr dans la ligne située décalage lignes avant (LAG) ou après (LEAD) la ligne courante dans la partition ordonnée. décalage vaut 1 par défaut ; c'est un entier constant supérieur ou égal à 0 (0 désigne la ligne courante ; valeur négative, NULL ou non constante : erreur 1210). Hors de la partition, le résultat est défaut (converti au type du résultat), ou NULL s'il est omis. Le cadre de la fenêtre est sans effet sur ces fonctions. Une valeur NULL de expr dans la ligne visée est rendue telle quelle. Trois arguments au plus (sinon 1582).

SELECT id, LAG(v) OVER (ORDER BY id), LEAD(v, 1, -1) OVER (ORDER BY id) FROM T;

FIRST_VALUE / LAST_VALUE#

FIRST_VALUE(expr) OVER (...)
LAST_VALUE(expr) OVER (...)

Valeur de expr dans la première, respectivement la dernière, ligne du cadre. Attention : avec le cadre par défaut (terminé à la ligne courante et ses ex æquo), LAST_VALUE rend la valeur de la dernière ex æquo de la ligne courante ; pour obtenir la dernière ligne de la partition, écrire ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. NULL si le cadre est vide. Une valeur NULL n'est pas ignorée.

SELECT id, FIRST_VALUE(id) OVER (PARTITION BY g ORDER BY d DESC, id) FROM T;

NTH_VALUE#

NTH_VALUE(expr, n) OVER (...)

Valeur de expr dans la n-ième ligne (à partir de 1) du cadre ; NULL si le cadre compte moins de n lignes. n est un entier constant supérieur ou égal à 1 (sinon 1210). Exactement deux arguments (sinon 1582).

SELECT id, NTH_VALUE(id, 2) OVER (PARTITION BY g ORDER BY d DESC, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM T;

MEDIAN / PERCENTILE_CONT / PERCENTILE_DISC#

MEDIAN(expr) OVER ([PARTITION BY ...])
PERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY expr [DESC]) OVER ([PARTITION BY ...])
PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY expr [DESC]) OVER ([PARTITION BY ...])

Valeur centrale ou percentile de expr sur toute la partition (les valeurs NULL sont écartées ; une partition sans valeur rend NULL). MEDIAN(x) est PERCENTILE_CONT(0.5). PERCENTILE_CONT interpole entre les deux valeurs voisines et rend un DOUBLE (expr numérique) ; PERCENTILE_DISC rend la première valeur dont la position cumulée atteint fraction, de n'importe quel type. fraction est une constante de 0 à 1 (sinon 1210) ; l'ORDER BY du WITHIN GROUP porte sur une seule clé. Ces fonctions n'acceptent ni ORDER BY ni cadre dans leur OVER (erreur 1235) et exigent OVER (sans lui, erreur).

SELECT id, MEDIAN(v) OVER (PARTITION BY g) FROM T;
SELECT PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY v DESC) OVER () FROM T;

Agrégats en fenêtre#

COUNT, SUM, AVG, MIN, MAX, STD, STDDEV, STDDEV_POP et VARIANCE acceptent une clause OVER et se calculent alors sur le cadre de chaque ligne, avec les mêmes règles de types et de NULL qu'en 9.5 (les NULL sont ignorés ; SUM, AVG, MIN, MAX, STD, VARIANCE rendent NULL sur un cadre sans valeur non NULL, COUNT rend 0). COUNT(*) OVER (...) est accepté.

Non pris en charge avec OVER (erreur 1235) : GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_AND/BIT_OR/BIT_XOR, ANY_VALUE, STDDEV_SAMP, VAR_POP, VAR_SAMP, DISTINCT dans l'argument, et SUM/AVG/MIN/MAX sur des vecteurs VECTOR(n).

SELECT id, SUM(v) OVER (PARTITION BY g ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;  -- somme courante
SELECT id, SUM(d) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM T;                        -- moyenne mobile

9.14 Colonnes dynamiques#

Les colonnes dynamiques de MariaDB rangent un jeu de paires clé / valeur dans une colonne BLOB, au format binaire de MariaDB. Le principe, les types de valeurs (AS INT, AS DOUBLE, AS CHAR, AS DATE...) et un exemple complet sont décrits dans la section « Colonnes dynamiques » du chapitre 4. Types de données. Une clé est un nombre ou un nom ; un blob invalide ou une clé en double rend NULL, sans les avertissements 1919 à 1922 de la référence (docs/LIMITES.md).

COLUMN_CREATE#

Construit un jeu à partir de paires clé / valeur (au moins une paire). AS type fixe le type de la valeur stockée ; sans lui, il se déduit de la valeur.

SELECT COLUMN_CREATE('couleur', 'rouge', 'poids', 2.5 AS DOUBLE);

COLUMN_ADD#

Ajoute des colonnes à un jeu, ou remplace celles qui existent déjà (même clé).

UPDATE produit SET attrs = COLUMN_ADD(attrs, 'taille', 42) WHERE id = 1;

COLUMN_DELETE#

Retire les colonnes de clés données ; une clé absente est ignorée.

COLUMN_GET#

Lit la colonne de clé donnée, convertie vers type (INTEGER, DOUBLE, DECIMAL(p,s), CHAR, DATE, TIME, DATETIME...) ; NULL si la colonne n'existe pas.

SELECT COLUMN_GET(attrs, 'poids' AS DOUBLE) FROM produit;

COLUMN_EXISTS#

1 si le jeu contient la clé, 0 sinon.

COLUMN_LIST#

Liste des clés du jeu, séparées par des virgules et entre apostrophes inverses (`couleur`,`poids`).

COLUMN_JSON#

Le jeu en texte JSON ({"couleur":"rouge","poids":2.5}).

COLUMN_CHECK#

1 si l'argument est un jeu valide, 0 sinon.

9.15 Fonctions de séquence#

NEXT VALUE FOR s (ou NEXTVAL(s)), PREVIOUS VALUE FOR s (ou LASTVAL(s)) et SETVAL(s, n [, est_appelée]) lisent et font avancer une séquence créée par CREATE SEQUENCE. Leur description, leurs droits et leurs écarts figurent au chapitre 5. Langage DDL (section « Séquences ») ; les erreurs 4084 à 4091 sont au chapitre 27.