MySQL distinguishes a field case solution

Today's production environment MySQL encountered a problem, an application in the production environment is a distinction between uppercase, but MySQL is not case-insensitive, this is the pit dad, the data found to have two, the program error said it was overwhelmed, how to do ... What to do ... Baidu after found the solution, as follows:

• MySQL default is case-sensitive, if you have done some configuration, then the size is not differentiated, you can see the value of Lower_case_table_names by show Variables like '%table_names ', 0 for the distinction, 1 for the non-differentiated.

• MySQL by default in Linux is to distinguish between table name case, and in Windows under the table name is not case-insensitive, the way MySQL does not differentiate between table name casing is actually very simple:

1. Log in with root, modify/ETC/MY.CNF

2. Add a line under [mysqld]: Lower_case_table_names=1

3. Restart the database to

• MySQL field varchar case-sensitive utf8_bin, UTF8_GENERAL_CI encoding differences

  There are various UTF8 encoding formats in MySQL:

  Utf8_bin stores each character in a string with binary data, which is case-sensitive.

  Utf8_genera_ci is case-insensitive, CI is the abbreviation for case insensitive, which is casing insensitivity.

  The Utf8_general_cs is case-sensitive, and CS is a case-sensitive abbreviation, that is, uppercase and lowercase.

  Using UTF8_GENERA_CI is not case-sensitive, which causes the contents of this field to be case-sensitive, such as a case-sensitive code or verification code.

  Utf8_general_cs This option is generally not available, so you can only use Utf8_bin to match case

Solution One:

After opening the design table: we will see the following options:

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8F/3E/wKioL1jYs0vyuBR2AAA-NqN-mKA340.png "title=" QQ picture 20170327143732.png "alt=" Wkiol1jys0vyubr2aaa-nqn-mka340.png "/>

UTF8_GENERAL_CI: Case insensitive

Utf8_bin: Case Sensitive

But : Using the above workaround is not good If this is the case, it may spread to other tables or fields, and other problems can not be imagined, so there is the following solution, solution two:

When the query is followed by a keyword binary in the where, then the latter relationship is searched in binary way.

SELECT * from Tab_name WHERE BINARY url = ';

This article is from the "Learning to change the Destiny" blog, please make sure to keep this source http://xinsir.blog.51cto.com/5038915/1910722

MySQL distinguishes a field case solution