🔢

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

MariaDB 5.5+→ 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.

WHERE ABS(col - 100) < 10 cannot use an index on col because the function wraps the column. Rewrite as WHERE col BETWEEN 90 AND 110 to allow a range-index scan. Reserve ABS() for SELECT expressions where index use is not needed.

example

SELECT * FROM measurements WHERE value BETWEEN ABS(target) - 0.5 AND ABS(target) + 0.5;

→-- index-friendly tolerance query

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

CEIL

MariaDB 5.5+→ 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))`.

CEIL(total_rows / page_size) gives the total number of pages without floating-point rounding errors. This is more readable and correct than FLOOR((total + page_size - 1) / page_size).

example

SELECT CEIL(COUNT(*) / 25.0) AS total_pages FROM products;

→-- 5 (for 101-125 rows with page size 25)

◆MariaDB Note

Standard SQL function. Returns BIGINT for integer arguments.

⇄ vs MySQL: Identical to MySQL.

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

CEILING

MariaDB 5.5+→ 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.

CEILING and CEIL are 100% interchangeable synonyms in MariaDB. Pick one convention for your project and enforce it in code review. CEIL is shorter; CEILING matches the SQL-standard name.

example

SELECT CEILING(7.1);

→-- 8

◆MariaDB Note

Synonym for CEIL(). Standard SQL function.

⇄ vs MySQL: Identical to MySQL.

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

FLOOR

MariaDB 5.5+→ 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.

FLOOR(UNIX_TIMESTAMP() / 3600) * 3600 truncates the current time to the start of the current hour as a Unix integer. This pattern generalizes to any bucket width (minute=60, day=86400) and is efficient for time-series GROUP BY buckets.

example

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(ts) / 3600) * 3600) AS hour_bucket FROM events;

→-- datetime truncated to current hour

◆MariaDB Note

Standard SQL function. Returns BIGINT for integer arguments.

⇄ vs MySQL: Identical to MySQL.

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

ROUND

MariaDB 5.5+→ 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.

ROUND(1234567, -3) returns 1235000, rounding to the nearest thousand. This is cleaner than dividing, rounding, and multiplying. Use it for rounding large financial figures to significant digits in summary reports.

example

SELECT ROUND(revenue, -3) AS revenue_k FROM summary;

→-- 1235000 for revenue = 1234567

◆MariaDB Note

Standard SQL function. Uses banker's rounding (round-half-away-from-zero) by default.

⇄ vs MySQL: Identical to MySQL.

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

TRUNCATE

MariaDB 5.5+→ 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.

TRUNCATE(9.999, 2) returns 9.99, never rounding up. This is the correct behavior for price-floor calculations and computing 'whole units' from fractional quantities without any rounding bias.

example

SELECT TRUNCATE(price * 0.9, 2) AS discounted_price FROM products;

→-- 10% discount truncated to 2 decimal places, never rounded up

◆MariaDB Note

Standard SQL function. Both arguments are required (unlike PostgreSQL trunc).

⇄ vs MySQL: Identical to MySQL.

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

MOD

MariaDB 5.5+→ 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.

MOD(id, N) = 0 selects every Nth row — the most common pattern for rate-limiting bulk jobs without cursors. MOD also works with decimals (unlike % in some languages), returning the exact remainder.

example

UPDATE jobs SET assigned_worker = MOD(id, 4) WHERE status = 'pending';

→-- distributes jobs across 4 workers by ID

◆MariaDB Note

Standard SQL function. Also available as the % and MOD operators.

⇄ vs MySQL: Identical to MySQL.

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

POWER

MariaDB 5.5+→ 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)`.

POWER(base, exp) is the standard function for compound interest: principal * POWER(1 + rate, years). Unlike application-layer math, running this in SQL avoids a full table fetch for bulk calculations.

example

SELECT principal * POWER(1 + rate, years) AS future_value FROM investments;

→-- compound future value in one SQL expression

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

POW

MariaDB 5.5+→ 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.

POW and POWER are identical in MariaDB. PostgreSQL also supports POWER but not POW. Use POWER in shared SQL that may run on multiple databases.

example

SELECT POW(2, 10);

→-- 1024

◆MariaDB Note

Alias for POWER(). Standard SQL function.

⇄ vs MySQL: Identical to MySQL.

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

SQRT

MariaDB 5.5+→ 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.

SQRT(POWER(x2-x1, 2) + POWER(y2-y1, 2)) computes the Euclidean distance between two 2D points. For approximate geospatial queries (small areas), this is simpler than the Haversine formula.

example

SELECT SQRT(POWER(x - 50, 2) + POWER(y - 50, 2)) AS distance FROM points ORDER BY distance;

→-- nearest points to coordinate (50, 50)

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

EXP

MariaDB 5.5+→ 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.

EXP(x) = e^x. Use it to implement the sigmoid function directly in SQL: 1 / (1 + EXP(-score)) converts a linear score to a 0-1 probability, enabling in-database logistic regression inference.

example

SELECT 1 / (1 + EXP(-score)) AS probability FROM predictions;

→-- sigmoid-transformed probability between 0 and 1

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LOG

MariaDB 5.5+→ 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)`.

LOG(base, x) computes the logarithm of x in the specified base. LOG(2, x) gives bits of information; LOG(10, x) gives orders of magnitude. The single-argument form LOG(x) computes the natural logarithm (base e).

example

SELECT LOG(2, file_size) AS bits_needed FROM files;

→-- number of bits required to address file size

◆MariaDB Note

Two-argument form LOG(base, x) is a MySQL/MariaDB extension to standard SQL.

⇄ vs MySQL: Identical to MySQL. Two-argument LOG(base, x) is a MariaDB/MySQL extension.

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

LOG2

MariaDB 5.5+→ 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.

LOG2(n) returns the base-2 logarithm, telling you how many doublings reach n. CEIL(LOG2(n)) gives the minimum number of bits to represent n values — useful for capacity planning and hash bucket sizing.

example

SELECT CEIL(LOG2(user_count)) AS min_bits FROM stats;

→-- minimum bit-width to uniquely identify all users

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LOG10

MariaDB 5.5+→ 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.

LOG10(value / reference) gives the order-of-magnitude difference between two values, similar to a dB scale. Use it for response-time ratio comparisons, revenue variance analysis, or any metric where relative magnitude matters more than absolute difference.

example

SELECT LOG10(current_sales / previous_sales) AS growth_order FROM metrics;

→-- 1.0 means 10× growth, 0.3 means ~2× growth

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

LN

MariaDB 5.5+→ 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.

LN(x) is the ISO SQL-standard form of LOG(x). Use LN in portable SQL that must work on PostgreSQL or other ANSI-compliant databases. In MariaDB both LN and LOG(x) are identical.

example

SELECT LN(probability / (1 - probability)) AS log_odds FROM model_outputs;

→-- logit transformation of probability

◆MariaDB Note

SQL-standard form of the natural logarithm. Equivalent to LOG(x).

⇄ vs MySQL: Identical to MySQL.

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

PI

MariaDB 5.5+→ 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.

PI() returns the constant π to 15 decimal places. Use it for circular area calculations, converting degrees to radians, and simple geographic distance approximations. More precise than hardcoding 3.14159.

example

SELECT radius * radius * PI() AS circle_area FROM circles;

→-- precise circular area

◆MariaDB Note

Returns π to 15 significant digits of precision.

⇄ vs MySQL: Identical to MySQL.

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

RAND

MariaDB 5.5+→ 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.

ORDER BY RAND() produces a uniformly random shuffle. For large tables, this is expensive (full table sort). For random sampling of large tables, use the WHERE id > RAND() * MAX(id) LIMIT n trick or reservoir sampling in application code.

example

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

→-- 5 random products

◆MariaDB Note

Standard SQL function. RAND(seed) produces a repeatable sequence for testing.

⇄ vs MySQL: Identical to MySQL.

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

SIGN

MariaDB 5.5+→ 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`.

SIGN(x) returns -1, 0, or 1 — use it to normalize a value to its direction without a CASE expression. Multiply by ABS() to reconstruct the signed value: SIGN(x) * ABS(x) = x.

example

SELECT amount, SIGN(amount) AS direction FROM transactions;

→-- 1 for credits, -1 for debits, 0 for zeros

◆MariaDB Note

Standard SQL function, available in all MariaDB versions.

⇄ vs MySQL: Identical to MySQL.

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

GREATEST

MariaDB 5.5+→ 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))`.

GREATEST(a, b, c) returns the maximum of its arguments in one expression, replacing a chain of nested CASE WHEN a > b AND a > c THEN a ... END. Returns NULL if any argument is NULL.

example

SELECT GREATEST(q1, q2, q3, q4) AS best_quarter FROM performance;

→-- highest value among the four quarters

◆MariaDB Note

Returns NULL if any argument is NULL. Supports all comparable data types.

⇄ vs MySQL: Identical to MySQL.

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

LEAST

MariaDB 5.5+→ 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.

LEAST(a, b, c) returns the minimum of its arguments. Use it for capping values: GREATEST(0, LEAST(100, score)) clamps a score to [0, 100] cleanly. Returns NULL if any argument is NULL — wrap with COALESCE if needed.

example

SELECT GREATEST(0, LEAST(100, raw_score)) AS clamped_score FROM results;

→-- score clamped to 0-100 range

◆MariaDB Note

Returns NULL if any argument is NULL. Supports all comparable data types.

⇄ vs MySQL: Identical to MySQL.

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

DIV

MariaDB 5.5+→ 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.

a DIV b returns the integer quotient, truncating toward zero. It's equivalent to FLOOR(a/b) for positive numbers but handles negatives differently (truncates toward zero, not toward negative infinity). Use it for batch-size calculations and pagination.

example

SELECT total_items DIV items_per_page AS page_count FROM carts;

→-- integer number of full pages

◆MariaDB Note

Integer division operator. Equivalent to FLOOR(a/b) for non-negative operands.

⇄ vs MySQL: Identical to MySQL.

⇄ 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

MariaDB 5.5+→ 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.

SIN operates in radians, not degrees. Always convert with RADIANS(degrees) before calling SIN. Use SIN/COS together with the Haversine formula for approximate geographic distance calculations on a sphere.

example

SELECT SIN(RADIANS(angle_degrees)) AS sine_value FROM measurements;

→-- sine of angle given in degrees

◆MariaDB Note

Standard SQL function. Argument is in radians.

⇄ vs MySQL: Identical to MySQL.

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

See also:COS TAN ASIN RADIANS DEGREES PI

COS

MariaDB 5.5+→ 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.

COS operates in radians. In the Haversine formula for geographic distance: a = POWER(SIN(RADIANS(dlat)/2),2) + COS(lat1)*COS(lat2)*POWER(SIN(RADIANS(dlon)/2),2). Convert degrees to radians first.

example

SELECT COS(RADIANS(angle_degrees)) AS cosine_value FROM geometry;

→-- cosine of angle given in degrees

◆MariaDB Note

Standard SQL function. Argument is in radians.

⇄ vs MySQL: Identical to MySQL.

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

See also:SIN TAN ACOS RADIANS DEGREES PI

TAN

MariaDB 5.5+→ 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.

TAN operates in radians. TAN(RADIANS(angle)) gives the slope (rise/run). ATAN2(y, x) is the inverse — use it to convert Cartesian coordinates back to angles. Avoid TAN(PI()/2) as the result approaches infinity.

example

SELECT TAN(RADIANS(slope_degrees)) AS slope_ratio FROM terrain;

→-- rise-over-run ratio for each slope

◆MariaDB Note

Standard SQL function. Argument is in radians.

⇄ vs MySQL: Identical to MySQL.

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

ASIN

MariaDB 5.5+→ 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)))`.

ASIN(x) returns the arc sine in radians for x in [-1, 1]. Returns NULL outside this range. Use DEGREES(ASIN(x)) to convert the result to degrees. Part of the standard trig toolkit for geometric computations.

example

SELECT DEGREES(ASIN(opposite / hypotenuse)) AS angle FROM triangles;

→-- angle in degrees from opposite/hypotenuse ratio

◆MariaDB Note

Standard SQL function. Returns NULL for input outside [-1, 1].

⇄ vs MySQL: Identical to MySQL.

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

ACOS

MariaDB 5.5+→ 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)))`.

ACOS(x) returns the arc cosine in radians for x in [-1, 1]. Use DEGREES(ACOS(dot_product)) to compute the angle between two unit vectors — a common operation in recommendation similarity scoring.

example

SELECT DEGREES(ACOS(LEAST(1, similarity))) AS angle_deg FROM vectors;

→-- angle between vectors in degrees

◆MariaDB Note

Standard SQL function. Returns NULL for input outside [-1, 1].

⇄ vs MySQL: Identical to MySQL.

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

ATAN

MariaDB 5.5+→ 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.

ATAN(y/x) fails when x=0 and gives the wrong quadrant for negative x. ATAN2(y, x) handles all quadrants correctly and is safe when x=0. Use ATAN2 for all angle-from-coordinates conversions.

example

SELECT DEGREES(ATAN2(delta_y, delta_x)) AS bearing FROM vectors;

→-- bearing in degrees, -180 to 180

◆MariaDB Note

Standard SQL function. Single-argument form returns arc tangent in radians.

⇄ vs MySQL: Identical to MySQL.

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

ATAN2

MariaDB 5.5+→ 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.

ATAN2(y, x) returns the angle in radians between the positive x-axis and the point (x, y), ranging from -π to π. It correctly handles all four quadrants and x=0. Essential for Haversine geocoding and compass bearing calculations.

example

SELECT DEGREES(ATAN2(SIN(RADIANS(lon2-lon1))*COS(RADIANS(lat2)), COS(RADIANS(lat1))*SIN(RADIANS(lat2)) - SIN(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1)))) AS bearing FROM routes;

→-- compass bearing from point 1 to point 2

◆MariaDB Note

Standard SQL function. Returns angle in radians in the range [-π, π].

⇄ vs MySQL: Identical to MySQL.

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

COT

MariaDB 5.5+→ 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)`.

COT(x) = COS(x)/SIN(x). Returns NULL (or an error) when x = 0 since the cotangent is undefined at multiples of π. Less commonly used than SIN/COS/TAN but needed for certain trigonometric identities in scientific data processing.

example

SELECT COT(RADIANS(45)) AS cot_45;

→-- 1.0 (cotangent of 45 degrees)

◆MariaDB Note

Standard SQL function. Returns NULL (or error) when argument is zero.

⇄ vs MySQL: Identical to MySQL.

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

DEGREES

MariaDB 5.5+→ 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.

All MariaDB trig functions work in radians. Wrap DEGREES() around any ASIN/ACOS/ATAN/ATAN2 result to convert to the degrees your application or users expect. DEGREES(PI()) = 180 as a quick sanity check.

example

SELECT DEGREES(ATAN2(y, x)) AS angle_degrees FROM vectors;

→-- angle in degrees between -180 and 180

◆MariaDB Note

Standard SQL function. Converts radians to degrees.

⇄ vs MySQL: Identical to MySQL.

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

RADIANS

MariaDB 5.5+→ 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.

Every MariaDB trig function (SIN, COS, TAN, ASIN, ACOS, ATAN) expects radians. If your data stores angles in degrees, apply RADIANS() at the point of use. A forgotten RADIANS() conversion produces silently wrong results.

example

SELECT SIN(RADIANS(latitude)) FROM locations;

→-- correct sine of latitude (not garbage from treating degrees as radians)

◆MariaDB Note

Standard SQL function. Converts degrees to radians.

⇄ vs MySQL: Identical to MySQL.

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

CRC32

MariaDB 5.5+→ 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.

CRC32 produces a 32-bit checksum in microseconds — ideal for detecting whether a row's content has changed since last sync (etag-style). It has a high collision rate and is not secure. For security, use SHA2(256) instead.

example

SELECT id, CRC32(CONCAT_WS('|', col1, col2, col3)) AS row_hash FROM mytable;

→-- fast row fingerprint for change detection

◆MariaDB Note

MySQL/MariaDB-specific function. Uses CRC-32 algorithm for 32-bit checksums.

⇄ vs MySQL: Identical to MySQL.

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

CONV

MariaDB 5.5+→ 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.

CONV(N, from_base, to_base) converts integer N from one base to another. CONV(255, 10, 16) = 'FF'. CONV('FF', 16, 10) = '255'. The result is always a string. Bases 2-36 are supported.

example

SELECT CONV(file_permissions, 10, 8) AS octal_perms FROM files;

→-- '755' for permission integer 493

◆MariaDB Note

MySQL/MariaDB-specific function. Supports bases 2-36.

⇄ vs MySQL: Identical to MySQL.

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

BIT_COUNT

MariaDB 5.5+→ 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))`.

BIT_COUNT(n) returns the number of 1-bits in the binary representation of n (the Hamming weight or popcount). Use it to count how many permissions are set in a bitmask column, or to find rows with exactly k features enabled.

example

SELECT user_id, BIT_COUNT(permissions) AS permission_count FROM users ORDER BY permission_count DESC;

→-- users sorted by number of permissions granted

◆MariaDB Note

MySQL/MariaDB-specific function. Equivalent to POPCOUNT in hardware terminology.

⇄ vs MySQL: Identical to MySQL.

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

FORMAT

MariaDB 5.5+; locale argument added in MariaDB 10.0→ 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.

FORMAT(1234567.891, 2, 'de_DE') returns '1.234.567,89' in German locale. The locale argument (MariaDB 10.0+) lets you produce region-appropriate number formatting. The output is always a string — never use it in further numeric calculations.

example

SELECT FORMAT(salary, 2, 'en_US') AS salary_display FROM employees;

→-- '75,000.00'

◆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

MariaDB 5.5+→ 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.

OCT(n) is equivalent to CONV(n, 10, 8). It's the cleanest way to display Unix file permissions in the familiar octal notation. OCT(493) = '755', OCT(420) = '644'.

example

SELECT OCT(file_mode) AS octal_mode FROM filesystem;

→-- '755' for file_mode = 493

◆MariaDB Note

MySQL/MariaDB-specific function. Equivalent to CONV(n, 10, 8).

⇄ vs MySQL: Identical to MySQL.

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

HEX

MariaDB 5.5+→ 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.

HEX(255) returns 'FF'. This is useful for displaying integer IDs in hex, formatting memory addresses, or converting IP address integers. For string encoding, HEX encodes each byte of the string.

example

SELECT HEX(255), HEX(65536);

→-- 'FF', '10000'

◆MariaDB Note

MySQL/MariaDB-specific function when used with numeric input.

⇄ vs MySQL: Identical to MySQL.

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

INET_ATON

MariaDB 5.5+→ 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.

INET_ATON converts a dotted-quad IPv4 address to a 32-bit integer. Storing IPs as INT UNSIGNED (4 bytes) instead of VARCHAR(15) saves space and enables fast range queries: WHERE ip_int BETWEEN INET_ATON('192.168.1.0') AND INET_ATON('192.168.1.255').

example

SELECT INET_ATON('192.168.1.1');

→-- 3232235777

◆MariaDB Note

MySQL/MariaDB-specific function for IPv4. Returns BIGINT UNSIGNED.

⇄ vs MySQL: Identical to MySQL.

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

INET_NTOA

MariaDB 5.5+→ 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.

INET_NTOA is the inverse of INET_ATON. Use it in SELECT queries to display human-readable IP addresses from integer columns without needing application-layer conversion.

example

SELECT INET_NTOA(ip_address) AS readable_ip FROM connections;

→-- '192.168.1.1'

◆MariaDB Note

MySQL/MariaDB-specific function for IPv4. Input must be a valid 32-bit integer.

⇄ vs MySQL: Identical to MySQL.

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

INET6_ATON

MariaDB 10.0.5+→ 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.

INET6_ATON converts an IPv6 address string to a 16-byte binary value. Storing as VARBINARY(16) is 4x more compact than VARCHAR(39) and enables byte-range queries on subnets. INET6_ATON also handles IPv4-mapped IPv6 addresses.

example

INSERT INTO connections (ip) VALUES (INET6_ATON('2001:db8::1'));

→-- 16-byte binary stored in VARBINARY(16)

◆MariaDB Note

INET6_ATON was added in MariaDB 10.0.12.

⇄ vs MySQL: Identical to MySQL 5.6+.

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

INET6_NTOA

MariaDB 10.0.5+→ 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.

INET6_NTOA reverses INET6_ATON, returning the canonical compressed IPv6 representation (e.g., '2001:db8::1' not '2001:0db8:0000:...'). It also handles IPv4-mapped addresses, returning dotted-quad notation for those.

example

SELECT INET6_NTOA(ip_binary) AS ip_display FROM connections;

→-- '2001:db8::1'

◆MariaDB Note

INET6_NTOA was added in MariaDB 10.0.12.

⇄ vs MySQL: Identical to MySQL 5.6+.

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

Related MariaDB Categories

Aggregate Functions →String Functions →