6.3.3.2 Mathematical Functions

All mathematical functions return NULL in case of an error.

-

Unary minus. Changes the sign of the argument:

mysql> SELECT - 2;
        -> -2

Note that if this operator is used with a BIGINT, the return value is a BIGINT! This means that you should avoid using - on integers that may have the value of -2^63!

ABS(X)

Returns the absolute value of X:

mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32

This function is safe to use with BIGINT values.

SIGN(X)

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive:

mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1

MOD(N,M)

%

Modulo (like the % operator in C). Returns the remainder of N divided by M:

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

This function is safe to use with BIGINT values. The last example only works in MySQL 4.1

FLOOR(X)

Returns the largest integer value not greater than X:

mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2

Note that the return value is converted to a BIGINT!

CEILING(X)

CEIL(X)

Returns the smallest integer value not less than X:

mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEIL(-1.23);
        -> -1

The CEIL() alias was added in version 4.0.6. Note that the return value is converted to a BIGINT!

ROUND(X)

ROUND(X,D)

Returns the argument X, rounded to the nearest integer. With two arguments rounded to a number to D decimals.

mysql> SELECT ROUND(-1.23);
        -> -1
mysql> SELECT ROUND(-1.58);
        -> -2
mysql> SELECT ROUND(1.58);
        -> 2
mysql> SELECT ROUND(1.298, 1);
        -> 1.3
mysql> SELECT ROUND(1.298, 0);
        -> 1
mysql> SELECT ROUND(23.298, -1);
        -> 20

Note that the behaviour of ROUND() when the argument is half way between two integers depends on the C library implementation. Some round to the nearest even number, always up, always down, or always toward zero. If you need one kind of rounding, you should use a well-defined function like TRUNCATE() or FLOOR() instead.

DIV

Integer division. Similar to FLOOR() but safe with BIGINT values.

mysql> SELECT 5 DIV 2
        -> 2

DIV is new in MySQL 4.1.0.

EXP(X)

Returns the value of e (the base of natural logarithms) raised to the power of X:

mysql> SELECT EXP(2);
        -> 7.389056
mysql> SELECT EXP(-2);
        -> 0.135335

LN(X)

Returns the natural logarithm of X:

mysql> SELECT LN(2);
        -> 0.693147
mysql> SELECT LN(-2);
        -> NULL

This function was added in MySQL version 4.0.3. It is synonymous with LOG(X) in MySQL.

LOG(X)

LOG(B,X)

If called with one parameter, this function returns the natural logarithm of X:

mysql> SELECT LOG(2);
        -> 0.693147
mysql> SELECT LOG(-2);
        -> NULL

If called with two parameters, this function returns the logarithm of X for an arbitary base B:

mysql> SELECT LOG(2,65536);
        -> 16.000000
mysql> SELECT LOG(1,100);
        -> NULL

The arbitrary base option was added in MySQL version 4.0.3. LOG(B,X) is equivalent to LOG(X)/LOG(B).

LOG2(X)

Returns the base-2 logarithm of X:

mysql> SELECT LOG2(65536);
        -> 16.000000
mysql> SELECT LOG2(-100);
        -> NULL

LOG2() is useful for finding out how many bits a number would require for storage. This function was added in MySQL version 4.0.3. In earlier versions, you can use LOG(X)/LOG(2) instead.

LOG10(X)

Returns the base-10 logarithm of X:

mysql> SELECT LOG10(2);
        -> 0.301030
mysql> SELECT LOG10(100);
        -> 2.000000
mysql> SELECT LOG10(-100);
        -> NULL

POW(X,Y)

POWER(X,Y)

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

mysql> SELECT POW(2,2);
        -> 4.000000
mysql> SELECT POW(2,-2);
        -> 0.250000

SQRT(X)

Returns the non-negative square root of X:

mysql> SELECT SQRT(4);
        -> 2.000000
mysql> SELECT SQRT(20);
        -> 4.472136

PI()

Returns the value of PI. The default shown number of decimals is 5, but MySQL internally uses the full double precession for PI.

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116

COS(X)

Returns the cosine of X, where X is given in radians:

mysql> SELECT COS(PI());
        -> -1.000000

SIN(X)

Returns the sine of X, where X is given in radians:

mysql> SELECT SIN(PI());
        -> 0.000000

TAN(X)

Returns the tangent of X, where X is given in radians:

mysql> SELECT TAN(PI()+1);
        -> 1.557408

ACOS(X)

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NULL if X is not in the range -1 to 1:

mysql> SELECT ACOS(1);
        -> 0.000000
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.570796

ASIN(X)

Returns the arc sine of X, that is, the value whose sine is X. Returns NULL if X is not in the range -1 to 1:

mysql> SELECT ASIN(0.2);
        -> 0.201358
mysql> SELECT ASIN('foo');
        -> 0.000000

ATAN(X)

Returns the arc tangent of X, that is, the value whose tangent is X:

mysql> SELECT ATAN(2);
        -> 1.107149
mysql> SELECT ATAN(-2);
        -> -1.107149

ATAN(Y,X)

ATAN2(Y,X)

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result:

mysql> SELECT ATAN(-2,2);
        -> -0.785398
mysql> SELECT ATAN2(PI(),0);
        -> 1.570796

COT(X)

Returns the cotangent of X:

mysql> SELECT COT(12);
        -> -1.57267341
mysql> SELECT COT(0);
        -> NULL

RAND()

RAND(N)

Returns a random floating-point value in the range 0 to 1.0. If an integer argument N is specified, it is used as the seed value (producing a repeatable sequence):

mysql> SELECT RAND();
        -> 0.9233482386203
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND(20);
        -> 0.15888261251047
mysql> SELECT RAND();
        -> 0.63553050033332
mysql> SELECT RAND();
        -> 0.70100469486881

You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. From version 3.23 you can do: SELECT * FROM table_name ORDER BY RAND() This is useful to get a random sample of a set SELECT * FROM table1,table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000. Note that a RAND() in a WHERE clause will be re-evaluated every time the WHERE is executed. RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that will be portable between platforms for the same MySQL version.

LEAST(X,Y,...)

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

• If the return value is used in an INTEGER context, or all arguments are integer-valued, they are compared as integers.
• If the return value is used in a REAL context, or all arguments are real-valued, they are compared as reals.
• If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
• In other cases, the arguments are compared as case-insensitive strings:

mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST("B","A","C");
        -> "A"

In MySQL versions prior to Version 3.22.5, you can use MIN() instead of LEAST.

GREATEST(X,Y,...)

Returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST:

mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST("B","A","C");
        -> "C"

In MySQL versions prior to Version 3.22.5, you can use MAX() instead of GREATEST.

DEGREES(X)

Returns the argument X, converted from radians to degrees:

mysql> SELECT DEGREES(PI());
        -> 180.000000

RADIANS(X)

Returns the argument X, converted from degrees to radians:

mysql> SELECT RADIANS(90);
        -> 1.570796

TRUNCATE(X,D)

Returns the number X, truncated to D decimals. If D is 0, the result will have no decimal point or fractional part:

mysql> SELECT TRUNCATE(1.223,1);
        -> 1.2
mysql> SELECT TRUNCATE(1.999,1);
        -> 1.9
mysql> SELECT TRUNCATE(1.999,0);
        -> 1
mysql> SELECT TRUNCATE(-1.999,1);
        -> -1.9

Starting from MySQL 3.23.51, all numbers are rounded toward zero. If D is negative, then the whole part of the number is zeroed out:

mysql> SELECT TRUNCATE(122,-2);
       -> 100

Note that as decimal numbers are normally not stored as exact numbers in computers, but as double-precision values, you may be fooled by the following result:

mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1027

The above happens because 10.28 is actually stored as something like 10.2799999999999999.

User Comments

You bring up some important issues, but
dealing with ages really isn't <em>that</em>
hard. For
example you could do something like this:

mysql> SELECT DATE_FORMAT(
FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(dob)), '%Y')+0
AS age FROM people;

Where 'dob' is obviously their date of birth.
It'll also work with pre and post-epoch dates.
Please excuse the funky formatting as the
comment system seems to insist on inserting line
breaks into the code block. I
ran into this problem while working on some
genealogical things over at <a
href='http://www.mullenweg.com'>Mullenweg.com</a>
, a family site. I hope this helps!

<style>
div.comment {
border: 1px dashed black;
padding: 2px;
background-color: #F0F0F0;
}
</style>
<div class="comment">
Seems to be a real pain to get the days in the
month, but here is one way

<code>select
DATE_FORMAT(CONCAT(YEAR('2002-05-05'), '-',
MONTH('2002-05-05' + INTERVAL 1 MONTH), '-01') -
INTERVAL 1 DAY, '%e') as numDays</code>

I guess it would be nice if we could just have a
DATE_FORMAT entity for this</div>

SELECT RAND() AS n
works fine. It returns everytime a different result.

SELECT RAND(RAND()) AS n
always returns the same result.

SELECT RAND() AS n, RAND(n) AS caso
returns an error: MySQL doesnt recognize n. (maybe this is right... honestly, I dont know...)

It is not important but I think it's strange...

As of MySQL 3.23.52, MySQL changed the way
RAND() functions, such that you MUST supply a
SEED to get an actual random number. If you do
not, each new connection will return close to the
same number as the previous new connection. An
example to test this would be to run the follow
command multiple times in a row. mysql -u
username -p -e'select rand()' A basic way to seed
this would be to run RAND(NOW()).

My brother had a case where he wanted to sort
randomly but ALSO use LIMIT so he could page
results - of course random will be different each time.

He wanted a random order that was not random for
the same session; so here is the idea:

In the web-side code calculate a numeric value which
is likely to stay the same for a session, perhaps
based on some session id, or timed-expiring cookie
value, etc, or from short-term stable HTTP headers.

Also require a numeric and well distributed value for
each record (doesn't have to be unique but works
well if it is).

Then:

... order by rand(numeric_field + session_value)
LIMIT blah;

So we see the ordering is preserved as
numeric_field+session_value will be the same for a
session, and numeric_field + session value are NOT
the same from row to row so we still get random
ordering.

Sam Liddicott

Windows2000(mysql 3.23.27-beta):
select pi()
+0.000000000000000000000000000000;
-> result :
3.141592653589793100000000000000

Linux ( mysql 3.23.27-beta):
select pi()
+0.000000000000000000000000000000;
-> result :
3.141592653589793115997963468544

WHY? PLEASE EMAIL TO ME( ahj6@hotmail.com)

I might be caused by compiler ability to count to upto 30 places under zero. Win32 mysql probably mighe be compiled with 32bit compiler rather than 64bit. -- Hyungjin Ahn(ahj6@hotmail.com)

I had this problem with RAND() in 3.23.54 . I wanted to select a random row (for a banner script), but mySQL kept on giving me the lovest ID even that I used RAND().

Original query:
SELECT id, image, RAND() AS myRandom
FROM banner ORDER BY myRandom LIMIT 0, 1

The problem was, that the random string was lower for lover IDs. By MD5-ing it I got better randon strings. It's not the perfect solution, but it helped me. You could probably do hashes from the id or other content and get the same effect.

Soultion:
SELECT id, image, MD5( RAND( ) ) AS myRandom
FROM banner ORDER BY myRandom LIMIT 0, 1

I am trying to use the order by rand() function in a where statement :

select * from table_name where a=a and b=b order by rand()limit 3

the 1st result is always the 1st record in my mysql db, it only performs the random function on the 2nd and 3rd record. any idea why it is happening?

Jonny Fribergs idea with the MD5 works great! Thanks!

In reply to robin lee, use RAND(NOW()). Its solved the problem for me.

On MySQL 3.23.36:

SELECT PI()
SELECT PI()+0.0000000

Return 3.142

I'm not sure but it seems to be a bug.

That MD5 works fine for me... I just mixed some ideas:

SELECT column,MD5(RAND()*NOW()) as MyRND FROM table ORDER BY MyRND DESC LIMIT 2

Alberto,

I tried some of the other combinations from above, but yours actually fixed my problem.

Thanks!

Sean

Like many of you, I was having problems with ORDER BY RAND() actually resulting in a random result. Seeding RAND with NOW() did not seem to help. So, I tried the suggestion to use an MD5 hash, which worked great.

However, I have another possible solution that, at least in testing, has resulted in a 50% improvement in execution time over MD5. I suggest using the REVERSE string command on the RAND result.

The reason this works is because, for whatever reason, the RAND function seems to return "just about" the same number every time. However, as you get less and less significant in the digits (the farther right you go from the decimal), the numbers change more and more. So, by starting the number with the least significant digit, you end up with a fairly random number.

And, because a simple string manipulation is quite a bit less strenuous on the processor, your server will thank you. 9.489228 seconds for MD5 verses 5.412398 seconds for REVERSE on 10,000 selects.

Add your own comment.