Oracle 5.0, Reference Manual

Page: 933 / 3232

Mathematical Functions
913
+------+------------------+
| 1 | 0.90576975597606 |
| 2 | 0.37307905813035 |
| 3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.01 sec)
The effect of using a nonconstant argument is undefined. As of MySQL 5.0.13, nonconstant
arguments are not permitted.
To obtain a random integer
R
in the range
i
<=
R
<
j
, use the expression
FLOOR(i + RAND() *
(j
[910] –
i))
. For example, to obtain a random integer in the range the range
7
<=
R
<
12
, you
could use the following statement:
SELECT FLOOR(7 + (RAND() * 5));
RAND()
[912] in a
WHERE
clause is re-evaluated every time the
WHERE
is executed.
You cannot use a column with
RAND()
[912] values in an
ORDER BY
clause, because
ORDER BY
would evaluate the column multiple times. However, you can retrieve rows in random order like this:
mysql> SELECT * FROM tbl_name ORDER BY RAND();
ORDER BY RAND()
combined with
LIMIT
is useful for selecting a random sample from a set of
rows:
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;
RAND()
[912] is not meant to be a perfect random generator. It is a fast way to generate random
numbers on demand that is portable between platforms for the same MySQL version.
•
ROUND(X)
[913] ,
ROUND(X,D)
[913]
Rounds the argument
X
to
D
decimal places. The rounding algorithm depends on the data type of
X
.
D
defaults to 0 if not specified.
D
can be negative to cause
D
digits left of the decimal point of the
value
X
to become zero.
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
The return type is the same type as that of the first argument (assuming that it is integer, double, or
decimal). This means that for an integer argument, the result is an integer (no decimal places):
mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
| 150.00 | 150 |
+------------------+--------------+
Before MySQL 5.0.3, the behavior of
ROUND()
[913] when the argument is halfway between two
integers depends on the C library implementation. Different implementations 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 such as
TRUNCATE()
[914] or
FLOOR()
[910] instead.