Is there any Collation type in MySQL which supports Case Sensitive. I had all type of collation in MySQL they all have _ci at the end of their name so they are case Insensitive collation.
Asked
Active
Viewed 4.0k times
3 Answers
92
According to MySQL Manual http://dev.mysql.com/doc/refman/5.0/en/charset-mysql.html you should be able to set collation to _cs for case sensitivity. You can get a list of _cs collations by executing SHOW COLLATION WHERE COLLATION LIKE "%_cs" query
After a little research:
Apparently there are no utf8_*_cs in MySQL (yet). If you need case sensitive collation for utf8 fields, you should use utf8_bin. This will mess up ORDER BY, but this can be fixed by ORDER BY column COLLATE utf8_general_ci
Source: http://forums.mysql.com/read.php?103,19380,200971#msg-200971 and http://forums.mysql.com/read.php?103,156527,198794#msg-198794
German Rumm
- 5,672
- 1
- 23
- 30
-
2[This](http://mysqlserverteam.com/mysql-8-0-1-accent-and-case-sensitive-collations-for-utf8mb4/) comes with MySQL 8.0.1 and higher. – Jagger Oct 11 '17 at 13:48
-
1`utf8mb4_bin` exists, too, since it's 2019 now :-) – artfulrobot Aug 10 '19 at 10:12
16
Try a collation ending in _bin, such as latin1_bin or utf8_bin, depending on your character set.
Neil E. Pearson
- 2,981
- 2
- 15
- 19
-
Thanks Neil IT works when i set user column to utf8_bin it retrives only the data which is specified in query though i have the data with same word i mean Keyur,keyur,KEYUR. any way thanks............. – Keyur Padalia Dec 29 '10 at 23:33
