Working with Strings - MySQL - page 157

+-----------+
| @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, 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 not suited to the kind of com‐
parisons for which you typically use it, use ALTER TABLE to change the type. Suppose
that this table stores 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 . That is a binary string type, so com‐
parisons of text stored in the column are made without regard to character set. (In effect,
they are case sensitive.) If that's not what you want, use ALTER TABLE to convert the
column to a nonbinary type that has a case-insensitive collation:
ALTER TABLE news
MODIFY article TEXT CHARACTER SET utf8 COLLATE utf8_general_ci ;