| name | mariadb-numeric-functions |
| description | MariaDB numeric and math functions — rounding/truncation (ROUND, the TRUNCATE function vs the TRUNCATE TABLE statement, FLOOR, CEILING, SIGN, ABS), integer-vs-decimal division (the DIV operator vs /, MOD / %), powers and logs (POW/POWER, SQRT, EXP, LN, LOG, LOG2, LOG10), trigonometry (SIN, COS, TAN, ASIN, ACOS, ATAN, ATAN2, COT, PI, DEGREES, RADIANS), randomness (RAND, seeded RAND(N)), base conversion (CONV, OCT, BIN, HEX), checksums (CRC32, CRC32C), and number formatting (FORMAT). Use when writing SQL that does arithmetic, rounding, base conversion, or random sampling in MariaDB — especially where exact-vs-approximate types or division-by-zero behavior matter. |
MariaDB Numeric Functions
Last updated: 2026-06-24
Catalog of every built-in numeric/math function in MariaDB, with signature and a one-line semantic summary per entry. For a function not listed here, fall back to the canonical reference at https://mariadb.com/docs/server/reference/sql-functions/numeric-functions.
Default context: Assume MariaDB 11.8 LTS (GA May 2025) unless the user states another version. Functions with a *(since X.Y)* annotation are only available from that version onward; everything else is in every current LTS branch (10.6, 10.11, 11.4, 11.8).
What LLMs Often Miss
| If the agent writes / assumes… | …prefer the MariaDB form |
|---|
a / b between two integers returns an integer (e.g. 7/2 = 3) | / always returns a decimal — 7/2 is 3.5000 (four fractional digits by default, via div_precision_increment). Use a DIV b for integer (toward-zero) division |
7 DIV 2 returns a float or rounds | DIV returns an integer, discarding the remainder (3); it's BIGINT-based. Get the remainder with MOD / % |
x / 0 raises a division-by-zero error | By default it returns NULL. You get an error only when ERROR_ON_DIVISION_BY_ZERO is in sql_mode (it's part of the default strict mode). Same for MOD by zero |
ROUND(2.5) uses banker's rounding (round-half-to-even) | For DECIMAL/exact values MariaDB rounds half away from zero: ROUND(2.5)=3, ROUND(-2.5)=-3. For FLOAT/DOUBLE the platform C library decides, so results can differ between operating systems — round a DECIMAL if you need determinism |
Storing money in FLOAT/DOUBLE and rounding for display | FLOAT/DOUBLE are approximate (0.1 + 0.2 != 0.3). Use DECIMAL(p,s) for currency and anything needing exactness (up to 38 digits) |
TRUNCATE(x, d) and TRUNCATE TABLE are related | They're unrelated despite the name. TRUNCATE(X, D) is the function that chops to D decimal places toward zero; TRUNCATE TABLE is the DDL statement that empties a table (see mariadb-drop-table) |
FLOOR(x) / CEILING(x) always return a BIGINT | The return type follows the argument: integer/string/double args yield BIGINT, but a DECIMAL argument yields a DECIMAL. Don't assume the result fits an integer column |
RAND() is fine for security tokens or unbiased sampling | RAND() is not cryptographically secure; RAND(N) with a constant seed is repeatable (good for tests, not secrets), and RAND() is unsafe for statement-based replication. ORDER BY RAND() scans the whole table — avoid on large ones |
MOD(n, 0) errors, or MOD of a negative behaves like Python's % | MOD returns NULL for a zero divisor (unless ERROR_ON_DIVISION_BY_ZERO), and the result takes the sign of the dividend (-7 MOD 3 = -1), not the divisor. N % M, N MOD M, MOD(N,M) are equivalent |
FORMAT(n, 2) returns a number you can compute with | FORMAT returns a string with locale-aware thousands separators ('1,234,567.89'). Feeding it back into arithmetic re-parses and truncates at the first separator |
CONV(n, 10, 62) works on every version | Bases up to 62 are supported (since 11.4); earlier versions cap at base 36. CONV returns a string at 64-bit precision |
CRC32('abc') is unary and CRC32C always exists | CRC32 gained an optional rolling-checksum argument — CRC32([par,] expr) — and CRC32C (Castagnoli, as used by InnoDB/MyRocks) was added, both (since 10.8) |
Related operators
Arithmetic lives partly as operators, not functions, so they aren't in the catalog below:
/ — division; always returns a decimal. div_precision_increment (default 4) sets the fractional digits.DIV — integer division (toward zero).% / MOD — modulo; result takes the dividend's sign; NULL on zero divisor unless strict mode errors.
FORMAT() (locale-aware number-to-string) and HEX/BIN are catalogued under mariadb-string-functions, not here.
Functions
Auto-generated from the canonical server/reference/sql-functions/numeric-functions/ pages by agent-skills/extractor/extract_function_category.py; regenerate when the doc tree changes. Do not hand-edit between the markers.
ABS
ABS(X)
Returns the absolute (non-negative) value of X.
ACOS
ACOS(X)
Returns the arc cosine of X, that is, the value whose cosine is X.
ASIN
ASIN(X)
Returns the arc sine of X, that is, the value whose sine is X.
ATAN
ATAN(X)
Returns the arc tangent of X, that is, the value whose tangent is X.
ATAN2
ATAN(Y,X), ATAN2(Y,X)
Returns the arc tangent of the two variables X and Y.
CEIL
CEIL(X)
CEIL() is a synonym for CEILING().
CEILING
CEILING(X)
Returns the smallest integer value not less than X.
CONV
CONV(N,from_base,to_base)
Converts numbers between different number bases.
COS
COS(X)
Returns the cosine of X, where X is given in radians.
COT
COT(X)
Returns the cotangent of X.
CRC32
CRC32([par,]expr)
Computes a cyclic redundancy check (CRC) value and returns a 32-bit unsigned value.
CRC32C
CRC32C([par,]expr)
MariaDB has always included a native unary function CRC32() that computes the CRC-32 of a string using the ISO 3309 polynomial that used by zlib and many others.
DEGREES
DEGREES(X)
Returns the argument X, converted from radians to degrees.
DIV
DIV
Integer division.
EXP
EXP(X)
Returns the value of e (the base of natural logarithms) raised to the power of X.
FLOOR
FLOOR(X)
Returns the largest integer value not greater than X.
LN
LN(X)
Returns the natural logarithm of X; that is, the base-e logarithm of X.
LOG
LOG(X), LOG(B,X)
If called with one parameter, this function returns the natural logarithm of X.
LOG10
LOG10(X)
Returns the base-10 logarithm of X.
LOG2
LOG2(X)
Returns the base-2 logarithm of X.
MOD
MOD(N,M), N % M, N MOD M
Modulo operation.
OCT
OCT(N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number.
PI
PI()
Returns the value of π (pi).
POW
POW(X,Y)
Returns the value of X raised to the power of Y.
POWER
POWER(X,Y)
This is a synonym for POW(), which returns the value of X raised to the power of Y.
RADIANS
RADIANS(X)
Returns the argument X, converted from degrees to radians.
RAND
RAND(), RAND(N)
Returns a random DOUBLE precision floating point value v in the range 0 <= v < 1.0.
ROUND
ROUND(X), ROUND(X,D)
Rounds the argument X to D decimal places.
SIGN
SIGN(X)
Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.
SIN
SIN(X)
Returns the sine of X, where X is given in radians.
SQRT
SQRT(X)
Returns the square root of X.
TAN
TAN(X)
Returns the tangent of X, where X is given in radians.
TO_NUMBER
TO_NUMBER(number_or_string_subject)
The function returns the DOUBLE data type for all signatures and input data types.
TRUNCATE
TRUNCATE(X,D)
Returns the number X, truncated to D decimal places.
XXH3
XXH3(expr)
XXH3 returns a fast, non-cryptographic xxHash of a text value as a 64-bit number (BIGINT UNSIGNED). (since 13.1)
XXH32
XXH32(expr)
XXH32 returns a fast, non-cryptographic xxHash of a text value as a 32-bit number (INT UNSIGNED). (since 13.1)
See Also
mariadb-create-table — choosing DECIMAL vs FLOAT/DOUBLE for the columns these functions operate onmariadb-drop-table — the TRUNCATE TABLE statement (not the TRUNCATE() function above)mysql-to-mariadb (topical) — division-by-zero sql_mode behavior and money-in-DECIMAL guidance- Canonical reference on
mariadb.com/docs: https://mariadb.com/docs/server/reference/sql-functions/numeric-functions
