ABS(X)

返回 X 的绝对值，如果 X 为 NULL，则返回 NULL。Returns the absolute value of X, or NULL if X is NULL.

结果类型由参数类型决定。这意味着 ABS(-9223372036854775808) 会产生错误，因为结果无法存储在有符号 BIGINT 值中。The result type is derived from the argument type. An implication of this is that ABS(-9223372036854775808) produces an error because the result cannot be stored in a signed BIGINT value.

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

此函数可安全用于BIGINT 值。This function is safe to use with BIGINT values.

ACOS(X)

返回 X 的反余弦，即余弦值为 X 的值。如果 X 不在 -1 到 1 范围内，或者 X 为 NULL，则返回 NULL。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, or if X is 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 的范围内，或者 X 为 NULL，则返回 NULL。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, or if X is 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 的值。如果 X 为 NULL，则返回 NULLReturns the arc tangent of X, that is, the value whose tangent is X. Returns NULL if X is NULL

mysql> SELECT ATAN(2);
        -> 1.1071487177941
mysql> SELECT ATAN(-2);
        -> -1.1071487177941

ATAN(Y,X), ATAN2(Y,X)

返回两个变量 X 和 Y 的反正切。它类似于计算 Y / X 的反正切，不同之处在于两个参数的符号用于确定结果的象限。如果 X 或 Y 为 NULL，则返回 NULL。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. Returns NULL if X or Y is NULL.

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

CEIL(X)

CEIL() 是 CEILING() 的同义词。CEIL() is a synonym for CEILING().

CEILING(X)

返回不小于 X 的最小整数值。如果 X 为 NULL，则返回 NULL。Returns the smallest integer value not less than X. Returns NULL if X is NULL.

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)

在不同的数字进制之间转换数字。返回数字 N 的字符串表示形式，该数字已从进制 from_base 转换为进制 to_base。如果任何参数为 NULL，则返回 NULL。参数 N 被视为整数，但可以以整数或字符串的形式指定。最小进制为 2，最大进制为 36。如果 from_base 是负数，则 N 被视为有符号数。否则，N 被视为无符号数。CONV() 支持 64 位精度。Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If from_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

CONV() 如果其任何参数为 NULL，则返回 NULL。CONV() returns NULL if any of its arguments are NULL.

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'+X'0a',10,10);
        -> '40'

COS(X)

返回 X 的余弦值，其中 X 以弧度为单位。 如果 X 为 NULL，则返回 NULL。Returns the cosine of X, where X is given in radians. Returns NULL if X is NULL.

mysql> SELECT COS(PI());
        -> -1

COT(X)

返回 X 的余切。 如果 X 为 NULL，则返回 NULL。Returns the cotangent of X. Returns NULL if X is NULL.

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

CRC32(expr)

计算循环冗余校验值并返回一个 32 位无符号值。如果参数为 NULL，则结果为 NULL。参数预期为字符串，如果可能的话，如果它不是字符串，也会被当作字符串处理。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 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)

返回参数 X，将其从弧度转换为度。如果 X 为 NULL，则返回 NULL。Returns the argument X, converted from radians to degrees. Returns NULL if X is NULL.

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

EXP(X)

返回 e（自然对数的底数）X次幂的值。此函数的逆函数是 LOG()（仅使用单个参数）或 LN()。Returns the value of e (the base of natural logarithms) raised to the power of X. The inverse of this function is LOG() (using a single argument only) or LN().

如果 X 为 NULL，此函数返回 NULL。If X is NULL, this function returns NULL.

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

FLOOR(X)

返回不大于X的最大整数值。如果X为NULL，则返回NULL。Returns the largest integer value not greater than X. Returns NULL if X is NULL.

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)

将数字X格式化为类似'#,###,###.##'的格式，四舍五入到D位小数，并将结果作为字符串返回。详情请参见第 14.8 节，“字符串函数和操作符”。Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 14.8, “String Functions and Operators”.

HEX(N_or_S)

此函数可用于获取十进制数字或字符串的十六进制表示；其实现方式取决于参数的类型。有关详细信息，请参见本函数在第 14.8 节，“字符串函数和操作符”中的描述。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 in Section 14.8, “String Functions and Operators”, for details.

LN(X)

返回的自然对数X；即以e为底的对数X。如果X小于或等于 0.0E0，函数将返回NULL并生成警告“对数的参数无效”。如果NULL返回X is NULL.Returns the natural logarithm of X; that is, the base-e logarithm of X. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported. Returns NULL if X is NULL.

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

此函数与LOG(X)同义。此函数的反函数是EXP()函数。This function is synonymous with LOG(X). The inverse of this function is the EXP() function.

LOG(X), LOG(B,X)

如果只调用一个参数，该函数返回 X 的自然对数。如果 X 小于或等于 0.0E0，函数返回 NULL 并报告警告 “对数的参数无效”。如果 X 或 B 为 NULL，则返回 NULL。If called with one parameter, this function returns the natural logarithm of X. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported. Returns NULL if X or B is NULL.

这个函数的逆函数（当只传入一个参数时）是EXP()函数。The inverse of this function (when called with a single argument) is the EXP() function.

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

如果传入两个参数，该函数返回X以B为底的对数。如果X小于或等于 0，或者B小于或等于 1，则返回NULL。If called with two parameters, this function returns the logarithm of X to the base B. If X is less than or equal to 0, or if B is less than or equal to 1, then NULL is 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)。LOG(B,X) is equivalent to LOG(X) / LOG(B).

LOG2(X)

返回X的以 2 为底的对数。如果X小于或等于 0.0E0，则该函数返回NULL，并报告警告“对数的参数无效”。如果X为NULL，则返回NULL。Returns the base-2 logarithm of X. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported. Returns NULL if X is NULL.

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

LOG2() 对于确定一个数字存储所需的位数非常有用。该函数大致等同于表达式 LOG(X) / LOG(2)。LOG2() is useful for finding out how many bits a number requires for storage. This function is approximately equivalent to the expression LOG(X) / LOG(2).

LOG10(X)

返回 X 的以 10 为底的对数。如果 X 小于或等于 0.0E0，该函数返回 NULL 并报告警告 “对数的参数无效”。如果 X 为 NULL，则返回 NULL。Returns the base-10 logarithm of X. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported. Returns NULL if X is NULL.

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

LOG10(X) 大致相当于 LOG(10,X)。LOG10(X) is approximately equivalent to LOG(10,X).

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

取模运算。返回 N 除以 M 的余数。如果 M 或 N 为 NULL，则返回 NULL。Modulo operation. Returns the remainder of N divided by M. Returns NULL if M or N is NULL.

mysql> SELECT MOD(234, 10);
        -> 4
mysql> SELECT 253 % 7;
        -> 1
mysql> SELECT MOD(29,9);
        -> 2
mysql> SELECT 29 MOD 9;
        -> 2

此函数可安全用于BIGINT 值。This function is safe to use with BIGINT values.

MOD() 也适用于有小数部分的值，并返回除法后的精确余数：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) 返回 NULL。MOD(N,0) returns NULL.

PI()

返回π（圆周率）的值。默认显示的小数位数为七位，但 MySQL 在内部使用全双精度值。Returns the value of π (pi). The default number of decimal places displayed is seven, but MySQL uses the full double-precision value internally.

由于此函数的返回值是双精度值，其精确表示可能因平台或实现而异。这同样适用于使用PI()的任何表达式。请参阅第 13.1.4 节，“浮点类型（近似值）- FLOAT, DOUBLE”。Because the return value of this function is a double-precision value, its exact representation may vary between platforms or implementations. This also applies to any expressions making use of PI(). See Section 13.1.4, “Floating-Point Types (Approximate Value) - FLOAT, DOUBLE”.

mysql> SELECT PI();
        -> 3.141593
mysql> SELECT PI()+0.000000000000000000;
        -> 3.141592653589793000

POW(X,Y)

返回X的Y次幂的值。如果X或Y为NULL，则返回NULL。Returns the value of X raised to the power of Y. Returns NULL if X or Y is NULL.

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

POWER(X,Y)

这是 POW() 的同义词。This is a synonym for POW().

RADIANS(X)

返回参数 X，将其从度转换为弧度。（注意 π 弧度等于 180 度。）如果 X 为 NULL，则返回 NULL。Returns the argument X, converted from degrees to radians. (Note that π radians equals 180 degrees.) Returns NULL if X is NULL.

mysql> SELECT RADIANS(90);
        -> 1.5707963267949

RAND([N])

返回一个随机浮点数 v，范围为 0 <= v < 1.0。要获得范围为 i <= R < j 的随机整数 R，使用表达式 FLOOR(i + RAND() * (j − i))。例如，要获得范围为 7 <= R < 12 的随机整数，请使用以下语句：Returns a random floating-point value v in the range 0 <= v < 1.0. To obtain a random integer R in the range i <= R < j, use the expression FLOOR(i + RAND() * (j − i)). For example, to obtain a random integer in the range the range 7 <= R < 12, use the following statement:

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

如果指定了一个整数参数N，它将作为种子值使用：If an integer argument N is specified, it is used as the seed value:

这种行为的一个含义是，对于相等的参数值，RAND(N) 每次都会返回相同的值，从而生成可重复的列值序列。在以下示例中，RAND(3) 产生的值序列在它出现的两个地方都是相同的。One implication of this behavior is that for equal argument values, RAND(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 RAND(3) is the same both places it occurs.

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() 在 WHERE 子句中会对每一行（当从一个表中选择时）或每一行组合（当从多个表联接中选择时）进行计算。因此，对于优化器而言，RAND() 不是一个常量值，不能用于索引优化。更多信息，请参见 第 10.2.1.20 节，“函数调用优化”。RAND() in a WHERE clause 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, RAND() is not a constant value and cannot be used for index optimizations. For more information, see Section 10.2.1.20, “Function Call Optimization”.

在 ORDER BY 或 RAND() 子句中使用带有 RAND() 值的列可能会产生意外结果，因为对于任一子句，GROUP BY 表达式可能会对同一行多次计算，每次返回不同的结果。如果目标是以随机顺序检索行，可以使用类似以下的语句：Use of a column with RAND() values in an ORDER BY or GROUP BY clause may yield unexpected results because for either clause a RAND() 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 * FROM tbl_name ORDER BY RAND();

要从一组行中选择随机样本，可以将 ORDER BY RAND() 与 LIMIT 结合使用：To select a random sample from a set of rows, combine ORDER BY RAND() with LIMIT:

SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;

RAND() 不旨在成为完美的随机生成器。它是一种快速生成随需随机数的方法，对于相同 MySQL 版本来说在各个平台之间是可移植的。RAND() 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.

该函数在基于语句的复制中不安全。如果在 binlog_format 设置为 STATEMENT 时使用此函数，将记录一个警告。This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.

ROUND(X), ROUND(X,D)

将参数 X 四舍五入到 D 位小数。四舍五入算法取决于 X 的数据类型。如果未指定，D 默认为 0。D 可以为负数，使 X 值的小数点左侧的 D 位变为零。D 的最大绝对值为 30；超过 30（或 -30）的位数将被截断。如果 X 或 D 为 NULL，函数将返回 NULL。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. The maximum absolute value for D is 30; any digits in excess of 30 (or -30) are truncated. If X or D is NULL, the function returns NULL.

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() 根据第一个参数的类型使用以下规则：ROUND() uses the following rules depending on the type of the first argument:

以下示例显示了精确值和近似值的舍入方式有何不同：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 |
+------------+--------------+

有关更多信息，请参见 第 14.24 节，“精确数学”。For more information, see Section 14.24, “Precision Math”.

ROUND()（以及 TRUNCATE()）返回的数据类型根据此处列出的规则确定：The data type returned by ROUND() (and TRUNCATE()) is determined according to the rules listed here:

SIGN(X)

返回参数符号为，-1, 0，或1取决于X是负、零还是正。返回NULL返回X is NULL.Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive. Returns NULL if X is NULL.

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

SIN(X)

返回的正弦X，其中X以弧度表示。返回NULL返回X is NULL.Returns the sine of X, where X is given in radians. Returns NULL if X is NULL.

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

SQRT(X)

返回非负数 X 的平方根。如果 X 为 NULL，该函数返回 NULL。Returns the square root of a nonnegative number X. If X is NULL, the function returns NULL.

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

TAN(X)

返回 X 的正切，其中 X 以弧度为单位。如果 X 为 NULL，则返回 NULL。Returns the tangent of X, where X is given in radians. Returns NULL if X is NULL.

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

TRUNCATE(X,D)

返回数字 X，截断到 D 位小数。如果 D 为 0，结果没有小数点或小数部分。D 可以为负，使得 X 值的小数点左侧 D 位变为零。如果 X 或 D 为 NULL，函数返回 NULL。Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part. D can be negative to cause D digits left of the decimal point of the value X to become zero. If X or D is NULL, the function returns NULL.

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.

TRUNCATE() 返回的数据类型遵循与 ROUND() 函数返回类型相同的规则；详情请参见 ROUND() 的描述。The data type returned by TRUNCATE() follows the same rules that determine the return type of the ROUND() function; for details, see the description for ROUND().