"Resolved" MySQL Case sensitive solution and analysis under Windows

Windows MySQL case sensitive configuration [email Protected]http://www.cnblogs.com/swje/zhouwan 2017-3-27

Recently, under the Windows system operation of the database created under the Linux system, found something wrong, compared to a half-day to find that the case is sensitive to the problem caused by. Search the Internet, solve the problem, make a concise record.

Follow the online instructions:

WINDOWS:

Edit the My.ini file under the MySQL installation directory, add lower_case_table_names=0 under the [Mysqld] section (note: Case sensitive for 0 o'clock, not sensitive for 1 o'clock case, default is 1), You can implement MySQL to define table names according to the case state of the SQL statement that was built.

When you do that, there are always differences in theory and practice:

My MySQL installation path is: C:\Program files\mysql\mysql Server 5.7\bin (online some say My.ini configuration file is in this folder)

However, the file path where My.ini is located is: C:\ProgramData\MySQL\MySQL Server 5.7\my.ini

Then add lower_case_table_names=0 under the [mysqld] section.

Configure the file, to try how.

Then, not rebooting the system for testing, in another blog post see the official manual URL link, can't restrain curiosity to look at. It is not feasible to find the above configuration (see the following highlights for a specific analysis)

Possible errors can be found in the content of this Help blog and the following discussion: http://bbs.csdn.net/topics/391860287

MySQL official website manual-Case Sensitive Description:

(On the basis of the official website to add my exclusive Chinese commentary O (∩_∩) o~ English good students are best to read English documents, said more clearly)

How table and database names be stored on disk and used lower_case_table_names in MySQL was affected by the system variable, which can Set when starting mysqld. lower_case_table_namescan take the values shown in the following table. This variable does is affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows, the default value is 1. On OS X., the default value is 2.

Ps:mac os (2012 ago called Mac OS X, 2012-2016 called OS x)

value	meaning
0	Table and database names is stored on disk using the LE Ttercase specified in The create TABLE  or create DATABASE  statement. Name comparisons is case sensitive. (summary: Both case-sensitive when storing and querying, as specified when building the table) You should  not  set the variable to 0 if you were running MySQL on a system th At have case-insensitive file names (such as Windows or OS X). If you are variable to 0 with --lower-case-table-names=0  on A Case-insensitive file system and Access myisam  tablenames using different Lettercases, index corruption may result . ( highlight here )
1	Table names is stored in lowercase on disk and name com Parisons is not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. (summary: The case is not sensitive when stored and queried, both are converted to lowercase letters)
2	Table and database names is stored on disk using the "Lettercase specified CREATE TABLE CREATE DATABASE in the" or statement, but MySQL Conv ERTs them to lowercase on lookup. Name comparisons is not case sensitive. This works with file systems that is not a case sensitive! InnoDB table names is stored in lowercase, as fo R lower_case_table_names=1 . (Summary: Storage-case sensitive, as specified when the table is built; all queries are converted to lowercase letters)

1. Single-platform: If you is using MySQL on only one platform, you do not normally has to change the lower_case_table_names variable Default value.

2. Cross-platform: However, encounter difficulties if you want to transfer tables between platforms this differ in Fil e system case sensitivity. For example, on Unix, you can have both different tables named my_table MY_TABLE and, but on Windows these-names are consider Ed identical. To avoid data transfer problems arising from lettercase of the database or table names, you have the options:(cross-platform MySQL case Sensitive solution here!!! See what the official website says ~)

• use lower_case_table_names=1  on All Systems . The main disadvantage with this is if you use show TABLES  or show DATABASES , you don't see the names in their original lettercase.

• Use lower_case_table_names=0  on Unix and lower_case_table_names=2  on Windows . This preserves the lettercase of database and table names. The disadvantage of must ensure that your statements all refer to your database and table names with T He correct lettercase on Windows. If you are transfer your statements to Unix, where Lettercase is significant, they does not work if the lettercase is incorrect.

  Exception: If You is using InnoDB tables and you is trying to avoid these data transfer problems, you s Hould set to 1 on the all platforms to force names to being lower_case_table_names converted to lowercase.

It's a little complicated ... Originally understood, and came out a exception ... Originally happy mood again down, because my table is InnoDB, looked for a long day want to go to configure file ...

Well, it doesn't matter what the cross-platform is, and whether the configuration file is My.ini. To use the words directly, you want to distinguish the case, you can add binary when querying the statement, the concrete implementation is as follows:

(Reference post for this text: http://www.cnblogs.com/softidea/p/6047766.html. On the basis of this blog post, the content has been greatly streamlined and refined)

Reference scheme:

1. Specify case sensitivity when building a table:

The MySQL default query is case-insensitive, and binary identifies sensitive attributes if it is necessary to differentiate him from the table.

CREATE TABLE NAME (
Name VARCHAR (Ten) BINARY);

2. Add binary before the field name of the query condition:

Implementation of the SELECT * from TABLE in the SQL statement NAME where BINARY name= ' Clip ';

3. Set the character set to be case sensitive:

Utf8_general_ci --Case insensitive

Utf8_Bin-case-sensitive

"Set collate(proofing). COLLATE rules:

*_bin: Represents the binary case sensitive collation, which means
*_CS: Case sensitive collation, uppercase and lowercase
*_ci:case insensitive collation, case insensitive "

4, you can modify the field of collation is binary
Like what:
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME VARCHAR () BINARY CHARACTER SET UTF8 COLLATE utf8_bin DEFAULT NULL;

Resolved MySQL Case sensitive solution and analysis under Windows