Case sensitivity in MySQL

It is sometimes necessary to perform a case-sensitive query for text stored in a MySQL table. By default, searches are case insensitive.

In MySQL, the character set defines what kind of characters you can store, but not how they are compared or sorted. This is determined by the character set’s collation setting.

For example, take the following table:

CREATE TABLE `CHEM_FRAGMENT` (
`CFR_ID` bigint(20) NOT NULL auto_increment,
`CFR_SMILES` varchar(255) NOT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=latin1


The character set is ‘latin1′. The collation settings are not shown because they are set to the default value. What’s that? Well:


mysql> show collation where charset = 'latin1';

+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |
| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       1 |
| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |
| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |
| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |
+-------------------+---------+----+---------+----------+---------+

8 rows in set (0.00 sec)


The default collation is ‘latin1_swedish_ci’. Ignoring everything else, the important thing is the ‘_ci’ at the end, which stands for ‘case insensitive’. So change it! ‘latin1_general_cs’ is case sensitive, and so is ‘latin1_bin’.

To alter the collation for an entire table:

ALTER TABLE `THE_TABLE` COLLATE latin1_general_cs;


To alter the collation for a single column:

ALTER TABLE `THE_TABLE` MODIFY `THE_COLUMN` varchar(760) default NULL COLLATE latin1_general_cs;


Advertisement