MySQL Server Forum - case sensative issues?

Author

case sensative issues?

Peter Kellner

2005-08-14, 8:23 pm

I notice that on unix the default for case sensative is off which means
tables created with upper case need to be referenced in sql as upper
case. It seems that if the case sensative variable is set to 1, then
select emp works as well as select EMP. Is there any reason not to set
it to 1? Also, is there an ansi standard for this? I could not find a
reference to one.

Thanks,

-Peter

(reference I found to case sensitivity)
http://dev.mysql.com/doc/mysql/en/n...ensitivity.html

--
Peter Kellner
http://peterkellner.net

Malcolm Dew-Jones

2005-08-15, 3:23 am

Peter Kellner (peter@peterkellner.net) wrote:
: I notice that on unix the default for case sensative is off which means
: tables created with upper case need to be referenced in sql as upper
: case. It seems that if the case sensative variable is set to 1, then
: select emp works as well as select EMP. Is there any reason not to set
: it to 1? Also, is there an ansi standard for this? I could not find a
: reference to one.

Are you talking about " lower_case_table_nam
es"?

I'm not quite sure what you mean when you say "on unix the default for
case sensative is off".

On unix the lower_case_table_nam
es has the value 0, which is one of three
settings (i.e. not just "on" or "off"). The word "off", if you did use
it, would refer to whether your queries will be modified to force them to
have lower case characters. In that interpretation, 0 means "do not
modify my queries", which is not really the same thing as being case
sensitive.

Indeed the table name on unix is always case sensative, since they are
stored in a file of that name. The lower_case_table_nam
es variable
doesn't change that. It just controls the modification of your queries to
force them to use lower case characters.

So, if the table was originally defined using upper case characters then
the modified query would not be able to access the table.

On the other hand, if you set the variable to 1 before defining anything,
or carefully defined everything in lowercase initialy, then everything
will be created with lower case names, so a modified query will work.

I do not know the ansi standards about case sensitivity for table names,
but I would be surprised if a feature such as that controlled by
lower_case_table_nam
es is covered by the standard.

The bottom of the page you mention has at least one user's feedback with
warnings about using this feature. However another lesson from that
feedback might be that you should turn the setting to 1 to force
everything to be lowercase right from the start.

--

This space not for rent.

Peter Kellner

2005-08-15, 3:23 am

This followup was posted to mailing.database.mysql

In article <42fff924@news.victoria.tc.ca>, yf110@vtn1.victoria.tc.ca
says...
> Peter Kellner (peter@peterkellner.net) wrote:
> : I notice that on unix the default for case sensative is off which means
> : tables created with upper case need to be referenced in sql as upper
> : case. It seems that if the case sensative variable is set to 1, then
> : select emp works as well as select EMP. Is there any reason not to set
> : it to 1? Also, is there an ansi standard for this? I could not find a
> : reference to one.
>
> Are you talking about " lower_case_table_nam
es"?
>

Sorry for not being more clear. You assumed correctly what I was
looking for. I'm inclined to change the setting to force lowercase,
however since this is not the default behavior it makes me nervous to
change it.

--
Peter Kellner
http://peterkellner.net

Malcolm Dew-Jones

2005-08-15, 3:23 am

Peter Kellner (peter@peterkellner.net) wrote:
: This followup was posted to mailing.database.mysql

: In article <42fff924@news.victoria.tc.ca>, yf110@vtn1.victoria.tc.ca
: says...
: > Peter Kellner (peter@peterkellner.net) wrote:
: > : I notice that on unix the default for case sensative is off which means
: > : tables created with upper case need to be referenced in sql as upper
: > : case. It seems that if the case sensative variable is set to 1, then
: > : select emp works as well as select EMP. Is there any reason not to set
: > : it to 1? Also, is there an ansi standard for this? I could not find a
: > : reference to one.
: >
: > Are you talking about " lower_case_table_nam
es"?
: >

: Sorry for not being more clear. You assumed correctly what I was
: looking for. I'm inclined to change the setting to force lowercase,
: however since this is not the default behavior it makes me nervous to
: change it.

I have no experience to share on this.

My guess is that if you use this setting from day 0 of your database then
you won't have a problem. Every item for which this would make a
difference will be forced to use lowercase.

If you then turned off the setting then you could still access everything
by writing your queries in lowercase.

That's my reading of it, but it's not based on experience.

--

This space not for rent.

Sponsored Links