10bet网址
MySQL 8.0 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual

12.6.2 Mathematical Functions

Table 12.10 Mathematical Functions

Name Description
ABS() Return the absolute value
ACOS() Return the arc cosine
ASIN() Return the arc sine
ATAN() Return the arc tangent
ATAN2(),ATAN() Return the arc tangent of the two arguments
CEIL() Return the smallest integer value not less than the argument
CEILING() Return the smallest integer value not less than the argument
CONV() Convert numbers between different number bases
COS() Return the cosine
COT() Return the cotangent
CRC32() Compute a cyclic redundancy check value
DEGREES() Convert radians to degrees
EXP() Raise to the power of
FLOOR() Return the largest integer value not greater than the argument
LN() Return the natural logarithm of the argument
LOG() 返回第一个参数的自然对数
LOG10() Return the base-10 logarithm of the argument
LOG2() Return the base-2 logarithm of the argument
MOD() Return the remainder
PI() Return the value of pi
POW() Return the argument raised to the specified power
POWER() Return the argument raised to the specified power
RADIANS() Return argument converted to radians
兰特() Return a random floating-point value
ROUND() Round the argument
SIGN() Return the sign of the argument
SIN() Return the sine of the argument
SQRT() Return the square root of the argument
TAN() Return the tangent of the argument
TRUNCATE() Truncate to specified number of decimal places

All mathematical functions returnNULLin the event of an error.

  • ABS(X)

    Returns the absolute value ofX, orNULLifXisNULL.

    The result type is derived from the argument type. An implication of this is thatABS(-9223372036854775808)produces an error because the result cannot be stored in a signedBIGINTvalue.

    mysql> SELECT ABS(2); -> 2 mysql> SELECT ABS(-32); -> 32

    This function is safe to use withBIGINTvalues.

  • ACOS(X)

    Returns the arc cosine ofX, that is, the value whose cosine isX. ReturnsNULLifXis not in the range-1to1.

    mysql> SELECT ACOS(1); -> 0 mysql> SELECT ACOS(1.0001); -> NULL mysql> SELECT ACOS(0); -> 1.5707963267949
  • ASIN(X)

    Returns the arc sine ofX, that is, the value whose sine isX. ReturnsNULLifXis not in the range-1to1.

    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)

    Returns the arc tangent ofX, that is, the value whose tangent isX.

    mysql> SELECT ATAN(2); -> 1.1071487177941 mysql> SELECT ATAN(-2); -> -1.1071487177941
  • ATAN(Y,X),ATAN2(Y,X)

    Returns the arc tangent of the two variablesXandY. It is similar to calculating the arc tangent ofY/X, except that the signs of both arguments are used to determine the quadrant of the result.

    mysql> SELECT ATAN(-2,2); -> -0.78539816339745 mysql> SELECT ATAN2(PI(),0); -> 1.5707963267949
  • CEIL(X)

    CEIL()is a synonym forCEILING().

  • CEILING(X)

    Returns the smallest integer value not less thanX.

    mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1

    For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

  • CONV(N,from_base,to_base)

    Converts numbers between different number bases. Returns a string representation of the numberN, converted from basefrom_baseto baseto_base. ReturnsNULLif any argument isNULL. The argumentNis interpreted as an integer, but may be specified as an integer or a string. The minimum base is2and the maximum base is36. Iffrom_baseis a negative number,Nis regarded as a signed number. Otherwise,Nis treated as unsigned.CONV()works with 64-bit precision.

    mysql >选择CONV (' a ', 16日2);——>“1010”mysql >选取ECT CONV('6E',18,8); -> '172' mysql> SELECT CONV(-17,10,-18); -> '-H' mysql> SELECT CONV(10+'10'+'10'+X'0a',10,10); -> '40'
  • COS(X)

    Returns the cosine ofX, whereXis given in radians.

    mysql> SELECT COS(PI()); -> -1
  • COT(X)

    Returns the cotangent ofX.

    mysql> SELECT COT(12); -> -1.5726734063977 mysql> SELECT COT(0); -> out-of-range error
  • CRC32(expr)

    Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result isNULLif the argument isNULL. The argument is expected to be a string and (if possible) is treated as one if it is not.

    mysql> SELECT CRC32('MySQL'); -> 3259397556 mysql> SELECT CRC32('mysql'); -> 2501908538
  • DEGREES(X)

    Returns the argumentX, converted from radians to degrees.

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

    Returns the value ofe(the base of natural logarithms) raised to the power ofX. The inverse of this function isLOG()(using a single argument only) orLN().

    mysql> SELECT EXP(2); -> 7.3890560989307 mysql> SELECT EXP(-2); -> 0.13533528323661 mysql> SELECT EXP(0); -> 1
  • FLOOR(X)

    Returns the largest integer value not greater thanX.

    mysql> SELECT FLOOR(1.23), FLOOR(-1.23); -> 1, -2

    For exact-value numeric arguments, the return value has an exact-value numeric type. For string or floating-point arguments, the return value has a floating-point type.

  • FORMAT(X,D)

    Formats the numberXto a format like'#,###,###.##', rounded toDdecimal places, and returns the result as a string. For details, seeSection 12.8, “String Functions and Operators”.

  • HEX(N_or_S)

    This function can be used to obtain a hexadecimal representation of a decimal number or a string; the manner in which it does so varies according to the argument's type. See this function's description inSection 12.8, “String Functions and Operators”, for details.

  • LN(X)

    Returns the natural logarithm ofX; that is, the base-elogarithm ofX. IfXis less than or equal to 0.0E0, the function returnsNULLand a warningInvalid argument for logarithmis reported.

    mysql> SELECT LN(2); -> 0.69314718055995 mysql> SELECT LN(-2); -> NULL

    This function is synonymous withLOG(X). The inverse of this function is theEXP()function.

  • LOG(X),LOG(B,X)

    If called with one parameter, this function returns the natural logarithm ofX. IfXis less than or equal to 0.0E0, the function returnsNULLand a warningInvalid argument for logarithmis reported.

    The inverse of this function (when called with a single argument) is theEXP()function.

    mysql> SELECT LOG(2); -> 0.69314718055995 mysql> SELECT LOG(-2); -> NULL

    If called with two parameters, this function returns the logarithm ofXto the baseB. IfXis less than or equal to 0, or ifBis less than or equal to 1, thenNULLis returned.

    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)

    Returns the base-2 logarithm ofX. IfXis less than or equal to 0.0E0, the function returnsNULLand a warningInvalid argument for logarithmis reported.

    mysql> SELECT LOG2(65536); -> 16 mysql> SELECT LOG2(-100); -> NULL

    LOG2()is useful for finding out how many bits a number requires for storage. This function is equivalent to the expressionLOG(X) / LOG(2).

  • LOG10(X)

    Returns the base-10 logarithm ofX. IfXis less than or equal to 0.0E0, the function returnsNULLand a warningInvalid argument for logarithmis reported.

    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,NMODM

    Modulo operation. Returns the remainder ofNdivided byM.

    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 withBIGINTvalues.

    MOD()also works on values that have a fractional part and returns the exact remainder after division:

    mysql> SELECT MOD(34.5,3); -> 1.5

    MOD(N,0)returnsNULL.

  • PI()

    Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.

    mysql> SELECT PI(); -> 3.141593 mysql> SELECT PI()+0.000000000000000000; -> 3.141592653589793116
  • POW(X,Y)

    Returns the value ofXraised to the power ofY.

    mysql> SELECT POW(2,2); -> 4 mysql> SELECT POW(2,-2); -> 0.25
  • POWER(X,Y)

    This is a synonym forPOW().

  • RADIANS(X)

    Returns the argumentX, converted from degrees to radians. (Note that π radians equals 180 degrees.)

    mysql> SELECT RADIANS(90); -> 1.5707963267949
  • 兰特([N])

    Returns a random floating-point valuevin the range0<=v<1.0. To obtain a random integerRin the rangei<=R<j, use the expressionFLOOR(i+ RAND() * (ji)). For example, to obtain a random integer in the range the range7<=R<12, use the following statement:

    SELECT FLOOR(7 + (RAND() * 5));

    If an integer argumentNis specified, it is used as the seed value:

    • With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.

    • With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of兰特().

    One implication of this behavior is that for equal argument values,兰特(N)returns the same value each time, and thus produces a repeatable sequence of column values. In the following example, the sequence of values produced by兰特(3)is the same both places it occurs.

    mysql > t(我在创建表T); 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)

    兰特()in aWHEREclause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join). Thus, for optimizer purposes,兰特()is not a constant value and cannot be used for index optimizations. For more information, seeSection 8.2.1.20, “Function Call Optimization”.

    Use of a column with兰特()values in anORDER BYorGROUP BYclause may yield unexpected results because for either clause a兰特()expression can be evaluated multiple times for the same row, each time returning a different result. If the goal is to retrieve rows in random order, you can use a statement like this:

    SELECT * FROMtbl_nameORDER BY RAND();

    To select a random sample from a set of rows, combineORDER BY RAND()withLIMIT:

    SELECT * FROM table1, table2 WHERE a=b AND c

    兰特()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.

    This function is unsafe for statement-based replication. A warning is logged if you use this function whenbinlog_formatis set toSTATEMENT.

  • ROUND(X),ROUND(X,D)

    Rounds the argumentXtoDdecimal places. The rounding algorithm depends on the data type ofX.Ddefaults to 0 if not specified.Dcan be negative to causeDdigits left of the decimal point of the valueXto become zero. The maximum absolute value forDis 30; any digits in excess of 30 (or -30) are truncated.

    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 mysql> SELECT ROUND(.12345678901234567890123456789012345, 35); -> 0.123456789012345678901234567890

    The return value has the same type as 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 | +------------------+--------------+

    ROUND()uses the following rules depending on the type of the first argument:

    • For exact-value numbers,ROUND()uses theround half away from zeroorround toward nearestrule: A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. (In other words, it is rounded away from zero.) A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

    • For approximate-value numbers, the result depends on the C library. On many systems, this means thatROUND()uses the甚至转到最近的rule: A value with a fractional part exactly halfway between two integers is rounded to the nearest even integer.

    The following example shows how rounding differs for exact and approximate values:

    mysql> SELECT ROUND(2.5), ROUND(25E-1); +------------+--------------+ | ROUND(2.5) | ROUND(25E-1) | +------------+--------------+ | 3 | 2 | +------------+--------------+

    For more information, seeSection 12.25, “Precision Math”.

    In MySQL 8.0.21 and later, the data type returned byROUND()(andTRUNCATE()) is determined according to the rules listed here:

    • When the first argument is of any integer type, the return type is alwaysBIGINT.

    • When the first argument is of any floating-point type or of any non-numeric type, the return type is alwaysDOUBLE.

    • When the first argument is aDECIMALvalue, the return type is alsoDECIMAL.

    • The type attributes for the return value are also copied from the first argument, except in the case ofDECIMAL, when the second argument is a constant value.

      When the desired number of decimal places is less than the scale of the argument, the scale and the precision of the result are adjusted accordingly.

      In addition, forROUND()(but not for theTRUNCATE()function), the precision is extended by one place to accomodate rounding that increases the number of significant digits. If the second argument is negative, the return type is adjusted such that its scale is 0, with a corresponding precision. For example,ROUND(99.999, 2)returns100.00—the first argument isDECIMAL(5, 3), and the return type isDECIMAL(5, 2).

      If the second argument is negative, the return type has scale 0 and a corresponding precision;ROUND(99.999, -1)returns100, which isDECIMAL(3, 0).

  • SIGN(X)

    Returns the sign of the argument as-1,0, or1, depending on whetherXis negative, zero, or positive.

    mysql> SELECT SIGN(-32); -> -1 mysql> SELECT SIGN(0); -> 0 mysql> SELECT SIGN(234); -> 1
  • SIN(X)

    Returns the sine ofX, whereXis given in radians.

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

    Returns the square root of a nonnegative numberX.

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

    Returns the tangent ofX, whereXis given in radians.

    mysql> SELECT TAN(PI()); -> -1.2246063538224e-16 mysql> SELECT TAN(PI()+1); -> 1.5574077246549
  • TRUNCATE(X,D)

    Returns the numberX, truncated toDdecimal places. IfDis0, the result has no decimal point or fractional part.Dcan be negative to causeDdigits left of the decimal point of the valueXto become zero.

    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

    All numbers are rounded toward zero.

    In MySQL 8.0.21 and later, the data type returned byTRUNCATE()follows the same rules that determine the return type of theROUND()function; for details, see the description forROUND().