Solved

How to Configure MYSQL Server for case insensitive queries

Posted on 2010-11-21
9
1,374 Views
Last Modified: 2012-08-13
I need to Configure MYSQL server to process Case Insensitive Queries , most of the ODBC   Developers written SELECT Queries using either Upper/Lower Case, I need to configure to work
for both the cases
0
Comment
Question by:tittu
  • 5
  • 2
  • 2
9 Comments
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
The SQL Keywords are case-insensitive (SELECT, FROM, WHERE, etc), but are often written in all caps. Case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.    Mysql has a configuration option to enable/disable it. Usually case-sensitive table names are the default on Linux MySql and case-insensitive used to be the default on Windows...

Manual suggest - Although database, table, and trigger names are not case sensitive on some platforms, you should not refer to one of these using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE.

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

Column, index, stored routine, and event names are not case sensitive on any platform, nor are column aliases.
However, names of logfile groups are case sensitive. This differs from standard SQL.
By default, table aliases are case sensitive on Unix, but not so on Windows or Mac OS X. The following statement would not work on Unix, because it refers to the alias both as a and as A:
mysql> SELECT col_name FROM tbl_name AS a
    -> WHERE a.col_name = 1 OR A.col_name = 2;
However, this same statement is permitted on Windows. To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use.

You can check it here for more

http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
Make sure the 'collation' on your tables and database to one of the 'ci' (case insensitive) variations for the contents.  Database and table names are case sensitive if the file system is.  Windows isn't, Unix/Linux is.  That is because those names are used as part of file names of the databases and tables.  http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
0
 

Author Comment

by:tittu
Comment Utility
I am using RHEL Linux 5 , please suggest me how to change to case insensitive
0
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
Hi,

Set the value of parameter lower_case_table_namets to 1 , If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.

Please note that this parameter can't be set dynamically so you need to add this to conf file and thus require a restart of MySQL.

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_lower_case_table_names


0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
I don't believe you can change the database and table names to case insensitive on Linux.  Those names are used as part of the filenames on that system.  Filenames are case sensitive on Linux/Unix.  If that's what you really need, you will have to move to a Windows server.
0
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
The changes would take place to only new tables created after setting the parameter and for old tables you need to modify to either small or cap
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
Comment Utility
Here is the TestCase proving what I commented earlier....


Platform: RHEL5, MySQL 5.0.91x

#### Assume that lower_case_table_names value os 0(default)

[root@nodea ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.05 sec)

mysql> use test;create table Ushastry(id int not null);
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
Query OK, 0 rows affected (0.01 sec)

#####  We cannot refer it in other cases...

mysql> select * from ushastry;
ERROR 1146 (42S02): Table 'test.ushastry' doesn't exist
mysql> select * from USHASTRY;
ERROR 1146 (42S02): Table 'test.USHASTRY' doesn't exist
mysql> \q
Bye

##### Add the parameter to conf file lower_case_names=1

[root@nodea ~]# locate my.cnf
[root@nodea ~]# vi /etc/my.cnf

##### Restart MySQL server; to reflect the changes we made...

[root@nodea ~]# service mysql restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL.                                            [  OK  ]
[root@nodea ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.91-enterprise-gpl MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

########## Create a table by name UShastry and can be refered in any case....

mysql> create table UShastry(id int not null);
\Query OK, 0 rows affected (0.14 sec)

mysql> SELECT * from UShastry;
Empty set (0.00 sec)

mysql> SELECT * from ushastry;
Empty set (0.01 sec)

mysql> SELECT * from Ushastry;
Empty set (0.00 sec)

mysql> SELECT * from USHASTRY;
Empty set (0.08 sec)

mysql> insert into ushastry values(1),(2);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * from USHASTRY;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql> SELECT * from Ushastry;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> SELECT * from ushastry;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.01 sec)

mysql>
0
 

Author Closing Comment

by:tittu
Comment Utility
worked
0
 
LVL 26

Expert Comment

by:ushastry
Comment Utility
Cool..

Thanks,
Umesh
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now