MySQL provides various built-in functions for numerical operations. Commonly used functions are

  • ABS()
  • CEIL()
  • FLOOR()
  • ROUND()
  • MOD()
  • RAND()
  • POW()
  • TRUNCATE()
  • DIV

ABS()

It returns the absolute (positive) value of a number. Following shows the syntax of the ABS() function

ABS(number)

number is a literal number or an expression that evaluates to a number. In case number is zero or positive, ABS() function has no effect. The data type of the return value is the same as the data type of the input argument.

Below shows application of ABS() function.

SELECT ABS(-10.1)
SELECT ABS(10)

/* Output */
10.1
10

CEIL()

This function takes an input number and returns the smallest integer greater than or equal to that number. Syntax of the function is

CEIL (number)

number can be a literal number or an expression that evaluates to a number. Return type of number depends on the type of the input number. If the type of the input number is floating-point type, the type of the return value is floating-point type.

The following example applies the CEIL() function to a positive number and a negative number.

SELECT CEIL(3.14)
SELECT CEIL(-3.14)

/* Output */
4
-3

FLOOR()

This function accepts number and returns the largest integer number less than or equal to the argument. Syntax of the FLOOR() function is

FLOOR(number)

Data type of the return value depends on the type of the input number. If the type of the input number is exact numeric, the type of the returned value is exact numeric.

Below example applies the this function to a positive number and a negative number.

SELECT FLOOR(3.14)
SELECT FLOOR(-3.14)

/* Output */
3
-4

ROUND()

It round a number to a specified number of decimal places. The following shows the syntax of the ROUND() function.

ROUND(number, [d])

number is a number to be rounded and d is the number of decimal places to which the number is rounded. d is an optional parameter, defaults to zero. If it is negative, then the d digits left of the decimal point of the number n becomes zero.

Fractional part of .5 or greater is rounded up to the next integer if positive or rounded down to the next integer if negative. Below example shows rounding of number to given place.

SELECT ROUND(43.14)
SELECT ROUND(43.14, 1)
SELECT ROUND(43.14, -1)

/* Output */
43
43.1
40

MOD()

It returns the remainder of one number divided by another. Syntax of the MOD() function is

MOD(dividend,divisor)

dividend is a literal number or a numeric expression to divide. divisor is a literal number or a numeric expression by which to divide the dividend. If the divisor is zero, it returns NULL.

Below statement divides the number 21 by 4. It returns 1 as the reminder.

SELECT MOD(21, 4)

/* Output */
1

RAND()

This function generate a random number. It returns a random floating-point value in the range 0 to 1.0. Syntax of the function is

RAND([N])

N is an optional seed value that you can use to influence the result. Seed value influence the output of the function. This function will return the same value each time if an equal argument value is provided.

Below example generate random numbers using above function.

SELECT RAND()
SELECT RAND(5)

/* Output */
0.21123415623906455	
0.40613597483014313

POW()

This function raises a number to the power of another number. Syntax of this function goes like this

POW(X,Y)

The function returns the value of X raised to the power of Y. Below example shows usage of the function.

SELECT POW(2,3)

/* Output */
8

TRUNCATE()

This function truncates a number to a specified number of decimal places. Syntax of this function is

TRUNCATE(number,d)

number is a literal number or a numeric expression to be truncated. d is the number of decimal places to truncate to. If d is negative, then d digits left of the decimal point of number to become zero.

This function is similar to the ROUND() function in terms of reducing the number of decimal places. However, the TRUNCATE() function does not perform any rounding as the ROUND() function does. The following example shows usage of TRUNCATE() function with a negative and positive number of decimal places.

SELECT TRUNCATE(43.156,0)
SELECT TRUNCATE(43.156,1)
SELECT TRUNCATE(43.156,-1)

/* Output */
43
43.1
40

DIV

The DIV function is used for integer division (x is divided by y). An integer value is returned. Syntax for the same is

x DIV y

Below example performs integer and float division.

SELECT 16 DIV 3
SELECT 16 / 3

/* Output */
5
5.3333