MySQL limitations on capitalization

Today, the developers asked me, does MySQL have a limit on capitalization? I don't think so, say no limit. After a while, the developers told me that there was a limit to Linux. I suddenly had the feeling of beating my face. For their own uncertain problems, must think clearly, find out what to say.

In Windows and Mac OS, the default value for Lower_case_tables_name is 1.

If you are using MySQL on only one platform, you usually do not need to change the lower_case_tables_name variable. However, if you import the database on an operating system that is sensitive to case sensitivity, you will encounter problems. On Linux, test_table and test_table are two different tables. In Windows, both of these indicate the same.

Avoid data transfer problems due to the case of database names and table names when data is being transferred, using two options:

(1) lower_case_tables_name=1 can be used in any system. The disadvantage of using this option is that when you use show tables or show databases, you cannot see whether the name is originally capitalized or lowercase

(2) using Lower_case_tables_name=0 in Linux, use lower_case_tables_name=2 in Windows. This preserves the case of the database name and table name. The disadvantage is that you must ensure that queries in Windows always refer to the database name and table name with the correct case. If the queries are transferred to Linux, they do not work because the capitalization is important in Linux and if the casing is not correct.

Exception: If you are using the InnoDB table, you should set Lower_case_tables_name to 1 on any platform to force the name to lowercase. Please note that before Lower_case_tables_name is set to 1 in Linux, the old database name and table name must first be converted to lowercase before restarting mysqld.

MySQL limitations on capitalization