| Expression & Operators |
| |
| Comparison Operators |
| |
| Comparison operators, include operators for testing relative magnitude or lexical ordering of numbers and strings as well as operators for performing pattern matching and for testing NULL values. The <=> operator is MySQL-specific and was introduced in MySQL 3.23. |
| |
| Comparison Operators |
Operator |
Syntax |
Meaning |
= |
a = b |
True if operands are equal |
<=> |
a <=> b |
True if operands are equal (even if NULL) |
!=, <> |
a != b, a <> b |
True if operands are not equal |
< |
a < b |
True if a is less than b |
<= |
a <= b |
True if a is less than or equal to b |
>= |
a >= b |
True if a is greater than or equal to b |
> |
a > b |
True if a is greater than b |
IN |
a IN (b1, b2, ...) |
True if a is equal to any of b1, b2, … |
BETWEEN |
a BETWEEN b AND C |
True if a is between the values of b and c, inclusive |
NOT BETWEEN |
a NOT BETWEEN b AND C |
True if a is not between the values of band c, inclusive |
LIKE |
a LIKE b |
SQL pattern match; true if a matches b |
NOT LIKE |
a NOT LIKE b |
SQL pattern match; true if a does not match b |
REGEXP |
a REGEXP b |
Regular expression match; true if a matches b |
NOT REGEXP |
a NOT REGEXP b |
Regular expression match; true if a does not match b |
IS NULL |
a IS NULL |
True if operand is NULL |
IS NOT NULL |
a IS NOT NULL |
True if operand is not NULL |
|
| |
| Comparison operators return 1 if the comparison is true and 0 if the comparison is false. You can compare numbers or strings. Operands are converted as necessary according to the following rules: |
| |
| . Other than for the <=> operator, comparisons involving NULL values evaluate as NULL. (<=> is like =, except that the value of the expression NULL <=> NULL is true.) |
| |
| . If both operands are strings, they are compared lexically as strings. Binary strings are compared on a byte-by-byte basis using the numeric value of each byte. Comparisons for non-binary strings are performed character by character, using the collating sequence of the character set in which the strings are expressed. If the strings have different character sets (as is possible as of MySQL 4.1), the comparison might not yield meaningful results. A comparison between a binary and a non-binary string is treated as a comparison of binary strings. |
| |
| . If both operands are integers, they are compared numerically as integers. |
| |
| . As of MySQL 3.23.22, hexadecimal constants are compared as numbers. Before that, hex constants that are not compared to a number are compared as binary strings. |
| |
| . If either operand is a TIMESTAMP or DATETIME value and the other is a constant, the operands are compared as TIMESTAMP values. This is done to make comparisons work better for ODBC applications. |
| |
| . If none of the preceding rules apply, the operands are compared numerically as floating-point values. Note that this includes the case of comparing a string and a number. The string is converted to a number, which results in a value of 0 if the string doesn't look like a number. For example, '14.3' converts to 14.3,but 'L4.3' converts to 0. |
| |
| The following comparisons illustrate these rules: |
| |
| 2 < 12 1 |
| |
| '2' < '12' 0 |
| |
| '2' < 12 1 |
| |
| The first comparison involves two integers, which are compared numerically. The second comparison involves two strings, which are compared lexically. The third comparison involves a string and a number, so they are compared as floating-point values. |
| |
| |
|
|
| |
| |
