Logo Questions Linux Laravel Mysql Ubuntu Git Menu
HTML CSS JAVASCRIPT SQL PYTHON PHP BOOTSTRAP JAVA JQUERY R React Kotlin
 

Case Sensitive collation in MySQL

Tags:

database

mysql

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.

like image 548
Keyur Padalia Avatar asked Dec 29 '10 23:12

Keyur Padalia

People also ask

What is case-sensitive in MySQL?

Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.

How do I make MySQL case-sensitive?

It is important to note that MySql is not only case insensitive for columns using an _ci collation (which is typically the default), but also accent insensitive. This means that 'é' = 'e' . Using a binary collation (or the binary operator) will make string comparisons accent sensitive as well as case sensitive.

Which collation is case-insensitive?

A case-insensitive collation ignores the differences between uppercase and lowercase letters for string comparison and sorting, whereas a case-sensitive collation does not. For example, in case-insensitive collation, “A” and “a” are equal.

What is case-sensitive collation in SQL Server?

SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.

3 Answers

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

like image 82
German Rumm Avatar answered Oct 07 '22 09:10

German Rumm

Try a collation ending in _bin, such as latin1_bin or utf8_bin, depending on your character set.

like image 45
Neil E. Pearson Avatar answered Oct 07 '22 08:10

Neil E. Pearson

The new version of MySQL (8.0.1 and higher) comes (finally) with a set of utf8mb4_*_0900_as_cs collations.

More about it here

like image 3
Jagger Avatar answered Oct 07 '22 09:10

Jagger
Sign in to Comment

Related questions

MySQL server has gone away - in exactly 60 seconds
What is the difference between SERIAL and AUTO_INCREMENT in mysql
Alter multiple columns in a single statement [duplicate]
In a join, how to prefix all column names with the table it came from
MySQL - SELECT all columns WHERE one column is DISTINCT
Can MySQL replace multiple characters?
MySQL - Meaning of "PRIMARY KEY", "UNIQUE KEY" and "KEY" when used together while creating a table
Handling MySQL datetimes and timestamps in Java
How can I use now() in Doctrine 2 DQL?
Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
How can I loop through a MySQL result set more than once using the mysql_* functions?
How to get numeric types from MySQL using PDO?
Pass array to MySQL stored routine
Why can't easy_install find MySQLdb?
MySQL connection over SSH tunnel - how to specify other MySQL server?
MySQL error 1241: Operand should contain 1 column(s)
How to unblock with mysqladmin flush hosts
Case statement in MySQL
Forbidden :You don't have permission to access /phpmyadmin on this server
Pymysql Insert Into not working

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!