Pitfalls based on MySQL default sorting rules

The default varchar type in MySQL is case insensitive (insensitive). If you want MySQL to be case sensitive, you need to set the sorting rules:

utf8_bin stores each character in a string as binary data, distinguishing between uppercase and lowercase letters.

utf8_genera_ci is case-insensitive. ci is the abbreviation of case insensitive, which means it is case-insensitive.

utf8_general_cs is case-sensitive. cs is the abbreviation of case sensitive.

Using utf8_genera_ci is not case-sensitive, which causes problems when the content of this field is case-sensitive, such as when it is used as a case-sensitive code or verification code.

The option utf8_general_cs is generally not available, so you can only use utf8_bin to distinguish between upper and lower cases.

1) Setting the sorting rules is reversible. If the previously set sorting rules do not meet the requirements, garbled characters may appear after changing the sorting rules. When the original sorting rules are restored, the garbled characters will disappear.

2) You can change the varchar type to varbinary

3) If you have already used the default sorting rule, utf8_genera_ci, and want to query the results case-sensitively, you can restrict it when querying:

select binary column from table;

or

select column2 from table where binary cloumn;

Supplement: Character set and collation in MySQL

Character Set:

Character is the general term for various texts and symbols, and character set is a collection of characters. Common character set names: ASCII character set, GB2312 character set, BIG5 character set, GB18030 character set, Unicode character set, etc.

Character encoding is required for computers to recognize text in various character sets. So some character sets use 2 bytes per character, and some character sets use 3 bytes per character.

Sorting rules: Examples

What is the difference between utf_bin and utf_general_ci?

ci stands for case insensitive, which means "case insensitive". a and A will be treated the same in character judgment;

bin is binary, a and A will be treated differently.

utf8_general_ci is case-insensitive, which you should use when registering your username and email address.

utf8_general_cs is case-sensitive. If you use this for your username and email address, it will cause adverse consequences.

utf8_bin: string Each string is stored as binary data. It is case-sensitive and can store binary content.

Therefore, the sorting rules mainly describe whether it is case-sensitive and the storage method.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.