Question User Question User - 4 months ago 26
Linux Question

How to make MySQL colum name case insensitive in Cent OS?

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?

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.

Comments