If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true.

If both arguments in a comparison operation are strings, they are compared as strings.

If both arguments are integers, they are compared as integers.

Hexadecimal values are treated as binary strings if not compared to a number.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons.

In all other cases, the arguments are compared as floating-point (real) numbers.

=

Equal:

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

<=> was added in MySQL 3.23.0.

<>, !=

Not equal:

mysql> SELECT '.01' <> '0.01';
        -> 1
mysql> SELECT .01 <> '0.01';
        -> 0
mysql> SELECT 'zapp' <> 'zappp';
        -> 1

<=

Less than or equal:

mysql> SELECT 0.1 <= 2;
        -> 1

<

Less than:

mysql> SELECT 2 < 2;
        -> 0

>=

Greater than or equal:

mysql> SELECT 2 >= 2;
        -> 1

>

Greater than:

mysql> SELECT 2 > 2;
        -> 0

IS NULL

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

IS NOT NULL

Tests whether a value is not NULL.

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 11.2.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

mysql> SELECT 1 BETWEEN 2 AND 3;
        -> 0
mysql> SELECT 'b' BETWEEN 'a' AND 'c';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND '3';
        -> 1
mysql> SELECT 2 BETWEEN 2 AND 'x-3';
        -> 0

For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME to two DATE values, convert the DATE values to DATETIME values. If you use a string constant such as '2001-1-1' in a comparison to a DATE, cast the string to a DATE.

expr NOT BETWEEN min AND max

This is the same as NOT (expr BETWEEN min AND max).

COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

COALESCE() was added in MySQL 3.23.3.

GREATEST(value1,value2,...)

With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().

mysql> SELECT GREATEST(2,0);
        -> 2
mysql> SELECT GREATEST(34.0,3.0,5.0,767.0);
        -> 767.0
mysql> SELECT GREATEST('B','A','C');
        -> 'C'

GREATEST() returns NULL only if all arguments are NULL.

Before MySQL 3.22.5, you can use MAX() instead of GREATEST().

expr IN (value,...)

Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 11.2.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

mysql> SELECT 2 IN (0,3,5,7);
        -> 0
mysql> SELECT 'wefwf' IN ('wee','wefwf','weg');
        -> 1

You should never mix quoted and unquoted values in an IN list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an IN expression like this:

SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

Instead, write it like this:

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

The number of values in the IN list is only limited by the max_allowed_packet value.

To comply with the SQL standard, from MySQL 4.1.0 on IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL.

From MySQL 4.1.0 on, IN() syntax can also be used to write certain types of subqueries. See Section 12.2.8.3, “Subqueries with ANY, IN, and SOME”.

expr NOT IN (value,...)

This is the same as NOT (expr IN (value,...)).

ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1

ISNULL() can be used instead of = to test whether a value is NULL. (Comparing a value to NULL using = always yields false.)

The ISNULL() function shares some special behaviors with the IS NULL comparison operator. See the description of IS NULL.

INTERVAL(N,N1,N2,N3,...)

Returns 0 if N < N1, 1 if N < N2 and so on or -1 if N is NULL. All arguments are treated as integers. It is required that N1 < N2 < N3 < ... < Nn for this function to work correctly. This is because a binary search is used (very fast).

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0

LEAST(value1,value2,...)

With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

mysql> SELECT LEAST(2,0);
        -> 0
mysql> SELECT LEAST(34.0,3.0,5.0,767.0);
        -> 3.0
mysql> SELECT LEAST('B','A','C');
        -> 'A'

LEAST() returns NULL only if all arguments are NULL.

Before MySQL 3.22.5, you can use MIN() instead of LEAST().

Note that the preceding conversion rules can produce strange results in some borderline cases:

mysql> SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
        -> -9223372036854775808

This happens because MySQL reads 9223372036854775808.0 in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

NOT, !

Logical NOT. Evaluates to 1 if the operand is 0, to 0 if the operand is non-zero, and NOT NULL returns NULL.

mysql> SELECT NOT 10;
        -> 0
mysql> SELECT NOT 0;
        -> 1
mysql> SELECT NOT NULL;
        -> NULL
mysql> SELECT ! (1+1);
        -> 0
mysql> SELECT ! 1+1;
        -> 1

The last example produces 1 because the expression evaluates the same way as (!1)+1.

AND, &&

Logical AND. Evaluates to 1 if all operands are non-zero and not NULL, to 0 if one or more operands are 0, otherwise NULL is returned.

mysql> SELECT 1 && 1;
        -> 1
mysql> SELECT 1 && 0;
        -> 0
mysql> SELECT 1 && NULL;
        -> NULL
mysql> SELECT 0 && NULL;
        -> 0
mysql> SELECT NULL && 0;
        -> 0

Please note that MySQL versions prior to 4.0.5 stop evaluation when a NULL is encountered, rather than continuing the process to check for possible 0 values. This means that in these versions, SELECT (NULL AND 0) returns NULL instead of 0. As of MySQL 4.0.5, the code has been re-engineered so that the result is always as prescribed by the SQL standards while still using the optimization wherever possible.

OR, ||

Logical OR. When both operands are non-NULL, the result is 1 if any operand is non-zero, and 0 otherwise. With a NULL operand, the result is 1 if the other operand is non-zero, and NULL otherwise. If both operands are NULL, the result is NULL.

mysql> SELECT 1 || 1;
        -> 1
mysql> SELECT 1 || 0;
        -> 1
mysql> SELECT 0 || 0;
        -> 0
mysql> SELECT 0 || NULL;
        -> NULL
mysql> SELECT 1 || NULL;
        -> 1

XOR

Logical XOR. Returns NULL if either operand is NULL. For non-NULL operands, evaluates to 1 if an odd number of operands is non-zero, otherwise 0 is returned.

mysql> SELECT 1 XOR 1;
        -> 0
mysql> SELECT 1 XOR 0;
        -> 1
mysql> SELECT 1 XOR NULL;
        -> NULL
mysql> SELECT 1 XOR 1 XOR 1;
        -> 1

a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b).

XOR was added in MySQL 4.0.2.

expr LIKE pat [ESCAPE 'escape_char']

Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.

The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 | 
+------------+---------------+
1 row in set (0.00 sec)

With LIKE you can use the following two wildcard characters in the pattern:

mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1

To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.

mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1

To specify a different escape character, use the ESCAPE clause:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

The following two statements illustrate that string comparisons are not case sensitive unless one of the operands is a binary string:

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

In MySQL, LIKE is allowed on numeric expressions. (This is an extension to the standard SQL LIKE.)

mysql> SELECT 10 LIKE '1%';
        -> 1

expr NOT LIKE pat [ESCAPE 'escape_char']

This is the same as NOT (expr LIKE pat [ESCAPE 'escape_char']).

CREATE TABLE foo (bar VARCHAR(10));

INSERT INTO foo VALUES (NULL), (NULL);

SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;

expr NOT REGEXP pat, expr NOT RLIKE pat

This is the same as NOT (expr REGEXP pat).

expr REGEXP pat, expr RLIKE pat

Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression. The syntax for regular expressions is discussed in Section 11.4.2, “Regular Expressions”. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.

The pattern need not be a literal string. For example, it can be specified as a string expression or table column.

As of MySQL 3.23.4, REGEXP is not case sensitive, except when used with binary strings.

mysql> SELECT 'Monty!' REGEXP 'm%y%%';
        -> 0
mysql> SELECT 'Monty!' REGEXP '.*';
        -> 1
mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
        -> 1
mysql> SELECT 'a' REGEXP 'A', 'a' REGEXP BINARY 'A';
        -> 1  0
mysql> SELECT 'a' REGEXP '^[a-d]';
        -> 1

REGEXP and RLIKE use the current character set when deciding the type of a character. The default is latin1 (cp1252 West European).

STRCMP(expr1,expr2)

STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0

As of MySQL 4.0, STRCMP() uses the current character set when performing comparisons. This makes the default comparison behavior case insensitive unless one or both of the operands are binary strings. Before MySQL 4.0, STRCMP() is case sensitive.

^

Match the beginning of a string.

mysql> SELECT 'fo\nfo' REGEXP '^fo$';                   -> 0
mysql> SELECT 'fofo' REGEXP '^fo';                      -> 1

$

Match the end of a string.

mysql> SELECT 'fo\no' REGEXP '^fo\no$';                 -> 1
mysql> SELECT 'fo\no' REGEXP '^fo$';                    -> 0

.

Match any character (including carriage return and newline).

mysql> SELECT 'fofo' REGEXP '^f.*$';                    -> 1
mysql> SELECT 'fo\r\nfo' REGEXP '^f.*$';                -> 1

a*

Match any sequence of zero or more a characters.

mysql> SELECT 'Ban' REGEXP '^Ba*n';                     -> 1
mysql> SELECT 'Baaan' REGEXP '^Ba*n';                   -> 1
mysql> SELECT 'Bn' REGEXP '^Ba*n';                      -> 1

a+

Match any sequence of one or more a characters.

mysql> SELECT 'Ban' REGEXP '^Ba+n';                     -> 1
mysql> SELECT 'Bn' REGEXP '^Ba+n';                      -> 0

a?

Match either zero or one a character.

mysql> SELECT 'Bn' REGEXP '^Ba?n';                      -> 1
mysql> SELECT 'Ban' REGEXP '^Ba?n';                     -> 1
mysql> SELECT 'Baan' REGEXP '^Ba?n';                    -> 0

de|abc

Match either of the sequences de or abc.

mysql> SELECT 'pi' REGEXP 'pi|apa';                     -> 1
mysql> SELECT 'axe' REGEXP 'pi|apa';                    -> 0
mysql> SELECT 'apa' REGEXP 'pi|apa';                    -> 1
mysql> SELECT 'apa' REGEXP '^(pi|apa)$';                -> 1
mysql> SELECT 'pi' REGEXP '^(pi|apa)$';                 -> 1
mysql> SELECT 'pix' REGEXP '^(pi|apa)$';                -> 0

(abc)*

Match zero or more instances of the sequence abc.

mysql> SELECT 'pi' REGEXP '^(pi)*$';                    -> 1
mysql> SELECT 'pip' REGEXP '^(pi)*$';                   -> 0
mysql> SELECT 'pipi' REGEXP '^(pi)*$';                  -> 1

{1}, {2,3}

{n} or {m,n} notation provides a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern. m and n are integers.

To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive.

m and n must be in the range from 0 to RE_DUP_MAX (default 255), inclusive. If both m and n are given, m must be less than or equal to n.

mysql> SELECT 'abcde' REGEXP 'a[bcd]{2}e';              -> 0
mysql> SELECT 'abcde' REGEXP 'a[bcd]{3}e';              -> 1
mysql> SELECT 'abcde' REGEXP 'a[bcd]{1,10}e';           -> 1

[a-dX], [^a-dX]

Matches any character that is (or is not, if ^ is used) either a, b, c, d or X. A - character between two other characters forms a range that matches all characters from the first character to the second. For example, [0-9] matches any decimal digit. To include a literal ] character, it must immediately follow the opening bracket [. To include a literal - character, it must be written first or last. Any character that does not have a defined special meaning inside a [] pair matches only itself.

mysql> SELECT 'aXbc' REGEXP '[a-dXYZ]';                 -> 1
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]$';               -> 0
mysql> SELECT 'aXbc' REGEXP '^[a-dXYZ]+$';              -> 1
mysql> SELECT 'aXbc' REGEXP '^[^a-dXYZ]+$';             -> 0
mysql> SELECT 'gheis' REGEXP '^[^a-dXYZ]+$';            -> 1
mysql> SELECT 'gheisa' REGEXP '^[^a-dXYZ]+$';           -> 0

[.characters.]

Within a bracket expression (written using [ and ]), matches the sequence of characters of that collating element. characters is either a single character or a character name like newline. The following table lists the allowable character names.

The following table shows the allowable character names and the characters that they match. For characters given as numeric values, the values are represented in octal.

mysql> SELECT '~' REGEXP '[[.~.]]';                     -> 1
mysql> SELECT '~' REGEXP '[[.tilde.]]';                 -> 1

[=character_class=]

Within a bracket expression (written using [ and ]), [=character_class=] represents an equivalence class. It matches all characters with the same collation value, including itself. For example, if o and (+) are the members of an equivalence class, then [[=o=]], [[=(+)=]], and [o(+)] are all synonymous. An equivalence class may not be used as an endpoint of a range.

[:character_class:]

Within a bracket expression (written using [ and ]), [:character_class:] represents a character class that matches all characters belonging to that class. The following table lists the standard class names. These names stand for the character classes defined in the ctype(3) manual page. A particular locale may provide other class names. A character class may not be used as an endpoint of a range.

mysql> SELECT 'justalnums' REGEXP '[[:alnum:]]+';       -> 1
mysql> SELECT '!!' REGEXP '[[:alnum:]]+';               -> 0

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

mysql> SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]';   -> 1
mysql> SELECT 'a xword a' REGEXP '[[:<:]]word[[:>:]]';  -> 0

In the case of -, +, and *, the result is calculated with BIGINT (64-bit) precision if both arguments are integers.

If one of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer.

If any of the operands of a +, -, /, *, % is a real or string value, then the precision of the result is the precision of the argument with the maximum precision.

+

Addition:

mysql> SELECT 3+5;
        -> 8

-

Subtraction:

mysql> SELECT 3-5;
        -> -2

-

Unary minus. This operator changes the sign of the argument.

mysql> SELECT - 2;
        -> -2

*

Multiplication:

mysql> SELECT 3*5;
        -> 15
mysql> SELECT 18014398509481984*18014398509481984.0;
        -> 324518553658426726783156020576256.0
mysql> SELECT 18014398509481984*18014398509481984;
        -> 0

The result of the last expression is incorrect because the result of the integer multiplication exceeds the 64-bit range of BIGINT calculations. (See Section 10.2, “Numeric Types”.)

/

Division:

mysql> SELECT 3/5;
        -> 0.60

Division by zero produces a NULL result:

mysql> SELECT 102/(1-1);
        -> NULL

A division is calculated with BIGINT arithmetic only if performed in a context where its result is converted to an integer.

DIV

Integer division. Similar to FLOOR(), but is safe with BIGINT values.

mysql> SELECT 5 DIV 2;
        -> 2

DIV was implemented in MySQL 4.1.0.

N % M

Modulo operation. Returns the remainder of N divided by M. For more information, see the description for the MOD() function in Section 11.5.2, “Mathematical Functions”.

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

CEIL(X)

CEIL() is a synonym for CEILING(). It was added in MySQL 4.0.6.

CEILING(X)

Returns the smallest integer value not less than X.

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

Note that the return value is converted to a BIGINT.

CONV(N,from_base,to_base)

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 to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

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)

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 to be a string and (if possible) is 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. The inverse of this function is the LOG(). In MySQL 4.0.3 or later, its inverse is LOG() using a single argument or LN().

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 than X.

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

Note that the return value is converted to a BIGINT.

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. For details, see Section 11.4, “String Functions”.

HEX(N_or_S)

This function can be used to obtain a hexadecimal representation of a decimal number or (beginning with MySQL 4.0.1) a string; the manner in which it does so varies according to the argument's type. See this function's description in Section 11.4, “String Functions”, for details.

LN(X)

Returns the natural logarithm of X; that is, the base-e logarithm of X. If X is less than or equal to 0, then NULL is returned.

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

This function was added in MySQL 4.0.3. It is synonymous with LOG(X). The inverse of this function is the EXP() function.

LOG(X), LOG(B,X)

If called with one parameter, this function returns the natural logarithm of X. If X is less than or equal to 0, then NULL is returned.

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

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.000000
mysql> SELECT LOG(10,100);
        -> 2
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.0.

As of MySQL 4.1.7, MOD() works on values that have 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

MOD(N,0) returns NULL.

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

mysql> SELECT OCT(12);
        -> '14'

PI()

Returns the value of π (pi). The default number of decimal places displayed is five, 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 of X raised to the power of Y.

mysql> SELECT POW(2,2);
        -> 4.000000
mysql> SELECT POW(2,-2);
        -> 0.250000

POWER(X,Y)

This is a synonym for POW().

RADIANS(X)

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

mysql> SELECT RADIANS(90);
        -> 1.570796

RAND(), RAND(N)

Returns a random floating-point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

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
mysql> SELECT RAND(20);
        -> 0.15888261251047

The effect of using a non-constant argument is undefined. As of MySQL 4.1.15, non-constant arguments are disallowed.

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, you could use the following statement:

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

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. However, 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 from 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 is a fast way to generate ad hoc random numbers which is portable between platforms for the same MySQL version.

ROUND(X), ROUND(X,D)

Rounds the argument X to D decimal places. 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.

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

The return type is the same type as that of 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).

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());
        -> 1.2246063538224e-16
mysql> SELECT ROUND(SIN(PI()));
        -> 0

SQRT(X)

Returns the square root of a non-negative number X.

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

TAN(X)

Returns the tangent of X, where X is given in radians.

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

TRUNCATE(X,D)

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.

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);
       -> 1027

Starting from MySQL 3.23.51, all numbers are rounded toward zero.