MySQL に保存されているデータをクエリするときに大文字と小文字を区別する方法
- 王林転載
- 2023-05-31 16:26:313429ブラウズ
シーンの説明
今日、Hive テーブルを MySQL に同期した後、列の 1 つが唯一の列になりますが、MySQL でクエリを実行すると、 count と distinct count によってクエリされた値は異なります。この観点から見ると、重複したデータが存在します (Hive ではこれら 2 つの値が一致するため、重複するはずはありません)。 )、重複データをチェックアウトしたところ、大文字と小文字に問題があることがわかり、その後確認したところ、MySQL データベースでは、デフォルトで文字列フィールドに対するすべての関連操作が「大文字と小文字を区別しない」ことがわかりました。
これは、他の一般的なデータベースとは異なります。
解決策
1. クエリ時に大文字と小文字の区別を指定します
MySQL では、クエリ時に大文字と小文字の区別を指定できます。キーワード BINARY を使用する必要があります。クエリは次のとおりです。
SELECT * FROM student WHERE BINARY name = 'ZhangSan'; --或者 SELECT * FROM student WHERE name = BINARY 'ZhangSan';
MySQL データベースで上記の問題が見つかった場合、多くの場合、システムは一定期間稼働しており、方法 2 または 3 を使用するとコストが非常に高くなる可能性があります。 。
この方法を使用する最大の利点は、関数を迅速に実装できることです。
ただし、この方法には大きな制限もあります。インデックスが使用できないため、クエリのパフォーマンスが低下する可能性があります。
クエリ フィールドのインデックスも大文字と小文字を区別しない方法で確立されるため、その理由は簡単に理解できます。
データの量が大きくない場合、またはアプリケーションでのパフォーマンスの低下を気にしない場合を除き、選択できるのは方法 2 または 3 のみです。
2. テーブル構造の定義時にフィールドの大文字と小文字の区別を指定します
テーブルの作成時に特定のフィールドの大文字と小文字の区別を指定します。例は次のとおりです:
CREATE TABLE student ( ... name VARCHAR(64) BINARY NOT NULL, ... )
Keywords BINARY は、名前フィールドで大文字と小文字が区別されることを指定します。
このように、クエリで BINARY キーワードが使用されていない場合でも、クエリ ステートメントでは大文字と小文字が区別されます。
これに基づいて作成された名前関連のインデックスも大文字と小文字が区別されるため、インデックスを使用してパフォーマンスを向上させることができます。
MySQL では、ほとんどの文字列タイプで BINARY キーワードを使用して、このフィールドでのすべての操作が大文字と小文字を区別することを示すことができます。詳細については、MySQL 公式ドキュメントを参照してください。
このアプローチにより、設計者は各フィールドで大文字と小文字を区別するかどうかを正確に制御できます。多くのシステムの設計では、すべてのフィールド、さらにはほとんどのフィールドで大文字と小文字が区別されることが期待されます。 MySQL は、方法 3 を使用する必要があるソリューションも提供します。
3. ソート ルールを変更する (COLLATE)
MySQL で show create table <tablename></tablename> コマンドを実行すると、テーブル作成ステートメントを確認できます。
CREATE TABLE `table1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`field1` text COLLATE utf8_general_ci NOT NULL COMMENT '字段1',
`field2` varchar(128) COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '字段2',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_unicode_ci;ほとんどのフィールドは理解できますが、今日見ていきたいのは COLLATE キーワードです。この値に対応する utf8_general_ci は何を意味しますか?ここで私たちはそれを知りに来ました。
COLLATE は何に使用されますか?
Navicat を使用して開発されたものは、オプションに答えが示されているため、見覚えがあるかもしれません:
いわゆる utf8_general_ci , 実は、これは並べ替えに使われるルールです。 VARCHAR、CHAR、TEXT タイプのカラムなど、MySQL の文字タイプのカラムの場合、カラムのソートと比較の方法を MySQL に指示するために COLLATE タイプが必要です。つまり、COLLATE は ORDER BY ステートメント の順序に影響し、WHERE 条件 の大なり小なり記号によって除外された結果に影響し、 にも影響します。 DISTINCT 、 GROUP BY、 HAVING クエリ結果。さらに、MySQL がインデックスを構築するときに、インデックス カラムが文字型の場合、インデックスの作成にも影響しますが、この影響は認識できません。つまり、文字型の比較や並べ替えに関係する すべてが COLLATE に関連します。
文字列を含むさまざまな操作の中核には、文字ソート ルール (COLLATE、「チェック」とも訳される) が含まれている必要があります。 MySQL の文字列操作で大文字と小文字が区別されるかどうかは、基本的に、使用する COLLATE 照合順序によって異なります。
utf8_general_ci は、特定の COLLATE 値です。それぞれの特定の COLLATE は一意の文字セットに対応しており、この COLLATE に対応する文字セットは utf8 であることがわかります。大文字と小文字の区別の問題に関連するのは、サフィックス _ci です。MySQL の公式ドキュメントでは、これは Case Ignore の略語であり、大文字と小文字が区別されないことを意味すると説明されています。 MySQL は文字セット utf8 のデフォルト COLLATE として utf8_general_ci を指定しているため、これも記事の冒頭で述べた現象につながります。同時に、MySQL は他の COLLATE 値オプションも提供します。utf8_bin では大文字と小文字が区別されます。実際、大文字と小文字を区別するすべての COLLATE には _bin または _cs という接尾辞が付けられます。前者は Binary の略称で、後者は Case Sensitive です。 の略称。
さまざまな COLLATE
COLLATE の違いは、通常、データ エンコーディング (CHARSET) に関連しています。一般的に、各 CHARSET には複数の COLLATE がサポートされており、各 CHARSET は 1 つを指定します。COLLATE はデフォルト値です。たとえば、Latin1 エンコードのデフォルト COLLATE は latin1_swedish_ci、GBK エンコードのデフォルト COLLATE は gbk_chinese_ci、utf8mb4 エンコードのデフォルト値は utf8mb4_general_ci です。
ちなみに余談ですが、MySQL には utf8 と utf8mb4 という 2 つのエンコーディングがありますが、MySQL では utf8 は忘れて、常に utf8mb4 を使用してください。これは MySQL のレガシー問題です。MySQL の utf8 は、長さ 3 バイトまでの文字エンコーディングのみをサポートします。4 バイトを占める必要がある一部のテキストについては、MySQL の utf8 はサポートしません。utf8mb4 を使用する必要があります。
多くの COLLATE には _ci という単語が含まれます。これは Case Insensitive の省略形であり、大文字と小文字を区別しません。つまり "A" と "a" は、並べ替えと比較の際に同等に扱われます。 selection * from table1 where field1="a" field1 の値を "A" として選択することもできます。同時に、_cs サフィックスを持つ COLLATE では、Case Sensitive、つまり大文字と小文字が区別されます。
MySQL で showcollation コマンドを使用して、MySQL でサポートされているすべての COLLATE を表示します。 utf8mb4 を例に挙げると、このエンコーディングでサポートされるすべての COLLATE は次の図に示すとおりです。
写真では、多くの国の言語の並べ替えルールがわかります。中国で一般的に使用される 3 つは、utf8mb4_general_ci (デフォルト)、utf8mb4_unicode_ci、および utf8mb4_bin です。これら 3 つの違いを見てみましょう。
UTF8mb4_bin の比較方法は、すべての文字をバイナリ文字列として扱い、それらを最上位ビットから最下位ビットまで比較することです。したがって、明らかに大文字と小文字が区別されます。
実際には、中国語と英語の utf8mb4_unicode_ci と utf8mb4_general_ci の間に違いはありません。弊社が国内向けに開発したシステムですので、どれでもお選びいただけます。一部の西欧諸国の文字では、utf8mb4_general_ci よりも utf8mb4_unicode_ci の方がその言語の習慣に合っているというだけです。General は MySQL の古い標準です。たとえば、ドイツ語の文字 "ß" は、 utf8mb4_unicode_ci の 2 文字 "ss" と同等ですが (これはドイツの習慣に従っています)、 utf8mb4_general_ci では、文字 "s" に相当します。ただし、2 つのエンコーディング間の微妙な違いは、通常の開発では認識するのが困難です。テキスト フィールドを使用して直接並べ替えることはめったにありませんが、一歩下がって考えてみると、たとえ 1 つまたは 2 つの文字がずれていたとしても、それは本当にシステムに壊滅的な結果をもたらす可能性があるのでしょうか?インターネット上のさまざまな投稿や議論から判断すると、utf8mb4_unicode_ci の使用を推奨する人が増えていますが、デフォルト値を使用するシステムにはあまり抵抗がなく、大きな問題はないと考えています。結論: utf8mb4_unicode_ci を使用することをお勧めします。すでに utf8mb4_general_ci を使用しているシステムの場合、時間をかけて変更する必要はありません。
もう 1 つ注意すべき点は、MySQL 8.0 以降、MySQL のデフォルトの CHARSET が Latin1 ではなく utf8mb4 (参照リンク) に変更され、デフォルトの COLLATE も utf8mb4_0900_ai_ci に変更されたことです。 utf8mb4_0900_ai_ci は、通常、unicode をさらに細分したものです。0900 は、unicode 比較アルゴリズム (Unicode 照合アルゴリズムのバージョン) の番号を指し、ai は、e、egrave;、é、ê、ë などのアクセントを区別しない (発音は無関係) ことを意味します。 ; と同等に扱われます。関連参考リンク 1、関連参考リンク 2
COLLATE 設定レベルとその優先順位
MySQL データベースでは、library、table 、および 列 照合順序を 3 つのレベルで指定します。同時に指定した場合の優先関係は、カラム＞テーブル＞ライブラリとなります。
COLLATE は、インスタンス レベル、ライブラリ レベル、テーブル レベル、列レベル、#で設定できます。 ## SQL は を指定します。同時に指定した場合の優先関係は、SQL指定＞カラム＞テーブル＞ライブラリ＞インスタンスレベルとなります。
- インスタンス レベルの COLLATE 設定は、MySQL 構成ファイルまたは起動ディレクティブのcollation_connection システム変数です。
- ライブラリレベル設定の COLLATE ステートメントは次のとおりです。
CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
如果库级别没有设置 CHARSET 和 COLLATE，则库级别默认的 CHARSET 和 COLLATE 使用实例级别的设置。在 MySQL 8.0 以下版本中，你如果什么都不修改，默认的 CHARSET 是 Latin1，默认的 COLLATE 是 latin1_swedish_ci。从 MySQL 8.0 开始，默认的 CHARSET 已经改为了 utf8mb4，默认的 COLLATE 改为了 utf8mb4_0900_ai_ci。
表级别的 COLLATE 设置，则是在 CREATE TABLE 的时候加上相关设置语句，例如：
CREATE TABLE table_name ( …… ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT = '表注释';
如果表级别没有设置 CHARSET 和 COLLATE，则表级别会继承库级别的 CHARSET 与 COLLATE。
列级别的设置，则在 CREATE TABLE 中声明列的时候指定，例如
CREATE TABLE ( `field1` VARCHAR（64） CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '字段1', …… ) ……
如果列级别没有设置 CHARSET 和 COLATE，则列级别会继承表级别的 CHARSET 与 COLLATE。
最后，你也可以在写 SQL 查询的时候显示声明 COLLATE 来覆盖任何库表列的 COLLATE 设置，不太常用，了解即可：
SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1; SELECT field1, field2 FROM table1 ORDER BY field1 COLLATE utf8mb4_unicode_ci;
如果全都显示设置了，那么优先级顺序是 SQL 语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。
也就是说列上所指定的 COLLATE可以覆盖表上指定的 COLLATE，表上指定的 COLLATE 可以覆盖库级别的 COLLATE。如果没有指定，则继承下一级的设置。
即列上面没有指定 COLLATE，则该列的 COLLATE 和表上设置的一样。
以上がMySQL に保存されているデータをクエリするときに大文字と小文字を区別する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。