MySQL :: MySQL 5.6 リファレンスマニュアル

8.0 English
5.7 English
5.6 English
5.5 English

MySQL 5.6 リファレンスマニュアル / ... / 数学関数

12.6.2 数学関数

表 12.12 数学関数

名前	説明
`ABS()`	絶対値を返します
`ACOS()`	アークコサインを返します
`ASIN()`	アークサインを返します
`ATAN()`	アークタンジェントを返します
`ATAN2()`, `ATAN()`	2 つの引数のアークタンジェントを返します
`CEIL()`	引数以上のもっとも小さな整数値を返します
`CEILING()`	引数以上のもっとも小さな整数値を返します
`CONV()`	数値を異なる基数間で変換します
`COS()`	コサインを返します
`COT()`	コタンジェントを返します
`CRC32()`	巡回冗長検査値を計算します
`DEGREES()`	ラジアンを角度に変換します
`EXP()`	累乗します
`FLOOR()`	引数以下のもっとも大きな整数値を返します
`LN()`	引数の自然対数を返します
`LOG()`	最初の引数の自然対数を返します
`LOG10()`	引数の底 10 の対数を返します
`LOG2()`	引数の底 2 の対数を返します
`MOD()`	余りを返します
`PI()`	pi の値を返します
`POW()`	指定した指数で累乗された引数を返します
`POWER()`	指定した指数で累乗された引数を返します
`RADIANS()`	ラジアンに変換された引数を返します
`RAND()`	ランダムな浮動小数点値を返します
`ROUND()`	引数を丸めます
`SIGN()`	引数の符号を返します
`SIN()`	引数のサインを返します
`SQRT()`	引数の平方根を返します
`TAN()`	引数のタンジェントを返します
`TRUNCATE()`	指定された小数点以下の桁数に切り捨てます

すべての数学関数は、エラーの発生時に NULL を返します。

ABS(X)

X の絶対値を返します。
```
mysql> SELECT ABS(2);
        -> 2
mysql> SELECT ABS(-32);
        -> 32
```
この関数は、BIGINT 値でも安全に使用できます。
ACOS(X)

X のアークコサイン (つまり、コサインが X である値) を返します。X が -1 から 1 までの範囲内にない場合は、NULL を返します。
```
mysql> SELECT ACOS(1);
        -> 0
mysql> SELECT ACOS(1.0001);
        -> NULL
mysql> SELECT ACOS(0);
        -> 1.5707963267949
```

ASIN(X)

X のアークサイン (つまり、サインが X である値) を返します。X が -1 から 1 までの範囲内にない場合は、NULL を返します。

mysql> SELECT ASIN(0.2);
        -> 0.20135792079033
mysql> SELECT ASIN('foo');

+-------------+
| ASIN('foo') |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'foo' |
+---------+------+-----------------------------------------+

ATAN(X)

X のアークタンジェント (つまり、タンジェントが X である値) を返します。
```
mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941
```
ATAN(Y,X), ATAN2(Y,X)

2 つの変数 X および Y のアークタンジェントを返します。これは、両方の引数の符号が結果の象限の判定に使用される点を除いて、Y / X のアークタンジェントの計算と同様です。
```
mysql> SELECT ATAN(-2,2);
        -> -0.78539816339745
mysql> SELECT ATAN2(PI(),0);
        -> 1.5707963267949
```
CEIL(X)

CEIL() は CEILING() のシノニムです。
CEILING(X)

X 以上で最小の整数値を返します。
```
mysql> SELECT CEILING(1.23);
        -> 2
mysql> SELECT CEILING(-1.23);
        -> -1
```
引数が厳密値数値の場合は、戻り値の型も厳密値数値になります。引数が文字列または浮動小数点の場合は、戻り値の型が浮動小数点になります。
CONV(N,from_base,to_base)

数値を異なる基数間で変換します。基数 from_base から基数 to_base に変換された数値 N の文字列表現を返します。引数のいずれかが NULL である場合は、NULL を返します。引数 N は整数として解釈されますが、整数または文字列として指定される場合もあります。最小の基数は 2 で、最大の基数は 36 です。to_base が負の数字である場合は、N は符号付きの数字とみなされます。それ以外の場合は、N は符号なしとみなされます。CONV() は 64 ビット精度で動作します。
```
mysql> SELECT CONV('a',16,2);
        -> '1010'
mysql> SELECT CONV('6E',18,8);
        -> '172'
mysql> SELECT CONV(-17,10,-18);
        -> '-H'
mysql> SELECT CONV(10+'10'+'10'+0xa,10,10);
        -> '40'
```
COS(X)

X のコサインを返します。X はラジアンで指定されます。
```
mysql> SELECT COS(PI());
        -> -1
```

COT(X)

X のコタンジェントを返します。

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

CRC32(expr)

巡回冗長検査値を計算し、32 ビット値の符号なし値を返します。引数が NULL である場合は、結果も NULL になります。引数は文字列であると想定され、(可能な場合は) 文字列でない場合でも文字列として処理されます。
```
mysql> SELECT CRC32('MySQL');
        -> 3259397556
mysql> SELECT CRC32('mysql');
        -> 2501908538
```

DEGREES(X)

ラジアンからディグリーに変換された引数 X を返します。

mysql> SELECT DEGREES(PI());
        -> 180
mysql> SELECT DEGREES(PI() / 2);
        -> 90

EXP(X)

e (自然対数の底) の X 乗の値を返します。この関数の逆は、(単一の引数のみを使用する) LOG() または LN() です。
```
mysql> SELECT EXP(2);
        -> 7.3890560989307
mysql> SELECT EXP(-2);
        -> 0.13533528323661
mysql> SELECT EXP(0);
        -> 1
```
FLOOR(X)

X 以下で最大の整数値を返します。
```
mysql> SELECT FLOOR(1.23);
        -> 1
mysql> SELECT FLOOR(-1.23);
        -> -2
```
引数が厳密値数値の場合は、戻り値の型も厳密値数値になります。引数が文字列または浮動小数点の場合は、戻り値の型が浮動小数点になります。
FORMAT(X,D)

数値 X を '#,###,###.##' のような書式に変換し、小数点第 D 位に丸めて、その結果を文字列として返します。詳細は、セクション12.5「文字列関数」を参照してください。
HEX(N_or_S)

この関数を使用すると、10 進数または文字列の 16 進表現を取得できます。その方法は、引数の型によって異なります。詳細は、セクション12.5「文字列関数」で、この関数の説明を参照してください。
LN(X)

X の自然対数 (つまり、X の底 e の対数) を返します。X が 0 以下である場合は、NULL が返されます。
```
mysql> SELECT LN(2);
        -> 0.69314718055995
mysql> SELECT LN(-2);
        -> NULL
```
この関数は LOG(X) のシノニムです。この関数の逆は、EXP() 関数です。
LOG(X), LOG(B,X)

1 つのパラメータで呼び出される場合、この関数は X の自然対数を返します。X が 0 以下である場合は、NULL が返されます。

この関数 (単一の引数で呼び出された場合) の逆は、EXP() 関数です。
```
mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL
```
この関数が 2 つのパラメータで呼び出される場合は、B を底とする X の対数が返されます。X が 0 以下である場合、または B が 1 以下である場合は、NULL が返されます。
```
mysql> SELECT LOG(2,65536);
        -> 16
mysql> SELECT LOG(10,100);
        -> 2
mysql> SELECT LOG(1,100);
        -> NULL
```
LOG(B,X) は LOG(X) / LOG(B) と同等です。
LOG2(X)

X の底 2 の対数を返します。
```
mysql> SELECT LOG2(65536);
        -> 16
mysql> SELECT LOG2(-100);
        -> NULL
```
LOG2() は、格納に必要なビット数を調べる際に役立ちます。この関数は式 LOG(X) / LOG(2) と同等です。

LOG10(X)

X の底 10 の対数を返します。

mysql> SELECT LOG10(2);
        -> 0.30102999566398
mysql> SELECT LOG10(100);
        -> 2
mysql> SELECT LOG10(-100);
        -> NULL

LOG10(X) は LOG(10,X) と同等です。

MOD(N,M)、N % M、N MOD M

モジュロ演算。M で除算された N の余りを返します。
```
mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2
```
この関数は、BIGINT 値でも安全に使用できます。

MOD() は、小数部を持つ値でも機能し、除算後の正確な余りを返します。
```
mysql> SELECT MOD(34.5,3);
        -> 1.5
```
MOD(N,0) は NULL を返します。
PI()

π (pi) の値を返します。表示されるデフォルトの小数点以下の桁数は 7 ですが、MySQL では内部的に全倍精度値が使用されます。
```
mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793116
```

POW(X,Y)

X の Y 乗の値を返します。

mysql> SELECT POW(2,2);
        -> 4
mysql> SELECT POW(2,-2);
        -> 0.25

POWER(X,Y)

これは POW() のシノニムです。
RADIANS(X)

ディグリーからラジアンに変換された引数 X を返します。

注記

π ラジアンは、180 ディグリーと同等です。
```
mysql> SELECT RADIANS(90);
        -> 1.5707963267949
```
RAND(), RAND(N)

0 <= v < 1.0 の範囲内で、ランダムな浮動小数点値 v を返します。定数整数引数 N が指定されている場合は、カラム値の反復可能なシーケンスを生成するシード値として使用されます。次の例では、RAND(3) で生成される値のシーケンスが、発生した両方の場所で同じです。
```
mysql> CREATE TABLE t (i INT);
Query OK, 0 rows affected (0.42 sec)

mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT i, RAND() FROM t;
+------+------------------+
| i    | RAND()           |
+------+------------------+
|    1 | 0.61914388706828 |
|    2 | 0.93845168309142 |
|    3 | 0.83482678498591 |
+------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i    | RAND(3)          |
+------+------------------+
|    1 | 0.90576975597606 |
|    2 | 0.37307905813035 |
|    3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT i, RAND() FROM t;
+------+------------------+
| i    | RAND()           |
+------+------------------+
|    1 | 0.35877890638893 |
|    2 | 0.28941420772058 |
|    3 | 0.37073435016976 |
+------+------------------+
3 rows in set (0.00 sec)

mysql> SELECT i, RAND(3) FROM t;
+------+------------------+
| i    | RAND(3)          |
+------+------------------+
|    1 | 0.90576975597606 |
|    2 | 0.37307905813035 |
|    3 | 0.14808605345719 |
+------+------------------+
3 rows in set (0.01 sec)
```
定数イニシャライザを使用すると、シードは実行される前に、ステートメントのコンパイル時に一度初期化されます。引数として非定数イニシャライザ (カラム名など) が使用される場合は、RAND() が起動されるたびに、その値でシードが初期化されます。(引数値が等しい場合は、RAND() で毎回同じ値が返されることを示しています。)

i <= R < j の範囲内でランダムな整数 R を取得するには、式 FLOOR(i + RAND() * (j − i)) を使用します。たとえば、7 <= R < 12 の範囲内でランダムな整数を取得するには、次のようなステートメントを使用します。
```
SELECT FLOOR(7 + (RAND() * 5));
```
WHERE 句内の RAND() は、WHERE が実行されるたびに再評価されます。

ORDER BY ではカラムが複数回評価されるため、ORDER BY 句内では RAND() 値を持つカラムを使用できません。ただし、次のようにランダムな順序で行を取得できます。
```
mysql> SELECT * FROM tbl_name ORDER BY RAND();
```
LIMIT と組み合わせて ORDER BY RAND() を使用すれば、行のセットからランダムなサンプルを選択する際に役立ちます。
```
mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;
```
RAND() は、完全なランダムジェネレータとしては設計されていません。要求に応じてランダムな数字をすばやく生成する方法であり、同じ MySQL バージョンのプラットフォーム間で移植可能です。

この関数は、ステートメントベースのレプリケーションでは安全に使用できません。binlog_format が STATEMENT に設定されているときに、この関数を使用すると、警告のログが記録されます。(Bug #49222)
ROUND(X), ROUND(X,D)

引数 X を D 小数点に丸めます。丸めアルゴリズムは、X のデータ型に依存します。D が指定されていない場合は、デフォルトで 0 に設定されます。D を負の数に指定すると、値 X の小数点左側の D 桁をゼロにすることができます。
```
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
```
戻り型は、(整数、重複、または 10 進数と仮定すると) 1 番目の引数と同じ型です。つまり、引数が整数の場合は、結果が整数 (小数点なし) になります。
```
mysql> SELECT ROUND(150.000,2), ROUND(150,2);
+------------------+--------------+
| ROUND(150.000,2) | ROUND(150,2) |
+------------------+--------------+
|           150.00 |          150 |
+------------------+--------------+
```
ROUND() では、第 1 引数の型に応じて次のルールが使用されます。
- 真値の数字の場合、ROUND() では「四捨五入」または「切り捨て (切り上げ)」ルールが使用されます。0.5 以上の小数部を持つ値は、正の場合は次の整数に切り上げられ、負の場合は次の整数に切り下げられます。(つまり、ゼロから遠い方に丸められます。)0.5 未満の小数部を持つ値は、正の場合は次の整数に切り下げられ、負の場合は次の整数に切り上げられます。
- 近似値の数字の場合、結果は C ライブラリによって異なります。多くのシステムでは、これは、ROUND() で「偶数丸め」ルールが使用されることを意味します。任意の小数部を持つ値は、もっとも近い偶数の整数に丸められます。
次の例では、正確な値の丸めと近似値の丸めの相違点を示します。
```
mysql> SELECT ROUND(2.5), ROUND(25E-1);
+------------+--------------+
| ROUND(2.5) | ROUND(25E-1) |
+------------+--------------+
| 3          |            2 |
+------------+--------------+
```
詳細は、セクション12.20「高精度計算」を参照してください。
SIGN(X)

X が負、ゼロ、または正のいずれであるのかに応じて、引数の符号を -1、0、または 1 として返します。
```
mysql> SELECT SIGN(-32);
        -> -1
mysql> SELECT SIGN(0);
        -> 0
mysql> SELECT SIGN(234);
        -> 1
```

SIN(X)

X のサインを返します。X はラジアンで指定されます。

mysql> SELECT SIN(PI());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0

SQRT(X)

負ではない数字 X の平方根を返します。

mysql> SELECT SQRT(4);
        -> 2
mysql> SELECT SQRT(20);
        -> 4.4721359549996
mysql> SELECT SQRT(-16);
        -> NULL

TAN(X)

X のタンジェントを返します。X はラジアンで指定されます。

mysql> SELECT TAN(PI());
        -> -1.2246063538224e-16
mysql> SELECT TAN(PI()+1);
        -> 1.5574077246549

TRUNCATE(X,D)

D 小数点に切り捨てて、数字 X を返します。D が 0 の場合は、結果に小数点または小数部が含まれません。D を負の数に指定すると、値 X の小数点左側の D 桁をゼロにすることができます。
```
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
mysql> SELECT TRUNCATE(122,-2);
       -> 100
mysql> SELECT TRUNCATE(10.28*100,0);
       -> 1028
```
すべての数字は、ゼロ方向に丸められます。

PREV HOME UP NEXT

User Comments

Posted by on November 28, 2002

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

Posted by Hyungjin Ahn on January 9, 2003

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)

Posted by Girish Kshirsagar on November 27, 2003

You may need to compare columns in databases after converting say a string column to a numeric column. These comparisons are automatic

Example
in the WHERE clause you may have to do something like this

oem.oem_id=substring(sku,5,3)

Here sku is a string who substring starting from location 5 from left and then having total length of 3 is compared with a numeric value of oem_id to satisfy the WHERE clause.

Posted by on December 8, 2003

I wanted to round to the nearest 0 or 5 cents in currency and this query worked:
select round((((cost*100) - (cost*100)%5) /100), 2) from SessionCost;

Posted by on December 22, 2003

If "SELECT * FROM tab ORDER BY RAND()" doesn't work for you. Try to put a random value between the brackets.

Posted by Justin Laing on January 6, 2005

Here is my work around for MySQL rounding issues (On most systems it rounds to the nearest even number on 5). This mess of a calculation will round up always in mysql, which is how most people in the united states think about rounding:

num = the number you are rounding

ROUND( TRUNCATE(num,2) + REPLACE( ( (num*1000) - ( TRUNCATE(num,2) *1000) / 1000, '5', '6'), 2)

This example rounds to 2 decimal places. If you want to round to three decimals just switch out the 2s for 3s and the 1000s for 10000s, etc.

It basically works by replacing all the fives beyond the two decimal places with sixes, which will always round up. Then calling the round function.

Posted by Joel Maxson on January 22, 2005

Another way to round up to two decimals is using the following formula:
floor(num * 100 + .55)/100

Posted by Tim White on February 18, 2005

This may be self-evident but:
In a list where some elements had priority and others not I needed to randomise the prioritised items and not the rest. The prioritised entries all had a value of 1 in a field called 'enhanced' and all entries had an abbreviated name ('abbrev') that they were otherwise sorted by. Using
ORDER BY (RAND() * enhanced) desc, abbrev
I could change the order of the enhanced listings yet maintain an alphabetical listing thereafter.

Posted by Alejandro Vargas on September 27, 2005

WARNING WITH ROUND AND FORMAT FUNCTIONS:

As mentioned in the manual, ROUND function has problems with values near to the limit values. The same prblem is found in the format function Let's see it:

round(1.15,1)=1.2 OK
round(1.25,1)=1.2 BAD, sould be 1.3
round(1.35,1)=1.4 OK
round(1.45,1)=1.4 BAD, sould be 1.5
round(1.55,1)=1.6 OK

And so on...

A walkarround for this sould be to use truncate adding 0,06. The same problem in found in the format function.

+---------------+----------------+-----------------------+
| round(1.45,1) | FORMAT(1.45,1) | truncate(1.45+0.06,1) |
+---------------+----------------+-----------------------+
|           1.4 | 1.4            |                   1.5 |
+---------------+----------------+-----------------------+

Of corse, if you want to use more than one digit, you should add as many 0 as you need to de value added in the truncate function. Note that in case of using 2 digits, the result of format is correct but round stills failing. It is more reliable to do the calculation using your own formula, with truncate.

+----------------+-----------------+-------------------------+
| round(1.145,2) | FORMAT(1.145,2) | truncate(1.145+0.006,2) |
+----------------+-----------------+-------------------------+
|           1.14 | 1.15            |                    1.15 |
+----------------+-----------------+-------------------------+

Posted by Veerappan Kannan on January 3, 2006

I think the "bads" are actually bankers rounding

Posted by Craig Martin on January 10, 2006

As truncate comment above, but negative number safe:

Take special care when using the the unsafe version with grouping functions like SUM(), as the end result can be way off if there is a big mix of negative/positive numbers.

sign(num) * truncate(abs(num)+0.06,1)

E.g...

+-----------------------+------------------------+-------------------------------------------+
| truncate(1.45+0.06,1) | truncate(-1.45+0.06,1) | sign(-1.45) * truncate(abs(-1.45)+0.06,1) |
+-----------------------+------------------------+-------------------------------------------+
|                   1.5 |        WRONG -->  -1.3 |                         CORRECT -->  -1.5 |
+-----------------------+------------------------+-------------------------------------------+

Posted by Ken Halsted on January 25, 2006

I finally had to come up with my own solution for rounding with currency in the U.S.

Most of us consider this:

25.725 to be 25 dollars and 73 cents

But mysql was returning: round(25.725,2) as 25.72 which was throwing off my calc.

So, my workaround after not finding a solution is:

if num=25.725
============================
truncate(num + 0.0051,2)
============================

will yield this result: 25.73, which is correct.

I hope this helps someone else.

Ken

Posted by Justin Laing on March 2, 2006

The rounding functions above are a little bit off from what most people would consider standard rounding.

If you use 6 as the number you are adding to the digit beyond significance then you will be rounding up 0.4s as well as 0.5s.

Here is my method:
rounding to two decimals
TRUNCATE(num + (SIGN(num) * 0.005), 2)
example 1
TRUNCATE(0.004 + (SIGN(0.004) * 0.005),2) = TRUNCATE(0.009,2) = 0.00
example 2
TRUNCATE(0.005 + (SIGN(0.005) * 0.005),2) = TRUNCATE(0.010,2) = 0.01

for three decimals it would be
TRUNCATE(num + (SIGN(num) * 0.0005), 3)
etc.

BTW this seems to be how PHP's round function works, so if you are trying to get calculations in PHP to match MySQL this is how I did it.

Posted by Tim Reynolds on April 15, 2006

Am I mistaken about the command for an integer ranged RAND function...

Given what is printed here:
FLOOR(i + RAND() * (j - i))
I only ever get results in the range of i to j-1.
Shouldn't it be
FLOOR(i + RAND() * (j - i + 1 )) ?
I am getting results in the range I need with that. Maybe I am missing something, maybe once in a great while there will be a result that is j+1 and I have just not seen it.

BTW, I am using it as:
CREATE FUNCTION IRAND(param1 INT, param2 INT) RETURNS INT
RETURN FLOOR(param1 + RAND() * (param2-param1+1)) ;

Posted by Hans on May 31, 2006

That is true. The RAND() function returns a value 0.0 <= x <= 1.0
Thus, the values '0.0' and '1.0' can be returned althoug the changes are very very little.
In the example, where one wants a value between 7 and 12 inclusive, the value of '12' will hardly ever be returned.
I wanted a value of '0' or '1' (i.e. yes or no), so I used FLOOR(RAND() + 0.5), cuz if I'd used FLOOR(i + RAND() * (j – i), i.e. FLOOR(0 + RAND() * (1 – 0)) which evaluates to (FLOOR(RAND()), I would have gotten only one '1' and a trillillizillion 0's.

Posted by Florian Paulus on July 11, 2006

ROUND(X,Y)

ok i experienced like the description says different behaviour on rounding on different systems

so based on the examples by other ppl who might work for their issue but are neither save nor a
general purpose solution i have come up with my own solution for rounding up on 5

the number of decimal places you want : X
number : Y

general solution :

TRUNCATE((Y+SIGN(Y)*(POW(10,(1-X))/18)),X)

example (the other solutions fail here) :

y = 12.449
x = 1
result : 12.5

hope this helps you too

Posted by James Puddicombe on February 1, 2007

Simple but effective function for rounding to two decimals correctly (eg. 0.625 rounds to 0.63), unlike with the broken 'round' function

CREATE FUNCTION `v_round`(round_me DOUBLE)
RETURNS decimal(10,2)
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
BEGIN
return round_me;
END;

Posted by michael brenden on February 13, 2007

Since using MySQL's RAND() function on a large rowset is notoriously slow:

To quickly select a random row, basically, do it in two SELECTS:

1. first SELECT finds out number of rows available, usnig a WHERE clause if desired.

2. web code chooses a random row from the number of rows (from step 1.) and saves this number in $x.

3. second SELECT (using the same WHERE clause in step 1.) uses LIMIT 1,$x.

Posted by József Rekedt-Nagy on March 28, 2007

Actually Order by Rand() Limit(1,X) won't work on larger sets, as it has to read through X-1 records to return the 1 you need.
In avarage it means reading through <NumberOfRecords>/2 records every time, thus it's slow.

Posted by Nate Wiger on April 8, 2007

Yes, using limit is a silly way of doing that. Why not just select with id = the random id you picked?

Here's some Ruby:

max = dbh.query("select max(id) from table").fetch_row.first
rand_id = rand(max)
row = dbh.query("select * from table where id = #{rand_id}").fetch_hash
puts "Fetched: #{row['id']}"

Posted by Hero Ulster Magoncia on April 13, 2007

Nate:
Doing it that way doesn't work for everyone, some id values less than the max id might no longer exist in the table due to deletes.

Here's a simple solution (in php):

mysql_query('START TRANSACTION');

$count=mysql_fetch_row(mysql_query('SELECT COUNT(*) FROM table'));

$randomRow=mysql_fetch_row(mysql_query('SELECT * FROM table LIMIT '.(mt_rand(0,$count[0]-1)).',1'));

mysql_query('ROLLBACK');

Haven't really tested it, but you'd get the idea.
It's similar to michael's idea (posted above), only he had the limit parameters in the wrong order.

Posted by Mike McKee on February 11, 2008

Instead of using RAND and LIMIT tricks for randomness, with their limitations on speed, if you have a primary key that's an auto-incrementer, you could do it with these two SELECT like so:

SELECT MAX(pkey) FROM articles;

...then grab a random number (shown as $r below) between 1 and max in your code. Now return back to SQL like so:

SELECT * FROM articles where pkey > $r LIMIT $limit;

...where $limit is the number of rows you want to likely return.

...Then, to create the illusion of more randomness, just use an ORDER BY clause on the second SELECT above based on something arbitrary. For instance, if 'articles' has a column like author name and another like category, you could change the SELECT statement above like:

SELECT * FROM articles where pkey > $r ORDER BY category, name LIMIT $limit;

...So, by using this strategy, it's faster than having to randomly determine your pkeys and selecting only one record at a time.

In my case, I wanted to sort classified listings with some close approximation of randomness in order to rotate the listings, and this strategy has worked for me.

Posted by Gerhard Wolkerstorfer on April 4, 2008

After I had problems with the ROUND() function in an accounting application where i need commercial rounding I wrote this stored function that works very well for my needs:

CREATE FUNCTION ROUND_COMMERCIAL(value DOUBLE, preci INT(11)) RETURNS DOUBLE NO SQL
RETURN TRUNCATE((value * POW(10, preci)) + (IF(value = 0, 1,(value / ABS(value)))*(0.5 * POW(1, preci*-1))), 0) / POW(10, preci);

Posted by Szot Kamil on September 23, 2008

If you want to select more records randomly you can use following method:

SET @toGet=10;
SET @left=(SELECT COUNT(*) FROM tableName)+1;

SELECT *, @toGet:=@toGet-1
FROM tableName
WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left;

It's much faster than ORDER BY RAND() LIMIT 10 (especially if you want to fetch small random subset of rows stored in table) but if it happens to return same set of rows, it returns them always in same order. If you want them to have random order then you have to scramble them after fetching using subquery:

SET @toGet=10;
SET @left=(SELECT COUNT(*) FROM tableName)+1;
SELECT * FROM (
SELECT *, @toGet:=@toGet-1
FROM tableName
WHERE (@left:=@left-1)>0 AND RAND()<@toGet/@left
) t ORDER BY RAND()

or on client side.

Posted by Dave Turner on February 2, 2009

Oddly truncate produces 2 different results for the same mathematical function with variations in placement of truncate in relation to a conditional:

mysql> select version(),if(2.268>1.249,truncate(ceil(((2.268-1.249)/6+.01)*100)/100,2),'0.00') as fscpm;

+------------+-------+
| version()  | fscpm |
+------------+-------+
| 5.0.27-log | 0.18  | 
+------------+-------+

mysql> select version(),truncate(if(2.268>1.249,ceil(((2.268-1.249)/6+.01)*100)/100,'0.00'),2) as fscpm;

+------------+-------+
| version()  | fscpm |
+------------+-------+
| 5.0.27-log |  0.17 | 
+------------+-------+

Posted by Matthew Flaschen on March 28, 2009

Nearest even rounding is not bad. In fact, it results in less statistically biased results than always rounding up.

Posted by Guy Gordon on April 18, 2009

If you reached this page looking for functions like MIN(a,b,...) and MAX(a,b,...) they are named LEAST()and GREATEST(), and are in section 11.2.3. Comparison Functions and Operators.

Posted by Mike N on January 29, 2011

For those of you who need to implement banker's rounding in MySQL (handy if you're doing invoice reports and the numbers need to match up with accounting software like Simply Accounting that use banker's rounding), this is what I use:

CREATE DEFINER=`root`@`%` FUNCTION `BROUND`( value DECIMAL(65,30), places TINYINT(3) UNSIGNED ) RETURNS decimal(65,30) COMMENT 'WARNING over decimal(65,30) will round normally!'
DETERMINISTIC
RETURN
CASE WHEN
LOCATE( '.', value ) >= 1
AND LENGTH( SUBSTRING( value, LOCATE( '.', value ) +1 ) ) < 31
AND places > -1
AND LENGTH( value ) - LOCATE( '.', value ) > places
AND SUBSTRING( value, LOCATE( '.', value ) + places + 1, 1 ) = 5
AND SUBSTRING( value, LOCATE( '.', value ) + places + 2 ) = 0
AND SUBSTRING( value, LOCATE( '.', value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ), 1 ) % 2 = 1

THEN
SUBSTRING( value, 1, LOCATE( '.', value ) + places + (CASE WHEN places = 0 THEN -1 ELSE 0 END ) )
ELSE
ROUND( value, places )
END;

WARNINGS:

- The old function I had posted here before today was wrong, to anyone who used it I am deeply sorry.

- Also do not use Felipe's function below as it is broken because a correct BROUND(6.434503,3) function should indeed return 6.435, NOT 6.434, as there is a 3 to the right of the 5. However BROUND(6.434500,3) WILL return 6.434. In banker's rounding, the only difference between regular rounding occurs when what is being rounded either ends in a 5, or ends in a 5 with a few zeroes after it. If however you do want this incorrect behaviour for some reason, you can remove "AND SUBSTRING( value, LOCATE( '.', value ) + places + 2 ) = 0" from this or use Felipe's function instead.

- Note that if you pass a value greater than 29 into my second parameter you will get regular rounding, because the DECIMAL data type has a precision of 30, and to pass anything larger than 29 in the second parameter would mean you would have gone over that limit. To avoid getting a fatal error when doing this, and for simplicity, I used strings in this function, and made it fail over to ROUND() in this instance.

- Do your own testing first of course

Posted by Felipe Loredo on November 13, 2010

The BROUND posted by Mike N on March 9 2010 4:47pm doesn't works with bround(6.434503,3). The correct result is 6,435 but the function returns 6.434000000000000000000000000000.

Posted by Felipe Loredo on March 5, 2011

I've created this function for Bankers rounding:

CREATE FUNCTION BankersRound(Val DECIMAL(32,16), Digits INT)
RETURNS DECIMAL(32,16)
RETURN
IF(ABS(Val - ROUND(Val, Digits)) * POWER(10, Digits+1) = 5,
IF(CONVERT(TRUNCATE(ABS(Val) * POWER(10,Digits), 0),UNSIGNED) % 2,
ROUND(Val,Digits),
TRUNCATE(Val,Digits)
),
ROUND(Val, Digits)
);

The input test was

select BankersRound(1.346,3),BankersRound(4.735500,3),BankersRound(7.834500,3),BankersRound(2.983600,3),BankersRound(6.434503,3);

and the expected result

1.346 4.736 7.834 2.984 6.435

and i got

1.3460000000000000, 4.7360000000000000, 7.8340000000000000, 2.9840000000000000, 6.4350000000000000

on MySQL 5.1.42

Posted by Mike N on January 29, 2011

Before today, Felipe's comment above stating that my function was broken was correct. As of 2011-01-28, it is now correct, and actually his is wrong, though his was more correct than my function before I fixed it today. See my comment above for an explanation of why this is.

Posted by Felipe Loredo on March 5, 2011

I'm a bit confused. After some time thinking about your explanation and testing my function I still don't know why it's wrong. As you can see
select BankersRound(6.434503,3);
returns
6.4350000000000000
as there is a 3 to the right of the 5. So if can you help me to see why I'm wrong?

PS: Sorry for inconvenience, I don't want to be rude with you. For common purposes, for instace, if you have a decimal column (my case) you can use my version. If I'm not wrong it's probably faster. Although if you need more precision or work with bigger numbers you can use Mike's version. Finally I don't think we need to fight for this. ;-)

Sorry for the beautiful English

Posted by Károly Csabay on May 26, 2011

FLOOR, when its argument is a negative, is working in a mathematic manner. While FLOOR(2.5) returns 2, FLOOR(-2.5), however, returns -3. If you feel strange this behavior use @v-MOD(@v,1) instead of FLOOR.

SET @v = - 2.5;
SELECT @v - MOD( @v , 1 ) , FLOOR( @v )

You'll gain:

@v-MOD(@v,1) FLOOR(@v)
-2.000000000000000000000000000000 -3

Posted by Christopher Rigg-Milner on October 3, 2011

I had the need to do some Swedish Rounding on a value.
In order words I needed to round up or down to the nearest 5 cents.

It took me some time to work it out so I thought I would document it here in order to save somebody else the hair loss.

The basic formula was:-

ROUND( value / 5, 2 ) * 5.

My calc was a little more complex ...
ROUND( (v1 + ( v2 / 3 ) ) / 5, 2 ) * 5.

Some test result are:-

+--------+-------+------------+--------+
| v1     | v2    | simplecalc | answer |
+--------+-------+------------+--------+
|  46.25 | 24.50 |  54.416667 |  54.40 | // round down
|  46.25 | 44.05 |  60.933333 |  60.95 | // round up
|  79.15 | 24.50 |  87.316667 |  87.30 | // etc
|  79.15 | 44.05 |  93.833333 |  93.85 | 
| 111.10 | 24.50 | 119.266667 | 119.25 | 
| 111.10 | 44.05 | 125.783333 | 125.80 |
|  26.00 | 17.50 |  31.833333 |  31.85 |
|  45.50 | 15.50 |  50.666667 |  50.65 |
|  67.50 | 15.50 |  72.666667 |  72.65 |
|  26.00 | 31.55 |  36.516667 |  36.50 |
|  45.50 | 27.95 |  54.816667 |  54.80 |
|  67.50 | 27.95 |  76.816667 |  76.80 |
+--------+-------+------------+--------+

I was then asked to round everything UP to the nearest 5 cents.

The formula for this is:-

ROUND( ( ( v1 + ( v2 / 3 ) + 0.03 ) ) / 5, 2 ) * 5

+--------+-------+------------+--------+
| v1     | v2    | simplecalc | answer |
+--------+-------+------------+--------+
|  46.25 | 24.50 |  54.416667 |  54.45 |
|  46.25 | 44.05 |  60.933333 |  60.95 |
|  79.15 | 24.50 |  87.316667 |  87.35 |
|  79.15 | 44.05 |  93.833333 |  93.85 |
| 111.10 | 24.50 | 119.266667 | 119.30 |
| 111.10 | 44.05 | 125.783333 | 125.80 |
|  26.00 | 17.50 |  31.833333 |  31.85 |
|  45.50 | 15.50 |  50.666667 |  50.70 |
|  67.50 | 15.50 |  72.666667 |  72.70 |
|  26.00 | 31.55 |  36.516667 |  36.55 |
|  45.50 | 27.95 |  54.816667 |  54.85 |
|  67.50 | 27.95 |  76.816667 |  76.85 |
+--------+-------+------------+--------+

I do hope this helps somebody.

Posted by G Henle on May 7, 2012

Note: While CONV(N, from_base, to_base) does accept a string for N and returns a string. The return is still limited to type unsigned/signed bigint.

SET @N:= CAST(SHA1(RAND()) AS CHAR);
SELECT @N, CONV(@N, 16, 10), ~0 as max_bigint_unsigned;

+------------------------------------------+----------------------+----------------------+
| @N                                       | CONV(@N, 16, 10)     | max_bigint_unsigned  |
+------------------------------------------+----------------------+----------------------+
| 36cf9111723dba5bb0fe6e91465323d1390f252c | 18446744073709551615 | 18446744073709551615 | 
+------------------------------------------+----------------------+----------------------+

1 row in set (0.00 sec)