How to force case sensitive table names?

2023-03-10 21:18 问答作者：

I'开发者_StackOverflow中文版m working on a MySQL database in windows.

I must move it into a Linux environment. MySQL database contains a lot of tables and stored procedures which are CASE SENSITIVE.

When I backup my database, all table names are forced lowercase so when I restore it in Linux it complains because you can't make duplicate tables and stored procedures.

I don't have access to the MySQL configuration in the linux environment so I cant change MySQL settings to case insensitive mode.

Is there any way to force MySQL (v5.x) to use case sensitive table names in windows?

Read the following chapter in the official MySQL documentation: Identifier Case Sensitivity.

Then add the following system variable to the server section, [mysqld], inside the my.ini configuration file and restart the MySQL service:

lower_case_table_names=2

Have a look at this article - http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html

Mode 2 allows to store tables with specified lettercase, but anyway, name comparisons won't be case sensitive and you won't be able to store table1 and Table1 at the same time.

On Windows put lower_case_table_names=2 at the end of the
C:\ProgramData\MySQL\MySQL Server 5.7\my.ini file.

Unfortunately there's no way of making MySQL on windows to behave 100% as in Linux. What you can do, is to run a minimal VM on Virtual Box or VMware player with TurnKey - MySQL Appliance.

In my personal experience, I've found quite useful to have a VM with a configuration similar to the deployment environment to diagnose problems.

Add this property to lower_case_table_names=2 to my.ini

By default, table names are case sensitive in Windows, but you can make it case sensitive by updating the lower_case_table_names variable to 2.

ref: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

However, before going to the further process, create backups of your existing databases. Then you need to remove all existing databases to avoid table name inconsistency.

We were on the step to set lower_case_table_names to 2, but this variable is read-only, so you can not update it using the script. So need to change it to the configure file (my.ini). For windows, it's available in the C:\ProgramData\MySQL\MySQL Server X.X directory. Open this file in administrator mode and set the lower_case_table_names variable value to 2.

These changes will not take effect until you restart the MySQL server before restarting the MySQL Server by following steps.

Open the Run window by using the Windows+R keyboard
Type services.msc and press Enter
Select the MySQL service and click the restart button

ref: https://www.mysqltutorial.org/mysql-adminsitration/restart-mysql/

MySQL server might not start because of changes in the my.ini file. In that case, we can not proceed further. Therefore lower_case_table_names variable value set back 1 again. then restart the MySQL server mentioned above.

Finally, we only have one remaining option to make a case-sensitive table name for MySQL in windows, completely removing MySQL from your machine and configuring it during installation. During install, check Advanced and Logging Options from the Type and Networking tab, then select the radio button to preserve the given case from the Advanced Options tab.

ref: Can't set lower_case_table_names in MySQL 8.x on Windows 10

i add this line and it solve the different case problem

lower_case_table_names=1

in /etc/my.cnf

you can see how to set mysql variable "lower_case_table_names" form this page : https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_lower_case_table_names

In MySQL 8, lower_case_table_names setting is not allowed once installation is done. To enable the setting , you have to re-install mysql