1

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?

Community
  • 1
  • 1
Question User
  • 2,093
  • 3
  • 19
  • 29
  • _Now, I want to make MySQL work as case insensitive in Cent OS._ Why? – B001ᛦ Jul 08 '16 at 09:56
  • I have many tables in my server. While im fetching the result some of them are case insenstive. some of them are CAPS and some of them are lower case so i need to change the all over. If its to case insenstive that might save lot of time for me – Question User Jul 08 '16 at 09:58

2 Answers2

1

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.

danilonet
  • 1,757
  • 16
  • 33
0

Edit mysql configuration file (my.cnf), add lower_case_table_names = 1 under [mysqld]. and restart mysql service it will fix your issue.

Faizan Younus
  • 793
  • 1
  • 8
  • 13