MySQLノートの数学の関数の詳しい解

絶対値関数ABS(x)と円周率関数PI()

 
  
mysql> SELECT ABS(0.5), ABS(-0.5), PI(); 
  
+----------+-----------+----------+ 
  
| ABS(0.5) | ABS(-0.5) | PI()     | 
  
+----------+-----------+----------+ 
  
|      0.5 |       0.5 | 3.141593 | 
  
+----------+-----------+----------+ 
  
 row in set (0.00 sec) 
  
 
 

平方根関数SQRT(x)と余剰関数MOD(x,y)を求める

 
  
mysql> SELECT SQRT(16), SQRT(3), MOD(13,4); 
  
+----------+--------------------+-----------+ 
  
| SQRT(16) | SQRT(3)            | MOD(13,4) | 
  
+----------+--------------------+-----------+ 
  
|        4 | 1.7320508075688772 |         1 | 
  
+----------+--------------------+-----------+ 
  
 row in set (0.00 sec) 
  
 
 

整数関数CEIL(x)、CEILING(x)、FLOOR(x)

 
  
mysql> SELECT CEIL(2.3), CEIL(-2.3), CEILING(2.3), CEILING(-2.3); 
  
+-----------+------------+--------------+---------------+ 
  
| CEIL(2.3) | CEIL(-2.3) | CEILING(2.3) | CEILING(-2.3) | 
  
+-----------+------------+--------------+---------------+ 
  
|         3 |         -2 |            3 |            -2 | 
  
+-----------+------------+--------------+---------------+ 
  
 row in set (0.00 sec) 
   
  
mysql> SELECT FLOOR(2.3), FLOOR(-2.3);
+------------+-------------+
| FLOOR(2.3) | FLOOR(-2.3) |
+------------+-------------+
|          2 |          -3 |
+------------+-------------+
 row in set (0.00 sec)
 
 

CEIL(x)およびCEILING(x)は、x以上の最小整数を返します.
FLOOR(x)は、x以下の最大整数を返す
乱数関数RAND()とRAND(x)

 
  
mysql> SELECT RAND(), RAND(2), RAND(2); 
  
+--------------------+--------------------+--------------------+ 
  
| RAND()             | RAND(2)            | RAND(2)            | 
  
+--------------------+--------------------+--------------------+ 
  
| 0.8269294489425881 | 0.6555866465490187 | 0.6555866465490187 | 
  
+--------------------+--------------------+--------------------+ 
  
 row in set (0.00 sec) 
  
 
 

RAND()とRAND(x)の2つの関数が失われて0～1の乱数を返す
違いは、RAND()が返す数は完全にランダムであり、RAND(x)がxと同時に返す値は同じである
四捨五入関数ROUND(x)、ROUND(x,y)、TRUNCATE(x,y)

 
  
mysql> SELECT ROUND(2.3), ROUND(2.5), ROUND(2.53,1), ROUND(2.55,1); 
  
+------------+------------+---------------+---------------+ 
  
| ROUND(2.3) | ROUND(2.5) | ROUND(2.53,1) | ROUND(2.55,1) | 
  
+------------+------------+---------------+---------------+ 
  
|          2 |          3 |           2.5 |           2.6 | 
  
+------------+------------+---------------+---------------+ 
  
 row in set (0.00 sec) 
  
 
 

ROUND(x)は、xに最も近い整数、すなわちxを四捨五入処理する
ROUND(x,y)は、xが小数点以下のyビットに保持する値を返し、切り取り時に四捨五入処理を行う

 
  
mysql> SELECT TRUNCATE(2.53,1), TRUNCATE(2.55,1); 
  
+------------------+------------------+ 
  
| TRUNCATE(2.53,1) | TRUNCATE(2.55,1) | 
  
+------------------+------------------+ 
  
|              2.5 |              2.5 | 
  
+------------------+------------------+ 
  
 row in set (0.00 sec) 
  
 
 

TRUNCATE(x,y)は、xが小数点以下のyビットに保持する値を返し、四捨五入操作を行わない
シンボル関数SIGN(x)

 
  
mysql> SELECT SIGN(-2), SIGN(0), SIGN(2); 
  
+----------+---------+---------+ 
  
| SIGN(-2) | SIGN(0) | SIGN(2) | 
  
+----------+---------+---------+ 
  
|       -1 |       0 |       1 | 
  
+----------+---------+---------+ 
  
 row in set (0.00 sec) 
  
 
 

SIGN(x)はxの符号を返し、-1は負数、0は不変、1は整数
べき乗演算関数POW(x,y)、POWER(x,y)

 
  
mysql> SELECT POW(3,2), POWER(3,2); 
  
+----------+------------+ 
  
| POW(3,2) | POWER(3,2) | 
  
+----------+------------+ 
  
|        9 |          9 | 
  
+----------+------------+ 
  
 row in set (0.00 sec)