MYSQLクエリーの大文字と小文字が敏感かどうかの問題分析

mysqlデータベースはクエリーを行うとき、英語のアルファベットの大文字と小文字が敏感な場合もあり、そうでない場合もあります.主にmysqlの文字チェックルールの設定によって決まります.通常、デフォルトではサポートされていない大文字と小文字が敏感です.
 
 1.文字セットと検証ルールとは?
文字セットは記号と符号化のセットです.校正規則は、文字セット内で文字を比較するための規則です.任意の指定された文字セットには、少なくとも1つの校正規則があり、いくつかの校正規則がある可能性があります.文字セットの照合ルールをリストするには、SHOW COLLATION文を使用します.
校正規則には、一般的に次のような特徴があります.

の2つの異なる文字セットには、同じ校正規則はありません.

各文字セットには、デフォルトの校正規則があります.例えば、utf 8のデフォルトの校正規則はutf 8_であるgeneral_ci.

には、関連する文字セット名で始まり、通常は言語名を含み、_ci(大文字と小文字が敏感ではない)、cs(大文字と小文字が敏感)または_bin(二元)終了.

 
2.異なるレベルの文字セットとチェックルールにより、大文字と小文字の機密性を制御
MySQL5.1同じサーバ、同じデータベース、または同じテーブルで異なる文字セットまたは校正規則を使用して定義文字列をブレンドします.文字セットと校正規則には、サーバレベル、データベースレベル、テーブルレベル、接続レベルの4つのレベルのデフォルト設定があります.
2.1サーバ・レベル
MySQLでは、サーバ文字セットとサーバ照合ルールを次のように決定します.
(1)プロファイルの変更/etc/my.cnf
[mysqld]で追加:collation_server = utf8_bin
インスタンスの再起動
サーバ・レベルの検証ルール(collation_server)を変更すると、データベース検証ルール(collation_collation)はデフォルトでサーバ・レベルを継承します.
注意:
これは、再起動後に新規作成するライブラリにのみ適用され、既存のライブラリは影響を受けません.
同様に、ライブラリの検証ルールが変更されても、すでに存在するテーブルは変更の影響を受けません.
同じ理屈で既存の列と...

mysql> create database yutest0;
Query OK, 1 row affected (0.00 sec)
mysql> use yutest0;
Database changed
mysql> 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)

サーバレベルで対応する校正ルールの設定を行い、クエリーの大文字と小文字に敏感であることがわかります.
(2)サーバ起動時に有効なオプションに従って設定
mysqldを起動すると、使用する初期オプション設定に基づいてサーバ文字セットと校正ルールが決定されます.

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

2.2データベースレベル
MySQLでは、データベース文字セットとデータベース照合ルールを選択します.

character set Xとcollate Yが指定されている場合、文字セットXと校正規則Yが使用されます.

character set Xが指定され、collate Yが指定されていない場合、character set Xとcharacter set Xのデフォルトの校正規則が使用されます.

それ以外の場合、サーバ文字セットとサーバ校正規則が使用されます.

(1)プロファイルの変更/etc/my.cnf
2つのテストが行われました.
1)[mysqld]で追加:
collation_server = utf8_bin
collation_database = utf8_bin
2)[mysqld]で追加:
collation_database = utf8_bin
再起動インスタンスでは、両方のグループが正常に起動できません.エラーメッセージは次のとおりです.
見えるよcnfプロファイルではcollationの設定はサポートされていません.database変数.
 
(2)データベース作成時のデータベース検証ルールの設定

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)

データベース・レベルで対応する校正ルールの設定が行われ、クエリーの大文字と小文字が敏感であることがわかります.
 
2.3表級
MySQLでは、次のように表文字セットと校正規則を選択します.

character set Xとcollate Yが指定されている場合、character set Xとcollate Yが使用されます.

character set Xが指定され、collate Yが指定されていない場合、character set Xとcharacter set Xのデフォルトの校正規則が使用されます.

それ以外の場合、データベース文字セットとサーバ照合ルールが使用されます.

テーブルの作成時に、テーブル・レベルの検証ルールを設定します.

mysql> create database yutest2;
Query OK, 1 row affected (0.01 sec)
mysql> use yutest2;
Database changed

mysql> 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)

テーブル・レベルで対応する校正ルールの設定が行われ、クエリーの大文字と小文字が敏感であることがわかります.
 
2.4接続レベル
接続とは何かを考えます.サーバに接続するときに行われることです.クライアントは、クエリなどのSQL文を送信し、接続を介してサーバに送信します.サーバは、接続によって結果セットなどのクライアントに応答を送信します.クライアント接続では、システム変数によって解決できる、接続に関する文字セットと校正規則の問題が発生します.

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)

クエリーがクライアントを離れた後、クエリーでどの文字セットを使用しますか?

サーバ使用character_set_クライアント変数は、クライアントが送信するクエリで使用される文字セットとして使用されます.

サーバがクエリーを受信したら、どの文字セットに変換しますか?

変換時にサーバがcharacterを使用するset_接続とcollation_接続システム変数.クライアントから送信されたクエリーをcharacter_からset_クライアントシステム変数をcharacterに変換set_connection.

サーバが結果セットを送信するか、クライアントにエラーメッセージを返す前にどの文字セットに変換すればいいですか?

character_set_results変数は、サーバがクエリー結果をクライアントに返す文字セットを示します.カラム値やカラム名などの結果メタデータなどの結果データが含まれます.
3.データベース・テーブルの作成時に大文字と小文字を区別する方法
3.1 SQL文でのcollateの使用
collate句を使用すると、比較のためにデフォルトの校正規則を上書きできます.collateは、where、having、group by、order by、as、集約関数など、複数のSQL文で使用できます.

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オペレータ
binaryオペレータはcollate句の速記子です.binary’x‘等価は’x‘collatey,ここでyは文字セット’x‘二元校正規則の名前である.各文字セットには2元校正規則があります.たとえば、latin 1文字セットの二元校正規則はlatin 1_です.bin、したがって、カラムaが文字セットlatin 1の場合、次の2つの文は同じ効果を有します.

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)

記事の出典:http://blog.aliyun.com/538