MySQL Query case sensitivity problem analysis

MySQL Query case sensitivity problem analysis

http://blog.rds.aliyun.com/2014/05/06/rds%e4%b8%ad%e7%9a%84mysql%e5%a4%87%e4%bb%bd%e6%81%a2%e5%a4%8d/

MySQL Query case sensitivity problem analysisApril 30, 2014 dba released reply

MySQL database in the query time, sometimes is the English letter case sensitive, and sometimes not, mainly by the MySQL character check rule settings, usually by default is not supported by uppercase and lowercase letters sensitive.

1. What are character sets and validation rules?

A character set is a set of symbols and encodings. Proofing rules are a set of rules that are used to compare characters within a character set. Any given character set has at least one proofing rule, and it may have several proofing rules. To list the proofing rules for a character set, use the show collation statement.

Proofreading rules generally have these characteristics:

• Two different character sets cannot have the same proofing rules.
• Each character set has a default proofing rule. For example, the UTF8 default proofing rule is utf8_general_ci.
• There are proofing rule naming conventions: they start with their associated character set names, usually include a language name, and end with _CI (case insensitive), _cs (case sensitive), or _bin (two yuan).

2. Different levels of character sets and check rules can control case sensitivity

MySQL5.1 use different character sets or proofing rules to mix definitions of strings on the same server, in the same database, or even in the same table. There are 4 levels of default settings for character set and proofing rules: Server-level, database-level, table-level, and connection-level.

2.1 Server-level

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

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

Added under [mysqld]: Collation_server = Utf8_bin

Restart Instance

After you change the server-level validation rules (Collation_server), the Database validation rules (Collation_collation) inherit the server-level by default.

Attention:

This applies only to newly created libraries after a reboot, and the existing libraries are not affected.

Similarly, even if the library's validation rules are changed, the existing tables are not affected by the modification;

In the same vein as the already existing column ...

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)

As you can see, the appropriate collation rules are set at the server level and the query is case sensitive.

(2) When the server starts, it is set according to the valid option

When Mysqld is started, the server character set and proofing rules are determined based on the initial option settings used.

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

2.2 Database level

MySQL this selects the database character set and database proofing rules:

• If character set X and collate y are specified, then character set X and proofing rule y are used.
• If character set X is specified and no collate Y is specified, the default proofing rules for character set X and character set X are used.
• Otherwise, the server character set and the server proofing rules are used.

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

Two sets of tests were performed:

1) under [Mysqld], add:

Collation_server = Utf8_bin

Collation_database = Utf8_bin

2) under [Mysqld], add:

Collation_database = Utf8_bin

Restart the instance, both groups do not start normally, the error message is as follows:

It is visible that setting the Collation_database variable is not supported 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)

As you can see, the corresponding collation rules are set at the database level and the query is case sensitive.

2.3 Table-level

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

• If character set X and collate y are specified, then character set X and collate y are used.
• If character set X is specified and no collate Y is specified, the default proofing rules for character set X and character set X are used.
• Otherwise, the database character set and server proofing rules are used.

To set a table-level validation rule when you create 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))-& Gt 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) 

As you can see, the corresponding collation rules are set at the table level, and the query is case sensitive.

2.4 Connection Level

Consider what a "connection" is: It is what you do when you connect to the server. The client sends an SQL statement, such as a query, to the server via a connection. The server sends a response over a connection to the client, such as a result set. For client connections, this can cause problems with connection character sets and proofing rules that can be resolved by 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, what character set is used in the query?

The server uses the Character_set_client variable as the character set used in queries sent by the client.

• What kind of character set should the server convert to after it receives a query?

When converting, 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 the character_set_connection.

• What 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 instructs the server to return 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. Case insensitive when creating database tables, there are still methods that are case-sensitive at query time

3.1 in the SQL used in statements Collate

Using the COLLATE clause, you can override any default proofing rules for a comparison. Collate can be used in a variety of SQL statements, such as Where,having,group by,order By,as, 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 shorthand character for the COLLATE clause. Binary 'x' is equivalent to 'x' collate y, where y is the name of the character set 'x' Two-dollar proofing rules. Each character set has a two-dollar proofing rule. For example, the two-dollar collation rule for the latin1 character set is Latin1_bin, so if column A is a 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)

Reference Links:

MySQL5.1 Reference Manual Http://dev.mysql.com/doc/refman/5.1/en/charset-server.html