Numeric Functions

A list of the more useful numeric functions is shown in Table 8.4. There are many more in the manual.

Table 8.4. Numeric Functions Function
Purpose

abs(n)
Returns the absolute value of n—that is, the value without a sign in front of it.

ceiling(n)
Returns the value of n rounded up to the nearest integer.

floor(n)
Returns the value of n rounded down to the nearest integer.

mod(n,m) and div
These two functions divide n by m. div returns the integral quotient, and mod() returns the integral remainder.

power(n,m)
Returns n to the power of m.

rand(n)
Returns a random number between 0 and 1. The parameter n is optional, but if supplied, it is used as a seed for the pseudorandom number generation. (Giving the same n to rand will produce the same pseudorandom number.)

round(n[,d])
Returns n rounded to the nearest integer. If you supply d, n will be rounded to d decimal places.

sqrt(n)
Returns the square root of n.

Let's look at an example using mod() and div. One confusing thing about these functions is that whereas mod can be expressed as

mod(9,2)

or

9 mod 2

or even

9 % 2

the div function can be expressed only as

9 div 2

So, for example,

div(9, 2)

will not work and will give a syntax error.

Running mod and div in MySQL gives the following results:

mysql> select 9 mod 2;
+---------+
| 9 mod 2 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

mysql> select 9 div 2;
+---------+
| 9 div 2 |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)