Table of Contents
- 12.1. Operators
- 12.2. Control Flow Functions
- 12.3. String Functions
- 12.4. Numeric Functions
- 12.5. Date and Time Functions
- 12.6. What Calendar Is Used By MySQL?
- 12.7. Full-Text Search Functions
- 12.8. Cast Functions and Operators
- 12.9. Other Functions
-
12.10. Functions and Modifiers for Use with
GROUP BY
Clauses
Expressions can be used at several points in SQL statements, such as
in the ORDER BY
or HAVING
clauses of SELECT
statements, in the
WHERE
clause of a SELECT
,
DELETE
, or UPDATE
statement,
or in SET
statements. Expressions can be written
using literal values, column values, NULL
,
built-in functions, stored functions, user-defined functions, and
operators. This chapter describes the functions and operators that
are allowed for writing expressions in MySQL. Instructions for
writing stored functions and user-defined functions are given in
Chapter 17, Stored Procedures and Functions, and
Section 24.2, “Adding New Functions to MySQL”.
An expression that contains NULL
always produces
a NULL
value unless otherwise indicated in the
documentation for a particular function or operator.
Note: By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.
You can tell the MySQL server to accept spaces after function names
by starting it with the --sql-mode=IGNORE_SPACE
option. (See Section 5.2.5, “The Server SQL Mode”.) Individual client
programs can request this behavior by using the
CLIENT_IGNORE_SPACE
option for
mysql_real_connect()
. In either case, all
function names become reserved words.
For the sake of brevity, most examples in this chapter display the output from the mysql program in abbreviated form. Rather than showing examples in this format:
mysql> SELECT MOD(29,9);
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
1 rows in set (0.00 sec)
This format is used instead:
mysql> SELECT MOD(29,9);
-> 2
Operator precedences are shown in the following list, from lowest precedence to the highest. Operators that are shown together on a line have the same precedence.
:= ||, OR, XOR &&, AND NOT BETWEEN, CASE, WHEN, THEN, ELSE =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN | & <<, >> -, + *, /, DIV, %, MOD ^ - (unary minus), ~ (unary bit inversion) ! BINARY, COLLATE
The precedence shown for NOT
is as of MySQL
5.0.2. For earlier versions, or from 5.0.2 on if the
HIGH_NOT_PRECEDENCE
SQL mode is enabled, the
precedence of NOT
is the same as that of the
!
operator. See
Section 5.2.5, “The Server SQL Mode”.
The precedence of operators determines the order of evaluation of terms in an expression. To override this order and group terms explicitly, use parentheses. For example:
mysql>SELECT 1+2*3;
-> 7 mysql>SELECT (1+2)*3;
-> 9
When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa.
mysql>SELECT 1+'1';
-> 2 mysql>SELECT CONCAT(2,' test');
-> '2 test'
It is also possible to perform explicit conversions. If you want
to convert a number to a string explicitly, use the
CAST()
or CONCAT()
function (CAST()
is preferable):
mysql>SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8' mysql>SELECT 38.8, CONCAT(38.8);
-> 38.8, '38.8'
The following rules describe how conversion occurs for comparison operations:
-
If one or both arguments are
NULL
, the result of the comparison isNULL
, except for theNULL
-safe<=>
equality comparison operator. ForNULL <=> 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
orDATETIME
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 toIN()
! 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.
The following examples illustrate conversion of strings to numbers for comparison operations:
mysql>SELECT 1 > '6x';
-> 0 mysql>SELECT 7 > '6x';
-> 1 mysql>SELECT 0 > 'x6';
-> 0 mysql>SELECT 0 = 'x6';
-> 1
Note that when you are comparing a string column with a number,
MySQL cannot use an index on the column to look up the value
quickly. If str_col
is an indexed
string column, the index cannot be used when performing the
lookup in the following statement:
SELECT * FROMtbl_name
WHEREstr_col
=1;
The reason for this is that there are many different strings
that may convert to the value 1
, such as
'1'
, ' 1'
, or
'1a'
.
Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
mysql>SELECT '18015376320243458' = 18015376320243458;
-> 1 mysql>SELECT '18015376320243459' = 18015376320243459;
-> 0
Such results can occur because the values are converted to floating-point numbers, which have only 53 bits of precision and are subject to rounding:
mysql> SELECT '18015376320243459'+0.0;
-> 1.8015376320243e+16
Furthermore, the conversion from string to floating-point and from integer to floating-point do not necessarily occur the same way. The integer may be converted to floating-point by the CPU, whereas the string is converted digit by digit in an operation that involves floating-point multiplications.
The results shown will vary on different systems, and can be
affected by factors such as computer architecture or the
compiler version or optimization level. One way to avoid such
problems is to use CAST()
so that a value
will not be converted implicitly to a float-point number:
mysql> SELECT CAST('18015376320243459' AS UNSIGNED) = 18015376320243459;
-> 1
For more information about floating-point comparisons, see Section A.5.8, “Problems with Floating-Point Comparisons”.
Comparison operations result in a value of 1
(TRUE
), 0
(FALSE
), or NULL
. These
operations work for both numbers and strings. Strings are
automatically converted to numbers and numbers to strings as
necessary.
Some of the functions in this section (such as
LEAST()
and GREATEST()
)
return values other than 1
(TRUE
), 0
(FALSE
), or NULL
. However,
the value they return is based on comparison operations
performed according to the rules described in
Section 12.1.2, “Type Conversion in Expression Evaluation”.
To convert a value to a specific type for comparison purposes,
you can use the CAST()
function. String
values can be converted to a different character set using
CONVERT()
. See
Section 12.8, “Cast Functions and Operators”.
By default, string comparisons are not case sensitive and use
the current character set. The default is
latin1
(cp1252 West European), which also
works well for English.
-
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 returns1
rather thanNULL
if both operands areNULL
, and0
rather thanNULL
if one operand isNULL
.mysql>
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-> 1, 1, 0 mysql>SELECT 1 = 1, NULL = NULL, 1 = NULL;
-> 1, NULL, NULL -
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
boolean_value
,IS NOT
boolean_value
Tests a value against a boolean value, where
boolean_value
can beTRUE
,FALSE
, orUNKNOWN
.mysql>
SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
-> 1, 1, 1 mysql>SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
-> 1, 1, 0IS [NOT]
boolean_value
syntax was added in MySQL 5.0.2. -
Tests whether a value is or is not
NULL
.mysql>
SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
-> 0, 0, 1 mysql>SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
-> 1, 1, 0To work well with ODBC programs, MySQL supports the following extra features when using
IS NULL
:-
You can find the row that contains the most recent
AUTO_INCREMENT
value by issuing a statement of the following form immediately after generating the value:SELECT * FROM
tbl_name
WHEREauto_col
IS NULLThis behavior can be disabled by setting
SQL_AUTO_IS_NULL=0
. See Section 13.5.3, “SET
Syntax”. -
For
DATE
andDATETIME
columns that are declared asNOT NULL
, you can find the special date'0000-00-00'
by using a statement like this:SELECT * FROM
tbl_name
WHEREdate_column
IS NULLThis is needed to get some ODBC applications to work because ODBC does not support a
'0000-00-00'
date value.
-
-
If
expr
is greater than or equal tomin
andexpr
is less than or equal tomax
,BETWEEN
returns1
, otherwise it returns0
. This is equivalent to the expression(
min
<=expr
ANDexpr
<=max
) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.1.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 -
This is the same as
NOT (
expr
BETWEENmin
ANDmax
). -
Returns the first non-
NULL
value in the list, orNULL
if there are no non-NULL
values.mysql>
SELECT COALESCE(NULL,1);
-> 1 mysql>SELECT COALESCE(NULL,NULL,NULL);
-> NULL -
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'Before MySQL 5.0.13,
GREATEST()
returnsNULL
only if all arguments areNULL
. As of 5.0.13, it returnsNULL
if any argument isNULL
. -
Returns
1
ifexpr
is equal to any of the values in theIN
list, else returns0
. If all values are constants, they are evaluated according to the type ofexpr
and sorted. The search for the item then is done using a binary search. This meansIN
is very quick if theIN
value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 12.1.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');
-> 1You 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 anIN
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 themax_allowed_packet
value.To comply with the SQL standard,
IN
returnsNULL
not only if the expression on the left hand side isNULL
, but also if no match is found in the list and one of the expressions in the list isNULL
.IN()
syntax can also be used to write certain types of subqueries. See Section 13.2.8.3, “Subqueries withANY
,IN
, andSOME
”. -
This is the same as
NOT (
expr
IN (value
,...)). -
If
expr
isNULL
,ISNULL()
returns1
, otherwise it returns0
.mysql>
SELECT ISNULL(1+1);
-> 0 mysql>SELECT ISNULL(1/0);
-> 1ISNULL()
can be used instead of=
to test whether a value isNULL
. (Comparing a value toNULL
using=
always yields false.)The
ISNULL()
function shares some special behaviors with theIS NULL
comparison operator. See the description ofIS NULL
. -
Returns
0
ifN
<N1
,1
ifN
<N2
and so on or-1
ifN
isNULL
. All arguments are treated as integers. It is required thatN1
<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 -
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
-
If the return value is used in an
INTEGER
context or all arguments are integer-valued, they are compared as integers. -
If the return value is used in a
REAL
context or all arguments are real-valued, they are compared as reals. -
If any argument is a case-sensitive string, the arguments are compared as case-sensitive strings.
-
In all other cases, the arguments are compared as case-insensitive strings.
Before MySQL 5.0.13,
LEAST()
returnsNULL
only if all arguments areNULL
. As of 5.0.13, it returnsNULL
if any argument isNULL
.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'Note that the preceding conversion rules can produce strange results in some borderline cases:
mysql>
SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);
-> -9223372036854775808This 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. -
In SQL, all logical operators evaluate to
TRUE
, FALSE
, or
NULL
(UNKNOWN
). In MySQL,
these are implemented as 1 (TRUE
), 0
(FALSE
), and NULL
. Most of
this is common to different SQL database servers, although some
servers may return any non-zero value for
TRUE
.
-
Logical NOT. Evaluates to
1
if the operand is0
, to0
if the operand is non-zero, andNOT NULL
returnsNULL
.mysql>
SELECT NOT 10;
-> 0 mysql>SELECT NOT 0;
-> 1 mysql>SELECT NOT NULL;
-> NULL mysql>SELECT ! (1+1);
-> 0 mysql>SELECT ! 1+1;
-> 1The last example produces
1
because the expression evaluates the same way as(!1)+1
.Note that the precedence of the
NOT
operator changed in MySQL 5.0.2. See Section 12.1.1, “Operator Precedence”. -
Logical AND. Evaluates to
1
if all operands are non-zero and notNULL
, to0
if one or more operands are0
, otherwiseNULL
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 -
Logical OR. When both operands are non-
NULL
, the result is1
if any operand is non-zero, and0
otherwise. With aNULL
operand, the result is1
if the other operand is non-zero, andNULL
otherwise. If both operands areNULL
, the result isNULL
.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 -
Logical XOR. Returns
NULL
if either operand isNULL
. For non-NULL
operands, evaluates to1
if an odd number of operands is non-zero, otherwise0
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;
-> 1a XOR b
is mathematically equal to(a AND (NOT b)) OR ((NOT a) and b)
.