This short note requires some basic knowledge of MySQL Character Sets & Collation.

If you configured your mysql server Default Character set with LATIN1, then
default collation for that Character Set LATIN1 is LATIN1_SWEDISH_CI (Where CI denotes Case Insensitive).

Consider the below given example :

mysql> create table test( sno integer, name varchar(15) ); -- default character set will be used by the system.
mysql> insert into test values (1, 'xyz');
mysql> insert into test values (2, 'XYZ');
mysql> insert into test values (3, 'Xyz');
mysql> insert into test values (4, 'XYz');
mysql> insert into test values (5, 'xyZ');

mysql> select * from test where name = 'xyz'; -- this query will result all the five values.
+------+------+
| sno | name |
+------+------+
| 1 | xyz |
| 2 | XYZ |
| 3 | Xyz |
| 4 | XYz |
| 5 | xyZ |
+------+------+
5 rows in set (0.00 sec)

If we want to do case sensitive search then we need specify the charset collation to be CI - CASE SENSITIVE.
You can find the case sensitive collation by issuing

mysql> show collation;
mysql> show collation where charset = 'latin1';

'latin1_general_cs' is the case sensitive collation for character set 'latin1'

Now, drop the table and create again and specify the character set and collation as given below.

mysql> drop table if exists test;
mysql> create table test( sno integer, name varchar(15)) CHARACTER SET latin1 COLLATE latin1_general_cs;;
mysql> insert into test values (1, 'xyz');
mysql> insert into test values (2, 'XYZ');
mysql> insert into test values (3, 'Xyz');
mysql> insert into test values (4, 'XYz');
mysql> insert into test values (5, 'xyZ');
mysql> select * from test where name = 'xyz';
+------+------+
| sno | name |
+------+------+
| 1 | xyz |
+------+------+

Instead of drop & creating table once again, we can use alter statement to modify the character and collation.

mysql> ALTER TABLE test CHARACTER SET latin1 COLLATE latin1_general_ci;

This is the way we can achieve case sensitive select / search in MySQL.

hi,
this is really fantastic articles.
is there any possibility to set this without altering or creating new table?

__________________
With,
J. Jeyaseelan
Everything Possible

Dear jayseel,

Without changing the Character / Collation type it's not possible.

Hi Prakash,

ok Good. Nice 2 see u again.

can u pls tell me what are the characters type other than latin1 available as like this?

__________________
With,
J. Jeyaseelan
Everything Possible

Hi,

You can find the list of character set available in mysql by issuing the following show commnad.

SHOW CHARSET; -- this will list available character set in format given below.

----------------------------------------------------------------------
Charset Description Default collation Maxlen
----------------------------------------------------------------------
8-Dec DEC West European dec8_swedish_ci 1
latin1 cp1252 West European latin1_swedish_ci 1
latin2 ISO 8859-2 Central European latin2_general_ci 1
latin5 ISO 8859-9 Turkish latin5_turkish_ci 1
utf8 UTF-8 Unicode utf8_general_ci 3
binary Binary pseudo charset binary 1
geostd8 GEOSTD8 Georgian geostd8_general_ci 1
...
...
----------------------------------------------------------------------

If you want to list out the all the collations for each character set then try the following command.

SHOW COLLATION;
SHOW COLLATION WHERE Charset LIKE 'latin1%';
(like this you can find all the collation list for any available character set in mysql)
----------------------------------------------------------------------
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
----------------------------------------------------------------------

-- Priyan..

Hi Priyan/Jayaseelan,

Its really a wonderful explanation regarding the case sensitive search using alter table converting the field characterset and collation - By Priyan.

But can i tell even a very simple way to do that without changing the field Character set and Collation?

Try this one ....


mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test(sno INTEGER,name VARCHAR(15) );
Query OK, 0 rows affected (0.06 sec)

mysql> DESC test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sno | int(11) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test VALUES (1, 'xyz');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (2, 'XYZ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (3, 'Xyz');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (4, 'XYz');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test VALUES (5, 'xyZ');
Query OK, 1 row affected (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test WHERE name = 'xyz';
+------+------+
| sno | name |
+------+------+
| 1 | xyz |
| 2 | XYZ |
| 3 | Xyz |
| 4 | XYz |
| 5 | xyZ |
+------+------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM test WHERE HEX(name) = HEX('xyZ');
+------+------+
| sno | name |
+------+------+
| 5 | xyZ |
+------+------+
1 row in set (0.00 sec)

Just converting the String Value to Hexa Decimal Value, We have get the Result of the Exact String

Cool .....

__________________
-Murali..

Hi Murali,
There is another way to achieve the case sensitive search in mysql using a single keyword BINARY.

Considering the example given earlier, just use the query given below without bothering about what character set / collation and all.

mysql> SELECT * FROM test WHERE BINARY name = 'xyZ';
+------+------+
| sno | name |
+------+------+
| 5 | xyZ |
+------+------+
1 row in set (0.00 sec)

__________________
Keep smiling...