10. SQL Functions

This chapter describes all of Miraj's built-in SQL functions and operators in the MIRAJ language: their signature, their precise behavior, their return type, their behavior with NULL, and a sample call with its result. The results are those displayed by the miraj-cli console (strings are written here between apostrophes to distinguish them from numbers).

10.0 General notes#

• Case-insensitive names. CONCAT, Concat and concat designate the same function. This chapter writes them in uppercase by convention.
• NULL propagation. Unless explicitly stated otherwise in a function's description, any NULL argument makes the result NULL. The notable exceptions are COALESCE, IFNULL/NVL, CONCAT_WS, IF, ISNULL, ELT, FIELD, MAKE_SET, QUOTE, SFORMAT (for the arguments other than the format) and aggregates (which ignore the NULLs of their argument rather than propagating NULL to the whole row).
• String positions and lengths are counted in Unicode characters (not bytes), except LENGTH and BIT_LENGTH, which count UTF-8 bytes. ASCII returns the leading byte of the UTF-8 encoding of the first character; ORD combines the UTF-8 bytes of the first character in big-endian order (ORD('é') = 50089, whereas é has Unicode code point 233).
• CASE WHEN is not a function but a construct of the SQL language (see chapter 9. SELECT Queries); its result typing behavior follows the same rules as COALESCE/IF described in 10.4.
• This chapter documents all the scalar functions of the engine, the aggregate and window functions, and the operators (10.19). An unknown name returns error 1305 (FUNCTION name does not exist), and a wrong number of arguments returns error 1582.

Table of contents#

• 10.1 String functions
  • CONCAT · CONCAT_WS · UPPER / UCASE · LOWER / LCASE · LENGTH / OCTET_LENGTH / LENGTHB · BIT_LENGTH · CHAR_LENGTH / CHARACTER_LENGTH · TRIM · LTRIM · RTRIM · SUBSTRING / SUBSTR / MID · LEFT · RIGHT · REPLACE · LOCATE · INSTR · LPAD · RPAD · REVERSE · REPEAT · SPACE · ASCII · ORD · CHAR · CHR · STRCMP · FORMAT · INSERT · ELT · FIELD · FIND_IN_SET · SUBSTRING_INDEX · QUOTE · BIN · OCT · CONV · MAKE_SET · EXPORT_SET · SOUNDEX · NATURAL_SORT_KEY · WEIGHT_STRING · LOAD_FILE · CONVERT (character set) · FORMAT_BYTES · FORMAT_PICO_TIME
• 10.2 Numeric functions
  • ABS · SIGN · ROUND · TRUNCATE · FLOOR · CEILING / CEIL · MOD · POWER / POW · SQRT · EXP · LN · LOG · LOG10 · LOG2 · PI · RAND · SIN, COS, TAN · COT · ASIN · ACOS · ATAN / ATAN2 · DEGREES · RADIANS · GREATEST · LEAST · CRC32 / CRC32C · BIT_COUNT · TO_NUMBER
• 10.3 Date and time functions
  • NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP · SYSDATE · CURDATE / CURRENT_DATE · CURTIME / CURRENT_TIME · UTC_TIMESTAMP, UTC_DATE, UTC_TIME · DATE · TIME · TIMESTAMP · YEAR · MONTH · DAY / DAYOFMONTH · HOUR · MINUTE · SECOND · MICROSECOND · DAYOFWEEK · WEEKDAY · DAYOFYEAR · QUARTER · WEEK · WEEKOFYEAR · YEARWEEK · DAYNAME · MONTHNAME · LAST_DAY · DATE_FORMAT / TIME_FORMAT · TO_CHAR · STR_TO_DATE · DATE_ADD / ADDDATE · DATE_SUB / SUBDATE · ADD_MONTHS · MONTHS_BETWEEN · TIMESTAMPADD · DATEDIFF · TIMEDIFF · TIMESTAMPDIFF · ADDTIME · SUBTIME · UNIX_TIMESTAMP · FROM_UNIXTIME · TO_DAYS · FROM_DAYS · MAKEDATE · MAKETIME · TIME_TO_SEC · SEC_TO_TIME · EXTRACT · TO_SECONDS · PERIOD_ADD · PERIOD_DIFF · GET_FORMAT · CONVERT_TZ
• 10.4 Control and logic functions
  • IF · IFNULL / NVL · COALESCE · NULLIF · ISNULL · NVL2 · INTERVAL (rank) · DEFAULT · NAME_CONST
• 10.5 Aggregate functions
  • COUNT · SUM · AVG · MIN / MAX · GROUP_CONCAT · STD / STDDEV / STDDEV_POP · STDDEV_SAMP · VARIANCE / VAR_POP · VAR_SAMP · BIT_AND, BIT_OR, BIT_XOR · ANY_VALUE · GROUPING · JSON_ARRAYAGG · JSON_OBJECTAGG
• 10.6 Regular expressions
  • REGEXP_LIKE · REGEXP_INSTR · REGEXP_SUBSTR · REGEXP_REPLACE
• 10.7 Hash, encryption and encoding functions
  • MD5 · SHA1 / SHA · SHA2 · PASSWORD · HEX · UNHEX · TO_BASE64 · FROM_BASE64 · AES_ENCRYPT · AES_DECRYPT · COMPRESS · UNCOMPRESS · UNCOMPRESSED_LENGTH · OLD_PASSWORD · ENCODE / DECODE · DES_ENCRYPT / DES_DECRYPT · ENCRYPT · KDF
• 10.8 Network (IP) functions
  • INET_ATON · INET_NTOA · INET6_ATON · INET6_NTOA · IS_IPV4 · IS_IPV6 · IS_IPV4_COMPAT · IS_IPV4_MAPPED
• 10.9 System, session and lock functions
  • DATABASE / SCHEMA · VERSION · CHARSET · COLLATION · COERCIBILITY · BENCHMARK · LAST_INSERT_ID · ROW_COUNT · FOUND_ROWS · CONNECTION_ID · USER / SESSION_USER / SYSTEM_USER · CURRENT_USER · CURRENT_ROLE · SLEEP · UUID / UUID_V4 · UUID_V7 · SYS_GUID · RANDOM_BYTES · UUID_SHORT · GET_LOCK · RELEASE_LOCK · IS_FREE_LOCK · IS_USED_LOCK · RELEASE_ALL_LOCKS · MIRAJ_NOTIFY · MIRAJ_CHANGE_SEQ · BINLOG_GTID_POS · MASTER_GTID_WAIT · MASTER_POS_WAIT · DECODE_HISTOGRAM · System variables @@name
• 10.10 JSON functions
  • JSON_OBJECT · JSON_ARRAY · JSON_MERGE_PRESERVE / JSON_MERGE · JSON_MERGE_PATCH · JSON_QUOTE · JSON_VALID · JSON paths · Operators -> and ->> · JSON_EXTRACT · JSON_UNQUOTE · JSON_VALUE · JSON_SET / JSON_INSERT / JSON_REPLACE · JSON_REMOVE · JSON_ARRAY_APPEND / JSON_ARRAY_INSERT · JSON_CONTAINS · JSON_CONTAINS_PATH · JSON_KEYS · JSON_LENGTH · JSON_DEPTH · JSON_TYPE · JSON_SEARCH · JSON_OVERLAPS · MEMBER OF · JSON_PRETTY · JSON_QUERY · JSON_EXISTS · JSON_EQUALS · JSON_NORMALIZE · JSON_COMPACT / JSON_LOOSE / JSON_DETAILED · JSON_ARRAY_INTERSECT · JSON_OBJECT_FILTER_KEYS · JSON_OBJECT_TO_ARRAY · JSON_KEY_VALUE · JSON_SCHEMA_VALID / JSON_SCHEMA_VALIDATION_REPORT · JSON_TABLE
• 10.11 Vector functions (VECTOR(n))
  • VEC_FROMTEXT · VEC_TOTEXT · VEC_DISTANCE_EUCLIDEAN · VEC_DISTANCE_COSINE · VEC_DISTANCE · STRING_TO_VECTOR / TO_VECTOR · VECTOR_TO_STRING / FROM_VECTOR · VECTOR_DIM · DISTANCE · L2_DISTANCE · COSINE_DISTANCE · INNER_PRODUCT · VECTOR_NEGATIVE_INNER_PRODUCT · L1_DISTANCE · VECTOR_DIMS · VECTOR_NORM · L2_NORMALIZE · SUBVECTOR · VECTOR_ADD · VECTOR_SUB · VECTOR_MUL
• 10.12 Advanced formatting
  • SFORMAT
• 10.13 Window functions
  • ROW_NUMBER · RANK · DENSE_RANK · PERCENT_RANK · CUME_DIST · NTILE · LAG / LEAD · FIRST_VALUE / LAST_VALUE · NTH_VALUE · MEDIAN / PERCENTILE_CONT / PERCENTILE_DISC · Window aggregates
• 10.14 Dynamic columns
  • COLUMN_CREATE · COLUMN_ADD · COLUMN_DELETE · COLUMN_GET · COLUMN_EXISTS · COLUMN_LIST · COLUMN_JSON · COLUMN_CHECK
• 10.15 Sequence functions
• 10.16 Full-text search (MATCH … AGAINST)
• 10.17 Spatial functions
  • Conversions · Constructors · Accessors · Predicates · Measures and constructions · ST_Union / ST_Intersection / ST_Difference / ST_SymDifference · ST_Buffer / Buffer · Geohash · ST_Collect · Spatial index
• 10.18 XML functions
  • EXTRACTVALUE · UPDATEXML
• 10.19 Operators
  • Arithmetic · Comparison · Logical · Bitwise · Patterns · Other · Type conversion · Precedence

10.1 String functions#

Summary:

CONCAT#

CONCAT(string1, string2, ...)

Concatenates from one to an unlimited number of arguments, converted to text if needed. NULL propagates: if a single argument is NULL, the whole result is NULL (unlike CONCAT_WS). If one of the arguments is a binary string (VARBINARY, BLOB, X'…', result of COMPRESS…), the result is one too: the bytes of each argument are joined end to end as they are.

Returns: VARCHAR/text.

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

CONCAT_WS#

CONCAT_WS(separator, string1, string2, ...)

"Concat With Separator": concatenates string1, string2, ... inserting separator between each pair of non-NULL arguments; a NULL argument is simply skipped (no empty insertion). If separator itself is NULL, the result is NULL.

Returns: text.

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

UPPER / UCASE#

UPPER(string)

Converts string to uppercase (Unicode rules, not only ASCII). UCASE is a strict alias.

Returns: text. NULL → NULL.

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

LOWER / LCASE#

LOWER(string)

Converts string to lowercase (Unicode rules). LCASE is a strict alias.

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

LENGTH / OCTET_LENGTH / LENGTHB#

LENGTH(string)

Length of string in bytes of its UTF-8 encoding (not in characters); for a binary value (VARBINARY/BLOB), length in raw bytes, without any UTF-8 decoding. OCTET_LENGTH and LENGTHB are strict aliases.

Returns: BIGINT.

SELECT LENGTH('café');  -- 5 (the é takes two bytes in UTF-8)

BIT_LENGTH#

BIT_LENGTH(string)

Length in bits, that is eight times LENGTH(string).

SELECT BIT_LENGTH('a');  -- 8

CHAR_LENGTH / CHARACTER_LENGTH#

CHAR_LENGTH(string)

Length of string in Unicode characters (not in bytes); for a binary value, one byte counts as one character. CHARACTER_LENGTH is a strict alias.

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

TRIM#

TRIM([BOTH | LEADING | TRAILING] [characters] FROM string)
TRIM(string)
TRIM(characters, string)

Removes repeated occurrences of characters (the space ' ' by default, with the one-argument form) at the start, at the end, or on both sides (BOTH, the default) of string. If characters is an empty string, string is returned unchanged.

Returns: text.

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

LTRIM#

LTRIM(string)

Removes the spaces (' ') at the start of string only.

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

RTRIM#

RTRIM(string)

Removes the spaces (' ') at the end of string only.

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

SUBSTRING / SUBSTR / MID#

SUBSTRING(string, position [, length])

Substring of string starting at character position (1 = first character). A negative position counts from the end (-1 = last character). Without length, goes to the end; with length < 1, position = 0 or position outside the string, returns an empty string (not NULL). SUBSTR and MID are strict aliases. On a binary string, the positions and the length count bytes and the result is a binary string (the same applies to LEFT and RIGHT).

Returns: text.

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

LEFT#

LEFT(string, n)

The first n characters of string (negative n treated as 0; n beyond the length returns the whole string).

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

RIGHT#

RIGHT(string, n)

The last n characters of string.

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

REPLACE#

REPLACE(string, search, replacement)

Replaces all non-overlapping occurrences of search with replacement in string, respecting case. If search is an empty string, string is returned unchanged (no infinite loop).

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

LOCATE#

LOCATE(substring, string [, start_position])

Position (in characters, 1-based) of the first occurrence of substring in string, search according to the resolved collation: in utf8mb4_general_ci (default) case-insensitive but accent-sensitive (LOCATE('eme', 'Crème') returns 0); in utf8mb4_unicode_ci and utf8mb4_bin, each window of the same byte length as substring is compared under the collation (LOCATE('ss', 'Straße' COLLATE utf8mb4_unicode_ci) returns 5, LOCATE('a', 'A' COLLATE utf8mb4_bin) returns 0; see Collations), starting from start_position (1 by default). Returns 0 if substring is not found or if start_position < 1.

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

INSTR#

INSTR(string, substring)

Equivalent to LOCATE(substring, string) (reversed argument order), searching from the start, according to the collation like LOCATE. The standard form POSITION(substring IN string) is another synonym.

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

LPAD#

LPAD(string, length, padding)

Pads string on the left with repetitions of padding up to length characters; if string already has length characters or more, it is truncated to length characters. Returns NULL if length < 0 or exceeds one million characters; returns an empty string if padding is empty and padding would be needed.

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

RPAD#

RPAD(string, length, padding)

Like LPAD but pads on the right.

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

REVERSE#

REVERSE(string)

Reverses the order of the (Unicode) characters of string.

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

REPEAT#

REPEAT(string, n)

Repeats string n times end to end (negative n treated as 0). Returns NULL if n exceeds one million, or if the result would exceed 64 MiB (default value of max_allowed_packet).

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

SPACE#

SPACE(n)

String of n spaces (negative n treated as 0). Returns NULL if n exceeds one million.

SELECT SPACE(3);  -- '   '

ASCII#

ASCII(string)

Leading byte of the UTF-8 encoding of the first character of string (so the ASCII code for an ASCII character; for a multi-byte character, only the leading byte is returned, not the Unicode code point). 0 if string is empty.

Returns: BIGINT.

SELECT ASCII('A');  -- 65

ORD#

ORD(string)

Numeric code of the first character of string, computed by combining its UTF-8 bytes in big-endian order. For an ASCII character, identical to ASCII. 0 if string is empty.

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

CHAR#

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

Builds a string from code points (each argument becomes one character). A NULL argument is skipped (it does not break the concatenation, unlike most string functions). Each code is first interpreted as the significant bytes of a multi-byte character; if the result is not valid UTF-8, each code is read again as a direct Unicode code point.

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

CHR#

CHR(code)

The character whose code is code in the database character set (utf8mb4): equivalent to CHAR(code USING utf8mb4) with a single argument. code is read as the significant bytes of the character (CHR(65) is 'A', CHR(50089) is 'é', whose encoding is C3 A9), and not as a Unicode code point. If these bytes do not form valid UTF-8 (CHR(233)), the result is NULL with warning 1300.

Returns: text.

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

STRCMP#

STRCMP(string1, string2)

Compares two strings according to the resolved collation of its arguments (STRCMP('a' COLLATE utf8mb4_bin, 'A') returns 1): 0 if equal, a negative number if string1 < string2, positive otherwise.

Returns: BIGINT.

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

FORMAT#

FORMAT(number, decimals [, locale])

Formats number with decimals digits after the decimal point (rounded to the nearest value, halves away from zero; decimals clamped to 0–38) and a thousands separator. For a floating-point value (DOUBLE, PI(), literal in scientific notation), number × 10^decimals is rounded to the nearest even integer (FORMAT(2.5e0, 0) is '2') and the result is written with its shortest significant digits, padded with zeros (FORMAT(PI(), 38) is '3.14159265358979300000000000000000000000'); a negative float rounded to zero keeps its sign (FORMAT(-0.004e0, 2) is '-0.00'). Without locale, uses the Anglo-American conventions (, for thousands, . for the decimal point). With a recognized locale (for example 'fr_FR', 'de_DE'), the separators and the digit grouping follow that locale; an unknown locale or NULL is equivalent to 'en_US'. NULL if number or decimals is NULL.

Returns: text.

SELECT FORMAT(1234567.891, 2);              -- '1,234,567.89'
SELECT FORMAT(1234567.891, 2, 'fr_FR');     -- '1234567,89' (fr_FR does not group thousands)
SELECT FORMAT(1234567.891, 2, 'de_DE');     -- '1.234.567,89'

INSERT#

INSERT(string, position, length, replacement)

Replaces length characters of string starting at position (1-based) with replacement. If position is outside string (< 1 or > length(string)), string is returned unchanged. A negative length goes to the end of string.

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

ELT#

ELT(n, string1, string2, ...)

Returns the n-th string (1-based) among string1, string2, .... Returns NULL if n is NULL, out of range (< 1 or > the number of strings) or if the chosen string is NULL, but the other NULLs in the list do not affect the result if they are not chosen.

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

FIELD#

FIELD(string, value1, value2, ...)

Position (1-based) of string in the list value1, value2, ... (case-insensitive comparison), 0 if not found. Returns 0 (not NULL) if string is NULL; a NULL valueN is skipped and never matches.

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

FIND_IN_SET#

FIND_IN_SET(string, list)

Position (1-based) of string in list, a string of comma-separated values; 0 if not found or if list is empty.

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

SUBSTRING_INDEX#

SUBSTRING_INDEX(string, delimiter, n)

If n > 0, everything before the n-th occurrence of delimiter counting from the start; if n < 0, everything after the |n|-th occurrence counting from the end. Returns the whole string if delimiter does not appear enough times. Returns an empty string if delimiter is empty or n is 0.

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

QUOTE#

QUOTE(string)

Wraps string in apostrophes and escapes the apostrophes and backslashes it contains, to produce a SQL literal that can be reused directly in a statement. Does not propagate NULL: QUOTE(NULL) returns the literal string 'NULL' (four letters, without quotes), the SQL keyword as it would appear in a statement.

SELECT QUOTE("l'ami");  -- 'l\'ami'  (apostrophes included in the returned text)
SELECT QUOTE(NULL);     -- 'NULL'  (the four-letter text, not the SQL NULL value)

BIN#

BIN(n)

Binary representation of n (64-bit signed integer, read in two's complement if negative).

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

OCT#

OCT(n)

Octal representation of n, equivalent to CONV(n, 10, 8). A text argument is read as a decimal integer prefix (reading stops at the first non-numeric character, overflow saturates); an empty string returns NULL.

SELECT OCT(8);  -- '10'

CONV#

CONV(number, from_base, to_base)

Converts the text representation of number (read in from_base) to to_base (bases from 2 to 36, absolute value). Reading stops at the first digit that is invalid for the source base; leading spaces are ignored. NULL if either base is outside 2–36.

Returns: text.

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

MAKE_SET#

MAKE_SET(mask, string1, string2, ...)

Comma-separated list of the stringN whose bit N-1 of mask (integer) is 1; bits beyond the number of strings supplied are ignored. A NULL stringN is skipped even if its bit is 1. NULL if mask is NULL.

SELECT MAKE_SET(5, 'a', 'b', 'c');  -- 'a,c' (bits 0 and 2 set: 5 = 0b101)

EXPORT_SET#

EXPORT_SET(mask, on, off [, separator [, num_bits]])

For each of the num_bits bits of mask (64 by default, clamped to 0–64), returns on if the bit is 1, off otherwise, from the least significant bit upward, joined by separator (, by default).

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

SOUNDEX#

SOUNDEX(string)

Soundex phonetic code of string: the first letter is copied (uppercase), then one digit per following consonant according to the classic Soundex table (vowels, H, W, Y skipped without separating two identical digits), padded with zeros up to 4 characters. Empty string if string contains no letter.

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

NATURAL_SORT_KEY#

NATURAL_SORT_KEY(string)

Produces a text key such that a lexicographic sort of these keys is equivalent to a "natural sort" of string (numbers embedded in the text are sorted by value, not character by character: 'a2' before 'a10'). Useful as ORDER BY NATURAL_SORT_KEY(column).

SELECT NATURAL_SORT_KEY('a2'), NATURAL_SORT_KEY('a10');
-- 'a02', 'a110' (the second sorts after the first)

WEIGHT_STRING#

WEIGHT_STRING(string)
WEIGHT_STRING(string AS CHAR(n))
WEIGHT_STRING(string AS BINARY(n))

Returns the sort weight bytes of string according to its collation. In utf8mb4_unicode_ci: big-endian 16-bit units (zero, one or several per character, 'ß' → 0FEA0FEA), AS CHAR(n) cutting at n units then padding with 0209; in utf8mb4_bin: 3 bytes per character (code point), AS CHAR(n) padding with 000020. In utf8mb4_general_ci (default): one big-endian 16-bit weight per character, trailing spaces included (HEX(WEIGHT_STRING('Crème')) returns 004300520045004D0045, as for 'creme'; a character outside the BMP weighs FFFD). AS CHAR(n) first brings the text back to n characters (truncated or padded with spaces), which returns the same result for two strings that are equal under =; AS BINARY(n) brings the raw bytes back to n (truncated or padded with zeros). A numeric value returns NULL (no sort weight is defined for a number by this function); a date or a time uses the bytes of its text.

Returns: binary (VARBINARY).

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

LOAD_FILE#

LOAD_FILE(path)

Content of a file on the server, as binary. The file must be located in the folder allowed by the server variable secure_file_priv, and the user must have the FILE privilege; a relative path starts from that folder, and a path that would leave it (via ..) is refused. Returns NULL, without error, if no folder is allowed for the session, if the file is missing, unreadable, is not a regular file, or exceeds max_allowed_packet.

Returns: binary.

-- secure_file_priv designates a folder that contains note.txt (text "bonjour")
SELECT LOAD_FILE('note.txt');     -- 'bonjour'
SELECT LOAD_FILE('absent.txt');   -- NULL
SELECT LOAD_FILE('../x');         -- NULL (outside the allowed folder)

CONVERT (character set)#

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

Converts expr to the named character set. To binary, returns the raw UTF-8 bytes of the text (binary type); to another set, returns a text in which characters not representable in that set are replaced by ?. This form of CONVERT (with a character set) is distinct from CONVERT(expr, type), which performs a SQL type change, described in chapter 6. Data Types.

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

FORMAT_BYTES#

FORMAT_BYTES(bytes)

Writes a size in bytes in the most readable binary unit: below 1024, N bytes (integer part); above, the value is divided by 1024 as long as it stays at least 1024 and is written with two decimals followed by KiB, MiB, GiB, TiB, PiB or EiB. A negative value keeps its sign.

Returns: text.

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

FORMAT_PICO_TIME#

FORMAT_PICO_TIME(picoseconds)

Writes a duration expressed in picoseconds in the most readable unit: below 1000, N ps; above, ns, us, ms, s (steps of 1000), then min, h and d (steps of 60, 60 and 24), with two decimals. A negative value keeps its sign.

Returns: text.

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

10.2 Numeric functions#

Calculations on DECIMAL remain exact (scaled integer arithmetic); a division or modulo by zero or an invalid domain (logarithm of a negative number, square root of a negative number...) returns NULL rather than raising an error.

Summary:

ABS#

ABS(number)

Absolute value. An integer stays an integer, a DECIMAL stays DECIMAL (same scale), the others become DOUBLE. NULL on overflow (minimum value of a signed BIGINT, whose opposite does not fit in a BIGINT).

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

SIGN#

SIGN(number)

1 if number > 0, -1 if number < 0, 0 otherwise.

Returns: BIGINT.

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

ROUND#

ROUND(number [, decimals])

Rounds number to decimals decimal places (0 by default; negative to round to tens, hundreds...). An integer stays an integer (rounded to tens if decimals < 0); a BIGINT UNSIGNED rounded to tens by a written number returns a DECIMAL, since the rounded value can exceed 2⁶⁴ − 1 (ROUND(18446744073709551615, -1) = 18446744073709551620); a DECIMAL is rounded away from zero for a half (ROUND(0.5, 0) = 1) and the result keeps the requested number of decimals, padded with zeros if needed (ROUND(2.567, 5) = 2.56700); a floating-point value (FLOAT/DOUBLE) is rounded to the nearest even (default IEEE 754 mode: ROUND(2.5e0) = 2, ROUND(1.5e0) = 2, ROUND(0.5e0) = 0), as for any floating-point calculation.

SELECT ROUND(1250, -2);   -- 1300 (integer)
SELECT ROUND(2.5);        -- 3   (DECIMAL literal: rounded away from zero)
SELECT ROUND(1.005, 2);   -- 1.01 (exact DECIMAL)
SELECT ROUND(2.5e0), ROUND(1.5e0), ROUND(0.5e0);  -- 2.0, 2.0, 0.0 (DOUBLE: to the nearest even)

TRUNCATE#

TRUNCATE(number, decimals)

Truncates number to decimals decimal places without rounding (truncation toward zero). decimals is required, unlike ROUND. TRUNC(number, decimals) is a synonym, with the same rules (negative decimals, NULL, two arguments).

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

FLOOR#

FLOOR(number)

Largest integer less than or equal to number. NULL if outside the range of a signed BIGINT.

Returns: BIGINT.

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

CEILING / CEIL#

CEILING(number)

Smallest integer greater than or equal to number. CEIL is a strict alias.

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

MOD#

MOD(a, b)

Remainder of the integer division of a by b (same sign as a, like the % operator). NULL if b is 0. An integer divided by an integer returns an integer; otherwise the calculation goes through a floating-point division but the result keeps the numeric representation of the first argument (integer, DECIMAL or DOUBLE).

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

POWER / POW#

POWER(base, exponent)

base raised to the power exponent. POW is a strict alias.

Returns: DOUBLE.

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

SQRT#

SQRT(number)

Square root. NULL if number < 0.

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

EXP#

EXP(number)

e raised to the power number.

SELECT EXP(1);  -- 2.718281828459045

LN#

LN(number)

Natural logarithm. NULL if number <= 0.

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

LOG#

LOG(number)
LOG(base, number)

Without base, natural logarithm (same as LN). With base, logarithm of number in base base. NULL if number <= 0, base <= 0 or base = 1.

SELECT LOG(2, 8);  -- 3.0

LOG10#

LOG10(number)

Base-10 logarithm. NULL if number <= 0.

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

LOG2#

LOG2(number)

Base-2 logarithm. NULL if number <= 0.

SELECT LOG2(8);  -- 3.0

PI#

PI()

The constant π (3.141592653589793), with no argument.

Returns: DOUBLE. Constant function (evaluated once per statement).

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

RAND#

RAND([seed])

Pseudo-random floating-point number in [0, 1). Without an argument, a new value on each call. With seed (a constant integer or an expression), the generator is reseeded with that seed: two calls with the same seed return the same value. A seed equal to NULL is equivalent to 0.

SELECT RAND();              -- for example 0.02946782614786525 (changes on each call)
SELECT RAND(42), RAND(42);   -- 0.5265574090027738, 0.5265574090027738

SIN, COS, TAN#

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

Usual trigonometric functions, argument in radians.

Returns: DOUBLE.

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

COT#

COT(angle_radians)

Cotangent (1 / TAN(angle_radians)). NULL if TAN(angle_radians) = 0.

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

ASIN#

ASIN(number)

Arc sine, in radians. NULL if number is outside [-1, 1].

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

ACOS#

ACOS(number)

Arc cosine, in radians. NULL if number is outside [-1, 1].

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

ATAN / ATAN2#

ATAN(number)
ATAN(y, x)
ATAN2(y, x)

ATAN(number): arc tangent in radians. ATAN(y, x) (or ATAN2(y, x)): angle of the point (x, y), taking the quadrant into account.

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

DEGREES#

DEGREES(angle_radians)

Converts radians to degrees.

SELECT DEGREES(PI());  -- 180.0

RADIANS#

RADIANS(angle_degrees)

Converts degrees to radians.

SELECT RADIANS(180);  -- 3.141592653589793

GREATEST#

GREATEST(expr1, expr2, ...)

The largest of the expressions, compared according to a common order deduced from their types (see the COALESCE typing table in 10.4). NULL if any of the expressions is NULL.

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

LEAST#

LEAST(expr1, expr2, ...)

The smallest of the expressions, same rules as GREATEST.

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

CRC32 / CRC32C#

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

CRC-32 checksum (standard polynomial for CRC32, Castagnoli for CRC32C) of the bytes of expr (raw bytes for a binary value, UTF-8 text otherwise). With a seed (result of a previous call), chains the computation: CRC32C(CRC32C('a'), 'b') is equivalent to CRC32C('ab').

Returns: BIGINT (unsigned 32-bit value, represented as a positive integer).

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

BIT_COUNT#

BIT_COUNT(n)

Number of bits set to 1 in the 64-bit integer n (BIT_COUNT(-1): 64); NULL if n is NULL.

Returns: BIGINT.

SELECT BIT_COUNT(7);  -- 3

TO_NUMBER#

TO_NUMBER(expr)

Number read from the text expr, like CAST(expr AS DECIMAL) without loss of decimals; a number is returned as is. A text that is not entirely a number is worth its numeric prefix (0 if there is none), with warning 1292 (Truncated incorrect DECIMAL value).

Returns: BIGINT for a constant integer, DECIMAL (scale of the text) for a constant decimal, DOUBLE otherwise.

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

10.3 Date and time functions#

The functions below accept a DATE, TIME, DATETIME or TIMESTAMP value, or a text that reads as one ('2026-09-13', '2026-09-13 08:09:10.5'). The current-time functions accept an optional fractional precision of 0 to 6 digits (NOW(3), CURTIME(6)).

Summary:

NOW / CURRENT_TIMESTAMP / LOCALTIME / LOCALTIMESTAMP#

NOW([precision])

Current date and time in the session time zone (variable time_zone, see Session time zone), frozen at the start of the statement: all calls to NOW() within the same statement (including across several rows) return the same value. precision (0 to 6) sets the number of digits of the fractional seconds. CURRENT_TIMESTAMP, LOCALTIME and LOCALTIMESTAMP are strict aliases, which can also be written without parentheses.

Returns: DATETIME.

SELECT NOW();                         -- '2026-10-19 03:25:54' (instant of execution)
SELECT NOW(3);                        -- '2026-10-19 03:27:45.989'
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP() = NOW();  -- '2026-10-19 03:27:45', 1

SYSDATE#

SYSDATE()

Like NOW(), but reads the clock at each call (not frozen for the statement): two calls to SYSDATE() in the same statement may differ.

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

CURDATE / CURRENT_DATE#

CURDATE()

Current date (without time), frozen for the statement. CURRENT_DATE is a strict alias (with or without parentheses).

Returns: DATE.

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

CURTIME / CURRENT_TIME#

CURTIME([precision])

Current time (without date), frozen for the statement. CURRENT_TIME is a strict alias (with or without parentheses).

Returns: TIME.

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

UTC_TIMESTAMP, UTC_DATE, UTC_TIME#

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

Equivalents of NOW(), CURDATE() and CURTIME() in UTC rather than local time, all frozen for the statement.

-- session in the Europe/Paris time zone (daylight saving time)
SELECT NOW(), UTC_TIMESTAMP(), UTC_DATE(), UTC_TIME();
-- '2026-10-19 03:27:45', '2026-10-19 02:27:45', '2026-10-19', '02:27:45'

DATE#

DATE(expr)

Date part of a date/time expression (time set to zero).

Returns: DATE.

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

TIME#

TIME(expr)

Time part of a date/time expression.

Returns: TIME.

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

TIMESTAMP#

TIMESTAMP(expr)
TIMESTAMP(date_expr, time_expr)

With one argument, converts expr to DATETIME. With two arguments, adds the duration time_expr to date_expr (the result may roll over to the next day).

Returns: DATETIME.

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

YEAR#

YEAR(expr)

Year (BIGINT).

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

MONTH#

MONTH(expr)

Month, from 1 to 12 (BIGINT).

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

DAY / DAYOFMONTH#

DAY(expr)

Day of the month, from 1 to 31 (BIGINT). DAYOFMONTH is a strict alias.

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

HOUR#

HOUR(expr)

Hour, from 0 to 23 for a date/time; for a TIME value, it can exceed 23 (a TIME duration can represent more than 24 hours), and a negative duration gives its hour without the sign. A text is first read as a [-]H:MM:SS duration, then, if it is not one, as a date/time.

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

MINUTE#

MINUTE(expr)

Minute, from 0 to 59. A text is read as for HOUR: first as a duration, then as a date/time.

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

SECOND#

SECOND(expr)

Second, from 0 to 59. A text is read as for HOUR.

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

MICROSECOND#

MICROSECOND(expr)

Microseconds of the fractional part (0 to 999999).

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

DAYOFWEEK#

DAYOFWEEK(expr)

Day of the week according to the usual convention: 1 = Sunday, ..., 7 = Saturday.

SELECT DAYOFWEEK('2026-09-13'), DAYOFWEEK('2026-09-19');  -- 1, 7 (Sunday, Saturday)

WEEKDAY#

WEEKDAY(expr)

Day of the week according to the ISO convention: 0 = Monday, ..., 6 = Sunday.

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

DAYOFYEAR#

DAYOFYEAR(expr)

Rank of the day in the year (1 to 366).

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

QUARTER#

QUARTER(expr)

Quarter of the year, from 1 to 4.

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

WEEK#

WEEK(expr [, mode])

Week number in the year (0 by default without mode). mode (0 to 7) combines three independent choices through its bits:

SELECT WEEK('2026-01-01', 0);  -- 0 (January 1st, 2026 is a Thursday, before the first Sunday)
SELECT WEEK('2026-01-01', 1);  -- 1
SELECT WEEK('2026-09-13');     -- 37

WEEKOFYEAR#

WEEKOFYEAR(expr)

Equivalent to WEEK(expr, 3) (ISO 8601 weeks: Monday, numbered 1 to 53, week 1 = the one containing the first Thursday of the year).

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

YEARWEEK#

YEARWEEK(expr [, mode])

Year and week combined into a single integer YYYYWW (mode defaults to 0, same bits as WEEK, with the "weeks 1–53" bit always on).

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

DAYNAME#

DAYNAME(expr)

Full name of the day of the week, in English ('Sunday', 'Monday', ...).

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

MONTHNAME#

MONTHNAME(expr)

Full name of the month, in English ('January', ...).

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

LAST_DAY#

LAST_DAY(expr)

Date of the last day of the month of expr.

Returns: DATE.

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

DATE_FORMAT / TIME_FORMAT#

DATE_FORMAT(expr, format)

Formats expr according to format, a string with %X specifiers:

TIME_FORMAT is a strict alias (useful in documentation with a format that has no date part).

Returns: text.

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

TO_CHAR#

TO_CHAR(expr [, template])

Formats a date, a time or a date-time (or a text read as a date-time) according to an Oracle-style template. Without template, 'YYYY-MM-DD HH24:MI:SS' is used whatever the type of expr (so a date is written with 00:00:00, a time with 0000-00-00). Fields are read case-insensitively:

Punctuation and spaces are copied; a text between double quotes is copied too, without its quotes ('YYYY"abc"MM'). A single | separates two fields without writing anything; of several consecutive |, only the first is omitted. Month and day names are in English and are written January, Jan, Monday, Mon whatever the case of the template. For a time (TIME), the year, month and day are zero (MON, DY: 00) and HH24 can exceed 23.

NULL if an argument is NULL, if the template is empty or if a text does not read as a date (warning 1292). Any other letter, a digit or a non-ASCII character outside quotes is refused: error 3047 (Invalid argument error: date format not recognized at … in function to_char.) for a constant template, NULL and warning 3047 for a template read from a column. A number as the first argument is refused (3047): TO_CHAR does not format numbers (see FORMAT). The template is at most 144 bytes long.

Returns: text under the connection collation (@@collation_connection), whatever the collation of a text first argument; with a constant template, its maximum length is known (VARCHAR(10) for 'YYYY-MM-DD', 27 characters counted for MONTH and DAY).

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

In the PostgreSQL language, to_char is the PostgreSQL function (date and number templates, see chapter 30).

STR_TO_DATE#

STR_TO_DATE(string, format)

Parses string according to format (same specifiers as DATE_FORMAT, plus %p and %T when reading) and returns the corresponding date/time. NULL if string does not exactly match the format or describes an invalid date (e.g. '2026-02-30'). The return type (DATE, TIME or DATETIME) is deduced from the specifiers present in format when it is a constant.

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

DATE_ADD / ADDDATE#

DATE_ADD(expr, INTERVAL n unit)

Adds n units unit to expr. Simple units: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND. Composite units accepted with a textual interval (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. A month addition that goes past the last day of the target month brings it back to that last day ('2026-01-31' + INTERVAL 1 MONTH = '2026-02-28'). NULL if the result falls outside the range 0001-01-01 to 9999-12-31, with warning 1441 (Datetime function: datetime field overflow). ADDDATE is a strict alias, also accepting the three-argument form ADDDATE(expr, n, 'unit').

Returns: DATE if expr is a date and the unit is a day or more, TIME if expr is a time, DATETIME otherwise.

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

DATE_SUB / SUBDATE#

DATE_SUB(expr, INTERVAL n unit)

Like DATE_ADD, but subtracts. SUBDATE is a strict alias, which also accepts a number of days as the second argument.

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

ADD_MONTHS#

ADD_MONTHS(expr, n)

Adds n months to expr (subtracts them if n is negative): equivalent to DATE_ADD(expr, INTERVAL n MONTH). n is rounded to the integer. If the day does not exist in the target month, the last day of that month is used; the last day of a short month is not carried over to the last day of the target month (ADD_MONTHS('2024-02-29', 1) = '2024-03-29'). NULL if an argument is NULL, if the result falls outside the years 1 to 9999 (warning 1441, like DATE_ADD) or if a text does not read as a date (warning 1292).

Returns: like DATE_ADD with a MONTH unit: DATE for a date (or a constant string without time), DATETIME otherwise (time and fractional second kept).

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

MONTHS_BETWEEN#

MONTHS_BETWEEN(expr1, expr2)

Number of months from expr2 to expr1 (expr1 - expr2), negative if expr1 precedes expr2. The result is an integer when both dates fall on the same day of the month or are both last days of a month; otherwise the fractional part counts the difference in days (and hours) on the basis of a 31-day month.

Returns: DOUBLE.

SELECT MONTHS_BETWEEN('2026-09-13', '2026-06-13');   -- 3.0
SELECT MONTHS_BETWEEN('2026-03-31', '2026-02-28');   -- 1.0 (two month ends)
SELECT MONTHS_BETWEEN('2026-02-15', '2026-01-01');   -- 1.4516129032258065
SELECT MONTHS_BETWEEN('2024-01-01', '2024-03-15');   -- -2.4516129032258065

TIMESTAMPADD#

TIMESTAMPADD(unit, n, expr)

Adds n units unit (same simple units as DATE_ADD) to expr; argument order differs from DATE_ADD.

Returns: DATETIME.

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

DATEDIFF#

DATEDIFF(expr1, expr2)

Difference in whole days between the date parts of expr1 and expr2 (expr1 - expr2), ignoring the time.

Returns: BIGINT.

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

TIMEDIFF#

TIMEDIFF(expr1, expr2)

Difference expr1 - expr2 as a duration (negative if expr1 precedes expr2); both arguments must be of the same kind (two date-times or two times).

Returns: TIME.

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

TIMESTAMPDIFF#

TIMESTAMPDIFF(unit, expr1, expr2)

Whole difference expr2 - expr1 expressed in unit (YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND). For YEAR, QUARTER and MONTH, the calculation is exactly calendar-based: it counts the full months actually elapsed (taking the day and the time into account), not an approximation by dividing an average number of days. For example, between two consecutive January 15ths, the difference in years is always exactly 1, whether or not a leap year lies between them — a calculation using 365.25 days would sometimes wrongly give 0.

Returns: BIGINT.

SELECT TIMESTAMPDIFF(MONTH, '2026-01-15', '2026-04-10');  -- 2 (not yet 3 full months)
SELECT TIMESTAMPDIFF(YEAR, '2001-01-01', '2002-01-01');   -- 1 (exact, calendar-based)

ADDTIME#

ADDTIME(expr, duration)

Adds duration (a time, or a time-interval text) to expr.

Returns: DATETIME if expr carries a date, TIME otherwise.

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

SUBTIME#

SUBTIME(expr, duration)

Like ADDTIME, but subtracts.

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

UNIX_TIMESTAMP#

UNIX_TIMESTAMP()
UNIX_TIMESTAMP(expr)

Without an argument, seconds elapsed since the Unix epoch (January 1st, 1970 UTC) up to the start of the statement. With expr (local time of the session time zone, time_zone), the corresponding Unix seconds.

Returns: BIGINT.

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

FROM_UNIXTIME#

FROM_UNIXTIME(seconds [, format])

Date-time, in the session time zone (time_zone), corresponding to seconds (Unix seconds, fractions accepted to the nearest millisecond). With format, directly returns the formatted text (like DATE_FORMAT).

Returns: DATETIME, or text if format is supplied.

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

TO_DAYS#

TO_DAYS(expr)

Number of days elapsed since day 0 of the proleptic Gregorian calendar, for the date part of expr.

Returns: BIGINT.

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

FROM_DAYS#

FROM_DAYS(n)

Date corresponding to the number of days n since day 0 (inverse of TO_DAYS). Does not take into account the Gregorian calendar before its adoption; to be reserved for computing differences between dates rather than actual historical dates.

Returns: DATE.

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

MAKEDATE#

MAKEDATE(year, day_of_year)

Date of the day_of_year-th day of year (January 1st = day 1). NULL if day_of_year < 1.

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

MAKETIME#

MAKETIME(hour, minute, second)

Builds a time from its components (second accepts a fraction).

Returns: TIME.

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

TIME_TO_SEC#

TIME_TO_SEC(expr)

Whole number of seconds corresponding to the time part of expr (or to expr directly if it is already a time).

Returns: BIGINT.

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

SEC_TO_TIME#

SEC_TO_TIME(seconds)

Time corresponding to seconds (fractions of a second accepted).

Returns: TIME.

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

EXTRACT#

EXTRACT(unit FROM expr)

Extracts a field from expr: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, or a composite unit (YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND, HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND) that combines several fields into a single integer (e.g. YEAR_MONTH gives YYYYMM).

Returns: BIGINT.

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

TO_SECONDS#

TO_SECONDS(expr)

Number of seconds elapsed from day 0 of year 0 up to expr (combines TO_DAYS and the time of day).

Returns: BIGINT.

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

PERIOD_ADD#

PERIOD_ADD(period, n)

Adds n months to period, a period in YYMM or YYYYMM format (two-digit years below 70 are read as 20xx, otherwise 19xx). 0 stays 0 whatever n is.

Returns: BIGINT in YYYYMM format.

SELECT PERIOD_ADD(202609, 3);  -- 202612

PERIOD_DIFF#

PERIOD_DIFF(period1, period2)

Difference in months between two YYMM/YYYYMM periods.

SELECT PERIOD_DIFF(202612, 202609);  -- 3

GET_FORMAT#

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

Returns the format string (compatible with DATE_FORMAT) corresponding to a named regional convention, for example to use it afterwards with STR_TO_DATE or DATE_FORMAT. NULL if the combination is not defined.

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

Session time zone#

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

time_zone is SYSTEM at the start (time zone of the machine, with its daylight saving changes). The other values are a fixed offset from -13:59 to +14:00 ('+02:00') or a name from the IANA database ('Europe/Paris', 'UTC', case ignored), shipped with MIRAJ: no system file to install, on Windows, macOS and Linux. An unknown time zone or an out-of-range offset returns error 1298. SET time_zone changes the session only; SET GLOBAL time_zone sets the value that sessions opened afterwards receive (it is not kept after a server restart) and DEFAULT takes the global value again.

The session time zone determines:

• NOW() and its synonyms, SYSDATE(), CURDATE(), CURTIME(), UNIX_TIMESTAMP() and FROM_UNIXTIME(); UTC_TIMESTAMP(), UTC_DATE() and UTC_TIME() remain in UTC;
• TIMESTAMP columns: the value designates an instant, stored in UTC (files, indexes, journals, replication, backups), which each session reads and writes in its own time zone (including DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP). Changing the machine's time zone does not move the stored values (a session in SYSTEM shows them in the new time zone), and two instants of the repeated hour at the change back to standard time remain distinct once stored (keys, unique indexes, index order), even if a session in that time zone reads them at the same civil time. Generated columns and the values of change events see a TIMESTAMP column at the machine's time zone time. DATETIME, DATE, TIME and YEAR columns keep the time as written, without 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) converts a number or a date to a year (CAST(69 AS YEAR): 2069, CAST(70 AS YEAR): 1970), NULL outside 1901 to 2155 (0 stays 0); an integer of the form YYYYMMDD[hhmmss] is converted to DATE or DATETIME by CAST, and so is a free-form text ('200101', '2020.01.01', '20200101100000') (see chapter 6). A DATE or a DATETIME used in a calculation takes its numeric form (YYYYMMDDhhmmss): CAST('2020-01-01 10:00:00' AS DATETIME) + 0 is 20200101100000.

CONVERT_TZ#

CONVERT_TZ(expr, from_tz, to_tz)

Converts expr from time zone from_tz to to_tz. Each time zone is either 'SYSTEM' (local time of the server), a fixed offset ('+01:00', from -13:59 to +14:00), or an IANA time zone name ('Europe/Paris', 'UTC', ...). NULL if a time zone is invalid or if the result falls outside the range of years 1 to 9999 (never error 1298, which is reserved for SET time_zone). CONVERT_TZ does not depend on the session time_zone.

Returns: DATETIME.

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

10.4 Control and logic functions#

Summary:

IF#

IF(condition, value_if_true, value_if_false)

Returns value_if_true if condition is true (non-null and different from zero), otherwise value_if_false. A NULL condition counts as false. The return type is the common type of value_if_true and value_if_false (see the COALESCE table below); the branch not taken is not evaluated for its error type but counts toward the result typing.

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

IFNULL / NVL#

IFNULL(expr1, expr2)

Returns expr1 if it is not NULL, otherwise expr2. NVL is a strict alias.

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

COALESCE#

COALESCE(expr1, expr2, ...)

Returns the first non-NULL expression in the list, or NULL if all are NULL.

Return type: derived from all arguments — all integers → BIGINT; all numeric with a floating-point → DOUBLE; all numeric without a floating-point → DECIMAL (at the widest scale); all temporal with a DATETIME → DATETIME, otherwise the type of the first argument; all VECTOR → VECTOR; otherwise text. These same rules apply to IF, IFNULL, GREATEST, LEAST and CASE WHEN.

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

NULLIF#

NULLIF(expr1, expr2)

Returns NULL if expr1 = expr2, otherwise expr1 (with its original type and scale, including DECIMAL).

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

ISNULL#

ISNULL(expr)

1 if expr is NULL, 0 otherwise. Unlike most functions, it never returns NULL itself.

Returns: BIGINT.

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

NVL2#

NVL2(expr, if_not_null, if_null)

Returns if_not_null if expr is not NULL, if_null otherwise. The result type is the same as for IF.

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

INTERVAL (rank)#

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

Rank of n in the ascending list n1 < n2 < ...: 0 if n < n1, 1 if n1 <= n < n2, and so on; -1 if n is NULL (NULL elements of the list are ignored). At least two arguments are required (otherwise syntax error 1064). Not to be confused with the INTERVAL n unit of date calculations, which is unchanged.

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

DEFAULT#

DEFAULT(column)

Declared default value of the column (NULL if it has none and accepts NULL). A NOT NULL column without a default returns error 1364, an unknown column 1054, an expression that is not a table column 1210. It can be used in a select list, a WHERE or an UPDATE (SET a = DEFAULT(a) + 1). VALUES(column) outside an ON DUPLICATE KEY UPDATE returns NULL.

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

NAME_CONST#

NAME_CONST(name, value)

Returns value; name plays no part in the calculation (the form is used by statements replayed by replication).

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

The comparisons a IS [NOT] DISTINCT FROM b (equivalent to NOT (a <=> b), NULL being compared as a value) and x IS [NOT] UNKNOWN (test of a NULL boolean result) complete these functions (see 10.19).

10.5 Aggregate functions#

Aggregate functions summarize the values of a group of rows (GROUP BY, or the whole table without GROUP BY) into a single value. Unlike scalar functions, they silently ignore the NULLs of their argument rather than making the whole calculation fail.

These functions, except GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, BIT_*, ANY_VALUE, STDDEV_SAMP, VAR_POP and VAR_SAMP, can also be used as window functions with OVER (...): see 10.13.

The examples in this section use the following tables:

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

Summary:

COUNT#

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

COUNT(*) counts all rows of the group. COUNT(expr) counts the rows where expr is not NULL. COUNT(DISTINCT expr) counts the distinct non-NULL values of expr. COUNT(DISTINCT expr, expr2, ...) counts the distinct combinations of values none of which is NULL; each text is compared according to its collation ('a' and 'A' count as one value in utf8mb4_general_ci). Several expressions can only be written with DISTINCT (COUNT(a, b) and COUNT(): syntax error 1064), and not in a window function.

Returns: BIGINT.

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

SUM#

SUM([DISTINCT] expr)

Sum of the non-NULL values of expr. NULL if the group contains no non-NULL value. On a VECTOR(n) column, performs an element-by-element sum (result VECTOR(n)).

Returns: BIGINT if expr is an integer, DECIMAL (same scale) if expr is DECIMAL, DOUBLE otherwise.

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

AVG#

AVG([DISTINCT] expr)

Average of the non-NULL values of expr. On VECTOR(n), element-by-element average.

Returns: DECIMAL with 4 more decimals than expr if expr is an integer or DECIMAL, DOUBLE otherwise.

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

MIN / MAX#

MIN(expr)
MAX(expr)

Smallest / largest non-NULL value of expr according to the natural order of the type (the collation for a text). NULL if the group contains no non-NULL value.

Returns: the type of expr.

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

GROUP_CONCAT#

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

Concatenates the non-NULL values of expr of the group (converted to text) into a single string. With several expressions (expr, expr2, ...), each is concatenated directly (with no separator between them) for each row, and the entire row is skipped if any of these expressions is NULL. DISTINCT removes duplicates before concatenation. ORDER BY (with a position number or an expression) sets the order of the values in the result — the sort key can be an expression that is not itself concatenated. SEPARATOR sets the separator between values (',' by default); SEPARATOR '' uses no separator. LIMIT keeps only n values after skipping m (none by default), once duplicates have been removed and values sorted (in read order without ORDER BY); n and m are unsigned integers, ? parameters or routine variables. A limit that keeps no value returns an empty text, not NULL, for a group that has values. The clauses are written in this order: ORDER BY, SEPARATOR, LIMIT.

The length of the result is bounded by the group_concat_max_len variable (1,048,576 bytes by default, from 4 to 4,294,967,295; startup option --group-concat-max-len): beyond that, the text is truncated to this length in bytes, never cutting a UTF-8 character, and the statement receives warning 1260 (N line(s) were cut by GROUP_CONCAT(), N being the number of truncated groups), visible with SHOW WARNINGS. The text beyond the limit is never kept in memory, including in a parallel read. SET [SESSION] group_concat_max_len = n changes the limit for the session, SET GLOBAL for sessions opened afterwards, SET STATEMENT group_concat_max_len = n FOR … for a single statement.

Returns: text.

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

STD / STDDEV / STDDEV_POP#

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

Population standard deviation of the non-NULL values of expr (divisor = number of values). The three names are strictly equivalent. NULL if the group is empty.

Returns: DOUBLE.

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

STDDEV_SAMP#

STDDEV_SAMP(expr)

Corrected (sample) standard deviation, divisor = number of values minus one. NULL if the group has fewer than two values.

SELECT STDDEV_SAMP(x) FROM mesures;   -- 1.632993161855452

VARIANCE / VAR_POP#

VARIANCE(expr)
VAR_POP(expr)

Population variance (square of STD). Both names are equivalent.

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

VAR_SAMP#

VAR_SAMP(expr)

Corrected variance (square of STDDEV_SAMP). NULL if the group has fewer than two values.

SELECT VAR_SAMP(x) FROM mesures;   -- 2.6666666666666665

BIT_AND, BIT_OR, BIT_XOR#

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

Bitwise AND, OR and exclusive OR of all non-NULL integer values of expr (BIT_AND of an empty group is all bits set to 1, BIT_OR and BIT_XOR of an empty group are 0).

Returns: 64-bit unsigned integer.

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

ANY_VALUE#

ANY_VALUE(expr)

A value of expr taken from the group (NULL for an empty group), with no guarantee about which one. Used to cite a column that is neither aggregated nor in the GROUP BY when sql_mode contains ONLY_FULL_GROUP_BY, which would reject it bare (§10.5).

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

GROUPING#

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

For a GROUP BY ... WITH ROLLUP, ROLLUP(...), CUBE(...) or GROUPING SETS (...) (chapter 9, §9.17): 1 if the row is a subtotal row that summarizes expr (the column is NULL there), 0 if NULL is a real value or if the column is grouped. With several arguments, a bit mask whose first argument is the most significant bit (GROUPING(a, b) is 3 on the grand total). Each argument must be an expression of the GROUP BY (error 3580); GROUPING() without an argument returns 1582, and its presence in a WHERE or without grouping 1111. Without ROLLUP, always 0.

Returns: BIGINT.

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

JSON_ARRAYAGG#

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

Builds a JSON array ([...]) from the values of expr of the group (in read order, or that of ORDER BY), each value being converted to a scalar JSON document (number as is, text in quotes, NULL as null) unless expr already produces a JSON document. DISTINCT removes duplicates, judged on the value of expr under its collation as in GROUP_CONCAT ('a', 'A' and 'a ' merged in utf8mb4_general_ci), not on its JSON text; LIMIT keeps only n elements after skipping m, once duplicates have been removed and elements sorted, as in GROUP_CONCAT (a NULL remains an element, sorted before the other values; [] if the limit keeps nothing). The content between the brackets is bounded by group_concat_max_len, like GROUP_CONCAT (warning 1260 beyond it; the truncated array is then no longer a valid JSON document).

Returns: JSON text.

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

JSON_OBJECTAGG#

JSON_OBJECTAGG(key, value)

Builds a JSON object ({...}) from the pairs of the group: each key becomes a member name, each value is converted as for JSON_ARRAYAGG. A repeated key keeps its first position and its last value; a NULL key raises error 3158. NULL for an empty group. The content is bounded by group_concat_max_len, as for JSON_ARRAYAGG.

Returns: JSON text.

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

10.6 Regular expressions#

MIRAJ implements PCRE-compatible regular expressions (linear-time engine, without back-references or zero-width assertions); it is the same engine as the REGEXP/RLIKE operator. By default, the search is case-insensitive, except under the utf8mb4_bin collation ('ABC' COLLATE utf8mb4_bin REGEXP 'abc' is 0); the c / i type always takes precedence. Weights do not apply ('é' REGEXP 'e' is false). The optional type parameter (last argument of each function) accepts a combination of the letters:

An invalid pattern raises an error; a quantifier applied directly to ^ or $ is rejected.

Summary:

REGEXP_LIKE#

REGEXP_LIKE(expr, pattern [, type])

1 if pattern matches somewhere in expr, 0 otherwise.

Returns: BIGINT.

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

REGEXP_INSTR#

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

Position (in characters, 1-based) of the start of the occurrence-th match (1 by default) from position (1 by default); 0 if it does not exist. If option is 1, returns the position after the end of the match rather than its start (0 by default).

Returns: BIGINT.

SELECT REGEXP_INSTR('abc123def456', '[0-9]+', 1, 2);     -- 10 (start of the second group of digits)
SELECT REGEXP_INSTR('abc123def456', '[0-9]+', 1, 2, 1);  -- 13 (position after its end)

REGEXP_SUBSTR#

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

Text of the occurrence-th match of pattern in expr from position. Returns an empty string (not NULL) if no match is found.

SELECT REGEXP_SUBSTR('prix: 42.50 EUR', '[0-9]+\\.[0-9]+');  -- '42.50'
SELECT REGEXP_SUBSTR('abc', '[0-9]+');                        -- '' (empty string)

REGEXP_REPLACE#

REGEXP_REPLACE(expr, pattern, replacement [, position [, occurrence [, type]]])

Replaces with replacement all matches of pattern from position (all of them, by default), or only the occurrence-th one if specified (> 0). In replacement, \N (N from 0 to 9) inserts the text of the N-th captured group (\0 = whole match); a \N without a corresponding group inserts an empty string.

SELECT REGEXP_REPLACE('2026-09-13', '([0-9]+)-([0-9]+)-([0-9]+)', '\\3/\\2/\\1');
-- '13/09/2026'
SELECT REGEXP_REPLACE('a1b2c3', '[0-9]', '#', 1, 2);   -- 'a1b#c3' (second match only)

10.7 Hash, encryption and encoding functions#

Summary:

MD5#

MD5(expr)

MD5 digest (RFC 1321) of expr, in lowercase hexadecimal (32 characters).

Returns: text.

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

Warning — not for passwords: MD5, SHA1 and PASSWORD are fast, unsalted and, for the first two, broken against collisions. A table of MD5(password) or SHA1(password) digests can be recovered by dictionary or brute force in a few minutes on a graphics card. For an application's passwords, hash them in the application with a slow, salted function (Argon2id, scrypt or bcrypt) and store only the result. These functions remain useful for a checksum, a deduplication key or compatibility.

SHA1 / SHA#

SHA1(expr)

SHA-1 digest (FIPS 180-4), in lowercase hexadecimal (40 characters). SHA is a strict alias.

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

SHA2#

SHA2(expr, bit_length)

Digest of the SHA-2 family, in lowercase hexadecimal. bit_length must be 224, 256, 384, 512, or 0 (equivalent to 256); any other value returns NULL.

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

PASSWORD#

PASSWORD(password)

Digest used internally for the authentication_string of an account (see chapter 15. Accounts and Privileges). Not normally needed in application queries.

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

Warning: PASSWORD() is an unsalted double SHA-1 (* followed by 40 hexadecimal digits), kept for protocol compatibility. Do not use it for your application's passwords (see the box under MD5); and a PASSWORD() digest must not be shared: it is enough to log in to the account with a modified client.

HEX#

HEX(expr)

Hexadecimal representation (uppercase) of expr: for a number, its 64-bit integer value; for a binary value, its raw bytes; for a text, its UTF-8 bytes.

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

UNHEX#

UNHEX(hex_string)

Decodes hex_string (an implicit leading digit 0 is added if its length is odd) into raw bytes. NULL if hex_string contains a character that is not a hexadecimal digit.

Returns: binary (VARBINARY).

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

TO_BASE64#

TO_BASE64(expr)

Encodes expr in base64 (standard alphabet, with = padding), inserting a line break every 76 characters.

Returns: text.

SELECT TO_BASE64('MIRAJ'), TO_BASE64('é');            -- 'TUlSQUo=', 'w6k='
SELECT LOCATE('\n', TO_BASE64(REPEAT('a', 60)));        -- 77 (line break after 76 characters)

FROM_BASE64#

FROM_BASE64(string)

Decodes string (standard base64) into raw bytes; spaces, tabs and line breaks are ignored; NULL if the text is not valid base64.

Returns: binary.

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

AES_ENCRYPT#

AES_ENCRYPT(data, key)

Encrypts data with AES-128-ECB (with PKCS7 padding), key being folded by exclusive OR onto 16 bytes whatever its length (default mode, corresponding to block_encryption_mode = aes-128-ecb).

Returns: binary.

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

Warning — compatibility only, not data protection: AES_ENCRYPT and AES_DECRYPT reproduce a legacy format, and this format is weak:

• ECB mode, no initialization vector: two identical 16-byte blocks give the same ciphertext, so equal values (or equal value beginnings) can be spotted without the key;
• key folded onto 16 bytes by exclusive OR, with no derivation: a short or predictable passphrase can be attacked directly, and two different keys can give the same AES key;
• no authentication: a modified ciphertext often decrypts to wrong data without an error (the only check is the padding);
• the key travels in the query text: MIRAJ masks it in its journals and in SHOW PROCESSLIST, but anything that sees the query before the server (application, client-side traces, network without TLS) sees it in clear.

Use them to read back data already encrypted this way. To protect new data, encrypt it in the application (AES-256-GCM or ChaCha20-Poly1305, random nonce, key kept outside the database) and store the result as VARBINARY.

AES_DECRYPT#

AES_DECRYPT(encrypted_data, key)

Inverse of AES_ENCRYPT with the same key derivation. NULL if decryption fails (wrong key, corrupted data).

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

COMPRESS#

COMPRESS(data)

Compresses data (text or binary): the original length on four bytes, least significant byte first (30 useful bits), followed by the zlib stream. An empty string stays an empty string. If the result ends with a space, a period . is appended to it, so that a CHAR or VARCHAR column does not strip it; preferably store the result in a VARBINARY or BLOB column.

Returns: binary.

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

UNCOMPRESS#

UNCOMPRESS(compressed_data)

Inverse of COMPRESS. An empty string stays an empty string. Unreadable data returns NULL with a warning:

An announced length greater than the actual content has no effect: the decompressed content is returned as is.

Returns: binary.

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

UNCOMPRESSED_LENGTH#

UNCOMPRESSED_LENGTH(compressed_data)

Original length of a value produced by COMPRESS, read from its header without decompressing anything. 0 for an empty string; NULL with warning 1259 for a value of four bytes or fewer.

Returns: integer.

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

OLD_PASSWORD#

OLD_PASSWORD(password)

Legacy 64-bit password digest: 16 lowercase hexadecimal digits; empty string for an empty string. Kept to read back old data, like PASSWORD (see the box under MD5).

Returns: text.

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

ENCODE / DECODE#

ENCODE(string, password)
DECODE(cipher, password)

Legacy symmetric scrambling: ENCODE returns a binary value of the same length as string, DECODE restores it to clear text with the same password. An empty password returns the string as is. No real security: reserved for reading back old data.

Returns: binary.

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

DES_ENCRYPT / DES_DECRYPT#

DES_ENCRYPT(string, key)
DES_DECRYPT(cipher, key)

Triple DES (CBC) encryption with a text key: the result starts with the byte FF (text key), followed by the ciphertext padded to a multiple of 8 bytes. Only the form with a text key is available: without a second argument, or with a key number, DES_ENCRYPT returns NULL with warning 1105 (DES key file not loaded). DES_DECRYPT returns as is a value that does not carry this header, and NULL if the key is wrong.

Returns: binary.

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

ENCRYPT#

ENCRYPT(password [, salt])

crypt digest of a password: traditional DES with a two-character salt (alphabet ./0-9A-Za-z), or MD5 ($1$salt$), SHA-256 ($5$salt$) and SHA-512 ($6$salt$). Without salt, a random DES salt is drawn. NULL if the salt is too short, invalid or of an unknown format.

Returns: text.

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

KDF#

KDF(key, salt [, {info | iterations} [, method [, width_bits]]])

Derives a binary key of width_bits / 8 bytes (128 bits by default) from key and salt. method is 'hkdf' (default, HKDF-SHA256; the third argument is then the info text) or 'pbkdf2_hmac' (PBKDF2-HMAC-SHA256; the third argument is the number of iterations, 1000 by default, from 1 to 10,000,000). An unknown method, a zero, negative, non-multiple-of-8 or too large width, or an iteration count out of bounds raise error 1210.

Returns: binary.

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

10.8 Network (IP) functions#

Summary:

INET_ATON#

INET_ATON(ipv4_address)

Converts an IPv4 address in strict dotted-decimal notation (four parts, no abbreviated form) to its 32-bit integer. NULL if ipv4_address is not a valid IPv4 in this notation.

Returns: BIGINT.

SELECT INET_ATON('192.168.1.1');  -- 3232235777

INET_NTOA#

INET_NTOA(integer)

Inverse of INET_ATON. NULL if integer is not in [0, 4294967295].

SELECT INET_NTOA(3232235777);  -- '192.168.1.1'

INET6_ATON#

INET6_ATON(address)

Converts an IPv4 or IPv6 address (text notation) to its binary form (4 bytes for IPv4, 16 for IPv6). NULL if address is neither.

Returns: binary.

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

INET6_NTOA#

INET6_NTOA(binary)

Inverse of INET6_ATON: (abbreviated) text form of a binary address of 4 or 16 bytes. NULL for any other length.

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

IS_IPV4#

IS_IPV4(string)

1 if string is a valid IPv4 address, 0 otherwise (never NULL for a non-NULL input).

Returns: BIGINT.

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

IS_IPV6#

IS_IPV6(string)

1 if string is a valid IPv6 address, 0 otherwise.

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

IS_IPV4_COMPAT#

IS_IPV4_COMPAT(binary)

1 if binary (16 bytes, as produced by INET6_ATON) is an obsolete IPv4-compatible address (::a.b.c.d: first twelve bytes zero), 0 otherwise.

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

IS_IPV4_MAPPED#

IS_IPV4_MAPPED(binary)

1 if binary is an IPv4-mapped address (::ffff:a.b.c.d), 0 otherwise.

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

10.9 System, session and lock functions#

Summary:

DATABASE / SCHEMA#

DATABASE()

Name of the session's current database, NULL if no database is selected. SCHEMA is a strict alias. Constant function (evaluated once per statement).

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

VERSION#

VERSION()

Version number of the MIRAJ server, in a format compatible with existing connectors: 9.0.40-miraj-1.0.0, identical to @@version and to SHOW VARIABLES LIKE 'version'. The leading number, 9.0.40, is the one the server announces alone in the protocol handshake: connectors and applications compare it with the version numbers they expect to enable their features (WordPress, for example, requires at least 5.5.5 and does not install otherwise). The product name and version follow (see chapter 1. Introduction for the variables that distinguish editions).

Returns: text.

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

CHARSET#

CHARSET(expr)

Character set of expr: utf8mb4 for a text (literal, column), binary for a number, a date, NULL or a binary value; latin1 or utf8mb3 for CONVERT(... USING set). An unknown column returns 1054. CHARSET, COLLATION and COERCIBILITY take exactly one argument: CHARSET() or CHARSET(a, b) is a syntax error (1064), not 1582.

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

COLLATION#

COLLATION(expr)

Collation of expr: that of the column or of COLLATE (utf8mb4_general_ci by default for a literal, utf8mb4_unicode_ci, utf8mb4_bin, see chapter 13), that of the operand retained for CONCAT, UPPER, etc.; binary for a number, a date or NULL.

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

COERCIBILITY#

COERCIBILITY(expr)

Collation priority of expr, used to arbitrate between two texts with different collations: 0 for an explicit COLLATE, 2 for a column, 3 for a system function (USER()), 4 for a CAST(... AS CHAR), 6 for a text literal, 7 for a number, 8 for NULL. Details in chapter 13.

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

BENCHMARK#

BENCHMARK(n, expr)

Measurement function: always returns 0 (MIRAJ does not evaluate expr n times). BENCHMARK(NULL, x) returns NULL, BENCHMARK(-1, x) too, with warning 1411.

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

LAST_INSERT_ID#

LAST_INSERT_ID()

Last auto-increment column value generated by an INSERT statement of the session.

LAST_INSERT_ID(expr) also sets this value to expr (and returns it): LAST_INSERT_ID(55) then LAST_INSERT_ID() are 55, like @@last_insert_id. With NULL, the value becomes 0. Calls within the same statement are evaluated in row order. After a multi-row INSERT, it is the value generated for the first row.

Returns: BIGINT.

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

ROW_COUNT#

ROW_COUNT()

Number of rows affected by the session's last INSERT, UPDATE or DELETE statement.

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

FOUND_ROWS#

FOUND_ROWS()

Number of rows of the last result read (legacy usage, specific to the connection). After a SELECT SQL_CALC_FOUND_ROWS ... LIMIT n, the number of rows the SELECT would have returned without its LIMIT: SELECT SQL_CALC_FOUND_ROWS id FROM t LIMIT 2; SELECT FOUND_ROWS(); returns the total of t.

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

CONNECTION_ID#

CONNECTION_ID()

Connection identifier of the current session, as shown by SHOW PROCESSLIST.

SELECT CONNECTION_ID();   -- for example 12

USER / SESSION_USER / SYSTEM_USER#

USER()

Identity of the client as supplied at connection, in the format 'user@host' (the client's host, not that of the account). SESSION_USER and SYSTEM_USER are strict aliases. In an SQL SECURITY DEFINER routine, USER() remains the connection's account: only CURRENT_USER() returns the definer there.

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

CURRENT_USER#

CURRENT_USER()

Account actually used for privilege checks, in the format 'user@host' (the host defined for the account, which can differ from that of USER() if the account has a wildcard entry such as '%'). Can also be written without parentheses (CURRENT_USER).

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

CURRENT_ROLE#

CURRENT_ROLE()

Active roles of the session, or the string 'NONE' if no role is active (see chapter 15. Accounts and Privileges).

SELECT CURRENT_ROLE();   -- 'NONE'

SLEEP#

SLEEP(seconds)

Suspends execution for seconds (fractions accepted), then returns 0. A NULL or negative argument does not wait (returns 0 immediately). A call never waits more than one hour: a larger value is brought down to 3,600 seconds, without a warning (an excessive SLEEP, by mistake or malice, no longer ties up a session and a server thread for days). The wait is interruptible: KILL QUERY on the session cuts it short and SLEEP returns 1; KILL CONNECTION or exceeding max_statement_time raise an error rather than returning a value.

Returns: BIGINT.

SELECT SLEEP(0.5);  -- waits half a second, returns 0

UUID / UUID_V4#

UUID()
UUID_V4()

Random version 4 UUID identifier, in lowercase (xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx). UUID_V4 is a strict alias.

Its 122 random bits come from a cryptographic generator (ChaCha, seeded and reseeded by the system), distinct from that of RAND(): a UUID cannot be deduced from the previous ones nor from the values of RAND(). It can therefore be used as a hard-to-guess identifier (sharing link, token). For a raw secret (salt, key, binary token), prefer RANDOM_BYTES.

Returns: text.

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

UUID_V7#

UUID_V7()

Version 7 UUID identifier, ordered in time: the first 48 bits carry the instant in milliseconds, the rest is random. Two successive values sort in the order of their creation, which makes it a good textual primary key (inserts at the end of the index). A new value on each call and on each row.

Returns: text.

SELECT UUID_V7();  -- for example '01a151fb-ce7c-7000-a185-3b114b9e8c8c'

SYS_GUID#

SYS_GUID()

The same identifier as UUID, written without hyphens: 32 lowercase hexadecimal digits. Like UUID(), each call and each row gives a new value; the function is rejected in a CHECK constraint and a generated column, and allowed as a default value (DEFAULT SYS_GUID()).

Returns: text.

SELECT SYS_GUID();  -- for example '499fae041ce14b428ded02de5582f5c8'

RANDOM_BYTES#

RANDOM_BYTES(n)

n random bytes drawn directly from the operating system's generator (cryptographic quality), for a salt, a key, an initialization vector or a token. n ranges from 1 to 1024; outside this range, error 1690 (length value is out of range in 'random_bytes'). NULL if n is NULL. Never evaluated as a constant: each call, each row, gives a new value.

Returns: binary (VARBINARY).

SELECT HEX(RANDOM_BYTES(16));             -- for example '194E96E9343C758A83AC7012CC2E1502'
SELECT LENGTH(RANDOM_BYTES(32));          -- 32

UUID_SHORT#

UUID_SHORT()

Increasing integer identifier, unique over time: each call returns the previous value plus one.

Returns: BIGINT.

SELECT UUID_SHORT(), UUID_SHORT();   -- for example 102128687452782593, 102128687452782594

GET_LOCK#

GET_LOCK(name, timeout)

Tries to take a lock named name (64 characters at most) for the session. Returns 1 if the lock is taken (repeated acquisitions by the same session accumulate), 0 if timeout seconds (fraction allowed; NULL = 0) elapse without success, NULL if the wait is interrupted by KILL QUERY or outside any session. A negative timeout ("unlimited wait") or one greater than a year is brought down to one year, like the other server timeouts. Raises an error if KILL CONNECTION occurs, if max_statement_time is exceeded, or if the wait would close a cycle between named locks (deadlock detected).

A session holds at most 1,024 distinct named locks at a time (re-acquisitions of a lock it already holds do not count): beyond that, GET_LOCK raises error 1210 (Incorrect arguments to GET_LOCK (at most 1024 distinct named locks per session)) without taking anything. RELEASE_LOCK or RELEASE_ALL_LOCKS free up slots.

Returns: BIGINT.

SELECT GET_LOCK('verrou_import', 10);      -- 1
-- ... protected processing ...
SELECT RELEASE_LOCK('verrou_import');      -- 1

RELEASE_LOCK#

RELEASE_LOCK(name)

Releases one acquisition of the lock name held by the session. 1 if an acquisition was released, 0 if the lock is held by another session, NULL if it is not held at all.

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

IS_FREE_LOCK#

IS_FREE_LOCK(name)

1 if nobody holds the lock name, 0 otherwise.

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

IS_USED_LOCK#

IS_USED_LOCK(name)

Connection identifier of the session that holds the lock name, NULL if it is free.

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

RELEASE_ALL_LOCKS#

RELEASE_ALL_LOCKS()

Releases all named-lock acquisitions of the current session; returns their number.

Returns: BIGINT.

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

MIRAJ_NOTIFY#

MIRAJ_NOTIFY(channel [, payload])

Function form of NOTIFY channel, payload (chapter 21, §21.4): the notification is published to the channel's subscribers when the statement's transaction commits, never if it fails or is rolled back. Returns 1. A NULL or empty channel raises error 1210, a payload that is too long (--change-events-max-payload) error 9010; Enterprise and Cluster editions (9001 in Express edition).

Returns: BIGINT.

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

MIRAJ_CHANGE_SEQ#

MIRAJ_CHANGE_SEQ()

Last change-event number published by the server (or its numbering base, drawn from the clock at startup, if it has published none): resume point of a subscriber that wants to read everything again (LISTEN … FROM, chapter 21, §21.3). Enterprise and Cluster editions.

Returns: BIGINT.

SELECT MIRAJ_CHANGE_SEQ();   -- for example 1792376684321324

BINLOG_GTID_POS#

BINLOG_GTID_POS(file, position)

GTID state of the binary log at the given position of one of its files (see chapter 17). NULL if an argument is NULL, if the file is unknown or if the position is beyond its end.

Returns: text.

SELECT BINLOG_GTID_POS('absent.000001', 4);   -- NULL (unknown file)

MASTER_GTID_WAIT#

MASTER_GTID_WAIT(gtid_position [, timeout])

On a replica, waits until the given GTID position ('domain-server-number', several separated by commas) has been applied, for at most timeout seconds (without end if omitted). Returns 0 when the position is reached, -1 if the timeout elapses. A malformed position raises error 1210.

Returns: BIGINT.

SELECT MASTER_GTID_WAIT('0-1-1', 0);   -- -1 (nothing has been applied on this server)

MASTER_POS_WAIT#

MASTER_POS_WAIT(file, position [, timeout [, channel]])

On a replica, waits until the source has been applied up to the given position of its binary log. Returns the number of events waited for, -1 if the timeout elapses, NULL if replication is not running.

Returns: BIGINT.

SELECT MASTER_POS_WAIT('miraj-bin.000002', 4, 0);   -- NULL (no replication in progress)

DECODE_HISTOGRAM#

DECODE_HISTOGRAM(type, histogram)

Writes a binary column-statistics histogram in clear text: 'SINGLE_PREC_HB' reads one byte per bucket (value / 255), 'DOUBLE_PREC_HB' two bytes per bucket, least significant byte first (value / 65535), each bucket being written with three decimals and separated from the next by a comma; 'JSON_HB' returns the text as is. Any other type returns NULL.

Returns: text.

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

System variables @@name#

In addition to the functions above, an expression can read a session or global system variable with @@name or @@session.name / @@local.name / @@global.name (for example @@version, @@miraj_edition, @@max_allowed_packet). These variables are described in chapter 16. Server Administration; they are manipulated with SET and can also be queried with SHOW VARIABLES. A user variable @name (specific to the session, NULL until it has received something) is read the same way and assigned in an expression with @name := value (see 10.19).

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

10.10 JSON functions#

MIRAJ carries a JSON document as a simple text value, written in the form {"a": 1, "b": [1, 2]} (a space after : and after each comma). The order of an object's members is preserved as written, and a number keeps its original notation (1.50 stays 1.50).

String escapes. A string of a document (value or member name) keeps the text written between its quotes, escapes included ("é", "http:\/\/x.fr", "\x"):

• the functions that return JSON return it as is: JSON_EXTRACT (and ->), JSON_QUERY, JSON_SET / JSON_INSERT / JSON_REPLACE / JSON_REMOVE / JSON_ARRAY_APPEND / JSON_ARRAY_INSERT (the rest of the document is not rewritten: a document produced by PHP json_encode keeps its é and \/), JSON_MERGE_PRESERVE / JSON_MERGE_PATCH, JSON_COMPACT / JSON_LOOSE / JSON_DETAILED / JSON_NORMALIZE / JSON_PRETTY, JSON_KEYS, JSON_OBJECT_TO_ARRAY, JSON_KEY_VALUE, JSON_UNQUOTE of an object or an array, JSON_SEARCH (paths returned) and the JSON column of JSON_TABLE;
• the functions that return a SQL text decode it: JSON_UNQUOTE (and ->>) of a string, JSON_VALUE, the columns of JSON_TABLE; JSON_SEARCH compares its pattern with the decoded text; an extracted value compared with a SQL text is compared without quotes, decoded (JSON_EXTRACT('["A"]', '$[0]') = 'A' is 1);
• document comparisons operate on the written text: JSON_EQUALS, JSON_CONTAINS, JSON_OVERLAPS, JSON_ARRAY_INTERSECT, JSON_OBJECT_FILTER_KEYS and JSON_SCHEMA_VALID (enum, const, pattern, required, properties..., minLength / maxLength counted in bytes of this text) distinguish "A" from "A" (JSON_CONTAINS('["A"]', '"A"') is 0); only JSON_CONTAINS and JSON_OVERLAPS find an object member by its decoded name (JSON_CONTAINS('{"A": 1}', '{"A": 1}') is 1). MEMBER OF (MIRAJ extension) compares the decoded texts;
• a key of a path designates the member of the same decoded name ($.A and $."A" designate the member "A") for JSON_VALUE, JSON_QUERY, JSON_EXISTS, JSON_SET & co., JSON_REMOVE, JSON_KEYS, JSON_LENGTH, JSON_CONTAINS, JSON_KEY_VALUE and the columns of JSON_TABLE; the member of the same written name for JSON_EXTRACT (and ->), JSON_CONTAINS_PATH, the paths of JSON_SEARCH and the row path (and NESTED PATH) of JSON_TABLE (only $."A" then designates this member). A member added by JSON_SET takes the name written in the path.

A string coming from SQL (JSON_OBJECT, JSON_ARRAY, JSON_QUOTE, value of JSON_SET) is written with ", \ and control characters escaped, the rest as is (/ and é not escaped).

The functions fall into three families: construction and validation (JSON_OBJECT, JSON_ARRAY, merges, JSON_QUOTE, JSON_VALID), reading by path (JSON_EXTRACT and the -> operator, JSON_UNQUOTE and ->>, JSON_VALUE, JSON_CONTAINS, JSON_SEARCH...) and modification (JSON_SET, JSON_INSERT, JSON_REPLACE, JSON_REMOVE, JSON_ARRAY_APPEND, JSON_ARRAY_INSERT). The aggregates JSON_ARRAYAGG and JSON_OBJECTAGG are described with the aggregate functions.

Summary:

JSON_OBJECT#

JSON_OBJECT([key1, value1, key2, value2, ...])

Builds a JSON object from key/value pairs (an even number of arguments is required). Each key is converted to JSON text; each value is converted to a JSON scalar (number as is, text in escaped quotes, NULL as null) unless it is already a JSON document (produced by another JSON function or a CAST(... AS JSON)). A NULL key raises an error.

Returns: JSON text; binary string if a key or a value is a binary string (x'…', CAST(… AS BINARY), VARBINARY / BLOB column, or CHAR(n) without USING written as an argument): its bytes are kept as is (except ", \ and control characters, escaped), a text is written in it with one byte per character up to U+00FF and as \uXXXX beyond (JSON_ARRAY('é', CHAR(1)) returns the bytes [", E9, ", "\u0001"]). Same rule for JSON_ARRAY.

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

JSON_ARRAY#

JSON_ARRAY([value1, value2, ...])

Builds a JSON array from its arguments (same conversion rules as JSON_OBJECT).

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

JSON_MERGE_PRESERVE / JSON_MERGE#

JSON_MERGE_PRESERVE(document1, document2, ...)

Merges at least two JSON documents: two arrays are concatenated; two objects are combined (the values of a key present in both are themselves merged recursively); any other combination (e.g. an object and an array) treats each isolated value as a one-element array and then concatenates them. JSON_MERGE is a strict alias.

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

Merge according to RFC 7396: the members of the following document replace those of the previous one, a member equal to null removes the corresponding key; a document that is not an object entirely replaces the previous one. NULL if a document is NULL (unless it is followed by a document that is not an object, which takes precedence anyway).

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

JSON_QUOTE#

JSON_QUOTE(string)

Surrounds string with double quotes and escapes JSON special characters (control character as \u001F, uppercase digits), to produce a valid JSON string from a raw SQL text. A binary string (x'e9', CHAR(233)) is read as latin1, one character per byte ("é"); likewise for the value placed by JSON_SET & co.

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

JSON_VALID#

JSON_VALID(string)

1 if string is a syntactically valid JSON document, 0 otherwise.

Returns: BIGINT.

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

JSON paths#

The read and modification functions designate a part of a document with a path, written as a string:

An index applied to a value that is not an array treats it as a one-element array: $.a[0] designates $.a itself. Wildcards (.*, [*], **) and ranges are only allowed by the read functions that can return several values (JSON_EXTRACT, JSON_CONTAINS_PATH, JSON_SEARCH); elsewhere the function returns NULL with warning 4044, except JSON_VALUE and JSON_QUERY, which return NULL without a warning, and JSON_EXISTS, which accepts them. A badly written path or an invalid document does not raise an error: the function returns NULL with a warning (SHOW WARNINGS) that cites the argument and the function — 4041 (unfinished path) or 4042 (position of the faulty character) for a path, 4037 (unfinished text), 4038 (position) or 4036 (control character in a string, position) for a document. An unknown escape in a string (\x) is the character itself. A numeric or temporal argument passed as a document is read in its text form: JSON_EXTRACT(12, '$') returns 12, a date raises warning 4038.

Unless otherwise stated, a NULL argument (document or path) gives NULL.

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

Operators -> and ->>#

column->'path'
column->>'path'