DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) Mathematical functions

Info Catalog (mysql.info.gz) Arithmetic functions (mysql.info.gz) Numeric Functions
 
 12.4.2 Mathematical Functions
 -----------------------------
 
 All mathematical functions return `NULL' in case of an error.
 
 `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.
 
 `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
 
 `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
 
      Note that the return value is converted to a `BIGINT'!
 
      The `CEIL()' alias was added in MySQL 4.0.6.
 
 `COS(X)'
      Returns the cosine of X, where X is given in radians.
 
           mysql> SELECT COS(PI());
                   -> -1.000000
 
 `COT(X)'
      Returns the cotangent of X.
 
           mysql> SELECT COT(12);
                   -> -1.57267341
           mysql> SELECT COT(0);
                   -> NULL
 
 `CRC32(EXPR)'
      Computes a cyclic redundancy check value and returns a 32-bit
      unsigned value.  The result is `NULL' if the argument is `NULL'.
      The argument is expected be a string and will be treated as one if
      it is not.
 
           mysql> SELECT CRC32('MySQL');
                   -> 3259397556
 
      `CRC32()' is available as of MySQL 4.1.0.
 
 `DEGREES(X)'
      Returns the argument X, converted from radians to degrees.
 
           mysql> SELECT DEGREES(PI());
                   -> 180.000000
 
 `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
 
 `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'!
 
 `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 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 arbitrary base B.
           mysql> SELECT LOG(2,65536);
                   -> 16.000000
           mysql> SELECT LOG(1,100);
                   -> NULL
 
      The arbitrary base option was added in MySQL 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 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
 
 `MOD(N,M)'
 `N % M'
 `N MOD M'
      Modulo operation.  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 `N MOD M'
      syntax works only as of MySQL 4.1.
 
      As of MySQL 4.1.7, `MOD()' works on values with a fractional part
      and returns the exact remainder after division:
 
           mysql> SELECT MOD(34.5,3);
                   -> 1.5
 
      Before MySQL 4.1.7, `MOD()' rounds arguments with a fractional
      value to integers and returns an integer result:
 
           mysql> SELECT MOD(34.5,3);
                   -> 2
 
 `PI()'
      Returns the value of PI. The default number of decimals displayed
      is five, but MySQL internally uses the full double-precision value
      for PI.
 
           mysql> SELECT PI();
                   -> 3.141593
           mysql> SELECT PI()+0.000000000000000000;
                   -> 3.141592653589793116
 
 `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
 
 `RADIANS(X)'
      Returns the argument X, converted from degrees to radians.
 
           mysql> SELECT RADIANS(90);
                   -> 1.570796
 
 `RAND()'
 `RAND(N)'
      Returns a random floating-point value in the range from `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.  As of MySQL 3.23, 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 of a set of rows:
 
           mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d
               -> ORDER BY RAND() LIMIT 1000;
 
      Note that `RAND()' in a `WHERE' clause is 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.
 
 `ROUND(X)'
 `ROUND(X,D)'
      Returns the argument X, rounded to the nearest integer.  With two
      arguments, returns X rounded to D decimals.  If D is negative, the
      integer part of the number is zeroed out.
 
           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 behavior of `ROUND()' 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()' or `FLOOR()' instead.
 
 `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
 
 `SIN(X)'
      Returns the sine of X, where X is given in radians.
 
           mysql> SELECT SIN(PI());
                   -> 0.000000
 
 `SQRT(X)'
      Returns the non-negative square root of X.
 
           mysql> SELECT SQRT(4);
                   -> 2.000000
           mysql> SELECT SQRT(20);
                   -> 4.472136
 
 `TAN(X)'
      Returns the tangent of X, where X is given in radians.
 
           mysql> SELECT TAN(PI()+1);
                   -> 1.557408
 
 `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.  If D is
      negative, the integer part of the number is zeroed out.
 
           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
 
      Starting from MySQL 3.23.51, all numbers are rounded toward zero.
 
      Note that decimal numbers are normally not stored as exact numbers
      in computers, but as double-precision values, so you may be
      surprised by the following result:
 
           mysql> SELECT TRUNCATE(10.28*100,0);
                  -> 1027
 
      This happens because `10.28' is actually stored as something like
      `10.2799999999999999'.
 
 
Info Catalog (mysql.info.gz) Arithmetic functions (mysql.info.gz) Numeric Functions
automatically generated byinfo2html