MENU


Numeric

Trigonometric

SIN(x), COS(x), TAN(x), and COT(x) take x in radians. Their inverses are ASIN(x), ACOS(x), ATAN(x), and ATAN(Y,X) / ATAN2(Y,X). PI() returns the value of π. RADIANS(x) converts x from degrees to radians; DEGREES(x) converts x from radians to degrees. 

SELECT PI(), RADIANS(180), DEGREES(PI());

PI()RADIANS(180)DEGREES(PI())
3.1415933.141592653589793180

Powers, Roots, and Logarithms

POW(x,y) or POWER(x,y) returns x raised to the power y. SQRT(x) returns the square root of a non-negative x. EXP(x) returns e raised to the power x. LOG(x) or LN(x) returns the natural logarithm of x. LOG(B,x) returns the logarithm of x to base B. LOG2(x) returns the base-2 logarithm of x. LOG10(x) returns the base-10 logarithm of x. 

SELECT POW(2,10), SQRT(16), LOG2(8), LOG10(1000);

POW(2,10)SQRT(16)LOG2(8)LOG10(1000)
1024433

Rounding and Sign

MOD(N,M) is the same as N % M and N MOD M (see Operators). SIGN(x) returns -1, 0, or 1. ABS(x) returns the absolute value of x. CEIL(x) and CEILING(x) return the smallest integer not less than x. FLOOR(x) returns the largest integer not greater than x. ROUND(x) rounds x to the nearest integer; ROUND(x, D) rounds x to D decimal places, where D may be negative to round to the left of the decimal point. TRUNCATE(x,D) truncates (rather than rounds) x to D decimal places, where D may likewise be negative. 

SELECT MOD(10,3), SIGN(-5), ABS(-5);

SELECT CEIL(4.1), FLOOR(4.9);

SELECT ROUND(3.14159, 2), TRUNCATE(3.14159, 2);

SELECT ROUND(12345, -2), TRUNCATE(12345, -2);

SELECT MOD(10,3), SIGN(-5), ABS(-5):
MOD(10,3)SIGN(-5)ABS(-5)
1-15
SELECT CEIL(4.1), FLOOR(4.9):
CEIL(4.1)FLOOR(4.9)
54
SELECT ROUND(3.14159, 2), TRUNCATE(3.14159, 2):
ROUND(3.14159, 2)TRUNCATE(3.14159, 2)
3.143.14
SELECT ROUND(12345, -2), TRUNCATE(12345, -2):
ROUND(12345, -2)TRUNCATE(12345, -2)
1230012300

Miscellaneous

CONV(N,B1,B2) converts the number (given as a string) N from base B1 to base B2. CRC32(expr) computes a cyclic redundancy check value and returns a 32-bit unsigned integer. RAND() returns a random floating-point value from 0 up to (but not including) 1.0; RAND(s) uses s as a seed to produce a repeatable sequence.

MySQL 9.0 adds a separate family of numeric-adjacent functions for the VECTOR column type — conversion functions such as STRING_TO_VECTOR() and VECTOR_TO_STRING(), plus distance functions for embedding/similarity-search workloads. They're covered with the data type itself in Data Types rather than here. 

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

SELECT CRC32('hello');

SELECT CONV('FF', 16, 10):
CONV('FF', 16, 10)
255
SELECT CRC32('hello'):
CRC32('hello')
907060870

-- RAND() is non-deterministic; RAND(s) is repeatable for a given seed
-- but its exact value is version/platform-dependent, so no fixed
-- result is shown here.
SELECT RAND(), RAND(42);