I am using Cent OS 6.5 and MySQL 5.1.73 and I know database name and table name and also column names are case sensitive in Cent os
Now, I want to make MySQL work as case insensitive in Cent OS.
I have tried the below link its worked for the table name. But i need to case sensitive for the table-column names
How to force case sensitive table names?
Is it possible? If yes, how can I do it?
Advertisement
Answer
It depends from the type of the table you create. For strings (CHAR, VARCHAR, TEXT), string searches use the collation of the comparison operands.
http://dev.mysql.com/doc/refman/5.7/en/case-sensitivity.html
for exemple if the table collation is utf8_general_ci the _CI indicates case insensitive
else the collation utf8_general is case sensitive
you can easily change the collation of your table with an update.
example of a CASE INSENSITIVE TABLE (utf8_general_ci)
CREATE TABLE `test` ( `id` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , `value1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci
example of a CASE SENSITIVE TABLE (utf8_general)
CREATE TABLE `test` ( `id` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL , `value1` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general NOT NULL ) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general
Also if you want you can change the default collaction as explained here https://dev.mysql.com/doc/refman/5.7/en/charset-syntax.html
There are default settings for character sets and collations at four levels: server, database, table, and column.