Analysis of the case of English letters in Mysql query _mysql

MySQL database in the query, sometimes the English letter case sensitive, sometimes not, mainly by the MySQL character validation rules of the setting of the decision, usually by default is not supported uppercase and lowercase sensitive.

1. What are character sets and validation rules?

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

Proofing rules generally have these characteristics:

Two different character sets cannot have the same proofing rules.
There is a default collation for each character set. 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 including a language name, and end with _CI (case insensitive), _cs (case sensitive), or _bin (two yuan).

2. Different levels of character sets and validation rules to control case sensitive

MySQL5.1 uses different character sets or collation rules in the same server, the same database, or even in the same table to mix the definition strings. Character set and proofing rules have 4 levels of default settings: Server, database, table, and join.

2.1 Server-level

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

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

Add under [mysqld]: Collation_server = Utf8_bin

Restart Instance

After you change the server-level checksum rule (collation_server), the database checksum rule (collation_collation) Inherits server-level defaults.

Attention:

This only applies when a new library is created after a reboot, and the existing library is not affected.

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

The same with the existing columns ...

mysql> CREATE database yutest0;
Query OK, 1 row Affected (0.00 sec)
mysql> use yutest0;
Database changed
mysql> create table T1 (name varchar);
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 corresponding collation settings at the server level are sensitive to query capitalization.

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

When Mysqld is started, the server character set and collation rules are determined according to the initial option settings used.

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

2.2 Database level

MySQL this way select the database character set and the database proofing rules:

If character set X and collate y are specified, then character set X and collation rule y are used.
If you specify character set x without specifying collate Y, the default collation 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 add under [mysqld]:

Collation_server = utf8_bin

collation_database = Utf8_bin

2 add under [Mysqld]:

Collation_database = Utf8_bin

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

As is visible, setting collation_database variables is not supported in the MY.CNF configuration file.

(2) Set up 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)

It can be seen that the corresponding collation settings at the database level, query case sensitive.

2.3 Table level

MySQL selects the table character set and proofing rules in the following way:

If character set X and collate y are specified, then character set X and collate y are used.
If you specify character set x without specifying collate Y, the default collation rules for character set X and character set X are used.
Otherwise, the database character set and the server proofing rules are used.

Set table-level checksum rules when creating a table:

mysql> CREATE DATABASE Yutest2;
Query OK, 1 row affected (0.01 sec) mysql> use Yutest2;
Database changed mysql> CREATE table T1 (name varchar)-> 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 settings at the table level are sensitive to query capitalization.

2.4 Connection Level

Consider what a "connection" is: It is what happens when you connect to a server. The client sends SQL statements, such as queries, sent to the server via a connection. The server sends a response to the client via a connection, such as a result set. For client connections, this causes problems with connection character sets and collation rules that can be resolved 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)

What character set is used in a query when the query leaves the client?

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

What character set should the server convert to when it receives a query?

When converting, the server uses character_set_connection and collation_connection system variables. It converts queries sent by clients from Character_set_client system variables to character_set_connection.

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

The character_set_results variable instructs the server to return query results to the character set used by the client. Include result data, such as column values and result metadata (such as column names).

3. Case-insensitive when creating database tables, there are still methods for case sensitivity when querying

3.1 Using COLLATE in SQL statements

Using the COLLATE clause, you can overwrite any default collation 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-proof rule. Each character set has a two-dollar proofing rule. For example, the two-yuan 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)