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
7 Answers
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
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!
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.
Related