MySQL case sensitivity and proofing rules

In the process of using MySQL, you may encounter a similar problem:

[email protected] 07:42:00>select * FROM test where C1 like ' ab% ';
+-----+
| C1 |
+-----+
| ABC |
| ABD |
+-----+

Fuzzy matching ab%, the result of a string beginning with AB also appears in the result set, it is naturally considered to be case-sensitive problem. So how is case sensitivity in MySQL controlled, database names, table names, field names, and how the size sensitivity of field values is controlled, and the relationship of validation rules to indexes, which is what this article discusses.

The case sensitivity of controlling database names and table names in MySQL is controlled by the parameter lower_case_table_names, which is 0, which is case-sensitive, and is 1 o'clock, which means that the name is converted to lowercase and stored, not case-sensitive. Field names are usually case-insensitive, and are field values? The case of the field value is controlled by the MySQL proofing rules. When it comes to proofing rules, you have to say character sets. A character set is a set of symbols and encodings, a set of rules that are used to compare characters within a character set, such as a rule that defines a relationship such as ' A ' < ' B '. Different character sets have multiple proofing rules, in general, the proofing rules start with their associated character set names, usually including a language name, and end in _ci (case insensitive), _cs (case sensitive), or _bin (two). For example, the UTF8 character set, utf8_general_ci, is not case-sensitive, this is the default collation of the UTF8 character set, Utf8_general_cs is case-sensitive, Utf8_bin represents a binary comparison, and is also case-sensitive.

Proofing rules are specified by keyword collate, such as creating a database D1, specifying a character set of UTF8, and proofing rules for Utf8_bin

CREATE DATABASE d1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin;

The above statement illustrates that the data in database D1 is encoded by UTF8 and is case-sensitive. Sometimes when we build the library, we do not specify the collation of the collation of the character case-sensitive, but when we query, we need to be more case sensitive to characters, such as the beginning of the example, just want to begin with AB string. No problem, MySQL provides the collate syntax by specifying the Utf8_bin proofing rules.

[email protected] 08:19:35>select * FROM test where C1 like ' ab% ' collate utf8_bin;
+-----+
| C1 |
+-----+
| abc|
+-----+

There is another way to compare strings to binary by binary keyword, which can be thought of as a way to differentiate between uppercase and lowercase characters because they are definitely different in binary.

[email protected] 07:50:35>select * from test where binary c1 like ' ab% ';
+-----+
| C1 |
+-----+
| ABC |
+-----+

The last thing to note is the relationship between the proofing rules and the index store. Because proofing rules are used for comparisons between strings, and indexes are based on a relatively ordered arrangement, proofing rules affect the order in which records are indexed. Here is a small example to illustrate:

1	Build table	CREATE TABLE Test (c1 varchar, primary key (C1));	CREATE TABLE Test2 (c1 varchar, primary key (C1)) collate utf8_bin;
2	Initializing data	INSERT into Test (c1) VALUES (' abc '); INSERT into Test (c1) VALUES (' ABD '); INSERT into Test (c1) VALUES (' ZBC ');
3	Inquire	SELECT * from Test;	SELECT * from Test2;
4	Return Result set	| ABC | | ABD | | ZBC |	| ABD | | ZBC | | ABC |

Table 1

From table 1, you can see the result set returned by test and test2, the relative order of the records is different, because it is a full table scan, and the returned records reflect the primary key order. Because the test table check rule takes the default UTF8_GENERAL_CI, the case is not sensitive, so abc<abc<zbc;, test2 with Utf8_bin, case sensitive, so abd<zbc<abc.

MySQL case sensitivity and proofing rules