🔢

MariaDB Numeric Functions

MariaDB

Complete reference for MariaDB numeric functions covering ABS, ROUND, FLOOR, CEIL, SQRT, POW, LOG, RAND, TRUNCATE, MOD, CONV, and CRC32. Every function includes syntax, real SQL examples, and notes on precision and numeric type handling. Updated for MariaDB 11.x.

41 functions

Jump to function

What are MariaDB Numeric Functions?

MariaDB numeric functions perform mathematical calculations on integer, decimal, float, and double column types. They are compatible with MySQL numeric functions. Key differences from PostgreSQL include: TRUNCATE() takes a second argument for decimal places (vs PostgreSQL's trunc()), RAND() generates a random float between 0 and 1, and MariaDB uses integer division with DIV rather than PostgreSQL's / on integers.

ABS

→ NUMERIC

Returns the absolute (non-negative) value of a numeric expression.

Signature

ABS(X)

Parameters

Parameter	Type	Description
X	NUMERIC	Any numeric value or expression.

Examples

Positive value unchanged

sql

SELECT ABS(42);

→-- 42

Negative value made positive

sql

SELECT ABS(-5);

→-- 5

Zero returns zero

sql

SELECT ABS(0);

→-- 0

On a column

sql

SELECT ABS(balance) FROM accounts;

→-- absolute balance

NULL handling

sql

SELECT ABS(NULL);

→-- NULL

⚠Anti-Pattern— Using ABS for distance without indexing

WHERE ABS(col - 100) < 10 prevents index usage on col.

✓ Instead: Rewrite as `WHERE col BETWEEN 90 AND 110` to allow range-index scans.

⇄ vs PostgreSQL: PostgreSQL uses the same ABS() syntax; also supports the @ unary operator for absolute value.

CEIL

→ BIGINT

Returns the smallest integer value not less than (i.e., rounds up to) the argument.

Signature

CEIL(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A numeric value to round up.

Examples

Basic ceiling

sql

SELECT CEIL(4.2);

→-- 5

Negative value rounds toward zero

sql

SELECT CEIL(-4.2);

→-- -4

Zero returns zero

sql

SELECT CEIL(0);

→-- 0

On a column

sql

SELECT CEIL(price) FROM products;

→-- rounded price

NULL handling

sql

SELECT CEIL(NULL);

→-- NULL

⚠Anti-Pattern— Expecting CEIL to return a decimal

CEIL always returns a BIGINT (whole number), so CEIL(4.9) is 5, not 5.0.

✓ Instead: If you need a DECIMAL type, cast the result: `CAST(CEIL(x) AS DECIMAL(10,2))`.

⇄ vs PostgreSQL: PostgreSQL uses `ceil()` with the same behavior; also has `ceiling()` as an alias.

CEILING

→ BIGINT

Synonym for CEIL(); returns the smallest integer value not less than the argument.

Signature

CEILING(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A numeric value to round up.

Examples

Basic ceiling

sql

SELECT CEILING(4.2);

→-- 5

Negative rounds toward zero

sql

SELECT CEILING(-4.2);

→-- -4

Zero returns zero

sql

SELECT CEILING(0);

→-- 0

On a column

sql

SELECT CEILING(price) FROM products;

→-- rounded price

NULL handling

sql

SELECT CEILING(NULL);

→-- NULL

⚠Anti-Pattern— Mixing CEIL and CEILING

Using both names in the same project creates needless confusion without any functional difference.

✓ Instead: Standardize on one name (CEIL is shorter) across all queries and procedures.

⇄ vs PostgreSQL: PostgreSQL supports both `ceil()` and `ceiling()` as aliases.

FLOOR

→ BIGINT

Returns the largest integer value not greater than (i.e., rounds down to) the argument.

Signature

FLOOR(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A numeric value to round down.

Examples

Rounds down

sql

SELECT FLOOR(4.9);

→-- 4

Negative rounds away from zero

sql

SELECT FLOOR(-4.2);

→-- -5

Zero returns zero

sql

SELECT FLOOR(0);

→-- 0

On a column

sql

SELECT FLOOR(price) FROM products;

→-- floored price

NULL handling

sql

SELECT FLOOR(NULL);

→-- NULL

⚠Anti-Pattern— Using FLOOR for truncation of negatives

FLOOR(-2.5) = -3, not -2; for truncation toward zero use TRUNCATE().

✓ Instead: Use TRUNCATE(X, 0) when you want rounding toward zero regardless of sign.

⇄ vs PostgreSQL: PostgreSQL `floor()` behaves identically.

ROUND

→ NUMERIC

Rounds a value to a specified number of decimal places, defaulting to zero decimal places.

Signatures

ROUND(X)

ROUND(X, D)

Parameters

Parameter	Type	Description
X	NUMERIC	The value to round.
D	INT	Number of decimal places (default 0). Negative values round left of the decimal point.

Examples

Round to 2 decimal places

sql

SELECT ROUND(4.567, 2);

→-- 4.57

Negative value rounds away from zero

sql

SELECT ROUND(-4.5);

→-- -5

Zero returns zero

sql

SELECT ROUND(0);

→-- 0

On a column

sql

SELECT ROUND(price, 2) FROM products;

→-- rounded price

NULL handling

sql

SELECT ROUND(NULL);

→-- NULL

⚠Anti-Pattern— Rounding money in the database

Storing rounded FLOAT values for currency introduces floating-point errors.

✓ Instead: Store monetary values as DECIMAL(19,4) and only round at display time.

⇄ vs PostgreSQL: PostgreSQL `round()` has the same signature; `round(x)` returns NUMERIC type when x is NUMERIC.

TRUNCATE

→ NUMERIC

Truncates (drops digits without rounding) a number to a specified number of decimal places.

Signature

TRUNCATE(X, D)

Parameters

Parameter	Type	Description
X	NUMERIC	The value to truncate.
D	INT	Number of decimal places to keep; negative values truncate left of the decimal.

Examples

No rounding occurs

sql

SELECT TRUNCATE(4.999, 2);

→-- 4.99

Truncates toward zero for negatives

sql

SELECT TRUNCATE(-4.9, 0);

→-- -4

Zero input

sql

SELECT TRUNCATE(0, 2);

→-- 0.00

On a column

sql

SELECT TRUNCATE(price, 0) FROM products;

→-- integer part of price

NULL handling

sql

SELECT TRUNCATE(NULL, 2);

→-- NULL

⚠Anti-Pattern— Confusing TRUNCATE with ROUND

TRUNCATE(4.999, 2) is 4.99, not 5.00 — it never rounds up.

✓ Instead: Use ROUND() when you want standard rounding; use TRUNCATE() only when you intentionally want to discard trailing digits.

⇄ vs PostgreSQL: PostgreSQL uses `trunc(x, d)` — not TRUNCATE — with the same semantics.

MOD

→ NUMERIC

Returns the remainder of N divided by M (modulo operation).

Signatures

MOD(N, M)

N % M

N MOD M

Parameters

Parameter	Type	Description
N	NUMERIC	The dividend.
M	NUMERIC	The divisor.

Examples

Basic modulo

sql

SELECT MOD(10, 3);

→-- 1

Sign follows the dividend

sql

SELECT MOD(-10, 3);

→-- -1

Zero dividend

sql

SELECT MOD(0, 5);

→-- 0

Even/odd detection on a column

sql

SELECT MOD(id, 2) FROM orders;

→-- 0 or 1

NULL handling

sql

SELECT MOD(10, NULL);

→-- NULL

⚠Anti-Pattern— Dividing by zero with MOD

MOD(x, 0) returns NULL in MariaDB rather than raising an error, which can silently hide bugs.

✓ Instead: Add a NULLIF guard: `MOD(x, NULLIF(divisor, 0))` and handle NULL in application logic.

⇄ vs PostgreSQL: PostgreSQL uses the % operator or `mod(n, m)` function with identical semantics.

POWER

→ DOUBLE

Returns the value of X raised to the power of Y.

Signature

POWER(X, Y)

Parameters

Parameter	Type	Description
X	NUMERIC	The base value.
Y	NUMERIC	The exponent.

Examples

2 to the power of 10

sql

SELECT POWER(2, 10);

→-- 1024

Negative base with odd exponent

sql

SELECT POWER(-2, 3);

→-- -8

Zero base

sql

SELECT POWER(0, 5);

→-- 0

On a column

sql

SELECT POWER(amount, 2) FROM measures;

→-- squared amount

NULL handling

sql

SELECT POWER(NULL, 2);

→-- NULL

⚠Anti-Pattern— Using POWER for integer exponentiation expecting INT result

POWER always returns DOUBLE, so POWER(2,3) = 8.0 not 8.

✓ Instead: Cast the result if you need an integer: `CAST(POWER(2,3) AS UNSIGNED)`.

⇄ vs PostgreSQL: PostgreSQL uses `power(x, y)` with the same semantics; also supports the `^` operator.

POW

→ DOUBLE

Synonym for POWER(); returns X raised to the power of Y.

Signature

POW(X, Y)

Parameters

Parameter	Type	Description
X	NUMERIC	The base value.
Y	NUMERIC	The exponent.

Examples

2 to the power of 8

sql

SELECT POW(2, 8);

→-- 256

Negative base, even exponent

sql

SELECT POW(-3, 2);

→-- 9

Zero to the zero power is 1

sql

SELECT POW(0, 0);

→-- 1

On a column

sql

SELECT POW(radius, 2) * PI() FROM circles;

→-- area

NULL handling

sql

SELECT POW(NULL, 2);

→-- NULL

⚠Anti-Pattern— Chaining POW for large integer math

POW returns DOUBLE, which loses integer precision beyond 2^53.

✓ Instead: For large integer exponentiation, use application-side big-integer math or a stored procedure with DECIMAL arithmetic.

⇄ vs PostgreSQL: PostgreSQL does not have `pow()` as a standalone function name in all versions; use `power()`.

SQRT

→ DOUBLE

Returns the square root of a non-negative number.

Signature

SQRT(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A non-negative numeric value.

Examples

Perfect square

sql

SELECT SQRT(9);

→-- 3

Irrational result

sql

SELECT SQRT(2);

→-- 1.4142135623731

Zero input

sql

SELECT SQRT(0);

→-- 0

On a column

sql

SELECT SQRT(area) FROM shapes;

→-- side length

NULL handling

sql

SELECT SQRT(NULL);

→-- NULL

⚠Anti-Pattern— Assuming SQRT(-1) raises an error

SQRT(-1) silently returns NULL, which can propagate undetected through calculations.

✓ Instead: Validate or use ABS() before calling SQRT() when input sign is uncertain.

⇄ vs PostgreSQL: PostgreSQL `sqrt()` raises an error for negative input rather than returning NULL.

EXP

→ DOUBLE

Returns e (Euler's number, approximately 2.71828) raised to the power of X.

Signature

EXP(X)

Parameters

Parameter	Type	Description
X	NUMERIC	The exponent applied to e.

Examples

Value of e

sql

SELECT EXP(1);

→-- 2.718281828459

Negative exponent

sql

SELECT EXP(-1);

→-- 0.36787944117144

e^0 = 1

sql

SELECT EXP(0);

→-- 1

On a column

sql

SELECT EXP(log_return) FROM trades;

→-- gross return multiplier

NULL handling

sql

SELECT EXP(NULL);

→-- NULL

⚠Anti-Pattern— Using EXP for very large exponents

EXP(709) is near the DOUBLE overflow limit; EXP(710) returns NULL (overflow).

✓ Instead: Cap exponents at safe values or use logarithmic representations for very large ranges.

⇄ vs PostgreSQL: PostgreSQL `exp()` behaves identically.

LOG

→ DOUBLE

Returns the natural logarithm of X, or the logarithm of X to the given base B when two arguments are supplied.

Signatures

LOG(X)

LOG(B, X)

Parameters

Parameter	Type	Description
X	NUMERIC	The value whose logarithm is computed (must be > 0).
B	NUMERIC	Optional base for the logarithm (must be > 1).

Examples

Natural log of e

sql

SELECT LOG(EXP(1));

→-- 1

Log base 10 of 100

sql

SELECT LOG(10, 100);

→-- 2

Log of zero is undefined

sql

SELECT LOG(0);

→-- NULL

On a column

sql

SELECT LOG(views) FROM articles;

→-- log-scaled views

NULL handling

sql

SELECT LOG(NULL);

→-- NULL

⚠Anti-Pattern— Passing non-positive values to LOG

LOG(0) and LOG(-5) return NULL, silently poisoning aggregates like SUM().

✓ Instead: Filter rows before logging: `WHERE col > 0` or use `IF(col > 0, LOG(col), NULL)`.

⇄ vs PostgreSQL: PostgreSQL `log(x)` computes log base 10; `ln(x)` computes natural log; `log(b, x)` also supported.

LOG2

→ DOUBLE

Returns the base-2 logarithm of X.

Signature

LOG2(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A positive numeric value.

Examples

log2(8) = 3

sql

SELECT LOG2(8);

→-- 3

log2(1) = 0

sql

SELECT LOG2(1);

→-- 0

Undefined for zero

sql

SELECT LOG2(0);

→-- NULL

On a column

sql

SELECT LOG2(file_size) FROM files;

→-- bit depth

NULL handling

sql

SELECT LOG2(NULL);

→-- NULL

⚠Anti-Pattern— Using LOG2 for power-of-two checks

LOG2(n) = FLOOR(LOG2(n)) is only true for exact powers of two, but floating-point errors can affect this.

✓ Instead: Use the bitwise trick `n > 0 AND (n & (n-1)) = 0` for reliable power-of-two detection.

⇄ vs PostgreSQL: PostgreSQL does not have `log2()`; use `log(2, x)` or `ln(x)/ln(2)`.

LOG10

→ DOUBLE

Returns the base-10 logarithm of X.

Signature

LOG10(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A positive numeric value.

Examples

log10(100) = 2

sql

SELECT LOG10(100);

→-- 2

log10(1) = 0

sql

SELECT LOG10(1);

→-- 0

Undefined for zero

sql

SELECT LOG10(0);

→-- NULL

On a column

sql

SELECT LOG10(population) FROM cities;

→-- order of magnitude

NULL handling

sql

SELECT LOG10(NULL);

→-- NULL

⚠Anti-Pattern— Forgetting that LOG10 and LOG differ

In MariaDB, LOG(x) is the natural log, not base-10; confusing them gives wrong results.

✓ Instead: Use LOG10(x) explicitly when you need base-10, and LN(x) or LOG(x) for natural log.

⇄ vs PostgreSQL: PostgreSQL `log(x)` (single argument) returns base-10 log, unlike MariaDB where LOG(x) is natural log.

LN

→ DOUBLE

Returns the natural logarithm (base e) of X; equivalent to LOG(X).

Signature

LN(X)

Parameters

Parameter	Type	Description
X	NUMERIC	A positive numeric value.

Examples

LN of e equals 1

sql

SELECT LN(EXP(1));

→-- 1

LN of 1 is 0

sql

SELECT LN(1);

→-- 0

Undefined for zero

sql

SELECT LN(0);

→-- NULL

On a column

sql

SELECT LN(revenue) FROM financials;

→-- log-scaled revenue

NULL handling

sql

SELECT LN(NULL);

→-- NULL

⚠Anti-Pattern— Using LN for compound growth when base matters

LN gives natural log which implies continuous compounding; discrete period compounding requires LOG(1+r) per period.

✓ Instead: Ensure you understand the compounding basis before applying logarithms to financial data.

⇄ vs PostgreSQL: PostgreSQL `ln(x)` behaves identically.

PI

→ DOUBLE

Returns the value of π (pi) to 15 significant decimal digits.

Signature

PI()

Examples

Full precision pi

sql

SELECT PI();

→-- 3.141592653589793

Rounded to 4 places

sql

SELECT ROUND(PI(), 4);

→-- 3.1416

Area of a circle

sql

SELECT PI() * POW(r, 2) FROM circles;

→-- circle area

Circle circumference

sql

SELECT 2 * PI() * radius FROM circles;

→-- circumference

30 degrees in radians

sql

SELECT SIN(PI() / 6);

→-- 0.5

⚠Anti-Pattern— Hard-coding 3.14

Using 3.14 or 3.14159 instead of PI() introduces unnecessary rounding error.

✓ Instead: Always use PI() for maximum built-in precision.

⇄ vs PostgreSQL: PostgreSQL uses `pi()` with identical precision.

RAND

→ DOUBLE

Returns a pseudo-random floating-point value in the range [0, 1), optionally seeded with an integer.

Signatures

RAND()

RAND(N)

Parameters

Parameter	Type	Description
N	INT	Optional integer seed for reproducible sequences.

Examples

Random float [0,1)

sql

SELECT RAND();

→-- e.g. 0.7312...

Seeded, reproducible

sql

SELECT RAND(42);

→-- 0.6316...

Random integer in a range

sql

SELECT FLOOR(RAND() * 100);

→-- random int 0-99

Random row sampling

sql

SELECT * FROM products ORDER BY RAND() LIMIT 5;

→-- 5 random rows

NULL seed returns NULL

sql

SELECT RAND(NULL);

→-- NULL

⚠Anti-Pattern— ORDER BY RAND() on large tables

ORDER BY RAND() generates a random value for every row, sorts all of them, and returns N — O(n log n) cost.

✓ Instead: Use `WHERE RAND() < fraction` for approximate sampling, or join to a random primary key range for exact sampling.

⇄ vs PostgreSQL: PostgreSQL uses `random()` (no argument) returning [0,1); seeding is done via `setseed()`.

SIGN

→ INT

Returns -1, 0, or 1 depending on whether the argument is negative, zero, or positive.

Signature

SIGN(X)

Parameters

Parameter	Type	Description
X	NUMERIC	Any numeric value.

Examples

Positive

sql

SELECT SIGN(42);

→-- 1

Negative

sql

SELECT SIGN(-7.5);

→-- -1

Zero

sql

SELECT SIGN(0);

→-- 0

On a column

sql

SELECT SIGN(balance) FROM accounts;

→-- -1, 0, or 1

NULL handling

sql

SELECT SIGN(NULL);

→-- NULL

⚠Anti-Pattern— Using SIGN as a substitute for CASE

Multiplying by SIGN(x) to flip values works but can be confusing and does not handle zero gracefully in all contexts.

✓ Instead: Use a CASE expression for clarity when the zero case matters: `CASE WHEN x > 0 THEN ... WHEN x < 0 THEN ... ELSE ... END`.

⇄ vs PostgreSQL: PostgreSQL `sign()` behaves identically.

GREATEST

→ NUMERIC

Returns the largest value from a list of two or more expressions.

Signature

GREATEST(value1, value2, ...)

Parameters

Parameter	Type	Description
value1, value2, ...	ANY	Two or more values to compare; can be numeric, string, or date.

Examples

Max of a list

sql

SELECT GREATEST(3, 7, 2, 9, 1);

→-- 9

All negative values

sql

SELECT GREATEST(-5, -1, -10);

→-- -1

All zeros

sql

SELECT GREATEST(0, 0, 0);

→-- 0

Across columns in a row

sql

SELECT GREATEST(a, b, c) FROM scores;

→-- max of three columns

NULL propagates

sql

SELECT GREATEST(5, NULL, 3);

→-- NULL

⚠Anti-Pattern— Assuming GREATEST ignores NULLs

Unlike MAX(), GREATEST returns NULL if any argument is NULL.

✓ Instead: Wrap nullable arguments: `GREATEST(COALESCE(a, 0), COALESCE(b, 0))`.

⇄ vs PostgreSQL: PostgreSQL `greatest()` behaves identically including NULL propagation.

LEAST

→ NUMERIC

Returns the smallest value from a list of two or more expressions.

Signature

LEAST(value1, value2, ...)

Parameters

Parameter	Type	Description
value1, value2, ...	ANY	Two or more values to compare.

Examples

Min of a list

sql

SELECT LEAST(3, 7, 2, 9, 1);

→-- 1

Most negative value

sql

SELECT LEAST(-5, -1, -10);

→-- -10

All zeros

sql

SELECT LEAST(0, 0, 0);

→-- 0

Capping a column

sql

SELECT LEAST(price, max_price) FROM products;

→-- capped price

NULL propagates

sql

SELECT LEAST(5, NULL, 3);

→-- NULL

⚠Anti-Pattern— Assuming LEAST ignores NULLs like MIN()

LEAST returns NULL if any argument is NULL, unlike the aggregate MIN() which skips NULLs.

✓ Instead: Use COALESCE on nullable arguments before passing them to LEAST.

⇄ vs PostgreSQL: PostgreSQL `least()` behaves identically.

DIV

→ BIGINT

Performs integer division, returning the integer quotient of X divided by Y (truncates toward zero).

Signature

X DIV Y

Parameters

Parameter	Type	Description
X	NUMERIC	The dividend.
Y	NUMERIC	The divisor (non-zero).

Examples

Basic integer division

sql

SELECT 10 DIV 3;

→-- 3

Truncates toward zero

sql

SELECT -10 DIV 3;

→-- -3

Zero dividend

sql

SELECT 0 DIV 5;

→-- 0

On a column

sql

SELECT total DIV unit_size FROM batches;

→-- number of complete batches

Division by zero

sql

SELECT 10 DIV 0;

→-- NULL (with warning)

⚠Anti-Pattern— Expecting DIV to raise an error on division by zero

DIV returns NULL with a warning for division by zero rather than raising an error.

✓ Instead: Check divisor with NULLIF: `X DIV NULLIF(Y, 0)` and handle NULL explicitly.

⇄ vs PostgreSQL: PostgreSQL uses `/` for integer division; the DIV operator is not standard SQL but PostgreSQL does support `div(x,y)` as a function.

SIN

→ DOUBLE

Returns the sine of an angle given in radians.

Signature

SIN(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in radians.

Examples

sin(90°) = 1

sql

SELECT SIN(PI() / 2);

→-- 1

sin(0) = 0

sql

SELECT SIN(0);

→-- 0

Near zero due to floating point

sql

SELECT SIN(PI());

→-- ~1.22e-16

Converting degrees first

sql

SELECT SIN(RADIANS(angle_deg)) FROM measurements;

→-- sine of angle

NULL handling

sql

SELECT SIN(NULL);

→-- NULL

⚠Anti-Pattern— Passing degrees instead of radians

SIN(90) = 0.894... not 1, because 90 radians is not 90 degrees.

✓ Instead: Use SIN(RADIANS(90)) = 1.

⇄ vs PostgreSQL: PostgreSQL `sin()` behaves identically.

See also:COS TAN ASIN RADIANS DEGREES PI

COS

→ DOUBLE

Returns the cosine of an angle given in radians.

Signature

COS(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in radians.

Examples

cos(0) = 1

sql

SELECT COS(0);

→-- 1

cos(180°) = -1

sql

SELECT COS(PI());

→-- -1

Near zero (90 degrees)

sql

SELECT COS(PI() / 2);

→-- ~6.12e-17

With degree conversion

sql

SELECT COS(RADIANS(angle_deg)) FROM measurements;

→-- cosine of angle

NULL handling

sql

SELECT COS(NULL);

→-- NULL

⚠Anti-Pattern— Comparing COS results to exact zero

COS(PI()/2) is not exactly 0 due to floating-point representation.

✓ Instead: Use ABS(COS(x)) < 1e-10 or ROUND(COS(x), 10) = 0 when testing for zero.

⇄ vs PostgreSQL: PostgreSQL `cos()` behaves identically.

See also:SIN TAN ACOS RADIANS DEGREES PI

TAN

→ DOUBLE

Returns the tangent of an angle given in radians.

Signature

TAN(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in radians.

Examples

tan(0) = 0

sql

SELECT TAN(0);

→-- 0

tan(45°) = 1

sql

SELECT TAN(PI() / 4);

→-- 1

Near zero, floating point

sql

SELECT TAN(PI());

→-- ~-1.22e-16

Gradient from angle

sql

SELECT TAN(RADIANS(slope_deg)) FROM terrain;

→-- slope gradient

NULL handling

sql

SELECT TAN(NULL);

→-- NULL

⚠Anti-Pattern— Calling TAN near π/2 without checks

TAN(PI()/2) returns a very large (±1e15+) number, not an error or infinity.

✓ Instead: Validate angle ranges or use ROUND(angle_in_radians / (PI()/2)) % 2 != 1 as a guard.

⇄ vs PostgreSQL: PostgreSQL `tan()` behaves identically.

ASIN

→ DOUBLE

Returns the arc sine (inverse sine) of X in radians, where X is in [-1, 1].

Signature

ASIN(X)

Parameters

Parameter	Type	Description
X	DOUBLE	A value between -1 and 1.

Examples

arcsin(1) = π/2

sql

SELECT ASIN(1);

→-- 1.5707963267949 (π/2)

arcsin(0) = 0

sql

SELECT ASIN(0);

→-- 0

arcsin(-1) = -π/2

sql

SELECT ASIN(-1);

→-- -1.5707963267949

Convert result to degrees

sql

SELECT DEGREES(ASIN(ratio)) FROM measurements;

→-- angle in degrees

NULL handling

sql

SELECT ASIN(NULL);

→-- NULL

⚠Anti-Pattern— Passing out-of-range values to ASIN

ASIN(1.0001) returns NULL due to floating-point arithmetic, which can silently corrupt computations.

✓ Instead: Clamp the input: `ASIN(GREATEST(-1, LEAST(1, x)))`.

⇄ vs PostgreSQL: PostgreSQL `asin()` behaves identically; raises an error for out-of-range input instead of returning NULL.

ACOS

→ DOUBLE

Returns the arc cosine (inverse cosine) of X in radians, where X is in [-1, 1].

Signature

ACOS(X)

Parameters

Parameter	Type	Description
X	DOUBLE	A value between -1 and 1.

Examples

arccos(1) = 0

sql

SELECT ACOS(1);

→-- 0

arccos(0) = π/2

sql

SELECT ACOS(0);

→-- 1.5707963267949 (π/2)

arccos(-1) = π

sql

SELECT ACOS(-1);

→-- 3.1415926535898 (π)

Angle from dot product

sql

SELECT DEGREES(ACOS(dot_product)) FROM vectors;

→-- angle between vectors

NULL handling

sql

SELECT ACOS(NULL);

→-- NULL

⚠Anti-Pattern— Not clamping floating-point dot products

Floating-point arithmetic can yield values like 1.0000000000001 which make ACOS() return NULL.

✓ Instead: Always clamp: `ACOS(GREATEST(-1.0, LEAST(1.0, dot_product)))`.

⇄ vs PostgreSQL: PostgreSQL `acos()` raises an error for out-of-range input rather than returning NULL.

ATAN

→ DOUBLE

Returns the arc tangent of X in radians; with two arguments, behaves like ATAN2(Y, X).

Signatures

ATAN(X)

ATAN(Y, X)

Parameters

Parameter	Type	Description
X	DOUBLE	Tangent value (one-argument form) or the x-coordinate (two-argument form).
Y	DOUBLE	The y-coordinate when using two-argument form.

Examples

arctan(1) = π/4

sql

SELECT ATAN(1);

→-- 0.7853981633974 (π/4)

arctan(0) = 0

sql

SELECT ATAN(0);

→-- 0

Negative input

sql

SELECT ATAN(-1);

→-- -0.7853981633974

Two-arg form on a column

sql

SELECT DEGREES(ATAN(dy, dx)) FROM vectors;

→-- angle in degrees

NULL handling

sql

SELECT ATAN(NULL);

→-- NULL

⚠Anti-Pattern— Using ATAN(Y/X) for angle calculation

ATAN(Y/X) fails when X=0 and loses quadrant information for negative X.

✓ Instead: Always use ATAN2(Y, X) for robust two-argument arc tangent.

⇄ vs PostgreSQL: PostgreSQL `atan(x)` is single argument; use `atan2(y, x)` for the two-argument form.

ATAN2

→ DOUBLE

Returns the arc tangent of Y/X using the signs of both arguments to determine the correct quadrant, in radians.

Signature

ATAN2(Y, X)

Parameters

Parameter	Type	Description
Y	DOUBLE	The y-coordinate (numerator).
X	DOUBLE	The x-coordinate (denominator).

Examples

45 degrees

sql

SELECT ATAN2(1, 1);

→-- 0.7853981633974 (π/4)

135 degrees

sql

SELECT ATAN2(1, -1);

→-- 2.3561944901923 (3π/4)

0 degrees

sql

SELECT ATAN2(0, 1);

→-- 0

Bearing from vector components

sql

SELECT DEGREES(ATAN2(dy, dx)) FROM vectors;

→-- bearing angle

NULL handling

sql

SELECT ATAN2(NULL, 1);

→-- NULL

⚠Anti-Pattern— Using ATAN(Y/X) instead of ATAN2

ATAN(Y/X) is undefined when X=0 and wrong when X<0.

✓ Instead: Always use ATAN2(Y, X) for full-quadrant angle computation.

⇄ vs PostgreSQL: PostgreSQL `atan2(y, x)` behaves identically.

COT

→ DOUBLE

Returns the cotangent of X (in radians), equivalent to 1/TAN(X).

Signature

COT(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in radians; must not be a multiple of π.

Examples

cot(45°) = 1

sql

SELECT COT(PI() / 4);

→-- 1

cot(90°) ≈ 0

sql

SELECT COT(PI() / 2);

→-- ~6.12e-17 (near 0)

cot(1 radian)

sql

SELECT COT(1);

→-- 0.6421

With degree conversion

sql

SELECT COT(RADIANS(angle)) FROM geometry;

→-- cotangent

NULL handling

sql

SELECT COT(NULL);

→-- NULL

⚠Anti-Pattern— Calling COT without checking for multiples of π

COT(PI()) produces a very large number due to SIN(π) being near-zero from floating point.

✓ Instead: Guard: `IF(ABS(SIN(x)) > 1e-10, COT(x), NULL)`.

⇄ vs PostgreSQL: PostgreSQL does not have a COT() function; use `1/tan(x)` or `cos(x)/sin(x)`.

DEGREES

→ DOUBLE

Converts a value from radians to degrees.

Signature

DEGREES(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in radians to convert.

Examples

π radians = 180°

sql

SELECT DEGREES(PI());

→-- 180

π/2 radians = 90°

sql

SELECT DEGREES(PI() / 2);

→-- 90

Zero radians

sql

SELECT DEGREES(0);

→-- 0

On a column

sql

SELECT DEGREES(bearing_rad) FROM navigation;

→-- bearing in degrees

NULL handling

sql

SELECT DEGREES(NULL);

→-- NULL

⚠Anti-Pattern— Storing angles in radians and converting in the app

Converting in application code when the DB can do it is an unnecessary round-trip.

✓ Instead: Use DEGREES() in the SELECT list to present friendly angles directly from the query.

⇄ vs PostgreSQL: PostgreSQL `degrees()` behaves identically.

RADIANS

→ DOUBLE

Converts a value from degrees to radians.

Signature

RADIANS(X)

Parameters

Parameter	Type	Description
X	DOUBLE	Angle in degrees to convert.

Examples

180° = π radians

sql

SELECT RADIANS(180);

→-- 3.1415926535898

90° = π/2 radians

sql

SELECT RADIANS(90);

→-- 1.5707963267949

Zero degrees

sql

SELECT RADIANS(0);

→-- 0

Convert before trig function

sql

SELECT SIN(RADIANS(angle_deg)) FROM measurements;

→-- sine of angle

NULL handling

sql

SELECT RADIANS(NULL);

→-- NULL

⚠Anti-Pattern— Forgetting to convert degrees for trig functions

SIN(45) computes sin of 45 radians (~2578°), not sin of 45 degrees.

✓ Instead: Write SIN(RADIANS(45)) to get the correct result of ~0.7071.

⇄ vs PostgreSQL: PostgreSQL `radians()` behaves identically.

CRC32

→ BIGINT UNSIGNED

Computes a 32-bit cyclic redundancy check (CRC) value for a string and returns it as an unsigned integer.

Signature

CRC32(expr)

Parameters

Parameter	Type	Description
expr	STRING	The string expression to compute CRC32 for.

Examples

CRC32 of a string

sql

SELECT CRC32('hello');

→-- 907060870

Case-sensitive

sql

SELECT CRC32('Hello');

→-- 4157704578

Empty string

sql

SELECT CRC32('');

→-- 0

Fingerprinting a column

sql

SELECT CRC32(email) FROM users;

→-- hash per row

NULL handling

sql

SELECT CRC32(NULL);

→-- NULL

⚠Anti-Pattern— Using CRC32 for unique identifiers

CRC32 has a 32-bit output space (~4 billion values) and will produce collisions for large datasets.

✓ Instead: Use MD5() or SHA2() for checksum deduplication, or UUID() for unique IDs.

⇄ vs PostgreSQL: PostgreSQL has no built-in CRC32(); use the pgcrypto extension or application code.

CONV

→ VARCHAR

Converts a number from one numeric base to another and returns the result as a string.

Signature

CONV(N, from_base, to_base)

Parameters

Parameter	Type	Description
N	STRING or BIGINT	The number to convert, as a string or integer.
from_base	INT	The base of the input number (2–36).
to_base	INT	The target base (2–36); negative value returns signed representation.

Examples

Hex to decimal

sql

SELECT CONV('ff', 16, 10);

→-- 255

Decimal to hex

sql

SELECT CONV(255, 10, 16);

→-- FF

Decimal to binary

sql

SELECT CONV(10, 10, 2);

→-- 1010

On a column

sql

SELECT CONV(hex_col, 16, 10) FROM data;

→-- decimal value

NULL handling

sql

SELECT CONV(NULL, 16, 10);

→-- NULL

⚠Anti-Pattern— Using CONV for large numbers without UNSIGNED handling

CONV with a negative to_base returns a signed representation, which may confuse downstream parsing.

✓ Instead: Use a positive to_base unless you specifically need signed output, and document the behavior clearly.

⇄ vs PostgreSQL: PostgreSQL has no CONV(); use `to_hex()` for decimal-to-hex, or format strings with `%o`, `%b` in application code.

BIT_COUNT

→ BIGINT

Returns the number of bits set to 1 in the binary representation of the argument (popcount / Hamming weight).

Signature

BIT_COUNT(N)

Parameters

Parameter	Type	Description
N	BIGINT UNSIGNED	An integer value whose set bits are counted.

Examples

7 = 0b111, three 1-bits

sql

SELECT BIT_COUNT(7);

→-- 3

255 = 0xFF, eight 1-bits

sql

SELECT BIT_COUNT(255);

→-- 8

Zero has no set bits

sql

SELECT BIT_COUNT(0);

→-- 0

Bitmask popcount

sql

SELECT BIT_COUNT(permissions) FROM users;

→-- number of granted permissions

NULL handling

sql

SELECT BIT_COUNT(NULL);

→-- NULL

⚠Anti-Pattern— Using BIT_COUNT on signed integers with large values

Negative signed integers have their sign bit set, inflating the popcount unexpectedly.

✓ Instead: Cast to UNSIGNED before calling BIT_COUNT: `BIT_COUNT(CAST(col AS UNSIGNED))`.

⇄ vs PostgreSQL: PostgreSQL 14+ has `bit_count()`; earlier versions require a custom function.

FORMAT

→ VARCHAR

Formats a number with grouped thousands separators and a specified number of decimal places, returning a locale-formatted string.

Signatures

FORMAT(X, D)

FORMAT(X, D, locale)

Parameters

Parameter	Type	Description
X	NUMERIC	The number to format.
D	INT	Number of decimal places in the output.
locale	STRING	Optional locale string (e.g., 'de_DE') controlling decimal and thousands separator characters.

Examples

Default locale (en_US)

sql

SELECT FORMAT(1234567.891, 2);

→-- '1,234,567.89'

German locale

sql

SELECT FORMAT(1234567.891, 2, 'de_DE');

→-- '1.234.567,89'

Zero input

sql

SELECT FORMAT(0, 2);

→-- '0.00'

On a column

sql

SELECT FORMAT(price, 2) FROM products;

→-- formatted price string

NULL handling

sql

SELECT FORMAT(NULL, 2);

→-- NULL

⚠Anti-Pattern— Sorting or summing FORMAT() results

Aggregating or ordering FORMAT()'s VARCHAR output (e.g., '1,234') sorts lexicographically, not numerically.

✓ Instead: Apply FORMAT() only in the final SELECT list after all filtering, sorting, and aggregation is done.

◆MariaDB Note

The locale argument was added in MariaDB 10.0.5; older versions support only two arguments.

⇄ vs MySQL: MySQL 5.6+ also supports the optional locale argument; behavior is identical.

⇄ vs PostgreSQL: PostgreSQL uses `to_char(x, 'FM999,999,999.00')` for locale-aware numeric formatting.

OCT

→ VARCHAR

Returns the octal (base-8) string representation of a decimal integer N.

Signature

OCT(N)

Parameters

Parameter	Type	Description
N	BIGINT	An integer value to represent in octal.

Examples

8 in base 8 is 10

sql

SELECT OCT(8);

→-- '10'

255 in octal

sql

SELECT OCT(255);

→-- '377'

Zero

sql

SELECT OCT(0);

→-- '0'

File permissions as octal

sql

SELECT OCT(permission_bits) FROM files;

→-- Unix-style permission string

NULL handling

sql

SELECT OCT(NULL);

→-- NULL

⚠Anti-Pattern— Using OCT for arithmetic

OCT returns a VARCHAR string — '10' is not the integer 8 and cannot be used directly in arithmetic.

✓ Instead: Use CONV() or bitwise operations for base-conversion math; use OCT() only for display.

⇄ vs PostgreSQL: PostgreSQL has no OCT() function; use `to_hex()` for hex or format the number with application code.

HEX

→ VARCHAR

Returns the hexadecimal string representation of a decimal integer or the hex encoding of a string value.

Signature

HEX(N_or_S)

Parameters

Parameter	Type	Description
N_or_S	NUMERIC or STRING	An integer to convert to hex, or a string to encode each byte as two hex digits.

Examples

Integer to hex

sql

SELECT HEX(255);

→-- 'FF'

Zero

sql

SELECT HEX(0);

→-- '0'

String encoding

sql

SELECT HEX('ABC');

→-- '414243'

On an integer column

sql

SELECT HEX(id) FROM items;

→-- hex ID

NULL handling

sql

SELECT HEX(NULL);

→-- NULL

⚠Anti-Pattern— Comparing HEX output with lowercase hex strings

HEX() always returns uppercase 'FF', not 'ff' — string comparisons are case-sensitive in binary collations.

✓ Instead: Use LOWER(HEX(x)) or compare with UPPER() to normalize case when matching hex strings.

⇄ vs PostgreSQL: PostgreSQL uses `to_hex(n)` for integer-to-hex; there is no single HEX() that also handles strings.

INET_ATON

→ BIGINT UNSIGNED

Converts a dotted-quad IPv4 address string to its numeric (32-bit unsigned integer) representation.

Signature

INET_ATON(expr)

Parameters

Parameter	Type	Description
expr	STRING	A dotted-quad IPv4 address string, e.g. '192.168.1.1'.

Examples

Standard IPv4

sql

SELECT INET_ATON('192.168.1.1');

→-- 3232235777

Loopback address

sql

SELECT INET_ATON('127.0.0.1');

→-- 2130706433

Zero address

sql

SELECT INET_ATON('0.0.0.0');

→-- 0

On a column

sql

SELECT INET_ATON(ip_address) FROM connections;

→-- numeric IP

NULL handling

sql

SELECT INET_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Storing IPs as VARCHAR and filtering with LIKE

LIKE '192.168.%' performs a string scan and is both slow and error-prone for IP ranges.

✓ Instead: Store numeric representation and use BETWEEN for CIDR range queries.

⇄ vs PostgreSQL: PostgreSQL uses the `inet` type and operators instead of integer encoding; `host(inet_col)` retrieves string form.

INET_NTOA

→ VARCHAR

Converts a 32-bit unsigned integer to its dotted-quad IPv4 address string representation.

Signature

INET_NTOA(expr)

Parameters

Parameter	Type	Description
expr	BIGINT UNSIGNED	A numeric IPv4 address.

Examples

Numeric to dotted-quad

sql

SELECT INET_NTOA(3232235777);

→-- '192.168.1.1'

Loopback

sql

SELECT INET_NTOA(2130706433);

→-- '127.0.0.1'

Zero address

sql

SELECT INET_NTOA(0);

→-- '0.0.0.0'

On a column

sql

SELECT INET_NTOA(numeric_ip) FROM connections;

→-- dotted-quad IP

NULL handling

sql

SELECT INET_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Using INET_NTOA for IPv6 addresses

INET_NTOA only handles 32-bit IPv4; passing an IPv6-derived integer returns garbage or NULL.

✓ Instead: Use INET6_NTOA() for IPv6 or dual-stack addresses.

⇄ vs PostgreSQL: PostgreSQL uses the `inet` type and `host()` function rather than integer conversion functions.

INET6_ATON

→ VARBINARY(16)

Converts an IPv4 or IPv6 address string to its binary representation (4 or 16 bytes).

Signature

INET6_ATON(expr)

Parameters

Parameter	Type	Description
expr	STRING	A dotted-quad IPv4 or colon-notation IPv6 address string.

Examples

IPv6 loopback as hex

sql

SELECT HEX(INET6_ATON('::1'));

→-- '00000000000000000000000000000001'

IPv4 as 4-byte binary

sql

SELECT HEX(INET6_ATON('192.168.1.1'));

→-- 'C0A80101'

IPv6 is 16 bytes

sql

SELECT LENGTH(INET6_ATON('::1'));

→-- 16

On a column

sql

SELECT INET6_ATON(ip_address) FROM connections;

→-- binary IP

NULL handling

sql

SELECT INET6_ATON(NULL);

→-- NULL

⚠Anti-Pattern— Storing IPv6 as VARCHAR(39)

Storing IPv6 as a string wastes space and prevents efficient range queries; string form also has multiple valid representations.

✓ Instead: Use VARBINARY(16) with INET6_ATON/INET6_NTOA to canonicalize and compactly store addresses.

◆MariaDB Note

INET6_ATON was added in MariaDB 10.0.12.

⇄ vs PostgreSQL: PostgreSQL uses the native `inet` type which handles both IPv4 and IPv6 natively; no binary-packing function needed.

INET6_NTOA

→ VARCHAR

Converts a binary IPv4 or IPv6 address (as returned by INET6_ATON) back to its human-readable string representation.

Signature

INET6_NTOA(expr)

Parameters

Parameter	Type	Description
expr	VARBINARY	A 4-byte or 16-byte binary IP address.

Examples

Round-trip IPv6 loopback

sql

SELECT INET6_NTOA(INET6_ATON('::1'));

→-- '::1'

Round-trip IPv4

sql

SELECT INET6_NTOA(INET6_ATON('192.168.1.1'));

→-- '192.168.1.1'

From raw hex bytes

sql

SELECT INET6_NTOA(UNHEX('C0A80101'));

→-- '192.168.1.1'

On a column

sql

SELECT INET6_NTOA(ip_bin) FROM connections;

→-- readable IP string

NULL handling

sql

SELECT INET6_NTOA(NULL);

→-- NULL

⚠Anti-Pattern— Using INET6_NTOA with INET_ATON output

INET_ATON returns a BIGINT, not binary; passing it to INET6_NTOA gives wrong results.

✓ Instead: Use INET6_ATON for storing and INET6_NTOA for retrieving when handling both IPv4 and IPv6 uniformly.

◆MariaDB Note

INET6_NTOA was added in MariaDB 10.0.12.

⇄ vs PostgreSQL: PostgreSQL uses the `inet` type and `host()` function; no separate binary-form function is needed.

Related MariaDB Categories

Aggregate Functions →String Functions →