How to Make MySQL Table Names Case-Insensitive on Linux?

Solving MySQL Table Name Case Sensitivity in Linux

Introduction

MySQL's table name case sensitivity can create compatibility issues when migrating websites from Apple servers to Linux servers. While Mac and Windows servers have a default case-insensitive file system, Linux servers typically do not. This can lead to table names in queries being mismatched and causing website malfunctions.

Solution

To address this issue and make Linux MySQL behave case-insensitively like Mac and Windows servers, a system variable called lower_case_table_names can be modified. This variable dictates how the server handles identifier case sensitivity.

Steps

1. In the MySQL configuration file (my.cnf or my.ini), locate the lower_case_table_names variable and set its value to 1.
2. Restart the MySQL service to apply the changes.

By setting lower_case_table_names to 1, table names will be stored in lowercase on disk and queries will no longer be case-sensitive. This ensures compatibility with the old website's scripts without the need to modify table names or scripts.

Additional Information

• Database and table names are typically not case sensitive on Mac and Windows due to their case-insensitive file systems.
• On Linux, the file system is usually case-sensitive, making table names case-sensitive as well.
• Setting lower_case_table_names to 0 should be avoided if running MySQL on systems with case-insensitive file names (e.g., Windows or Mac OS X) to prevent index corruption.

The above is the detailed content of How to Make MySQL Table Names Case-Insensitive on Linux?. For more information, please follow other related articles on the PHP Chinese website!