Use a comparison operator. But remember that strings have properties such as case sensitivity that you must take into account. For example, a string comparison might be case-sensitive when you don’t want it to be, or vice versa.
As for other data types, you can compare string values for equality, inequality, or relative ordering:
mysql>SELECT 'cat' = 'cat', 'cat' = 'dog';
+---------------+---------------+ | 'cat' = 'cat' | 'cat' = 'dog' | +---------------+---------------+ | 1 | 0 | +---------------+---------------+ mysql>SELECT 'cat' != 'cat', 'cat' != 'dog';
+----------------+----------------+ | 'cat' != 'cat' | 'cat' != 'dog' | +----------------+----------------+ | 0 | 1 | +----------------+----------------+ mysql>SELECT 'cat' < 'awk', 'cat' < 'dog';
+---------------+---------------+ | 'cat' < 'awk' | 'cat' < 'dog' | +---------------+---------------+ | 0 | 1 | +---------------+---------------+ mysql>SELECT 'cat' BETWEEN 'awk' AND 'egret';
+---------------------------------+ | 'cat' BETWEEN 'awk' AND 'egret' | +---------------------------------+ | 1 | +---------------------------------+
However, comparison and sorting properties of strings are subject to certain complications that don’t apply to other types of data. For example, sometimes you need to make sure a string operation is case-sensitive that would not otherwise be, or vice versa. This section describes how to do that for ordinary comparisons. Controlling Case Sensitivity in Pattern Matching covers case sensitivity in pattern-matching operations.
String comparison properties depend on whether the operands are binary or nonbinary strings:
A binary string is a sequence of bytes and is compared using
numeric byte values. Lettercase has no meaning. However, because letters
in different cases have different byte values, comparisons of
binary strings effectively are case-sensitive. (That is, a
and A
are unequal.) If you want to compare
binary strings so that lettercase does not matter, convert them to
nonbinary strings that have a case-insensitive collation.
A nonbinary string is a sequence of characters and is compared in character units. (Depending on the character set, some characters might have multiple bytes.) The string has a character set that defines the legal characters and a collation that defines their sort order. The collation also determines whether to consider characters in different lettercases the same in comparisons. If the collation is case-sensitive, and you want a case-insensitive collation (or vice versa), convert the strings to use a collation with the desired case-comparison properties.
By default, strings have a character set of latin1
and a collation of latin1_swedish_ci
. This results in
case-insensitive string comparisons.
The following example shows how two binary strings that compare as unequal can be handled so that they are equal when compared as case-insensitive nonbinary strings:
mysql>SET @s1 = BINARY 'cat', @s2 = BINARY 'CAT';
mysql>SELECT @s1 = @s2;
+-----------+ | @s1 = @s2 | +-----------+ | 0 | +-----------+ mysql>SET @s1 = CONVERT(@s1 USING latin1) COLLATE latin1_swedish_ci;
mysql>SET @s2 = CONVERT(@s2 USING latin1) COLLATE latin1_swedish_ci;
mysql>SELECT @s1 = @s2;
+-----------+ | @s1 = @s2 | +-----------+ | 1 | +-----------+
In this case, because latin1_swedish_ci
is the default collation
for latin1
, you can omit the
COLLATE
operator:
mysql>SET @s1 = CONVERT(@s1 USING latin1);
mysql>SET @s2 = CONVERT(@s2 USING latin1);
mysql>SELECT @s1 = @s2;
+-----------+ | @s1 = @s2 | +-----------+ | 1 | +-----------+
The next example shows how to compare two strings that are not
case-sensitive (as demonstrated by the first SELECT
) in case-sensitive fashion (as
demonstrated by the second):
mysql>SET @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
mysql>SELECT @s1 = @s2;
+-----------+ | @s1 = @s2 | +-----------+ | 1 | +-----------+ mysql>SELECT @s1 COLLATE latin1_general_cs = @s2 COLLATE latin1_general_cs
->AS '@s1 = @s2';
+-----------+ | @s1 = @s2 | +-----------+ | 0 | +-----------+
If you compare a binary string with a nonbinary string, the comparison treats both operands as binary strings:
mysql>SELECT _latin1 'cat' = BINARY 'CAT';
+------------------------------+
| _latin1 'cat' = BINARY 'CAT' |
+------------------------------+
| 0 |
+------------------------------+
Thus, if you want to compare two nonbinary strings as binary
strings, apply the BINARY
operator
to either one when comparing them:
mysql>SET @s1 = _latin1 'cat', @s2 = _latin1 'CAT';
mysql>SELECT @s1 = @s2, BINARY @s1 = @s2, @s1 = BINARY @s2;
+-----------+------------------+------------------+ | @s1 = @s2 | BINARY @s1 = @s2 | @s1 = BINARY @s2 | +-----------+------------------+------------------+ | 1 | 0 | 0 | +-----------+------------------+------------------+
If you find that you’ve declared a column using a type that is
not suitable for the kind of comparisons for which you typically use
it, use ALTER
TABLE
to change the type. Suppose that you
have a table in which you store news articles:
CREATE TABLE news ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, article BLOB, PRIMARY KEY (id) );
Here the article
column is
declared as a BLOB
, which is a
binary string type. This means that if you store text in the column,
comparisons are made without regard to character set. (In effect, they
are case-sensitive.) If that’s not what you want, you can convert the
column to a nonbinary type that has a case-insensitive collation using
ALTER
TABLE
:
ALTER TABLE news MODIFY article TEXT CHARACTER SET utf8 COLLATE utf8_general_ci;
3.140.188.244