MySQL Numeric Functions

MySQL provides a set of built-in numeric functions for performing mathematical operations on column values. These functions can be used in SELECT lists, WHERE clauses, or anywhere an expression is valid.

---

ROUND

Round a number to a specified number of decimal places. If the decimal count is omitted it defaults to 0 (rounds to the nearest integer):

SELECT ROUND(4.567, 2);   -- 4.57
SELECT ROUND(4.567, 1);   -- 4.6
SELECT ROUND(4.567);      -- 5
SELECT ROUND(4.5);        -- 5
SELECT ROUND(-4.5);       -- -5

---

TRUNCATE

TRUNCATE cuts a number to a specified number of decimal places without rounding:

SELECT TRUNCATE(4.567, 2);   -- 4.56  (not rounded — just cut)
SELECT TRUNCATE(4.999, 0);   -- 4     (not 5)
SELECT TRUNCATE(1234, -2);   -- 1200  (negative places truncate integer digits)

---

CEIL and FLOOR

CEIL (or CEILING) returns the smallest integer greater than or equal to the value. FLOOR returns the largest integer less than or equal to the value:

SELECT CEIL(4.1);    -- 5
SELECT CEIL(4.9);    -- 5
SELECT CEIL(-4.1);   -- -4

SELECT FLOOR(4.9);   -- 4
SELECT FLOOR(4.1);   -- 4
SELECT FLOOR(-4.1);  -- -5

---

ABS

Return the absolute (non-negative) value:

SELECT ABS(-150);   -- 150
SELECT ABS(150);    -- 150

---

MOD

Return the remainder after integer division. The % operator is equivalent:

SELECT MOD(10, 3);   -- 1
SELECT 10 % 3;       -- 1
SELECT MOD(15, 5);   -- 0  (evenly divisible)

---

POWER and SQRT

POWER (or POW) raises a number to an exponent. SQRT returns the square root:

SELECT POWER(2, 10);   -- 1024
SELECT POW(3, 3);      -- 27
SELECT SQRT(144);      -- 12
SELECT SQRT(2);        -- 1.4142135623731

---

RAND

RAND() returns a random floating-point value between 0 (inclusive) and 1 (exclusive). To get a random integer in a range, use the formula:

SELECT RAND();                            -- e.g. 0.7341938...
SELECT FLOOR(RAND() * 100) + 1;          -- random integer 1–100

-- Select 5 random rows from a table
SELECT * FROM products ORDER BY RAND() LIMIT 5;

---

SIGN

Returns -1 for negative numbers, 0 for zero, and 1 for positive numbers:

SELECT SIGN(-50);   -- -1
SELECT SIGN(0);     --  0
SELECT SIGN(200);   --  1

---

Quick Reference

Function	Description
ROUND(n, d)	Round to d decimal places
TRUNCATE(n, d)	Cut to d decimal places without rounding
CEIL(n)	Smallest integer ≥ n
FLOOR(n)	Largest integer ≤ n
ABS(n)	Absolute value
MOD(n, d)	Remainder of n ÷ d
POWER(n, exp)	n raised to exp
SQRT(n)	Square root of n
RAND()	Random float between 0 and 1
SIGN(n)	-1, 0, or 1 based on sign of n