MySQL Entity Framework Code First 数据表大小写的问题
以前都是使用Linux平台下的MySQL,现在为了调试方便,在开发机上安装了MySQL的Windows版本5.6.10,在使用Entity Framework Code
以前都是使用Linux平台下的MySQL,现在为了调试方便,在开发机上安装了MySQL的Windows版本5.6.10,在使用Entity Framework Code First生成数据库对象时,发现所有的数据表的表名都变成了小写字符,而字段的名称是混合大小写的,这个问题在Linux平台上没有出现过,于是着手弄明白这是肿么一回事。
经过网上搜索,,找到这篇文章:Entity Framework with mysql, Table Capitalization issue between linux and windows
大致的意思是说:这个问题产生的根源是操作系统,MySQL保存数据表到文件,最初MySQL是在Linux平台开发的,文件名和数据表名称都是大小写敏感的,因为绝大多数的Linux文件系统是大小写敏感的。
后来,MySQL推出Windows平台的版本,而Windows平台是大小写不敏感的,所以无法区分大小写的名称。为了解决这个问题,需要添加设置来忽略表名的大小写。于是建立了lower_case_table_names设置选项。在Windows平台可以在my.ini文件中设置,该文件在Windows7或Windows2008操作系统中位于 C:\ProgramData\MySQL\MySQL Server 5.6 目录下。在Linux平台可以修改my.cnf中的设置项。
缺省的选项--
Linux平台:大小写敏感 Case-Sensitive
Windows平台:大小写不敏感 Case-Insenstitive
问题的原因找到啦,我们可以在Linux中设置lower_case_table_names为1启用大小写不敏感。也可以设置MySQL在Windows中大小写敏感,但是这不是个好主意。
记住,更改设置后需要重启MySQL服务。
在Linux中最好使用大小写敏感的设置,会获得更高的性能。在Windows平台最好使用大小写不敏感的设置,因为不能存在两个仅有大小写差异而文件名字母相同的表。
由此而生,这也是为什么Linux下MySQL的性能会好过Windows下的原因。(其他原因有:更好的计划调度更快的磁盘IO和文件系统)
也可以在创建数据库时使用下面的语句来指定大小写敏感设置:
utf8 COLLATE utf8_general_cs; //实测:这一句不能在Windows平台下的MySQL使用; utf8 COLLATE utf8_general_ci;
相应的,也可以为某一个数据表设置大小写敏感:
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;
官方文档5.6.10中指出:
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
如果使用InnoDB引擎,应该在所有的平台设置lower_case_table_names=1强制名称都转换为小写。
lower_case_table_names=1
如果要使Windows平台下的MySQL和Linux平台下的MySQL协同工作,你需要设置Linux平台下的全局变量lower_case_table_names=1,强制将数据表名称转换为小写(大小写不敏感)。
后记:
如果在Windows平台下设置了lower_case_table_names=0,意味着开启大小写敏感,这时Entity Framework Code First初始化数据库创建的表名是大小写混合的,可是在执行SQL语句查询时,SQL表名仍然是大小写不敏感的。
Entity Framework Code First初始化数据库创建了一个表:testdb.AppUsers,在Workbench中执行一个SQL语句,使用小写的数据表名(如果用testdb.AppUsers就不会出问题),例如SELECT * FROM testdb.appusers,不关查询窗口,然后尝试删除数据库:DROP DATABASE testdb,数据库无法正确的删除,会在testdb目录下留下一个文件:appusers.idb,而且MySQL服务也无法停掉,强制重启系统也无济于事,最后只能卸载MySQL数据库软件,删除数据目录,然后重新安装。
又经过反复交叉试验(这要感谢VMWare Fusion的Snapshot功能),Windows平台下去掉lower_case_table_names=0,就不会有上述问题,得出结论:不要*!不能在Windows平台下启用大小写敏感!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



InnoDB's full-text search capabilities are very powerful, which can significantly improve database query efficiency and ability to process large amounts of text data. 1) InnoDB implements full-text search through inverted indexing, supporting basic and advanced search queries. 2) Use MATCH and AGAINST keywords to search, support Boolean mode and phrase search. 3) Optimization methods include using word segmentation technology, periodic rebuilding of indexes and adjusting cache size to improve performance and accuracy.

Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.

Yes, MySQL can be installed on Windows 7, and although Microsoft has stopped supporting Windows 7, MySQL is still compatible with it. However, the following points should be noted during the installation process: Download the MySQL installer for Windows. Select the appropriate version of MySQL (community or enterprise). Select the appropriate installation directory and character set during the installation process. Set the root user password and keep it properly. Connect to the database for testing. Note the compatibility and security issues on Windows 7, and it is recommended to upgrade to a supported operating system.

The difference between clustered index and non-clustered index is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The non-clustered index stores index key values and pointers to data rows, and is suitable for non-primary key column queries.

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.
