<<

. 120
( 132 .)



>>

Round ROUND(X [,D])
RETURNS: int or float

ROUND returns the argument X rounded to a number with D decimals. If D is 0, or
does not exist, the result will have no decimal point or fractional part.

mysql> select round(8.53), round(8.47), round(8.534,2);
+-------------+-------------+----------------+
| round(8.53) | round(8.47) | round(8.534,2) |
+-------------+-------------+----------------+
| 9| 8| 8.53 |
+-------------+-------------+----------------+
1 row in set (0.33 sec)


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

TRUNCATE(X,D)
RETURNS: decimal

mysql> select truncate(8.53,0), truncate(8.43,0), truncate(8.534,2);
+------------------+------------------+-------------------+
| truncate(8.53,0) | truncate(8.43,0) | truncate(8.534,2) |
+------------------+------------------+-------------------+
| 8| 8| 8.53 |
+------------------+------------------+-------------------+
1 row in set (0.05 sec)


EXP
This function returns the value of e (the base of natural logarithms) raised to the
power of X:

EXP(X)
RETURNS: float
Appendix J: MySQL Function Reference 705


LOG
This function returns the natural logarithm of X. If you want the log of a number X
to some arbitrary base B, use the formula LOG(X)/LOG(B).

LOG(X)
RETURNS: float


LOG10
LOG10 returns the base-10 logarithm of X:

LOG10(X)
RETURNS: float


POW
This function returns the value of X raised to the power of Y:

POW(X,Y)
RETURNS: float


SQRT
This function returns the non-negative square root of X:

SQRT(X)
RETURNS: float


PI
This function returns an approximation of pi:

PI()
RETURNS: float

By default, only five decimal places of precision are returned. Additional preci-
sion can be gained by adding the result of PI() to 0, formatted as a floating point
number with many decimal places:

SELECT PI() + 0.0000000000
706 Part V: Appendixes


COS
COS returns the cosine of X, where X is given in radians:

COS(X)
RETURNS: float


SIN
SIN returns the sine of X, where X is given in radians:

SIN(X)
RETURNS: float


TAN
This function returns the tangent of X, where X is given in radians:

TAN(X)
RETURNS: float


ACOS
This function returns the arc cosine of X ” that is, the value whose cosine is X. It
returns NULL if X is not in the range -1 to 1.

ACOS(X)
float


ASIN
This returns the arc sine of X ” that is, the value whose sine is X. It returns NULL if X
is not in the range -1 to 1.

ASIN(X)
RETURNS: float


ATAN
ATAN returns the arc tangent of X ” that is, the value whose tangent is X:

ATAN(X)
RETURNS: float
Appendix J: MySQL Function Reference 707


ATAN2
ATAN2 returns the arc tangent of the two arguments X and Y. The process is similar
to that of calculating the arc tangent of Y/X, except that the sines of both arguments
are used to determine the quadrant of the result.

ATAN2(X,Y)
RETURNS: float


COT
This function returns the cotangent of X:

COT(X)
RETURNS: float


RAND
This function returns a random floating-point value in the range 0 to 1.0.

RAND()

or

RAND(N)
RETURNS: float

If an integer argument N is specified, it is used as the seed value. You can™t use a
column with RAND() values in an order by clause because in that case order by
would evaluate the column multiple times. In MySQL 3.23 and later you can, how-
ever, do the following: select * from table_name order by RAND(). This is use-
ful for getting a random sample. Note that a RAND() in a WHERE clause will be
reevaluated every time the WHERE is executed.

LEAST
With two or more arguments, this function returns the smallest (minimum-valued)
argument:

LEAST(X,Y,...)
RETURNS: type of X
708 Part V: Appendixes

Some unusual stuff goes on with LEAST, most of it having to do with casting
arguments into alternate forms before comparison. Here are some examples of the
behavior of this function:

— LEAST(22, 2.2) returns 2.2.

— LEAST(now(), ˜a™) returns the current date/time as a string.

— LEAST(now(), 50000000000000) returns the current date as a number.

— LEAST(˜a™, 10) returns 0.

— LEAST(˜a™, ˜10™) returns 10.

— LEAST(2.2, ˜22™) returns 2.2.

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


GREATEST
GREATEST returns the largest (maximum-valued) argument. In MySQL versions
prior to 3.22.5, you can use MAX() instead of GREATEST. Type conversion and cast-
ing works in the same way it does with LEAST, discussed previously.

GREATEST(X,Y,...)
RETURNS: type of X

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


DEGREES
This function returns the argument X, converted from radians to degrees:

DEGREES(X)
RETURNS: float
Appendix J: MySQL Function Reference 709


RADIANS
This function returns the argument X, converted from degrees to radians:

RADIANS(X)
RETURNS: float




String Functions
MySQL™s string functions return NULL if the length of the result would be greater
than the max_allowed_packet server parameter. You can set this parameter by
starting MySQL with a command like this:

safe_mysqld -O max_allowed_packet=16M

For functions that operate on string positions, the first position is numbered 1.

ASCII
This function returns the ASCII-code value of the leftmost character in the string
str. It returns 0 if str is the empty string and NULL if str is NULL.

ASCII(str)
RETURNS: int

mysql> select ascii(˜\n™);
+-------------+
| ascii(˜\n™) |
+-------------+
| 10 |
+-------------+
1 row in set (0.00 sec)


ORD
If the leftmost character in the string str is a multi-byte character, this function
returns the code of the multi-byte character by returning the ASCII-code value of
the character in the following format: ((first byte ASCII code)*256+(second
byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character
is not a multi-byte character, ORD returns the same value as the similar ASCII()
function.

ORD(str)
RETURNS: int
710 Part V: Appendixes


CONV
This function converts numbers between different number bases:

CONV(N,from_base,to_base)

<<

. 120
( 132 .)



>>