Analysis of English letters and cases in MySQL Query _ MySQL

This article mainly introduces the analysis on English letters and lowercase letters in MySQL. The author has conducted experiments from multiple perspectives. if you need a friend, refer to the mysql database for query, sometimes English letters are case-sensitive and sometimes not true. it is mainly determined by the character verification rules of mysql. Usually, it is not supported by default.

1. what are character sets and verification rules?

Character set is a set of symbols and encoding. A collation is a set of rules used to compare characters in a character set. Any given character set must have at least one Verification rule. it may have several verification rules. To list the COLLATION of a character set, use the show collation statement.

Proofreading rules generally have these features:

Two different character sets cannot have the same verification rules.
Each character set has a default verification rule. For example, the default verification rule for utf8 is utf8_general_ci.
There is a naming convention for proofreading rules: they start with their relevant character set names, usually include a language name, and are case-insensitive (_ ci), _ cs (case-sensitive) or _ bin (binary) ends.

2. different character sets and validation rules can control case sensitivity.

MySQL5.1 uses different character sets or verification rules in the same server, database, or even table to mix and define strings. Character set and verification rules have four default settings: Server-level, database-level, table-level, and connection-level.

2.1 Server level

MySQL determines the server character set and server verification rules as follows:

(1) modify the configuration file/etc/my. cnf

Add: collation_server = utf8_bin under [mysqld]

Restart an instance

After you change the server-level validation rules (collation_server), the database validation rules (collation_collation) inherit from the server-level by default.

Note:

This only applies to the new database after the restart, and the existing database is not affected.

Similarly, even if the database validation rule is changed, the existing tables are not affected by the modification;

Similarly, the existing columns...

mysql> create database yutest0;Query OK, 1 row affected (0.00 sec)mysql> use yutest0;Database changedmysql> create table t1 (name varchar(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values('AAA');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values('aaa');Query OK, 1 row affected (0.01 sec)mysql> select * from t1;+------+| name |+------+| AAA || aaa |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='aaa';+------+| name |+------+| aaa |+------+1 row in set (0.00 sec)

It can be seen that the checking rules are set at the server level to make the query case sensitive.

(2) set valid options when the server is started

When mysqld is started, the server character set and proofreading rules are determined based on the initial options used.

Shell> mysqld -- character-set-server = latin1 -- collation-server = latin1_swedish_ci

2.2 database level

MySQL selects the database character set and database verification rules as follows:

If character set X and collate Y are specified, character set X and checking rule Y are used.
If the character set X is specified but the collate Y is not specified, the character set X and character set X are used as the default proofreading rules.
Otherwise, the server character set and server verification rules are used.

(1) modify the configuration file/etc/my. cnf

Two groups of tests were conducted:

1) under [mysqld], add:

collation_server = utf8_bincollation_database = utf8_bin

2) under [mysqld], add:

collation_database = utf8_bin

Restart the instance. The two groups cannot start normally. the error message is as follows:

It can be seen that the collation_database variable cannot be set in the my. cnf configuration file.

(2) Set Database validation rules when creating a database

mysql> create database yutest default character set utf8 collate utf8_bin;Query OK, 1 row affected (0.00 sec)mysql> show variables like 'collation_%';+----------------------+-----------------+| Variable_name    | Value      |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database  | utf8_bin    || collation_server   | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || abc |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='abc';+------+| name |+------+| abc |+------+1 row in set (0.01 sec)

We can see that the checking rules are set at the database level to make the query case sensitive.

2.3 Table level

MySQL selects the table character set and proofreading rules as follows:

If character set X and collate Y are specified, character set X and collate Y are used.
If the character set X is specified but the collate Y is not specified, the character set X and character set X are used as the default proofreading rules.
Otherwise, the database character set and server verification rules are used.

Set Table-level validation rules when creating a table:

mysql> create database yutest2;Query OK, 1 row affected (0.01 sec)mysql> use yutest2;Database changedmysql> create table t1(name varchar(10))  -> default character set utf8 collate utf8_bin;Query OK, 0 rows affected (0.01 sec)mysql> insert into t1 values('ABC');Query OK, 1 row affected (0.00 sec)mysql> insert into t1 values('abc');Query OK, 1 row affected (0.00 sec)mysql> show variables like 'collation_%';+----------------------+-----------------+| Variable_name    | Value      |+----------------------+-----------------+| collation_connection | utf8_general_ci || collation_database  | utf8_general_ci || collation_server   | utf8_general_ci |+----------------------+-----------------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || abc |+------+2 rows in set (0.00 sec)mysql> select * from t1 where name='abc';+------+| name |+------+| abc |+------+1 row in set (0.00 sec)

It can be seen that the checking rules are set at the table level to make the query case sensitive.

2.4 Connection level

Consider what a "connection" means when connecting to the server. The client sends SQL statements, such as queries, to the server through connections. The server sends a response to the client through a connection, such as a result set. For client connections, this will cause some problems concerning the character set and verification rules of connections. these problems can be solved through system variables:

mysql> show variables like 'character%';+--------------------------+----------------------------+| Variable_name      | Value           |+--------------------------+----------------------------+| character_set_client   | utf8            || character_set_connection | utf8            || character_set_database  | utf8            || character_set_filesystem | binary           || character_set_results  | utf8            || character_set_server   | utf8            || character_set_system   | utf8            || character_sets_dir    | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)

When the query leaves the client, which character set is used in the query?

The server uses the character_set_client variable as the character set used in the query sent by the client.

What character set should the server convert to after receiving the query?

During conversion, the server uses character_set_connection and collation_connection system variables. It converts the query sent by the client from the character_set_client system variable to character_set_connection.

Which character set should the server convert to before sending a result set or returning an error message to the client?

The character_set_results variable indicates that the server returns the query result to the character set used by the client. Includes result data, such as column values and result metadata (such as column names ).

3. when creating a database table, it is not case sensitive. you can still query the time zone in case sensitive mode.

3.1 Use collate in SQL statements

The collate clause can overwrite any default checking rules for a comparison. Collate can be used in multiple SQL statements, such as where, having, group by, order by, as, and aggregate functions.

mysql> select * from t1 where name collate utf8_bin = 'ABC';+------+| name |+------+| ABC |+------+1 row in set (0.00 sec)mysql> select * from t1 where name = 'ABC';+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)mysql> select * from t1;+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)

3.2 binary operator

The binary operator is a stenographer of the collate clause. Binary 'X' is equivalent to 'X' collate y. here, y is the name of the character set 'X' binary verification rule. Each character set has a binary verification rule. For example, the dual check rule for latin1 character set is latin1_bin. Therefore, if Column a is character set latin1, the following two statements have the same effect:

select * from t1 order by binary a;select * from t1 order by a collate latin1_bin;mysql> select * from t1 where binary name = 'ABC';+------+| name |+------+| ABC |+------+1 row in set (0.00 sec)mysql>mysql> select * from t1 where name = 'ABC';+------+| name |+------+| ABC || Abc || abc |+------+3 rows in set (0.00 sec)