Linux under Deployment MySQL, case sensitive trample pit record

After replicating the portal database in the development environment to the new environment today, there is a phenomenon that the primary key cannot be found when using the Sqlsugar ORM Framework for database operations. The key point was discovered for a long time. Hereby record.

1. Development environment:
Operating system: CENTOS7 64-bit
Memory: 1GB
CPU 1/1
Network adapter: Bridge mode
Installation mode: Minimized installation
System Language Settings: zh_cn.gb2312

Database version: MySQL 5.6.29 binary mode installation
Before establishing a database: MY.CNF parameter configuration
Character-set-server = UTF8
Log-bin=mysql-bin
Skip-name-resolve
Lower_case_table_names = 0 #之后可能设置过1的现象
Development DB Instance Name: Goodmes_p_v0_5
Table name in DB instance, column name all uppercase
The specific settings are not remembered for the time being, but the end result is that the data can be found regardless of the case of the table name in the SQL statement. According to speculation, database capitalization can also be recognized. The operation on the database is Dos.orm

2. This deployment environment:
Operating system: CENTOS7 64-bit
Memory: 1GB
CPU 1/1
Network adapter: Bridge mode
Installation mode: Minimized installation
System language Settings: ZH_CN. UTF-8

Database version: MySQL 5.6.29 binary mode installation
DB Instance Name: G_start
Table name in DB instance, column name all uppercase

When using the Sqlsugar ORM Framework for database operations, there is a phenomenon where the primary key cannot be found. By logging the MySQL log information and getting the database operation record information, it is necessary to convert the entity name, table name, and column name to case before Sqlsugar's ORM framework is operated on the database. However, the primary key information is not found because MySQL is case-sensitive by default. At the same time for the need for primary key information to handle the content, there will be problems, because Sqlsugar insingle operation or other for the primary key operation, will be a primary key to get the action, and this gets the primary key action is through
Select DISTINCT table_name as tablename,column_name as KeyName from INFORMATION_SCHEMA. COLUMNS where table_name= ' "+ tableName +" ' and column_key= ' PRI ' This SQL statement to get, although here the TableName passed in is the correct table name, but because it was converted to lowercase, So there's no data to be found here. This leads to the case where the primary key is not found (the pit of the Sqlsugar frame or the pit of most database operations Framework?). ）。
Check the online information, said can be set lower_case_table_names this database parameters to set, set the Lower_case_table_names=1 (case insensitive) a more serious problem arises, Because the name of the DB instance is also uppercase. But sqlsugar the same lowercase operation. So the database is still not found, the same MySQL client when selecting the database, there is also a case that the database is not found.
For MySQL website, Lower_case_table_names has a third parameter:
0: default, case sensitive; Note that if you use--lower-case-table-names=0 to force 0 on case-insensitive file systems and access MyISAM table names with different casing, you can cause index corruption.
1: The table name is saved in lowercase on the hard disk, and the name is not case sensitive. MySQL converts all table names to lowercase to store and find
2: The table name and database name are saved on the hard disk using the case specified by the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase for lookup. (This operation needs to be cautious, because once this is set up, it turns out to be a duplicity situation, and finding the problem is more difficult.) ）
It's obvious whether my purpose can be achieved by the final decision or file system, no matter how I set the case sensitivity or case insensitive, can not operate on the database. The difference is that one is able to connect to the database, and the database is not even found.
So my final result is:
1. Setting Database case insensitivity
2. Reconfigure an instance: instance name lowercase
3. Export the data sheet from the development environment to a copy of the
4. Import the table structure into the deployment environment, and you will find that the imported table names become lowercase. However, the names of the columns in the table are still uppercase.

Conclusion: In Windows MySQL database instances, tables, column names are case-insensitive, random toss. But to Unix-like systems such as Ubuntu,centos, it is important to consider carefully before deploying the database, whether it is case-sensitive or case-insensitive. The name of the instance, table, column names in the database to be capitalized or lowercase. In case of bad handling, the worst possibility is the database rebuild. Time consuming, but also thankless.

Recommendation: Name all lowercase, recommended case sensitive. Once set, it is not recommended to modify the database settings. It is also prudent to consider the database operating framework when it is applied. Learn how the underlying database works.

Linux under Deployment MySQL, case sensitive trample pit record