Case sensitivity of MySQL & amp; Entity Framework Code First data table

I used to use MySQL on Linux. for debugging convenience, I installed MySQL Windows 5.6.10 on the development machine. When I used Entity Framework Code First to generate database objects, it is found that the table names of all data tables are changed to lower-case characters, while the field names are case-sensitive. This problem has not occurred on the Linux platform, so I started to figure out whether it is swollen or not.

Search online to find this article: Entity Framework with mysql, Table Capitalization issue between linux and windows

The root cause of this problem is the operating system. MySQL originally developed MySQL on the Linux platform to store data tables to files. The file names and data table names are case sensitive, most Linux file systems are case sensitive.

Later, MySQL launched the Windows platform version, while Windows platform is case-insensitive, so it cannot be case-sensitive. To solve this problem, you need to add settings to ignore the case sensitivity of the table name. Therefore, the lower_case_table_names setting option is set. On Windows, you can set the file in the my. ini file. The file is located in C: \ ProgramData \ MySQL Server 5.6 in Windows 7 or Windows.Directory. On Linux, you can modify the settings in my. cnf.

Default Option --
Linux: Case Sensitive-Sensitive
Windows: Case Insensitive-Insenstitive

The cause of the problem is found. In Linux, we can set lower_case_table_names to 1 to enable case insensitive. You can also set MySQL to lowercase in Windows, but this is not a good idea.

Remember, you need to restart the MySQL service after changing the settings.

In Linux, it is best to use case-sensitive settings to achieve higher performance. In Windows, it is best to use case-insensitive settings, because there cannot be two tables with only case-insensitive files with the same names and letters.

This is why MySQL performance in Linux is better than that in Windows. (Other reasons include better scheduled disk IO and file systems)

You can also use the following statement to specify the case sensitivity when creating a database:

Create database test_database character set utf8 COLLATE utf8_general_cs; // test: This sentence cannot be used in MySQL on Windows; create database test_database character set utf8 COLLATE utf8_general_ci;

You can also set Case sensitivity for a data table:

DROP TABLE IF EXISTS single_test_table;CREATE TABLE single_test_table(  single_test_id int unsigned NOT NULL auto_increment,  ...  PRIMARY KEY PK_single_test_id (single_test_id ),  ...) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_cs;

The official document 5.6.10 states:

If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.

If the InnoDB engine is used, lower_case_table_names = 1 should be set for all platforms to be converted to lowercase.

[mysqld]lower_case_table_names=1

To make MySQL on Windows and Linux work together, you need to set the global variable lower_case_table_names = 1 on Linux, convert the data table name to lowercase (Case Insensitive ).

 

Postscript:

If lower_case_table_names = 0 is set on Windows, it means that case sensitivity is enabled. At this time, the names of the tables created by the Entity Framework Code First initialization database are case-sensitive, but when SQL statement query is executed, the SQL table name is still case insensitive.

Entity Framework Code First initializes the database and creates a table: testdb. appUsers: Execute an SQL statement in Workbench and use a lower-case data table name (if testdb. appUsers will not cause problems), for example, SELECT * FROM testdb. appusers, not in the query window, and then try to delete the DATABASE: drop database testdb. If the DATABASE cannot be deleted correctly, a file: appusers will be left in the testdb directory. idb, and the MySQL service cannot be stopped, it does not help to force the system to restart. Finally, you can only uninstall the MySQL database software, delete the data directory, and reinstall it.

After repeated cross-tests (thanks to the Snapshot feature of VMWare Fusion), if lower_case_table_names = 0 is removed on Windows, the above problem will not occur and the conclusion is: do not get angry! You cannot enable case sensitivity on Windows!