Mysql table name not working in uppercase

Question

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 

Answer 1

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

Answer 2

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