MySQL - Numeric Functions

MySQL Numeric Functions: A Practical Guide

MySQL provides a rich set of built-in functions for performing mathematical operations and manipulating numeric data. These functions are essential for everything from simple calculations in SELECT statements to complex data analysis.

Below is a categorized list of the most common and useful numeric functions, complete with syntax and a practical example for each.

Rounding and Truncating Functions

ROUND(X, [D])

Rounds the number X to D decimal places. If D is omitted, it rounds to the nearest integer.

SELECT ROUND(123.456, 2) AS round_2_decimals, ROUND(123.456) AS round_to_integer;
-- Result: 123.46, 123

CEIL(X) or CEILING(X)

Returns the smallest integer value that is not less than X (rounds up).

SELECT CEIL(45.1) AS ceil_up, CEIL(-45.9) AS ceil_negative;
-- Result: 46, -45

FLOOR(X)

Returns the largest integer value that is not greater than X (rounds down).

SELECT FLOOR(45.9) AS floor_down, FLOOR(-45.1) AS floor_negative;
-- Result: 45, -46

TRUNCATE(X, D)

Truncates the number X to D decimal places without rounding.

SELECT TRUNCATE(123.456, 2) AS truncated, TRUNCATE(123.999, 0) AS truncated_zero;
-- Result: 123.45, 123

Arithmetic and Mathematical Functions

ABS(X)

Returns the absolute (non-negative) value of X.

SELECT ABS(-25.5) AS absolute_val;
-- Result: 25.5

MOD(N, M) or N % M

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

SELECT MOD(10, 3) AS modulo_val;
-- Result: 1

POW(X, Y) or POWER(X, Y)

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

SELECT POW(2, 8) AS power_of_2;
-- Result: 256

SQRT(X)

Returns the non-negative square root of X.

SELECT SQRT(64) AS square_root;
-- Result: 8

RAND()

Returns a random floating-point value between 0 (inclusive) and 1 (exclusive). To get a random integer in a range, use FLOOR(RAND() * (max - min + 1)) + min.

SELECT RAND() AS random_float, FLOOR(RAND() * 100) + 1 AS random_int_1_to_100;
-- Result: (a random float like 0.12345), (a random integer like 42)

SIGN(X)

Returns the sign of X as -1 (negative), 0 (zero), or 1 (positive).

SELECT SIGN(-50) AS sign_neg, SIGN(100) AS sign_pos;
-- Result: -1, 1

Trigonometric Functions

MySQL also supports standard trigonometric functions, which operate on values in radians.

• SIN(X): Returns the sine of X.
• COS(X): Returns the cosine of X.
• TAN(X): Returns the tangent of X.
• ACOS(X), ASIN(X), ATAN(X): Arc cosine, sine, and tangent functions.
• DEGREES(X): Converts radians to degrees.
• RADIANS(X): Converts degrees to radians.
• PI(): Returns the value of Pi (π).

SELECT SIN(PI() / 2) AS sin_90_deg, DEGREES(PI()) AS pi_in_degrees;
-- Result: 1, 180