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.5k times
3 Answers
94
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,782
- 1
- 25
- 30
-
1`utf8mb4_bin` exists, too, since it's 2019 now :-) – artfulrobot Aug 10 '19 at 10:12
-
To convert utf8 value to charset latin1 and then use latin1_general_cs for like-comparison use: select ... convert(cast(convert(myfield using utf8) as binary) using latin1) COLLATE latin1_general_cs like '...' – jfx Jan 12 '23 at 10:27
16
Try a collation ending in _bin, such as latin1_bin or utf8_bin, depending on your character set.
Neil
- 3,001
- 2
- 16
- 20
-
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
