Mysql Case sensitivity and checking rules

Mysql Case sensitivity and checking rules

When using mysql, you may encounter similar problems:

Root @ chuck 07:42:00> select * from test where c1 like 'AB % ';
+ ----- +
| C1 |
+ ----- +
| Abc |
| ABD |
+ ----- +

Fuzzy match AB %. Strings starting with AB also appear in the result set, which is naturally considered to be case sensitive. In mysql, how is the big and lowercase sensitivity controlled? How are the dictionary objects such as database names, table names, and field names and the size sensitivity of Field Values controlled? as well as the relationship between validation rules and indexes, this is the content to be discussed in this article.

The case sensitivity of database names and table names in mysql is controlled by the lower_case_table_names parameter. If the value is 0, it indicates case sensitivity. If the value is 1, it indicates that the name is converted to lower-case and stored without case sensitivity. Field names are usually case-insensitive. What is the field value? The case sensitivity of field values is controlled by mysql's checking rules. When it comes to proofreading rules, you have to talk about character sets. Character Set is A set of symbols and encodings. The collation is A set of rules used to compare characters in the character set, such as rules defining the relationship like 'A' <'B. Different character sets have multiple verification rules. Generally, the verification rules start with their character set names. Generally, they contain a language name and are case-insensitive (_ ci), _ cs (case sensitive), or _ bin (Binary. For example, the utf8_general_ci character set is not case sensitive. This is the default verification rule for the utf8_general_cs character set. The utf8_bin character set is case sensitive and the utf8_bin character set is case sensitive.

The checking rule is specified by the keyword collate. For example, when creating a database d1, the character set is utf8, and the checking rule is utf8_bin.

Create database d1 default character set utf8 COLLATE utf8_bin;

The preceding statement indicates that the data in database d1 is UTF-8 encoded and case sensitive. Sometimes, when we create a database, we do not specify the checking rules to check whether the characters are case sensitive. However, when we query the database, we need to be case sensitive to the characters. For example, in the example in the beginning, only a string with AB headers. It doesn't matter. mysql provides the collate syntax by specifying the utf8_bin proofreading rule.

Root @ chuck 08:19:35> select * from test where c1 like 'AB %' collate utf8_bin;
+ ----- +
| C1 |
+ ----- +
| Abc |
+ ----- +

Another way is to convert the string to binary by using the binary keyword for comparison. Because the binary of case-sensitive characters must be different, it can be considered as a way to differentiate the size.

Root @ chuck 07:50:35> select * from test where binary c1 like 'AB % ';
+ ----- +
| C1 |
+ ----- +
| Abc |
+ ----- +

The last thing to note is the relationship between checking rules and index storage. Because the checking rules are used for comparing strings, and the indexes are arranged in a more ordered manner, the checking rules affect the indexing order of records. The following is a small example:

 

1	Create a table	Create table test (c1 varchar (100), primary key (c1 ));	Create table test2 (c1 varchar (100), primary key (c1) collate utf8_bin;
2	Initialize data	Insert into test (c1) values ('abc '); Insert into test (c1) values ('abd '); Insert into test (c1) values ('zbc ');
3	Query	Select * from test;	Select * from test2;
4	Return Result set	| Abc | | ABD | | ZBC |	| ABD | | ZBC | | Abc |

Table 1

From table 1, we can see that the result sets returned by test and test2 have different records in different relative order. Because they are full table scans, the returned records reflect the primary key order. Because the test table validation rules use the default utf8_general_ci, Which is case insensitive, abc <ABC <ZBC; similarly, test2 uses utf8_bin, Which is case sensitive, so ABD <ZBC <abc.

How to Make fields case sensitive in mysql

Select * from abc whre id = "a" and
Select * from abc whre id = "A" the query results are different.
You do not need to set a single field. You want to set the default setting for mysql or the entire database.

Whether it is case sensitive depends on the checking rules. The default setting is case insensitive.
Show create table if you see that collate is the end of ci, there is no difference. If cs or bin ends, it is the difference.
If you select a case-sensitive rule when creating a table and do not want to distinguish it during query,
Similar
WHERE column_name COLLATE latin1_general_ci = 'xxx'
To change the checking rules used by the query.

How to Set case sensitivity for mysql database Columns

Mysql default settings or full database settings
Whether it is case sensitive depends on the checking rules. The default setting is case insensitive.
Show create table if you see that collate is the end of ci, there is no difference. If cs or bin ends, it is the difference.
If you select a case-sensitive rule when creating a table and do not want to distinguish it during query,
Similar
WHERE column_name COLLATE latin1_general_ci = 'xxx'
To change the checking rules used by the query.