Solved

How to Configure MYSQL Server for case insensitive queries

Posted on 2010-11-21
9
1,391 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
ID: 34185751
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 83

Expert Comment

by:Dave Baldwin
ID: 34185755
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
ID: 34185934
I am using RHEL Linux 5 , please suggest me how to change to case insensitive
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:ushastry
ID: 34185959
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
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 34185964
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
ID: 34185968
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
ID: 34186025
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
ID: 34195305
worked
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34195387
Cool..

Thanks,
Umesh
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

808 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