Steve Bals
Steve Bals

Reputation: 1989

Mysql table name not working in uppercase

I need to change mysql is accept both uppercase and lowercase table name ,

select * from users

the above query working fine but the below query is not working,

select * from USERS 

Upvotes: 11

Views: 40613

Answers (7)

Kiryamwibo Yenusu
Kiryamwibo Yenusu

Reputation: 176

In order to have your SQL tables created in either Uppercase or camelCase, do the following.

Step 1: open your MySQL my.ini configuration file:

            => [Linux]\xampp\mysql\bin\my.ini

           => [Windows]C:\xampp\mysql\bin\my.ini

            => [xampp] click on mysql -> config then open my.ini file

Step 2: Search [mysqld] in my.ini file

Step 3: Add this lower_case_table_names=2

Step 4: save the file and restart MySQL service or (Xampp)

You can now create SQL Tables in case sensitive format.

Upvotes: 0

IfYouSeekAnthony
IfYouSeekAnthony

Reputation: 348

I spent ages trying to resolve this, where all other SQL queries were working without issue on my system.

As it turns out, I'd added the new table I was looking for into a different database by accident (working on a different project).

I recommend just double check you're looking for the right table, in the right database, in the right domain... Good luck!

Upvotes: 0

Aness
Aness

Reputation: 670

windows = lowercase.

I do not recommend changing the mysqld config to 0 or 2.

  • lower_case_table_names = 0 (comparisons are case-sensitive) If set to 0, table names are stored as specified and comparisons are case-sensitive.

  • lower_case_table_names = 1 (comparisons are not case-sensitive) If set to 1, table names are stored in lowercase on disk and comparisons are not case sensitive.

  • lower_case_table_names = 2 (comparisons are case-sensitive) If set to 2, table names are stored as given but compared in lowercase.

If you want to change it, find my.ini file in windows usually mysql directory, in wamp check the bin directory and add the "lower_case_table_names = 0" or "lower_case_table_names = 2"

This option also applies to database names and table aliases. Reference to https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

Upvotes: 10

Yasir Hashmi
Yasir Hashmi

Reputation: 31

One option can be setting all the table names to either lower or upper as per your OS needs.

select concat('rename table ', table_name, ' to ' , lower(table_name) , ';') from information_schema.tables where table_schema = 'your_schema_name';

copy all the lines and execute them :)

Upvotes: 0

Nagaraj S
Nagaraj S

Reputation: 13484

It depends on your system (Unix, Windows and Mac OS for the main values).

You need to set your system variable "lower_case_table_names" to 1 or 2, to make your database case insensitive.

SET lower_case_table_names=1;

or

SET lower_case_table_names=2;

click at Mysql.com and here

Upvotes: 11

Scary Wombat
Scary Wombat

Reputation: 44854

Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix.

In MySQL, databases correspond to directories within the data directory. 
Each table within a database corresponds to at least one file within the database
directory. Consequently, the case sensitivity of the underlying operating system 
plays a part in the case sensitivity of database and table names.

Read the section: 8.2.2. Identifier Case Sensitivity

But to solve your problem, you could create a view of the table setting the name as the different case. But then what happens to users

Upvotes: 0

Krish R
Krish R

Reputation: 22721

Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

Upvotes: 0

Related Questions