Rechercher dans le manuel MySQL
12.3.2 Comparison Functions and Operators
Table 12.3 Comparison Operators
Name | Description |
---|---|
BETWEEN ... AND ... |
Check whether a value is within a range of values |
COALESCE() |
Return the first non-NULL argument |
= |
Equal operator |
<=> |
NULL-safe equal to operator |
> |
Greater than operator |
>= |
Greater than or equal operator |
GREATEST() |
Return the largest argument |
IN() |
Check whether a value is within a set of values |
INTERVAL() |
Return the index of the argument that is less than the first argument |
IS |
Test a value against a boolean |
IS NOT |
Test a value against a boolean |
IS NOT NULL |
NOT NULL value test |
IS NULL |
NULL value test |
ISNULL() |
Test whether the argument is NULL |
LEAST() |
Return the smallest argument |
< |
Less than operator |
<= |
Less than or equal operator |
LIKE |
Simple pattern matching |
NOT BETWEEN ... AND ... |
Check whether a value is not within a range of values |
!= , <> |
Not equal operator |
NOT IN() |
Check whether a value is not within a set of values |
NOT LIKE |
Negation of simple pattern matching |
STRCMP() |
Compare two strings |
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.
The following relational comparison operators can be used to compare not only scalar operands, but row operands:
- = > < >= <= <> !=
The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries”.
Some of the functions in this section return values other than
1
(TRUE
),
0
(FALSE
), or
NULL
. LEAST()
and GREATEST()
are examples of
such functions; Section 12.2, “Type Conversion in Expression Evaluation”, describes the
rules for comparison operations performed by these and similar
functions for determining their return values.
In previous versions of MySQL, when evaluating an expression
containing LEAST()
or
GREATEST()
, the server attempted to guess
the context in which the function was used, and to coerce the
function's arguments to the data type of the expression
as a whole. For example, the arguments to LEAST("11",
"45", "2")
are evaluated and sorted as strings, so
that this expression returns "11"
. In MySQL
8.0.3 and earlier, when evaluating the expression
LEAST("11", "45", "2") + 0
, the server
converted the arguments to integers (anticipating the addition
of integer 0 to the result) before sorting them, thus
returning 2.
Beginning with MySQL 8.0.4, the server no longer attempts to
infer context in this fashion. Instead, the function is
executed using the arguments as provided, performing data type
conversions to one or more of the arguments if and only if
they are not all of the same type. Any type coercion mandated
by an expression that makes use of the return value is now
performed following function execution. This means that, in
MySQl 8.0.4 and later, LEAST("11", "45", "2") +
0
evaluates to "11" + 0
and thus
to integer 11. (Bug #83895, Bug #25123839)
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.10, “Cast Functions and Operators”.
By default, string comparisons are not case-sensitive and use
the current character set. The default is
utf8mb4
.
Equal:
- -> 0
- -> 1
- -> 1
- -> 0
- -> 1
For row comparisons,
(a, b) = (x, y)
is equivalent to: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
.The
<=>
operator is equivalent to the standard SQLIS NOT DISTINCT FROM
operator.- -> 1, 1, 0
For row comparisons,
(a, b) <=> (x, y)
is equivalent to:Not equal:
- -> 1
- -> 0
- -> 1
For row comparisons,
(a, b) <> (x, y)
and(a, b) != (x, y)
are equivalent to:Less than or equal:
- -> 1
For row comparisons,
(a, b) <= (x, y)
is equivalent to:Less than:
- -> 0
For row comparisons,
(a, b) < (x, y)
is equivalent to:Greater than or equal:
- -> 1
For row comparisons,
(a, b) >= (x, y)
is equivalent to:Greater than:
- -> 0
For row comparisons,
(a, b) > (x, y)
is equivalent to:Tests a value against a boolean value, where
boolean_value
can beTRUE
,FALSE
, orUNKNOWN
.Tests a value against a boolean value, where
boolean_value
can beTRUE
,FALSE
, orUNKNOWN
.Tests whether a value is
NULL
.To work well with ODBC programs, MySQL supports the following extra features when using
IS NULL
:If
sql_auto_is_null
variable is set to 1, then after a statement that successfully inserts an automatically generatedAUTO_INCREMENT
value, you can find that value by issuing a statement of the following form:If the statement returns a row, the value returned is the same as if you invoked the
LAST_INSERT_ID()
function. For details, including the return value after a multiple-row insert, see Section 12.15, “Information Functions”. If noAUTO_INCREMENT
value was successfully inserted, theSELECT
statement returns no row.The behavior of retrieving an
AUTO_INCREMENT
value by using anIS NULL
comparison can be disabled by settingsql_auto_is_null = 0
. See Section 5.1.8, “Server System Variables”.The default value of
sql_auto_is_null
is 0.For
DATE
andDATETIME
columns that are declared asNOT NULL
, you can find the special date'0000-00-00'
by using a statement like this:This is needed to get some ODBC applications to work because ODBC does not support a
'0000-00-00'
date value.See Obtaining Auto-Increment Values, and the description for the
FLAG_AUTO_IS_NULL
option at Connector/ODBC Connection Parameters.
Tests whether a value is not
NULL
.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(
if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.min
<=expr
ANDexpr
<=max
)- -> 1, 0
- -> 0
- -> 1
- -> 1
- -> 0
For best results when using
BETWEEN
with date or time values, useCAST()
to explicitly convert the values to the desired data type. Examples: If you compare aDATETIME
to twoDATE
values, convert theDATE
values toDATETIME
values. If you use a string constant such as'2001-1-1'
in a comparison to aDATE
, cast the string to aDATE
.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.The return type of
COALESCE()
is the aggregated type of the argument types.With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for
LEAST()
.- -> 2
- -> 767.0
- -> 'C'
GREATEST()
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.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.IN
can be used to compare row constructors: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 anIN
expression like this:Instead, write it like this:
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.11.3, “Subqueries with ANY, IN, or SOME”.This is the same as
NOT (
.expr
IN (value
,...))If
expr
isNULL
,ISNULL()
returns1
, otherwise it returns0
.ISNULL()
can be used instead of=
to test whether a value isNULL
. (Comparing a value toNULL
using=
always yieldsNULL
.)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).With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:
If any argument is
NULL
, the result isNULL
. No comparison is needed.If all arguments are integer-valued, they are compared as integers.
If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a
DECIMAL
value, they are compared asDECIMAL
values.If the arguments comprise a mix of numbers and strings, they are compared as numbers.
If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.
In all other cases, the arguments are compared as binary strings.
The return type of
LEAST()
is the aggregated type of the comparison argument types.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-comparison-operators.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.